Basics of MySQL Stored Procedures

In this articcle we discuss about Basics of MySQL Stored Procedures.

Basics of MySQL Stored Procedures

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.

Basics of MySQL Stored Procedures

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.

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:

Parameter NameDescriptions
procedure_nameIt represents the name of the stored procedure.
parameterIt represents the number of parameters. It can be one or more than one.
Declaration_sectionIt represents the declarations of all variables.
Executable_sectionIt represents the code for the function execution.

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:

The Basics of MySQL Stored Procedures

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:

The Basics of MySQL Stored Procedures

Let us call the procedure to verify the output:

mysql> CALL get_merit_student();  


It will give the output as follows

The Basics of MySQL Stored Procedures

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.

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:

The Basics of MySQL Stored Procedures

Procedures with OUT Parameter

In this procedure, we have used the OUT parameter as the ‘highest mark’ of the 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;  
The Basics of MySQL Stored Procedures

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;  
The Basics of MySQL Stored Procedures

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 can list all stored procedure in the MySQL mystudentsb database using the below statement:

mysql> SHOW PROCEDURE STATUS WHERE db = 'mystudentdb';  
The Basics of MySQL 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;  
The Basics of MySQL Stored Procedures

Also Check out: Understanding about SQL Joins, All You Need To Know About SQL Joins

Related Posts

3 thoughts on “Basics of MySQL Stored Procedures

  1. After looking into a number of the blog articles on your blog, I really appreciate your way of blogging. I book-marked it to my bookmark website list and will be checking back in the near future. Please check out my website too and let me know your opinion.

  2. A fascinating discussion is worth comment. I do think that you ought to write more on this topic, it might not be a taboo subject but generally folks don’t discuss these topics. To the next! All the best!!

  3. Aw, this was a very good post. Finding the time and actual effort to generate a
    great article… but what can I say… I procrastinate a
    lot and don’t manage to get anything done.

Leave a Reply

Your email address will not be published. Required fields are marked *

How to Protect yourself from CovidVaccination saves lives at every stage of life
  • Clean your home regularly, particularly frequently touched surfaces like
  • Stay home stay safe. Follow the Golden Rule. Wash your hands frequently with soap and water or use alchohol based hand-rub.
  • Stay home stay safe. Stay physically fit. Exercise regulary. Eat a nutritious diet. Don’t smoke.
  • Stay home stay safe. If you show symptoms of COVID-19, self isolate yourself, wear a mask around others and seek medical advice