SQL functions are characterized by many general features:
SQL functions:
- 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 functions are more reliable than equivalent external functions.
- Support input parameters.
- SQL scalar functions return a scalar value.
- SQL table functions return a table result set.
- Support a simple, but powerful condition and error-handling model.
- Allow you to easily access the SQLSTATE and SQLCODE values as
special variables.
- Reside in the database and are automatically backed up and restored
as part of backup and restore operations.
- Can be invoked wherever expressions in an SQL statement are supported.
- Support nested functions calls to other SQL functions or functions
implemented in other languages.
- Support recursion (when dynamic SQL is used in compiled functions).
- Can be invoked from triggers.
- Many SQL statements can be included within SQL functions, however
there are exceptions. For the complete list of SQL statements that
can included and executed in SQL functions, see:SQL statements that can be executed in routines
SQL functions 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.
DB2 Version 9.7 for Linux, UNIX, and Windows