DB2 UDF

DB2 9 使用启示（733 检验）认证指南，第 9 部分: 用户界说的例程(3)

将定制的和复杂的业务逻辑集成到 SQL 语句中

用户界说的 SQL 函数

建立函数

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

在这种情形下，对该函数的调用不再按预期的那样前去网罗统统三个列的表，而是只前去该表建立时界说的两个列。之以是会出现这种情形，是由于函数的界说中运用了 *，该标志是在建立时剖析的，而不是在运转时剖析的。