Introduction to Stored Procedure
A stored procedure is a segment of declarative pre-compiled SQL statements stored inside the database catalog.
A stored procedure can be invoked by the triggers, other stored procedures and applications such as PHP, Java, Python.
A stored procedure that calls itself is called as a recursive stored procedure. Most database management systems support recursive stored procedures. However, MySQL does not support it very well.
A stored procedure can contain business logic, which is one of the key aspects that distinguishes stored procedures from views.
A stored procedures can accept parameters, and you can set variables, write IF statements etc. within a stored procedure.
MySQL stored procedures advantages
- Stored Procedure improves the performance of the applications. Stored procedure program statements is stored at database catalog.
- Stored Procedure reduces the network traffic between applications and database server. Because the application has to send only the procedure name instead of sending a multiple MySQL statement to the database.
- Stored Procedures are reusable. It can be used by application multiple times, or different modules of the application can use the same procedure. No need to write separate MySQL statement. Just call the existing procedure name.
- Stored Procedure maintain security. The database administrator can grant permissions to the users to execute the stored procedure without giving permission to the tables used in the stored procedure. This helps to prevent the database from SQL injection.
MySQL stored procedures disadvantages
Despite many advantages, stored procedures also have some disadvantages.
- Resource usage: If you use a number of stored procedures, the memory usage of each connection will increase. Also, it leads to the increase in the CPU usage because MySQL is not well-designed for logical operations.
- It is difficult to debug the stored procedures. MySQL does not provide any facility to debug stored procedure. Where as other enterprise database products like Oracle and SQL Server provides facility to debug Stored Procedure.
- It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required a specialized skill set.