MySQL Stored Procedure

MySQL creates the "stored procedure" function to operate database information. You can use parameters, blocks, and statements to create a new procedure. The procedure requires a database table to use as a reference. You can use the command-line client interface or workbench interface for MySQL stored procedure.

Syntax of creating MySQL stored procedure

Syntax with parameter

The following syntax is used to create stored procedures in the database.

DELIMITER //
CREATE PROCEDURE storeprocedure_name(parameter mode / parameter list)
BEGIN 
MySQL statement;
END//
DELIMTER;

This query is used to create a procedure to retrieve database information. The delimiter is necessary for the stored procedure. The double slash represents the starting point of the procedure. The semicolon after delimiter represents the endpoint of the stored procedure.

Syntax without parameter

The following syntax is used to create a stored procedure without parameters in the database.

DELIMITER //
CREATE PROCEDURE storeprocedure_name( )
BEGIN 
MySQL statement;
END//
DELIMTER;

The stored procedure does not contain parameters every time. You can create a procedure without a parameter list and mode.

Prerequisite of MySQL stored procedure

• Create or use a database in the MySQL system interface for table operation.

mysql > Use database_names;

• If MySQL database does not exist in the system, then create a new database.

mysql > create database database_names;

• Create a table with columns, data types, and basic constraints of MySQL.

CREATE TABLE emp_informations (
    emp_id int,
    emp_name varchar(155),
    emp_department varchar(155),
    emp_position varchar(65),
    emp_salary int,
    emp_phone bigint,
  PRIMARY KEY(emp_id)
);

• Insert information in the table according to MySQL validations.

INSERT INTO emp_informations (
    emp_id , emp_name ,  emp_department , emp_position,
    emp_salary, emp_phone
) values
(1, "aasha", "elecetrical", "employee", 12000, 7878463212),
(2, "Mahesh", "elecetrical", "senior", 50000, 7775463213),
(3, "radha", "elecetrical", "employee", 15000, 7975463216),
(4, "sadhana", "elecetrical", "maintenance", 25000, 9875483212),
(5, "samir", "elecetrical", "HR", 35000, 8875463212),
(6, "rose", "elecetrical", "trainee", 10000, 7275463212);

• Show the given table using MySQL query.

Select * from emp_informations;

EXAMPLE of the MySQL stored procedure

Example 1: It is the basic MySQL stored procedure example and output.

The following example is used to create a stored procedure. This procedure comes without parameter mode and parameter list.

mysql> DELIMITER //
mysql> CREATE PROCEDURE info_employee( )
BEGIN 
Select * from emp_informations;
END//

This image shows how to create MySQL stored procedures in the command-line client interface. The row does not affect the procedure, and the statement displays the required data. You can call the stored procedure to get output.

Output

The following query is used to get the output of the stored procedure.

mysql> CALL info_employee( );
          -> //

The call function displays all required information of the table in the stored procedure. The statement shows table data with its columns and rows.

Example 2: The MySQL stored procedure with declaration and executable statements example and output.

Execute the below query to create MySQL stored procedure. This stored procedure works without a parameter list.

mysql> DELIMITER //
mysql> CREATE PROCEDURE info_employees( )
BEGIN 
Select * from emp_informations LIMIT 3;
Select max(emp_salary) AS Max_salary from emp_informations;
END//

Here, you can see two statements inside of the block (BEGIN and END). The call function displays both statements output in one query.

Output

mysql> call info_employees;
         -> //

The image shows two output tables as per the statement. The first table shows the first statement of the procedure. This table displays information with three rows limit. The second table represents the second max statement of the procedure. This query shows the highest salary row and column of the table.

Example 3: The MySQL stored procedure with declaration and executable statements example and output.

The below example is used to create MySQL stored procedure. This stored procedure works with the "IN" parameter list.

mysql> DELIMITER //
mysql> CREATE PROCEDURE info_employes( IN salary BIGINT )
BEGIN 
Select * from emp_informations WHERE emp_salary > salary;
END//

You can see a single statement with IN parameter in the block (BEGIN and END). The function works with the parameter mode and conditional operator for the salary column.

Output

mysql> call info_employes (25000);
         -> //

The above output image shows two rows of the table. This table's data shows information using salary greater than the 25000.