MySQL Stored Procedure

A procedure (often called a stored procedure) is a collection of pre-compiled SQL statements stored inside the database. It is a subroutine or a subprogram in the regular computing language. A procedure always contains a name, parameter lists, and SQL statements. We can invoke the procedures by using triggers, other procedures and applications such as Java, Python, PHP, etc. It was first introduced in MySQL version 5. Presently, it can be supported by almost all relational database systems.

If we consider the enterprise application, we always need to perform specific tasks such as database cleanup, processing payroll, and many more on the database regularly. Such tasks involve multiple SQL statements for executing each task. This process might easy if we group these tasks into a single task. We can fulfill this requirement in MySQL by creating a stored procedure in our database.

A procedure is called a recursive stored procedure when it calls itself. Most database systems support recursive stored procedures. But, it is not supported well in MySQL.

Stored Procedure Features

• Stored Procedure increases the performance of the applications. Once stored procedures are created, they are compiled and stored in the database.
• Stored procedure reduces the traffic between application and database server. Because the application has to send only the stored procedure's name and parameters instead of sending multiple SQL statements.
• Stored procedures are reusable and transparent to any applications.
• A procedure is always secure. The database administrator can grant permissions to applications that access stored procedures in the database without giving any permissions on the database tables.

How to create a procedure?

The following syntax is used for creating a stored procedure in MySQL. It can return one or more value through parameters or sometimes may not return at all. By default, a procedure is associated with our current database. But we can also create it into another database from the current database by specifying the name as database_name.procedure_name. See the complete syntax:

DELIMITER &&
CREATE PROCEDURE procedure_name [[IN | OUT | INOUT] parameter_name datatype [, parameter datatype]) ]  
BEGIN  
    Declaration_section  
    Executable_section  
END &&
DELIMITER ; 

Parameter Explanations

The procedure syntax has the following parameters:

MySQL procedure parameter has one of three modes:

IN parameter

It is the default mode. It takes a parameter as input, such as an attribute. When we define it, the calling program has to pass an argument to the stored procedure. This parameter's value is always protected.

OUT parameters

It is used to pass a parameter as output. Its value can be changed inside the stored procedure, and the changed (new) value is passed back to the calling program. It is noted that a procedure cannot access the OUT parameter's initial value when it starts.

INOUT parameters

It is a combination of IN and OUT parameters. It means the calling program can pass the argument, and the procedure can modify the INOUT parameter, and then passes the new value back to the calling program.

How to call a stored procedure?

We can use the CALL statement to call a stored procedure. This statement returns the values to its caller through its parameters (IN, OUT, or INOUT). The following syntax is used to call the stored procedure in MySQL:

CALL procedure_name ( parameter(s))

Example

Let us understand how to create a procedure in MySQL through example. First, we need to select a database that will store the newly created procedure. We can select the database using the below statement:

mysql> USE database_name;

Suppose this database has a table named student_info that contains the following data:

Procedure without Parameter

Suppose we want to display all records of this table whose marks are greater than 70 and count all the table rows. The following code creates a procedure named get_merit_students:

DELIMITER &&
CREATE PROCEDURE get_merit_student ()
BEGIN
	SELECT * FROM student_info WHERE marks > 70;
	SELECT COUNT(stud_code) AS Total_Student FROM student_info;  
END &&
DELIMITER ;

If this code executed successfully, we would get the below output:

Let us call the procedure to verify the output:

mysql> CALL get_merit_student();

It will give the output as follows:

Procedures with IN Parameter

In this procedure, we have used the IN parameter as 'var1' of integer type to accept a number from users. Its body part fetches the records from the table using a SELECT statement and returns only those rows that will be supplied by the user. It also returns the total number of rows of the specified table. See the procedure code:

DELIMITER &&
CREATE PROCEDURE get_student (IN var1 INT)
BEGIN
	SELECT * FROM student_info LIMIT var1;
	SELECT COUNT(stud_code) AS Total_Student FROM student_info;  
END &&
DELIMITER ;

After successful execution, we can call the procedure as follows:

mysql> CALL get_student(4);

We will get the below output:

Procedures with OUT Parameter

In this procedure, we have used the OUT parameter as the 'highestmark' of integer type. Its body part fetches the maximum marks from the table using a MAX() function. See the procedure code:

DELIMITER &&
CREATE PROCEDURE display_max_mark (OUT highestmark INT)
BEGIN
	SELECT MAX(marks) INTO highestmark FROM student_info; 
END &&
DELIMITER ;

This procedure's parameter will get the highest marks from the student_info table. When we call the procedure, the OUT parameter tells the database systems that its value goes out from the procedures. Now, we will pass its value to a session variable @M in the CALL statement as follows:

mysql> CALL display_max_mark(@M);
mysql> SELECT @M;

Here is the output:

Procedures with INOUT Parameter

