Features of SQL functions

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 previously. When implemented according to best practices, they can play an essential role in database architecture, database application design, and in database system performance.