MySQL Stored Procedures first step

Saturday, August 25, 2012

print this page
send email





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:
1DELIMITER //
2CREATE PROCEDURE GetAllProducts()
3  BEGIN
4  SELECT *  FROM products;
5  END //
6DELIMITER ;
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:
1CALL STORED_PROCEDURE_NAME()
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:
1CALL GetAllProducts();
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.

1 comments:

  1. Thank you for this wonderful ideas you have discuss to your blog, I've learn from it....
    Web Development Company in Chandigarh

    ReplyDelete