Functions are relationships between sets of input data
values and a set of result values. They enable you to extend and
customize SQL. Functions are invoked from within elements of SQL
statements such as a select-list or a FROM clause.
There are four types of functions:
- Aggregate functions
- Also called a column function, this type of function returns a
scalar value that is the result of an evaluation over a set of like
input values. The similar input values can, for example, be specified
by a column within a table, or by tuples in a VALUES clause. This
set of values is called the argument set. For example, the following
query finds the total quantity of bolts that are in stock or on order
by using the SUM aggregate function:
SELECT SUM (qinstock + qonorder)
FROM inventory
WHERE description LIKE '%Bolt%'
- Scalar functions
- A scalar function is a function that, for each set of one or more
scalar parameters, returns a single scalar value. Examples of scalar
functions include the LENGTH function, and the SUBSTR function. Scalar
functions can also be created that do complex mathematical calculations
on function input parameters. Scalar functions can be referenced
anywhere that an expression is valid within an SQL statement, such
as in a select-list, or in a FROM clause. The following example shows
a query that references the built-in LENGTH scalar function:
SELECT lastname, LENGTH(lastname)
FROM employee
- Row functions
- A row function is a function that for each set of one or more
scalar parameters returns a single row. Row functions can only be
used as a transform function mapping attributes of a structured type
into built-in data type values in a row.
- Table functions
- Table functions are functions that for a group of sets of one
or more parameters, return a table to the SQL statement that references
it. Table functions can only be referenced in the FROM clause of a
SELECT statement. The table that is returned by a table function can
participate in joins, grouping operations, set operations such as
UNION, and any operation that could be applied to a read-only view.
The following example demonstrates an SQL table function that updates
an inventory table and returns the result set of a query on the updated
inventory table:
CREATE FUNCTION updateInv(itemNo VARCHAR(20), amount INTEGER)
RETURNS TABLE (productName VARCHAR(20),
quantity INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN ATOMIC
UPDATE Inventory as I
SET quantity = quantity + amount
WHERE I.itemID = itemNo;
RETURN
SELECT I.itemName, I.quantity
FROM Inventory as I
WHERE I.itemID = itemNo;
END
Functions provide support for the following features:
- Functions are supported across the DB2® brand
database products including, among others, DB2, DB2 for z/OS®, and DB2 Database for System i®
- Moderate support for SQL statement execution
- Parameter support for input parameters and scalar or aggregate
function return values
- Efficient compilation of function logic into queries that reference
functions
- External functions provide support for storing intermediate values
between the individual function sub-invocations for each row or value
There are system-defined functions that are ready-to-use,
or users can create user-defined functions. Functions can be implemented
as SQL functions or as external functions. SQL functions can be either
compiled or inlined. Inlined functions perform faster than compiled
functions, but can execute only a subset of the SQL PL language. See
the CREATE FUNCTION statement for more information.