SQL 函數(shù)的創(chuàng)建和在應(yīng)用程序中的使用都很容易。CREATE FUNCTION 語句定義函數(shù)的特征和邏輯，并將函數(shù)的特征和邏輯存儲(chǔ)在 DB2 系統(tǒng)編目中。該操作被稱為注冊(cè)函數(shù)。 清單 1 顯示了一個(gè)簡化版本的 CREATE FUNCTION 語法圖，后面有對(duì)其主要部分的解釋：
清單 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：函數(shù)名。
RETURNS type : 所創(chuàng)建的函數(shù)的類型。 可用的類型有 scalar、row 和 table。在后面的“標(biāo)量函數(shù)”、“行函數(shù)” 和 “表函數(shù)” 這幾個(gè)小節(jié)中，您將更詳細(xì)地學(xué)習(xí)這幾種類型。欲指定一個(gè)標(biāo)量函數(shù)，只需標(biāo)識(shí)返回的數(shù)據(jù)類型（不需要使用關(guān)鍵字 SCALAR）。
SPECIFIC：可以為函數(shù)指定一個(gè)特定的名稱，而不是讓 DB2 為之指定一個(gè)系統(tǒng)生成的惟一名稱。在使用重載（overloaded）函數(shù) —— 即具有相同名稱，但是所帶參數(shù)的數(shù)量不同的函數(shù)時(shí)，這一點(diǎn)很有用。
DETERMINISTIC： 指定是否每當(dāng)以相同的一組輸入?yún)?shù)執(zhí)行函數(shù)時(shí)，都返回相同的結(jié)果。 確定性（Deterministic）函數(shù)包括數(shù)學(xué)函數(shù)和不依賴于表中數(shù)據(jù)或變化數(shù)據(jù)源的函數(shù)。
EXTERNAL ACTION: 指定函數(shù)對(duì)外部程序是否有影響。
[READS|CONTAINS|MODIFIES] SQL: 指定函數(shù)如何通過 SQL 與數(shù)據(jù)庫交互。
SQL-function-body: 這是函數(shù)的核心，其中包含邏輯。
CREATE FUNCTION 語句
本節(jié)提供很多代碼實(shí)例，以展示 CREATE FUNCTION 語句中各子句的意義。
RETURNS
RETURNS 子句確定創(chuàng)建的函數(shù)的類型。主要的三種類型是 scalar、row 和 table。如清單 2 中的例子所示，標(biāo)量函數(shù)返回單個(gè)數(shù)據(jù)類型值：
清單 2. 一個(gè)簡單的標(biāo)量函數(shù)
CREATE FUNCTION tan (x DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN SIN(x)/COS(x)
如 清單 3 中的例子所示，行函數(shù)將一個(gè)用戶定義類型分解到它的不同部分中：
清單 3. 一個(gè)簡單的行函數(shù)
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 中的例子所示，表函數(shù)返回一個(gè)表的 0 到多個(gè)行。表可以在 SQL 語句中創(chuàng)建，也可以在編程邏輯中創(chuàng)建。
清單 4. 一個(gè)簡單的表函數(shù)
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 子句用于為函數(shù)提供一個(gè)特定的標(biāo)識(shí)符。當(dāng)在函數(shù)中添加注釋、刪除注釋或者將注釋變?yōu)樵创a時(shí)，可以使用這個(gè)標(biāo)識(shí)符。當(dāng)使用重載函數(shù)時(shí)，標(biāo)識(shí)符也特別有用。 清單 5 中的兩個(gè)函數(shù)演示了函數(shù)重載。第一個(gè)函數(shù)將兩個(gè)數(shù)相加。第二個(gè)函數(shù)將字符串 new_ 與一個(gè)輸入字符串拼接起來。注意，這兩個(gè)函數(shù)有相同的函數(shù)名，但是輸入?yún)?shù)的數(shù)量不一樣。
清單 5. 重載標(biāo)量函數(shù)
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
當(dāng)必須對(duì)函數(shù)進(jìn)行維護(hù)，例如為函數(shù)添加注釋或刪除函數(shù)時(shí)，提供 SPECIFIC 名稱的好處就很明顯了。在上述情況下，僅僅發(fā)出一條 DROP FUNCTION joinData 語句還不夠。DB2 不知道您要引用哪個(gè)函數(shù)。這時(shí)需要提供完整的函數(shù)簽名，例如 DROP FUNCTION joinData(int, int)，以便指定想要撤銷的 joinData 函數(shù)。但是，如果為函數(shù)提供一個(gè) SPECIFIC 名稱，那么只需使用那個(gè)名稱來引用該函數(shù) —— 例如 DROP SPECIFIC FUNCTION join_int2。
DETERMINISTIC
DETERMINISTIC 子句用于指定一個(gè)函數(shù)是否總是返回相同的值。然后，DB2 可以使用該信息來優(yōu)化調(diào)用函數(shù)的方式，如果之前該函數(shù)已經(jīng)執(zhí)行過一次，而返回的值又是確定的，那么 DB2 可以將函數(shù)的值緩存起來。如果函數(shù)使用了專用寄存器，或者調(diào)用了非確定性函數(shù)，那么該函數(shù)就是非確定性函數(shù)。
清單 6 展示了確定性標(biāo)量函數(shù)的一個(gè)例子，清單 7 展示了非確定性標(biāo)量函數(shù)的一個(gè)例子：
清單 6. 一個(gè)確定性標(biāo)量函數(shù)
CREATE FUNCTION joinData (x INT, y INT)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN x + y
清單 7. 一個(gè)非確定性標(biāo)量函數(shù)
CREATE FUNCTION futureDate (x INT)
RETURNS DATE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
NOT DETERMINISTIC
RETURN CURRENT DATE + x MONTHS
對(duì)于每個(gè) (x,y) 輸入對(duì)，清單 6 中函數(shù)的執(zhí)行結(jié)果總是一樣的。但是，清單 7 中的函數(shù)并不總是產(chǎn)生相同的值，因?yàn)樗枰@取當(dāng)前日期。
EXTERNAL ACTION
該子句指定一個(gè)函數(shù)是否更改數(shù)據(jù)庫之外的任何對(duì)象。如果函數(shù)要執(zhí)行會(huì)產(chǎn)生外部影響的動(dòng)作，那么必須將該選項(xiàng)設(shè)置為 EXTERNAL ACTION。例如，對(duì)于修改文件系統(tǒng)中的文件或者更改外部源中的數(shù)據(jù)的函數(shù)，就需要使用該子句。
[CONTAINS|READS|MODIFIES] SQL
該選項(xiàng)讓 DB2 知道一個(gè)函數(shù)如何與數(shù)據(jù)庫交互。交互方式有以下幾種選擇：
CONTAINS SQL: 表明函數(shù)中可以使用既不讀取也不修改 SQL 數(shù)據(jù)的 SQL 語句。
READS SQL DATA: 表明函數(shù)中可以使用不修改 SQL 數(shù)據(jù)的 SQL 語句。
MODIFIES SQL DATA: 表明函數(shù)中可以使用動(dòng)態(tài)復(fù)合語句中所支持的所有 SQL 語句。
函數(shù)規(guī)則
函數(shù)有一些限制需要特別注意：
如果一個(gè) SQL 函數(shù)多處引用一個(gè)日期或時(shí)間寄存器，那么所有引用都要返回相同的值。
SQL 函數(shù)的主體不能包含對(duì)其本身或者調(diào)用它的其他函數(shù)或方法的遞歸調(diào)用。
SQL 函數(shù)使用的語言實(shí)際上是存儲(chǔ)過程使用的 SQL PL 語言的一個(gè)子集。因此，在存儲(chǔ)過程中可以使用的某些語言結(jié)構(gòu)在函數(shù)中不能使用。
在函數(shù)中使用復(fù)合語句
復(fù)合 SQL 語句是包含在一個(gè) BEGIN...END 塊中的一組語句。這個(gè)塊中的 SQL 語句被當(dāng)作一個(gè)單元。
清單 8 顯示了動(dòng)態(tài)復(fù)合 SQL 塊的語法圖：
清單 8. 動(dòng)態(tài)復(fù)合 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---------------------------------------------
在以下的幾個(gè)小節(jié)中，將重點(diǎn)介紹復(fù)合語句的一些重要的組成部分。
DECLARE
DECLARE 允許您在塊內(nèi)聲明變量。其數(shù)據(jù)類型可以是除了 XML 數(shù)據(jù)類型之外的任何用戶定義的類型或標(biāo)準(zhǔn)的 SQL 數(shù)據(jù)類型。如果未給定數(shù)據(jù)類型的默認(rèn)值，當(dāng)聲明它時(shí)將自動(dòng)地設(shè)置為空。以下是一些示例：
DECLARE myInt INTEGER;
DECLARE myChar CHAR(6);
DECLARE myInt2 INTEGER DEFAULT 0;
DECLARE myChar2 VARCHAR(100) DEFAULT NULL;
CONDITION HANDLING
The CONDITION HANDLING：目前，函數(shù)尚不能使用該選項(xiàng)。
SQL 控制語句
注意，并不是 SQL 存儲(chǔ)過程中支持的所有語句在 UDF 中都受支持。而且，上面語法圖中的某些語句只在表函數(shù)中受支持。還有一些語句，例如 CALL 語句，在函數(shù)中使用它們時(shí)也有一些限制。
既然過程語句在函數(shù)中的使用與在存儲(chǔ)過程中的使用存在很多差異，下面的小節(jié)“存儲(chǔ)過程”將討論 SQL 復(fù)合語句的更高級(jí)的用法，并提供一些例子。
標(biāo)量函數(shù)
SQL 標(biāo)量函數(shù)是最常見的一種 SQL 函數(shù)。它返回單個(gè)受支持的 DB2 數(shù)據(jù)類型的值。 清單 9 中的簡單例子演示了如何將邏輯嵌入到一個(gè)函數(shù)中，而不是嵌入到一個(gè)客戶機(jī)應(yīng)用程序中。函數(shù) CHANGESAL 是使用一行過程代碼創(chuàng)建的： RETURN sal * 2。其他部分則構(gòu)成了函數(shù)的定義。該函數(shù)以一個(gè)雇員的薪水（一個(gè) DOUBLE 值）作為輸入。它也可以接受其他數(shù)字型值，例如一個(gè) INTEGER，因?yàn)?DB2 會(huì)隱式地進(jìn)行類型強(qiáng)制轉(zhuǎn)換。
清單 9. 一個(gè)簡單的標(biāo)量用戶定義函數(shù)
CREATE FUNCTION changeSal (v_sal DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN v_sal * 2
清單 10 展示了如何將函數(shù)作為 SQL 語句的一部分執(zhí)行：
清單 10. 執(zhí)行 CHANGESAL 用戶定義函數(shù)
SELECT empno,
changeSal(salary) AS newSalary
FROM employee
WHERE edlevel > 19
Result from the DB2 sample database:
EMPNO NEWSALARY
------ ----------------------
000030 +1.96500000000000E+005
標(biāo)量函數(shù)通常比這個(gè)例子更復(fù)雜一些，一般會(huì)包含更復(fù)雜的邏輯和其他 SQL 語句。 清單 11 展示了一個(gè)更復(fù)雜的標(biāo)量函數(shù)，該函數(shù)返回達(dá)到所要求的教育程度的雇員數(shù)量，要求的教育程度是在函數(shù)的輸入部分指定的：
清單 11. 一個(gè)更復(fù)雜的用戶定義函數(shù)
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 語句中使用該函數(shù)，如下面的 清單 12 所示：
清單 12. 執(zhí)行 EDCOUNT 用戶定義函數(shù)
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
在后臺(tái)，當(dāng)調(diào)用 SQL 函數(shù)時(shí)，DB2 接受函數(shù)邏輯，并將其內(nèi)聯(lián)（in-line）到 SQL 語句中。這意味著，SQL 語句中的函數(shù)調(diào)用實(shí)際上被函數(shù)邏輯替代。于是，DB2 優(yōu)化器會(huì)根據(jù)整個(gè)語句，而不是語句的一部分來創(chuàng)建最佳訪問計(jì)劃。這樣可以得到更好的總體訪問計(jì)劃。例如，清單 13 顯示了根據(jù) 清單 10 重新編寫的 SQL 語句：
清單 13. 根據(jù)清單 10 重新編寫的 SQL 語句
SELECT empno,
sal * 2 AS newSalary
FROM employee
WHERE edlevel > 19
與原先簡單的 SQL 語句相比，清單 13 中顯示的 SQL 語句的內(nèi)聯(lián)要更復(fù)雜一些。 清單 14 顯示了重新編寫的語句：
清單 14. 根據(jù)清單 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
行函數(shù)
行 函數(shù)并不是只返回一行數(shù)據(jù)，所以不能望文生義。實(shí)際上，行函數(shù)用于將一個(gè)結(jié)構(gòu)化數(shù)據(jù)類型轉(zhuǎn)換成它的各個(gè)組件。用戶定義的結(jié)構(gòu)化類型（UDST）是用戶定義的包含對(duì)一個(gè)或多個(gè) DB2 數(shù)據(jù)類型的引用的數(shù)據(jù)類型。因此，如果在數(shù)據(jù)庫中使用 UDST，那么只能使用行函數(shù)。行函數(shù)只能被定義為 SQL 函數(shù)。
清單 15 中的 PERSON 對(duì)象就是一個(gè) UDST 的例子。它包含一個(gè) lastName 字段和一個(gè) firstName 字段。行函數(shù) FROMPERSON 可以用于從 PERSON 類型的實(shí)例中提取特定的字段。
清單 15. 一個(gè)簡單的行函數(shù)
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)
表函數(shù)
DB2 函數(shù)一個(gè)更為強(qiáng)大的方面是它們能夠返回整個(gè)數(shù)據(jù)表，而非單個(gè)值。這將打開您可在 SQL 語句中使用的許多信息源。您不用指向一個(gè)數(shù)據(jù)庫表，而是可以編寫 C 函數(shù)以指向?qū)崟r(shí)數(shù)據(jù)流，例如股票市場的數(shù)據(jù)。
表函數(shù)實(shí)際上很容易編寫。表函數(shù)不像標(biāo)量函數(shù)那樣只返回一個(gè)數(shù)據(jù)值，而是返回一個(gè)表中的多行數(shù)據(jù)，如 清單 16 所示：
清單 16. 一個(gè)簡單的表函數(shù)
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
該函數(shù)枚舉一個(gè)部門中的一群雇員。它接收一個(gè) VARCHAR 類型的輸入?yún)?shù)。該函數(shù)返回的表由 4 個(gè)列組成，第一列是 INTEGER 類型，其余列是 VARCHAR 類型。該函數(shù)返回 SELECT 語句所定義的一組行。 SELECT 語句的第一個(gè)列是一個(gè)特殊表達(dá)式，它使用 DB2 的聚合函數(shù)。該表達(dá)式為每一行返回一個(gè)整數(shù)值，這個(gè)值從 1 開始，逐行加 1。其他列的值是從 EMPLOYEE 表中提取的，但是只適用于部門編號(hào)與輸入?yún)?shù)的值匹配的行。可以看到， ROW_NUMBER() OVER() 表達(dá)式非常便于為結(jié)果集生成一個(gè)連續(xù)的數(shù)字序列 —— 實(shí)際上，是為結(jié)果集中的每一行編號(hào)。
欲調(diào)用一個(gè)表函數(shù)，必須在查詢的 FROM 子句中引用它，并將它包裝在名為 TABLE 的函數(shù)中。 清單 17 演示了如何調(diào)用清單 16 中所示的表函數(shù)：
清單 17. 調(diào)用 GETENUMEMPLOYEE 表函數(shù)
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
當(dāng)使用表函數(shù)時(shí)，要記住一些限制。首先，必須知道函數(shù)將返回的表中的列數(shù)和數(shù)據(jù)類型。如果一個(gè)函數(shù)引用一個(gè)表的所有列，但是后來那個(gè)表又增加了列，那么該函數(shù)可能不會(huì)按預(yù)期運(yùn)行。例如，假設(shè)創(chuàng)建了 清單 18 中所示的表和函數(shù)，然后又使用一個(gè) ALTER 語句為那個(gè)表添加了一列：
清單 18. 簡單的表和表函數(shù)
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
在這種情況下，對(duì)該函數(shù)的調(diào)用不再按預(yù)期的那樣返回包含所有三個(gè)列的表，而是只返回該表創(chuàng)建時(shí)定義的兩個(gè)列。之所以會(huì)出現(xiàn)這種情況，是因?yàn)楹瘮?shù)的定義中使用了 *，該符號(hào)是在創(chuàng)建時(shí)解析的，而不是在運(yùn)行時(shí)解析的。