MySQL Stored Procedure Beginners Tutorial #1 - Introduction, Features, Drawbacks in detail
WHAT IS STORED PROCEDURE IN MYSQL ? ========================================= A stored procedure is a segment of declarative SQL statements stored inside the database catalog. A stored procedure can be invoked by triggers, other stored procedures, and applications such as Java, Python, PHP. Logic, functional and domain-specific languages = declarative paradigm A stored procedure that calls itself is known as a recursive stored procedure. Most database management systems support recursive stored procedures. However, MySQL does not support it very well. FEATURES OF USING STORED PROCEDURES: ========================================= 1. stored procedures help increase the performance of the applications. Once created, stored procedures are compiled and stored in the database. 2. Stored procedures help reduce the traffic between application and database server because instead of sending multiple lengthy SQL statements, the application has to send only the name and parameters of the stored procedure. 3. Stored procedures are reusable and transparent to any applications. 4. Stored procedures are secure. The database administrator can grant appropriate permissions to applications that access stored procedures in the database without giving any permissions on the underlying database tables.DRAWBACKS OF USING STORED PROCDURES ========================================== 1. If you use many stored procedures, the memory usage of every connection that is using those stored procedures will increase substantially. In addition, if you overuse a large number of logical operations inside stored procedures, the CPU usage will increase because the database server is not well-designed for logical operations. 2. Stored procedure’s constructs are not designed for developing complex and flexible business logic. 3. It is difficult to debug stored procedures. Only a few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide facilities for debugging stored procedures. 4. It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required a specialized skill set that not all application developers possess. This may lead to problems in both application development and maintenance phases.