Introduction to Stored Procedure in MySQL
Definition of stored procedure
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
Stored procedure in MySQL
MySQL certainly is the most open source RDBMS which is widely used by both community and enterprise but during the first decade of its existence, it did not support stored procedure, trigger, event…Since MySQL version 5.0, those features has been added to MySQL database engine to allow MySQL more flexible and powerful. Therefore before start the tutorial series about Stored procedure, it is required that you have MySQL version > 5.x installed in your computer.
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 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.
Stored procedure is reusable and transparent to any application which wants to use it. Stored procedure exposes the database interface to all applications so developer doesn’t have to program the functions which are already supported in stored procedure in all programs.
Stored procedure is secured. Database administrator can grant the right to application which to access which stored procedures in database catalog without granting any permission on the underlying database table.
Beside those advantages, stored procedure still has its own disadvantages which are bellow
Stored procedures disadvantages
Stored procedure make the database server high load in both memory for 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 role of it.
Stored procedure only contains declarative SQL so it is very difficult to write a procedure with complexity of business like other languages in application layer such as Java, C#, C++…
You cannot debug stored procedure in almost RDMBSs and in MySQL also. There are some workarounds on this problem but it still not good enough to do so.
Writing and maintain stored procedure usually required specialized skill set that not all developers possess. This introduced the problem in both application development and maintain phrase.
Stored procedure has it own advantages and disadvantages as mentioned above. So when developing application you should balance between them to choose whether to use stored procedure or not. The following tutorial we will guide you how to leverage stored procedure in your database programming task with a couple of practical examples.