How to create and execute Stored Procedure in MySQL

How to create and execute Stored Procedure in MySQL? | Syntax, Benefits, Types, Examples of MySQL CREATE PROCEDURE Statement

by

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

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)

Benefits of a stored procedure

Following are the benefits of a stored procedure:

  • Reduce the Network Traffic
  • Easy to maintain
  • Secure

Do Refer: Java MySQL Database Connectivity with Example

How to create a Stored Procedure?

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.

Syntax for Creating MySQL Stored procedure

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 ;

Parameter Explanations

The following is the explanation for the parameters used in the above syntax:

Parameter Name Descriptions
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.

  1. IN parameter: IN parameter is used to provide input values.
  2. OUT parameter: The OUT parameter is used to collect output values.
  3. 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.

How to create and execute Stored Procedure in MySQL 1

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.

How to create and execute Stored Procedure in MySQL 2

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.

How to create and execute Stored Procedure in MySQL 3

After clicking on the finish, you can see getAllRecord() Stored Procedure is stored in the Stored Procedure catalog.

How to create and execute Stored Procedure in MySQL 4

How to call Stored Procedure?

You use the CALL statement to call the Stored Procedure eg: to call the getAllRecords() Stored Procedure, you use the following Statement.

CALL getAllRecords();

If you execute the above statement, you will get all the records from the training table.

How to create and execute Stored Procedure in MySQL 5

MySQL Stored Procedures Advantages and Disadvantages

The following table illustrates the advantages and disadvantages of using MySQL Stored Procedure:

Advantages Disadvantages
Network Traffic Reduction Resource Usage
Improved Security No Portability
Centralized Business Logic Troubleshooting and Testing