Develop the first MySQL stored procedure

Advertisement
Hello friends i 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.

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 products table.
First of all you need to launch mysql client and type the following commands:
DELIMITER //
 CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT *  FROM products;
   END //
 DELIMITER ;

how to Develop the first MySQL stored procedure,the first MySQL stored procedure,develop the first MySQL stored procedure,first MySQL stored procedure,MySQL stored procedure,
Let’s examine the stored procedure in greater detail:

  • The first command is DELIMITER //, which is not related to the stored procedure syntax. The DELIMITER statement 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 mysql tool to interpret each statement at a time.  Following the END keyword, 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 PROCEDURE statement to create a new stored procedure. We specify the name of stored procedure after the CREATE PROCEDURE statement. 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 BEGIN and END is 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 products table.
Next time we'll learn about how to create store procedure using MySQL Workbench and phpmyAdmin.
Embed this post on your website Embed