How to create and execute Stored Procedure in MySQL?

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.

Similarly, in the database programming, if any group of SQL statement is repeatedly required then we can define that SQL statement in a single group and we can call that group repeatedly based on our requirement.

This group of SQL statement that performs a particular task is nothing but a Stored Procedure. Hence Stored Procedure is the best reusable component at the database level. Hence Stored Procedure is a group of SQL statement that performs a particular task.



These Procedure stored in database permanently for future purpose and hence the name Stored Procedure. Usually, Stored Procedure is created by Database Admin(DBA). Every Database has its own language to create the Stored Procedure.

Oracle has -> PL/SQL
MySQL has -> Stored Procedure language
MS SQL Server has -> Transact SQL(TSQL)

Similarly to method stored procedure has its own parameters. Stored Procedure has 3 types of parameters.

1) IN parameter : IN parameter is used to provide input values.
2) OUT parameter: OUT parameter is used to collect output values.
3) INOUT parameter: It is used to provide input and to collect output values.

Read: How to use a Prepared Statement in Java?

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 method Stored Procedure has its own parameter. In parenthesis, you can write IN and OUT parameter.

I am going to write a Stored Procedure to get all the record 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.
Stored Procedure in MySQL


Here it will open a new window, here 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.

Stored Procedure in MySQL


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 
Stored Procedure in MySQL


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


Stored Procedure in MySQL



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 record from the training table.




You may also like:
JDBC Driver in Java with Example
Java MySQL Connectivity with Example
Java prepared statement with an example

How to create and execute Stored Procedure in MySQL? How to create and execute Stored Procedure in MySQL? Reviewed by Prashant Srivastava on September 09, 2018 Rating: 5

No comments:

Powered by Blogger.