Functions, stored procedures, routines... FAQ

During the last workshop, the following question was raised: what's the difference between a function and a stored procedure?

Answer: Basically both execute a set of actions; the difference is that a stored procedure must be invoked with a CALL statement, and a cursor needs to be opened to return a result set. A function, on the other hand always returns a value, such as a scalar, a row, or a table; you cannot use a CALL statement to invoke it, but it can be invoked in an expression of a SELECT statement.

In DB2®, user-defined functions can include:

• Functions that you define from scratch.
• Functions in the SYSFUN schema. Examples include mathematical functions such as SIN, COS, and TAN; scientific functions such as RADIANS, LOG10, and POWER; and general purpose functions such as LEFT, DIFFERENCE, and UCASE.

Example of a Function:
Suppose that you have created a user-defined scalar function called TOTAL_SAL that adds the base salary and bonus together for each employee row in the EMPLOYEE table.

CREATE FUNCTION TOTAL_SAL
(SALARY DECIMAL(9,2), BONUS DECIMAL(9,2))
RETURNS DECIMAL(9,2)
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN SALARY+BONUS

The following is a SELECT statement that makes use of TOTAL_SAL:

SELECT LASTNAME, TOTAL_SAL(SALARY, BONUS) AS TOTAL
FROM EMPLOYEE

Example of a stored procedure: When working with C and C++ applications, a stored procedure, INOUT_PARAM, can be called using the following statement:
EXEC SQL CALL INOUT_PARAM(:inout_median:medianind, :out_sqlcode:codeind,
:out_buffer:bufferind);

where inout_median, out_sqlcode, and out_buffer are host variables and medianind, codeind, and bufferind are null indicator variables.
Note: Stored procedures can also be called dynamically by preparing a CALL statement.

Related information: Functions:
User-defined functions (UDFs) are extensions or additions to the existing built-in functions of the SQL language. A user-defined function can be a scalar function, which returns a single value each time it is called; an aggregate function, which is passed a set of like values and returns a single value for the set; a row function, which returns one row; or a table function, which returns a table. For more information and examples, see User-defined functions and Invoking user-defined functions

Procedures
A routine that can be invoked using the SQL CALL statement to perform operations that can include both host language statements and SQL statements.

SQL Procedures
A user-written program that is implemented entirely by using a subset of SQL statements and SQL PL statements and that is invoked by using the SQL CALL statement. For more information see Calling stored procedures in embedded SQL applications

Routines
A database object that can encapsulate procedural logic and SQL statements, is stored on a database server, and can be invoked using an SQL statement such as the CALL statement. The three main classes of routines are procedures, functions, and methods. See also aggregate function, built-in function, external routine, function, scalar function, sourced function, SQL routine, table function, user-defined function, SQL function, row function, SQL procedure, method, SQL method, procedure (by searching the DB2 Information Center).