Determining when to use SQL procedures or SQL functions

SQL Procedural Language (SQL PL) is a language extension of SQL that consists of statements and language elements that you can use to implement procedural logic in SQL statements. You can implement logic with SQL PL by using SQL procedures or SQL functions.

Procedure

Choose to implement an SQL function if:

  • Functional requirements can be met by an SQL function and you don't anticipate later requiring the features provided by an SQL procedure.

  • Performance is a priority and the logic to be contained in the routine consists only of queries or returns only a single result set.

    When they only contain queries or the return of a single result set an SQL function performs better than a logically equivalent SQL procedure, because of how SQL functions are compiled.

    In SQL procedures, static queries in the form of SELECT statements and full-select statements are compiled individually, such that each query becomes a section of a query access plan in a package when the SQL procedure is created. There is no recompilation of this package until the SQL procedure is recreated or the package is rebound to the database. This means that the performance of the queries is determined based on information available to the database manager at a time earlier than the SQL procedure execution time and hence might not be optimal. Also with an SQL procedure there is also a small overhead entailed when the database manager transfers between executing procedural flow statements and SQL statements that query or modify data.

    SQL functions however are expanded and compiled within the SQL statement that references them which means that they are compiled each time that SQL statement is compiled which depending on the statement might happen dynamically. Because SQL functions are not directly associated with a package, there is no overhead entailed when the database manager transfers between executing procedural flow statements and SQL statements that query or modify data.

Choose to implement an SQL procedure if:

  • SQL PL features that are only supported in SQL procedures are required. This includes: output parameter support, use of a cursor, the ability to return multiple result sets to the caller, full condition handling support, transaction and savepoint control, or other features.
  • You want to execute non-SQL PL statements that can only be executed in SQL procedures.
  • You want to modify data and modifying data is not supported for the type of function you need.

Results

Although it isn't always obvious, you can often easily re-write SQL procedures as SQL functions that perform equivalent logic. This can be an effective way to maximize performance when every little performance improvement counts.