A stored procedure, by definition, is a segment of declarative SQL code which is stored in the database catalog and can be invoked later by a program, a trigger or even a stored procedure.A stored procedure, which calls itself, is recursive stored procedure. Almost RDMBS supports recursive stored procedure but MySQL does not support it well. Check your version of MySQL before using recursive stored procedure.
Advantages
Stored procedure increases performance of application. Once created, stored procedure is compiled and stored in the database catalog. It runs faster than uncompiled SQL commands which are sent from application.
Stored procedure reduced the traffic between application and database server because instead of sending multiple uncompiled long SQL commands statement, application only has to send the stored procedure name and get the result back.
Writing Stored Procedures
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 is used to change the standard delimiter (semicolon) to another, in this case the delimiter is changed to //, so you can have multiple SQL statements insidestored procedure which can separate 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 newstored 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.
