SQL 函数的创建和在应用程序中的使用都很容易。CREATE FUNCTION 语句定义函数的特征和逻辑，并将函数的特征和逻辑存储在 DB2 系统编目中。该操作被称为注册函数。 清单 1 显示了一个简化版本的 CREATE FUNCTION 语法图，后面有对其主要部分的解释：
清单 1. CREATE FUNCTION 语法图
>>-CREATE FUNCTION--function-name------------------------------->
>--(--+--------------------------------+--)--*------------------>
| .-,--------------------------. |
| V | |
'---parameter-name--data-type1-+-'
>--RETURNS--+-data-type2-----------------+--*------------------->
'-+-ROW---+--| column-list |-'
'-TABLE-'
.-LANGUAGE SQL-.
>--+-------------------------+--*--+--------------+--*---------->
'-SPECIFIC--specific-name-'
.-NOT DETERMINISTIC-. .-EXTERNAL ACTION----.
>--+-------------------+--*--+--------------------+--*---------->
'-DETERMINISTIC-----' '-NO EXTERNAL ACTION-'
.-READS SQL DATA---------.
>--+------------------------+--*--+-----------------+--*-------->
+-CONTAINS SQL-----------+
| |
'-MODIFIES SQL DATA------'
>--| SQL-function-body |--------------------------------------->
column-list:
.-,-----------------------.
V |
|--(----column-name--data-type3-+--)----------------------------|
SQL-function-body:
|--+-RETURN Statement-----------+-------------------------------|
'-dynamic-compound-statement-'
CREATE FUNCTION 语句的最常见的子句是：
function-name：函数名。
RETURNS type : 所创建的函数的类型。 可用的类型有 scalar、row 和 table。在后面的“标量函数”、“行函数” 和 “表函数” 这几个小节中，您将更详细地学习这几种类型。欲指定一个标量函数，只需标识返回的数据类型（不需要使用关键字 SCALAR）。
SPECIFIC：可以为函数指定一个特定的名称，而不是让 DB2 为之指定一个系统生成的惟一名称。在使用重载（overloaded）函数 —— 即具有相同名称，但是所带参数的数量不同的函数时，这一点很有用。
DETERMINISTIC： 指定是否每当以相同的一组输入参数执行函数时，都返回相同的结果。 确定性（Deterministic）函数包括数学函数和不依赖于表中数据或变化数据源的函数。
EXTERNAL ACTION: 指定函数对外部程序是否有影响。
[READS|CONTAINS|MODIFIES] SQL: 指定函数如何通过 SQL 与数据库交互。
SQL-function-body: 这是函数的核心，其中包含逻辑。
CREATE FUNCTION 语句
本节提供很多代码实例，以展示 CREATE FUNCTION 语句中各子句的意义。
RETURNS
RETURNS 子句确定创建的函数的类型。主要的三种类型是 scalar、row 和 table。如清单 2 中的例子所示，标量函数返回单个数据类型值：
清单 2. 一个简单的标量函数
CREATE FUNCTION tan (x DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN SIN(x)/COS(x)
如 清单 3 中的例子所示，行函数将一个用户定义类型分解到它的不同部分中：
清单 3. 一个简单的行函数
CREATE FUNCTION fromperson (p person)
RETURNS ROW (name VARCHAR(10), firstname VARCHAR(10))
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN VALUES (p..name, p..firstname)
如 清单 4 中的例子所示，表函数返回一个表的 0 到多个行。表可以在 SQL 语句中创建，也可以在编程逻辑中创建。
清单 4. 一个简单的表函数
CREATE FUNCTION deptemployees (deptno CHAR(3))
RETURNS TABLE (
empno CHAR(6),
lastname VARCHAR(15),
firstname VARCHAR(12),
deptname VARCHAR(36)
)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT empno, lastname, firstnme, deptname
FROM employee, department
WHERE employee.workdept = department.deptno
SPECIFIC
SPECIFIC 子句用于为函数提供一个特定的标识符。当在函数中添加注释、删除注释或者将注释变为源代码时，可以使用这个标识符。当使用重载函数时，标识符也特别有用。 清单 5 中的两个函数演示了函数重载。第一个函数将两个数相加。第二个函数将字符串 new_ 与一个输入字符串拼接起来。注意，这两个函数有相同的函数名，但是输入参数的数量不一样。
清单 5. 重载标量函数
CREATE FUNCTION joinData (x INT, y INT)
RETURNS DOUBLE
SPECIFIC join_int2
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN x + y
*******
CREATE FUNCTION joinData (x VARCHAR(10))
RETURNS VARCHAR(15)
SPECIFIC join_str
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN 'new_' || x
当必须对函数进行维护，例如为函数添加注释或删除函数时，提供 SPECIFIC 名称的好处就很明显了。在上述情况下，仅仅发出一条 DROP FUNCTION joinData 语句还不够。DB2 不知道您要引用哪个函数。这时需要提供完整的函数签名，例如 DROP FUNCTION joinData(int, int)，以便指定想要撤销的 joinData 函数。但是，如果为函数提供一个 SPECIFIC 名称，那么只需使用那个名称来引用该函数 —— 例如 DROP SPECIFIC FUNCTION join_int2。
DETERMINISTIC
DETERMINISTIC 子句用于指定一个函数是否总是返回相同的值。然后，DB2 可以使用该信息来优化调用函数的方式，如果之前该函数已经执行过一次，而返回的值又是确定的，那么 DB2 可以将函数的值缓存起来。如果函数使用了专用寄存器，或者调用了非确定性函数，那么该函数就是非确定性函数。
清单 6 展示了确定性标量函数的一个例子，清单 7 展示了非确定性标量函数的一个例子：
清单 6. 一个确定性标量函数
CREATE FUNCTION joinData (x INT, y INT)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN x + y
清单 7. 一个非确定性标量函数
CREATE FUNCTION futureDate (x INT)
RETURNS DATE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
NOT DETERMINISTIC
RETURN CURRENT DATE + x MONTHS
对于每个 (x,y) 输入对，清单 6 中函数的执行结果总是一样的。但是，清单 7 中的函数并不总是产生相同的值，因为它需要获取当前日期。
EXTERNAL ACTION
该子句指定一个函数是否更改数据库之外的任何对象。如果函数要执行会产生外部影响的动作，那么必须将该选项设置为 EXTERNAL ACTION。例如，对于修改文件系统中的文件或者更改外部源中的数据的函数，就需要使用该子句。
[CONTAINS|READS|MODIFIES] SQL
该选项让 DB2 知道一个函数如何与数据库交互。交互方式有以下几种选择：
CONTAINS SQL: 表明函数中可以使用既不读取也不修改 SQL 数据的 SQL 语句。
READS SQL DATA: 表明函数中可以使用不修改 SQL 数据的 SQL 语句。
MODIFIES SQL DATA: 表明函数中可以使用动态复合语句中所支持的所有 SQL 语句。
函数规则
函数有一些限制需要特别注意：
如果一个 SQL 函数多处引用一个日期或时间寄存器，那么所有引用都要返回相同的值。
SQL 函数的主体不能包含对其本身或者调用它的其他函数或方法的递归调用。
SQL 函数使用的语言实际上是存储过程使用的 SQL PL 语言的一个子集。因此，在存储过程中可以使用的某些语言结构在函数中不能使用。
在函数中使用复合语句
复合 SQL 语句是包含在一个 BEGIN...END 块中的一组语句。这个块中的 SQL 语句被当作一个单元。
清单 8 显示了动态复合 SQL 块的语法图：
清单 8. 动态复合 SQL 语句的语法图
>>-+-------------+--BEGIN ATOMIC-------------------------------->
| (1) |
'-label:------'
>--+-----------------------------------------+------------------>
| .-------------------------------------. |
| V | |
'---+-| SQL-variable-declaration |-+--;-+-'
'-| condition-declaration |----'
>--+----------------------------------+--END--+-------+--------><
| .-,----------------------------. | '-label-'
| V | |
'---| SQL-routine-statement |--;-+-'
SQL-variable-declaration:
.-,-----------------.
V |
|--DECLARE----SQL-variable-name-+--data-type-------------------->
.-DEFAULT NULL------------.
>--+-------------------------+----------------------------------|
'-DEFAULT--default-values-'
condition-declaration:
|--DECLARE--condition-name--CONDITION--FOR---------------------->
.-VALUE-.
.-SQLSTATE--+-------+-.
>--+---------------------+--string-constant---------------------|
SQL-routine-statement:
|--+-CALL----------------------------------------------+--------|
+-FOR-----------------------------------------------+
+-+-----------------------------------+--fullselect-+
| | .-,-----------------------. | |
| | V | | |
| '-WITH----common-table-expression-+-' |
+-GET DIAGNOSTICS-----------------------------------+
+-IF------------------------------------------------+
+-INSERT--------------------------------------------+
+-ITERATE-------------------------------------------+
+-LEAVE---------------------------------------------+
+-MERGE---------------------------------------------+
+-searched-delete-----------------------------------+
+-searched-update-----------------------------------+
+-SET Variable--------------------------------------+
+-SIGNAL--------------------------------------------+
'-WHILE---------------------------------------------
在以下的几个小节中，将重点介绍复合语句的一些重要的组成部分。
DECLARE
DECLARE 允许您在块内声明变量。其数据类型可以是除了 XML 数据类型之外的任何用户定义的类型或标准的 SQL 数据类型。如果未给定数据类型的默认值，当声明它时将自动地设置为空。以下是一些示例：
DECLARE myInt INTEGER;
DECLARE myChar CHAR(6);
DECLARE myInt2 INTEGER DEFAULT 0;
DECLARE myChar2 VARCHAR(100) DEFAULT NULL;
CONDITION HANDLING
The CONDITION HANDLING：目前，函数尚不能使用该选项。
SQL 控制语句
注意，并不是 SQL 存储过程中支持的所有语句在 UDF 中都受支持。而且，上面语法图中的某些语句只在表函数中受支持。还有一些语句，例如 CALL 语句，在函数中使用它们时也有一些限制。
既然过程语句在函数中的使用与在存储过程中的使用存在很多差异，下面的小节“存储过程”将讨论 SQL 复合语句的更高级的用法，并提供一些例子。
标量函数
SQL 标量函数是最常见的一种 SQL 函数。它返回单个受支持的 DB2 数据类型的值。 清单 9 中的简单例子演示了如何将逻辑嵌入到一个函数中，而不是嵌入到一个客户机应用程序中。函数 CHANGESAL 是使用一行过程代码创建的： RETURN sal * 2。其他部分则构成了函数的定义。该函数以一个雇员的薪水（一个 DOUBLE 值）作为输入。它也可以接受其他数字型值，例如一个 INTEGER，因为 DB2 会隐式地进行类型强制转换。
清单 9. 一个简单的标量用户定义函数
CREATE FUNCTION changeSal (v_sal DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN v_sal * 2
清单 10 展示了如何将函数作为 SQL 语句的一部分执行：
清单 10. 执行 CHANGESAL 用户定义函数
SELECT empno,
changeSal(salary) AS newSalary
FROM employee
WHERE edlevel > 19
Result from the DB2 sample database:
EMPNO NEWSALARY
------ ----------------------
000030 +1.96500000000000E+005
标量函数通常比这个例子更复杂一些，一般会包含更复杂的逻辑和其他 SQL 语句。 清单 11 展示了一个更复杂的标量函数，该函数返回达到所要求的教育程度的雇员数量，要求的教育程度是在函数的输入部分指定的：
清单 11. 一个更复杂的用户定义函数
CREATE FUNCTION edCount (v_edLevel DOUBLE)
RETURNS INT
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
RETURN SELECT count(*)
FROM employee
WHERE edLevel = v_edLevel
然后，可以在一条 SQL 语句中使用该函数，如下面的 清单 12 所示：
清单 12. 执行 EDCOUNT 用户定义函数
SELECT edLevel,
edCount(edLevel) AS edQuantity
FROM employee
GROUP BY edlevel
Result from the DB2 sample database:
EDLEVEL EDQUANTITY
------- -----------
12 3
14 7
15 2
16 14
17 7
18 7
19 1
20 1
在后台，当调用 SQL 函数时，DB2 接受函数逻辑，并将其内联（in-line）到 SQL 语句中。这意味着，SQL 语句中的函数调用实际上被函数逻辑替代。于是，DB2 优化器会根据整个语句，而不是语句的一部分来创建最佳访问计划。这样可以得到更好的总体访问计划。例如，清单 13 显示了根据 清单 10 重新编写的 SQL 语句：
清单 13. 根据清单 10 重新编写的 SQL 语句
SELECT empno,
sal * 2 AS newSalary
FROM employee
WHERE edlevel > 19
与原先简单的 SQL 语句相比，清单 13 中显示的 SQL 语句的内联要更复杂一些。 清单 14 显示了重新编写的语句：
清单 14. 根据清单 12 重新编写的 SQL 语句
SELECT Q3.$C0 AS "EDLEVEL", Q6.$C0 AS "EDQUANTITY"
FROM
(SELECT Q2.$C0
FROM
(SELECT Q1.EDLEVEL
FROM TEDWAS.EMPLOYEE AS Q1) AS Q2
GROUP BY Q2.$C0) AS Q3,
(SELECT COUNT(* )
FROM
(SELECT $RID$
FROM TEDWAS.EMPLOYEE AS Q4
WHERE (Q4.EDLEVEL = DOUBLE(Q3.$C0))) AS Q5) AS Q6
行函数
行 函数并不是只返回一行数据，所以不能望文生义。实际上，行函数用于将一个结构化数据类型转换成它的各个组件。用户定义的结构化类型（UDST）是用户定义的包含对一个或多个 DB2 数据类型的引用的数据类型。因此，如果在数据库中使用 UDST，那么只能使用行函数。行函数只能被定义为 SQL 函数。
清单 15 中的 PERSON 对象就是一个 UDST 的例子。它包含一个 lastName 字段和一个 firstName 字段。行函数 FROMPERSON 可以用于从 PERSON 类型的实例中提取特定的字段。
清单 15. 一个简单的行函数
CREATE TYPE person_t AS (
lastname VARCHAR(20),
firstname VARCHAR(20))
MODE DB2SQL
CREATE FUNCTION fromperson (p person_t)
RETURNS ROW (lname VARCHAR(20), fname VARCHAR(20))
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN VALUES (p..lastname, p..firstname)
表函数
DB2 函数一个更为强大的方面是它们能够返回整个数据表，而非单个值。这将打开您可在 SQL 语句中使用的许多信息源。您不用指向一个数据库表，而是可以编写 C 函数以指向实时数据流，例如股票市场的数据。
表函数实际上很容易编写。表函数不像标量函数那样只返回一个数据值，而是返回一个表中的多行数据，如 清单 16 所示：
清单 16. 一个简单的表函数
CREATE FUNCTION getEnumEmployee(p_dept VARCHAR(3))
RETURNS TABLE
(
enum INT,
empno VARCHAR(6),
lastname VARCHAR(15),
firstnme VARCHAR(12)
)
SPECIFIC getEnumEmployee
RETURN
SELECT ROW_NUMBER() OVER(), e.empno, e.lastname, e.firstnme
FROM employee e
WHERE e.workdept = p_dept
该函数枚举一个部门中的一群雇员。它接收一个 VARCHAR 类型的输入参数。该函数返回的表由 4 个列组成，第一列是 INTEGER 类型，其余列是 VARCHAR 类型。该函数返回 SELECT 语句所定义的一组行。 SELECT 语句的第一个列是一个特殊表达式，它使用 DB2 的聚合函数。该表达式为每一行返回一个整数值，这个值从 1 开始，逐行加 1。其他列的值是从 EMPLOYEE 表中提取的，但是只适用于部门编号与输入参数的值匹配的行。可以看到， ROW_NUMBER() OVER() 表达式非常便于为结果集生成一个连续的数字序列 —— 实际上，是为结果集中的每一行编号。
欲调用一个表函数，必须在查询的 FROM 子句中引用它，并将它包装在名为 TABLE 的函数中。 清单 17 演示了如何调用清单 16 中所示的表函数：
清单 17. 调用 GETENUMEMPLOYEE 表函数
SELECT * FROM TABLE(getEnumEmployee('E11')) AS myNewTable
Result from the DB2 sample database:
ENUM EMPNO LASTNAME FIRSTNME
----------- ------ --------------- ------------
1 000090 HENDERSON EILEEN
2 000280 SCHNEIDER ETHEL
3 000290 PARKER JOHN
4 000300 SMITH PHILIP
5 000310 SETRIGHT MAUDE
6 200280 SCHWARTZ EILEEN
7 200310 SPRINGER MICHELLE
当使用表函数时，要记住一些限制。首先，必须知道函数将返回的表中的列数和数据类型。如果一个函数引用一个表的所有列，但是后来那个表又增加了列，那么该函数可能不会按预期运行。例如，假设创建了 清单 18 中所示的表和函数，然后又使用一个 ALTER 语句为那个表添加了一列：
清单 18. 简单的表和表函数
CREATE TABLE testTab (
varOne INTEGER,
varTwo INTEGER
)
CREATE FUNCTION returnAllTest (v_v1 int)
RETURNS TABLE (v_varOne INT,
v_varTwo INT)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
RETURN
SELECT *
FROM testTab
WHERE varOne = v_v1
ALTER TABLE testTab ADD varThree int
在这种情况下，对该函数的调用不再按预期的那样返回包含所有三个列的表，而是只返回该表创建时定义的两个列。之所以会出现这种情况，是因为函数的定义中使用了 *，该符号是在创建时解析的，而不是在运行时解析的。