» MySQL Stored Procedure » Getting Started with MySQL Stored Procedures

Getting Started with MySQL Stored Procedures

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:

 DELIMITER //
 CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT *  FROM products;
   END //
 DELIMITER ;

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 it is GetAllProducts.

The body part of the stored procedure started with between BEGIN and END block. You can write declarative SQL code here. We can analysis more details of each part later. Now we have created a new stored procedure, but we also need to know however to invoke it in program or in command line of MySQL.

Calling the stored procedure

In order to invoke a stored procedure we use the following SQL command:

CALL STORED_PROCEDURE_NAME()

For example, we can call the stored procedure we have created like this

CALL GetAllProducts();

We get all products in the products database table.

In this tutorial, you’ve learn how to change the delimiter by using DELIMITER statement. It allows you to type multiple SQL statements inside stored procedure. You’ve also learn how to write a simple stored procedure by using CREATE PROCEDURE statement and call it from command line by using CALL statement.

Related Tutorials
Search