In this tutorial, we are going to discuss the stored procedures in MySQL. This covers all the basics of the stored procedure along with How to Create and Execute Stored Procedure in MySQL with examples.
- Introduction to MySQL Stored Procedure
- Benefits of a stored procedure
- How to create a Stored Procedure?
- Syntax for Creating MySQL Stored procedure
- How to call Stored Procedure?
- MySQL Stored Procedures Advantages and Disadvantages
In our programming, if any code repeatedly required then we can define that code inside a method and we call that method multiple times based on our requirement. Hence the method is the best reusable component in our programming.
Likewise, in database programming, if any group of SQL statements is repeatedly required then we can determine that SQL statement in a single group and we can execute that group repeatedly as per our requirement.
This group of SQL statement that performs a particular task is nothing but a Stored Procedure. Thus, Stored Procedure is the best reusable component at the database level.
These Procedures stored in the database permanently for future purposes and hence it is termed as Stored Procedure. Normally, the Stored Procedure is designed by Database Admin(DBA). Every Database has its own language to create the Stored Procedure.
- Oracle has → PL/SQL
- MySQL has → a Stored Procedure language
- MS SQL Server has → Transact SQL(TSQL)
Following are the benefits of a stored procedure:
- Reduce the Network Traffic
- Easy to maintain
We use the CREATE PROCEDURE procedure_name() statement to create a Stored Procedure. you can give any name to the stored procedure. Similarly to the method Stored Procedure has its own parameter. In parenthesis, you can write IN and OUT parameters.
The syntax to create a MySQL Stored procedure is as follows:
DELIMITER && CREATE PROCEDURE procedure_name [[IN | OUT | INOUT] parameter_name datatype [, parameter datatype]) ] BEGIN Declaration_section Executable_section END && DELIMITER ;
The following is the explanation for the parameters used in the above syntax:
|procedure_name||It represents the name of the stored procedure.|
|parameter||It represents the number of parameters. It can be one or more than one.|
|Declaration_section||It represents the declarations of all variables.|
|Executable_section||It represents the code for the function execution.|
Thus, stored procedure methods have their own parameters. The stored procedure parameter has 3 types or modes.
- IN parameter: IN parameter is used to provide input values.
- OUT parameter: The OUT parameter is used to collect output values.
- INOUT parameter: It is used to provide input and to collect output values.
Here, we are writing a Stored Procedure to get all the records from the training table. Creating a Stored Procedure, you must apply the following steps:
- Select the database.
- Right, click on the Stored Procedure.
- Click on create Stored Procedure.
Here it will open a new window, where you will give a procedure name. After giving the Stored Procedure name, you can write a SQL statement between BEGIN and END. Then click on apply.
It will open this window. Before we apply to the database, we can review the script one time and click on apply.
It will open a new window. Click on finish.
After clicking on the finish, you can see getAllRecord() Stored Procedure is stored in the Stored Procedure catalog.
You use the CALL statement to call the Stored Procedure eg: to call the getAllRecords() Stored Procedure, you use the following Statement.
If you execute the above statement, you will get all the records from the training table.
The following table illustrates the advantages and disadvantages of using MySQL Stored Procedure:
|Network Traffic Reduction||Resource Usage|
|Improved Security||No Portability|
|Centralized Business Logic||Troubleshooting and Testing|