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
在這種情況下，對該函數的調用不再按預期的那樣返回包含所有三個列的表，而是只返回該表創建時定義的兩個列。之所以會出現這種情況，是因為函數的定義中使用了 *，該符號是在創建時解析的，而不是在運行時解析的。