In this procedure, we have used the INOUT parameter as 'var1' of integer type. Its body part first fetches the marks from the table with the specified id and then stores it into the same variable var1. The var1 first acts as the IN parameter and then OUT parameter. Therefore, we can call it the INOUT parameter mode. See the procedure code:

DELIMITER &&
CREATE PROCEDURE display_marks (INOUT var1 INT)
BEGIN
	SELECT marks INTO var1 FROM student_info WHERE stud_id = var1; 
END &&
DELIMITER ;

After successful execution, we can call the procedure as follows:

mysql> SET @M = '3';
mysql> CALL display_marks(@M);
mysql> SELECT @M;

We will get the below output:

How to show or list stored procedures in MySQL?

When we have several procedures in the MySQL server, it is very important to list all procedures. It is because sometimes the procedure names are the same in many databases. In that case, this query is very useful. We can list all procedure stored on the current MySQL server as follows:

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]

This statement displays all stored procedure names, including their characteristics. If we want to display procedures in a particular database, we need to use the WHERE clause. In case we want to list stored procedures with a specific word, we need to use the LIKE clause.

We can list all stored procedure in the MySQL mystudentsb database using the below statement:

mysql> SHOW PROCEDURE STATUS WHERE db = 'mystudentdb';

It will give the below output where we can see that the mystudentdb database contains four stored procedures:

How to delete/drop stored procedures in MySQL?

MySQL also allows a command to drop the procedure. When the procedure is dropped, it is removed from the database server also. The following statement is used to drop a stored procedure in MySQL:

DROP PROCEDURE [ IF EXISTS ] procedure_name;  

Suppose we want to remove the procedure named display_marks from the mystudentdb database. We can do this by first selecting the database and then use the syntax as follows to remove the procedure:

mysql> DROP PROCEDURE display_marks;

We can verify it by listing the procedure in the specified database using the SHOW PROCEDURE STATUS command. See the below output:

How to create a procedure in MySQL workbench?

We first launch the tool and log in with the username and password to create procedures in the MySQL Workbench. Now, we need to do the following steps for creating a stored procedure:

1. Go to the Navigation tab and click on the Schema menu where all the previously created databases available. Select your desired database (for example, employeedb). It will pop up the following options.

2. Right-click on the Stored Procedure, and we will get the default procedure code. See the below screen:

3. Complete the procedure code and click on the Apply button. In the next window, we will review the procedure code once again, and if no error was found, click on the Apply button.

4. After clicking on the Apply button, click on the Finish button for completion.

5. We can navigate to the schema menu again to verify this newly created procedure. It means first select your database and expand it to display its sub-menu. In the sub-menu, expanding the stored procedure option will show the newly created procedure. See the below image:

6. We can call the procedure by tapping on the red rectangle box or simply execute the CALL statement.

How to alter the procedure in MySQL?

MySQL does not allow any command to alter the procedure in MySQL. However, it provides a command that is used to change the characteristics of a stored procedure. This command may alter more than one change in the procedure but does not modify the stored procedure's parameters or body. If we want to make such changes, we must drop and re-create the procedure using the DROP PROCEDURE and CREATE PROCEDURE statement.

The below statement is used to change the characteristics of a procedure but not the actual procedure:

ALTER PROCEDURE procedure_name [characteristics ...]

characteristics: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

Suppose we want to add a comment to the existing procedure. In such a case, we can use the ALTER statement as follows to accomplish this task:

mysql> ALTER PROCEDURE get_merit_student
COMMENT 'It displays all records';

After executing this statement, we can verify it by using the below statement:

mysql> SHOW CREATE PROCEDURE get_merit_student \G;

It will display the below output where we can see that the comment is added successfully.

It is to note that we can alter the body of the stored procedure in MySQL using the workbench tool. So open this tool, navigate to the schema menu, and expand the database that contains stored procedures. Now, select your procedure, right-click on it and choose ALTER STORED PROCEDURE option. See the below screen:

After clicking this option, we will get a window that contains a procedure code. See the below screen that contains procedure code to display all employee:

Now, we will modify this code. Suppose we want to display only male employees. To do this, we can change this code from the below code and click on the Apply button:

SELECT * FROM employee WHERE gender = 'M';

In this window, we will review the procedure code once again, and if no error was found, click on the Apply -> Apply -> Finish button to complete the process.

Drawbacks of Using Stored Procedures

• If we use stored procedures, the memory uses of every connection that uses those stored procedures will increase substantially. Also, if we overuse many logical applications inside stored procedures, the CPU usage will increase. It is because the database server is not well designed for logical operations.
• Stored procedure's constructs are not designed to develop complex and flexible business logic.
• It is difficult to debug stored procedures. Only a few database management systems allow us to debug stored procedures. Unfortunately, MySQL does not provide facilities for debugging stored procedures.
• It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required a specialized skill set that not all application developers possess. It may lead to problems in both application development and maintenance phases.