Using SQL stored procedures, you can build large and complex database applications. In addition to a set of SQL control statements and condition handling statements, an SQL stored procedure can contain the following:
- Multiple input and output parameters.
- Local variables and cursors.
- SQL DDL, DCL, and DML statements, including dynamic SQL, with a few exceptions.
Dynamic SQL is a method of invoking an SQL statement by creating and submitting it at runtime from within a stored procedure.
Applications based on SQL stored procedures provide the following benefits. They:
- Reduce network traffic in the client-server environment because stored procedures reside and execute on the server.
- Allow encapsulation and enforcement of business rules on the server, contributing to improved application maintenance.
- Provide better transaction control.
- Provide better security. The data access clause of an SQL stored procedure can restrict access to the database.
- Provide better security by granting the user access to the procedures rather than to the data tables.
- Provide an exception handling mechanism to handle the runtime conditions generated by the application.
- Allow all the SQL and SQL control statements embedded in an SQL stored procedure to be executed by submitting one CALL statement. Nested CALL statements further extend the versatility.