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-' |
CREATEFUNCTION语句的最常见的子句是：
- 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语句中各子句的意义。
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子句用于为函数提供一个特定的标识符。当在函数中添加注释、删除注释或者将注释变为源代码时，可以使用这个标识符。当使用重载函数时，标识符也特别有用。 清单 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子句用于指定一个函数是否总是返回相同的值。然后，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。例如，对于修改文件系统中的文件或者更改外部源中的数据的函数，就需要使用该子句。
该选项让 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允许您在块内声明变量。其数据类型可以是除了 XML 数据类型之外的任何用户定义的类型或标准的 SQL 数据类型。如果未给定数据类型的默认值，当声明它时将自动地设置为空。以下是一些示例：
| DECLARE myInt INTEGER; DECLARE myChar CHAR(6); DECLARE myInt2 INTEGER DEFAULT 0; DECLARE myChar2 VARCHAR(100) DEFAULT NULL; |
The CONDITIONHANDLING：目前，函数尚不能使用该选项。
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 |
在这种情况下，对该函数的调用不再按预期的那样返回包含所有三个列的表，而是只返回该表创建时定义的两个列。之所以会出现这种情况，是因为函数的定义中使用了*，该符号是在创建时解析的，而不是在运行时解析的。
扫一扫
1058
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
