Summary: in this tutorial, we will show you step by step how to develop the first MySQL stored procedure by using CREATE PROCEDURE statement. In addition, we will show you how to call the stored procedures from SQL statements.
Writing the first MySQL stored procedure
We are going to develop a simple stored procedure named
GetAllProducts() to help you get familiar with the syntax. The
GetAllProducts() stored procedure selects all products from the
mysql client tool and type the following commands:
DELIMITER // CREATE PROCEDURE GetAllProducts() BEGIN SELECT * FROM products; END // DELIMITER ;
Let’s examine the stored procedure in greater detail:
- The first command is
DELIMITER //, which is not related to the stored procedure syntax. The
DELIMITERstatement changes the standard delimiter which is semicolon (
;) to another. In this case, the delimiter is changed from the semicolon(
;) to double-slashes
//. Why do we have to change the delimiter? Because we want to pass the stored procedure to the server as a whole rather than letting
mysqltool to interpret each statement at a time. Following the
ENDkeyword, we use the delimiter
//to indicate the end of the stored procedure. The last command (
DELIMITER;) changes the delimiter back to the standard one.
- We use the
CREATE PROCEDUREstatement to create a new stored procedure. We specify the name of stored procedure after the
CREATE PROCEDUREstatement. In this case, the name of the stored procedure is
GetAllProducts. We put the parentheses after the name of the stored procedure.
- The section between
ENDis called the body of the stored procedure. You put the declarative SQL statements in the body to handle business logic. In this stored procedure, we use a simple SELECT statement to query data from the
It’s kind of tedious to write the stored procedure in
mysql client tool, especially when the stored procedure is complex. Most of the GUI tools for MySQL allow you to create new stored procedures via an intuitive interface.
For example, in MySQL Workbench, you can create a new stored procedure as follows:
We have created a new stored procedure. Now, it’s time to learn how to use it.
Calling stored procedures
In order to call a stored procedure, you use the following SQL command:
You use the
CALL statement to call a stored procedure e.g., to call the
GetAllProducts stored procedure, you use the following statement:
If you execute the statement above, you will get all products in the
In this tutorial, you have learned how to write a simple stored procedure by using the
CREATE PROCEDURE statement and call it by using the