Using SQL stored procedures, you can build large and complex database applications.
An SQL stored procedure can contain the following:
- SQL control statements.
- Condition handling statements.
- Multiple input and output parameters.
- Local variables and cursors.
- SQL DDL, DCL, and DML statements, including dynamic SQL, with a few exceptions.
Dynamic SQL invokes an SQL statement by creating and submitting it at runtime from within a stored procedure.
Applications based on SQL stored procedures do the following:
- Reduce network traffic in the client-server environment (because stored procedures reside and execute on the server).
- Encapsulate and enforce business rules on the server, contributing to improved application maintenance.
- Provide better transaction control.
- Provide better security, for these reasons:
- The data access clause of an SQL stored procedure can restrict access to the database.
- You can grant users access to the procedures rather than the data tables.
- Provide an exception handling mechanism to handle the runtime conditions generated by the application.
- Enable you to run all the SQL and SQL control statements embedded in an SQL stored procedure by submitting one CALL statement. Nested CALL statements further extend the versatility.