SQL procedures are characterized by many features. SQL procedures:
- Can contain SQL Procedural Language statements and features which
support the implementation of control-flow logic around traditional
static and dynamic SQL statements.
- Are supported in the entire DB2® family
brand of database products in which many if not all of the features
supported in DB2 Version 9 are
supported.
- Are easy to implement, because they use a simple high-level, strongly
typed language.
- SQL procedures are more reliable than equivalent external procedures.
- Adhere to the SQL99 ANSI/ISO/IEC SQL standard.
- Support input, output, and input-output parameter passing modes.
- Support a simple, but powerful condition and error-handling model.
- Allow you to return multiple result sets to the caller or to a
client application.
- Allow you to easily access the SQLSTATE and SQLCODE values as
special variables.
- Reside in the database and are automatically backed up and restored.
- Can be invoked wherever the CALL statement is supported.
- Support nested procedure calls to other SQL procedures or procedures
implemented in other languages.
- Support recursion.
- Support savepoints and the rolling back of executed SQL statements
to provide extensive transaction control.
- Can be called from triggers.
SQL procedures provide extensive support not limited to what is
listed above. When implemented according to best practices, they
can play an essential role in database architecture, database application
design, and in database system performance.