Summary: In this tutorial, you will write the first simple stored procedure and invoke it from command line of MySQL.
Writing the first stored procedure
The first stored procedure is very simple. It retrieves all products from products table. First let’s take a look at the stored procedure source code below:
Let's examine the stored procedure above in a great details:
- The first command you see is DELIMITER //. This command is not related to the stored procedure. DELIMITER statement in MySQL is used to change the standard delimiter (semicolon) to another. In this case, the delimiter is changed from semicolon(;) to //, so you can have multiple SQL statements inside stored procedure which can be separated by the semicolon. After the END keyword we use delimiter // to show the end of the stored procedure. The last command changes the delimiter back to the standard one (semicolon).
- In order to create a new stored procedure you use CREATE PROCEDURE statement. After the CREATE PROCEDURE statement you can specify the name of stored procedure. In this case, the stored procedure name is GetAllProducts.
- Everything inside a pair of keyword BEGIN and END is called store procedure's body. You can write declarative SQL code in the stored procedure's body.
Now we have created a new stored procedure. It is time to know how to invoke in command line of MySQL.
Calling the stored procedure
In order to invoke a stored procedure, we use the following SQL command:
First you use keyword CALL followed by the stored procedure name and a pair of parenthesis. To invoke the stored procedure GetAllProducts, we use the following command:
If you run the command above you will get all products in the products database table.
In this tutorial, you’ve learned how to change the delimiter by using DELIMITER statement. It allows you to type multiple SQL statements inside a stored procedure. You’ve also learned how to write a simple stored procedure by using CREATE PROCEDURE statement and invoke it from command line using CALL statement.