Unit 5: Introduction to Stored Procedures (PDF)

Introduction to stored procedure The MySQL database supports stored procedures. A stored procedure is a subroutine stored in the database catalog. Applications can call and execute the stored procedure. The CALL SQL statement is used to execute a stored procedure Stored procedure avoids duplicatin...

Introduction to stored procedure The MySQL database supports stored procedures. A stored procedure is a subroutine stored in the database catalog. Applications can call and execute the stored procedure. The CALL SQL statement is used to execute a stored procedure Stored procedure avoids duplicating database code, saving time and effort when you make updates due to schema changes, tune the performance of queries, or add new database operations for logging, security, and so on Create Procedure CREATE PROCEDURE: statement that create different kinds of stored routine Example of stored procedure DELIMITER // CREATE PROCEDURE country_hos (IN con CHAR(20)) BEGIN SELECT Name, HeadOfState FROM Country WHERE Continent = con; END // DELIMITER ; Call Stored Procedures Test that the stored procedure works as expected by typing the following into the mysql command interpreter: CALL country_hos('Europe'); Advantages of stored procedure Stored procedures advantages Typically stored procedures help increase the performance of the applications. Once created, stored procedures are compiled and stored in the database. However MySQL implements the stored procedures slightly different. MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL puts it to a cache. And MySQL maintains its own stored procedure cache for every single connection. If an application uses a stored procedure multiple times in a single connection, the compiled version is used, otherwise the stored procedure works like a query. Stored procedures helps reduce the traffic between application and database server because instead of sending multiple lengthy SQL statements, the application has to send only name and parameters of the stored procedure. Advantages of stored procedure Stored procedures are reusable and transparent to any applications. Stored procedures expose the database interface to all applications so that developers don’t have to develop functions that are already supported in stored procedures. Stored procedures are secure. Database administrator can grant appropriate permissions to applications that access stored procedures in the database without giving any permission on the underlying database tables. Disadvantages of stored procedure If you use a lot of 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 store procedures, the CPU usage will also increase because database server is not well-designed for logical operations. A constructs of stored procedures make it more difficult to develop stored procedures that have complicated business logic. Disadvantages of stored procedure It is difficult to debug stored procedures. Only few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide facilities for debugging stored procedures. It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required specialized skill set that not all application developers possess. This may lead to problems in both application development and maintenance phases. Difference between Stored Procedure and Function 1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). 2. Functions can have only input parameters for it whereas Procedures can have input/output parameters. 3. Functions can be called from Procedure whereas Procedures cannot be called from Function 4. To execute stored procedure Call statement is used where as to execute stored function SELECT statement is used.