MySQL Stored Procedure Example

MySQL Stored Procedure Example

DELIMITER //

CREATE PROCEDURE GetAllCustomers()
BEGIN
SELECT * FROM customers;
END //

DELIMITER ;


SELECT name FROM mysql.proc; # SHOW NAME OF  STORED PROCEDURES

call GetAllCustomers();





Definition of stored procedure

A stored procedure is a segment of declarative SQL code, which is stored in the database catalog. A stored procedure can be invoked by a program, a trigger or even another 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. You should check your version of MySQL database before implementing recursive stored procedures.
Stored Procedure in MySQL

MySQL is known as the most popular open source RDBMS which is widely used by both community and enterprise. However during the first decade of its existence, it did not support stored procedure, trigger, event…etc. Since MySQL version 5.0, those features have been added to MySQL database engine to allow MySQL to be more flexible and powerful.
Before starting the tutorial series about stored procedure, it is required that you have MySQL version  5.x+  installed in your computer or server.
Stored Procedures 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 reduces the traffic between application and database server because instead of sending multiple uncompiled lengthy SQL commands statements, the application only has to send the stored procedure's name and get the data back.

Stored procedure is reusable and transparent to any application which wants to use it. Stored procedure exposes the database interface to all applications so developers don't have to program the functions which are already supported in stored procedure in all external applications.
Stored procedure is secured. Database administrator can grant the access right to application which wants to access stored procedures in database catalog without granting any permission on the underlying database tables.
Besides those advantages, stored procedure has its own disadvantages which you should be aware of before deciding using it.
Stored Procedures Disadvantages

Stored procedures make the database server high load in both memory and processors. Instead of being focused on the storing and retrieving data, you could be asking the database server to perform a number of logical operations or a complex of business logic which is not the well designed in database server.
Stored procedure only contains declarative SQL so it is very difficult to write a procedure with complexity of business logic like other languages in application layer such as Java, C#, C++…
Stored procedure is difficult to debug. You cannot debug stored procedure in almost RDMBSs and in MySQL also.

There are some workarounds on this problem but it still not easy enough to do so.
Store procedure is not easy to write and maintain. Writing and maintaining stored procedure is usually required specialized skill set that not all developers possess. This may introduced problems in both application development and maintain phase.

Stored procedure has it own advantages and disadvantages. Therefore when developing application, you should balance between them to choose whether to use stored procedure or not. In the following tutorials, we will guide you how to leverage stored procedure in your database programming tasks with a couple of practical examples.