DB2 9.5 SQL Procedure Developer 認證考試 735 备考指南：第三部分 UDF 與 SQL 函數全維度解析

本教程為 DB2 9.5 SQL Procedure Developer 認證考試 (Exam 735) 系列第三部分，深度講解用戶定義函數 (UDF)，聚焦 SQL 函數的創建、結構、調用與錯誤處理，為考試奠定堅實基礎。

函數分類與應用場景

DB2 四大函數類型：

• 標量函數：返回單值，可用於表達式、謂詞，支持服務端過濾減少網絡傳輸，但不支持事務管理
• 聚合函數：對列/元組計算返回單值 (如 MAX(), AVG())
• 表函數：返回表，僅用於 FROM 子句，支持聯結/分組/集合操作，可訪問文件/網絡/OS，但無事務管理
• 行函數：返回單行，專供結構化類型使用，將屬性映射為內置類型行

五類 UDF 創建機制

1. 有源/模板函數：基於現有函數構建，實現用戶定義類型繼承內置語義
2. SQL 標量/表/行函數：純 SQL PL 構建，本文重點
3. 外部標量函數：C/C++/Java/CLR/OLE 編寫，需編譯鏈接註冊
4. 外部表函數：高級語言編寫，返回數據集
5. OLE DB 外部表函數：通用 OLE DB 消費者訪問任意數據源

有源函數：類型語義繼承

CREATE FUNCTION AVG(YEAR) RETURNS YEAR
SOURCE SYSIBM.AVG(INTEGER);

• 參數自動轉換為源函數類型執行，結果再轉回目標類型
• 同名不同簽名函數可共存
• AS TEMPLATE 生成函數模板，供聯邦系統映射遠端函數

SQL 函數：核心語法與選項

CREATE FUNCTION julian_date(in_date DATE)
RETURNS CHAR(7)
LANGUAGE SQL
RETURN RTRIM(CHAR(YEAR(in_date))) || SUBSTR(DIGITS(DAYOFYEAR(in_date)), 8);

關鍵子句解析

外部函數註冊關鍵點

外部標量函數

CREATE FUNCTION center(INT, DOUBLE) RETURNS DOUBLE
EXTERNAL NAME '/home/db2inst1/myfuncs/double'
LANGUAGE C PARAMETER STYLE SQL DETERMINISTIC NO SQL;

• LANGUAGE: C, JAVA, CLR, OLE
• PARAMETER STYLE: DB2GENERAL (Java), JAVA (Java), SQL (C/C++/OLE/.NET)
• FENCED (默認): 隔離運行，不訪問 DB2 內存；NOT FENCED 需極高可靠性
• SCRATCHPAD: 持久內存區，跨調用保留狀態
• DBINFO: 傳遞 DB2 環境信息 (數據庫名、授權 ID、版本等)

外部表函數

• RETURNS TABLE (...) 定義返回列
• 不支持 DB2GENERAL 參數樣式

OLE DB 外部表函數

CREATE FUNCTION orders()
RETURNS TABLE (orderid INTEGER, customerid CHAR(5), ...)
LANGUAGE OLEDB
EXTERNAL NAME '!orders!Provider=Microsoft.Jet.OLEDB.3.51;Data Source=...';

• EXTERNAL NAME 語法: '[Server]!<Rowset>' 或 '!<Rowset>![ConnectString] <!COLLATING_SEQUENCE=[N|Y]>'
• 無需編寫代碼，直接映射 OLE DB 提供者

SQL 函數結構四大類型

1. 單語句標量函數

僅含一條 SQL，返回標量值。例：獲取當前月份名稱。

2. 單語句表函數

RETURNS TABLE (...) 定義列，體為單查詢。例：返回男性員工薪資信息。

3. 復合語句標量函數

體為 BEGIN ... END 塊，包含變量聲明、控制流、多語句，編譯為單一 SQL。例：計算本月最後一天星期幾。

4. 復合語句表函數

復合體返回表。例：返回薪資高於平均值的所有員工信息。

SQL PL 核心語句清單

• 變量相關: DECLARE ... DEFAULT, DECLARE CONDITION, SET
• 條件/循環: IF, FOR, WHILE
• 控制轉移: CALL, ITERATE, LEAVE, RETURN
• 錯誤管理: SIGNAL

SQL 函數錯誤處理：SIGNAL 機制

SIGNAL SQLSTATE '22007' SET MESSAGE_TEXT = 'Invalid date';

• CONDITION 映射 SQLSTATE 值 (如 22007 = 日期無效)
• MESSAGE_TEXT ≤ 70 字符
• 可在 IF 分支中按業務邏輯觸發

考試必考知識點

1. 四類函數特性與適用場景區別
2. 五類 UDF 創建語法形式與核心子句含義
3. 有源函數實現類型繼承的機制
4. SQL 函數關鍵選項 (DETERMINISTIC, MODIFIES SQL DATA 等) 對優化器/運行行為的影響
5. 外部函數註冊全流程 (編譯→鏈接→權限→註冊) 與關鍵子句 (FENCED, SCRATCHPAD, DBINFO)
6. OLE DB 表函數 EXTERNAL NAME 語法構造
7. 四類 SQL 函數結構差異與 RETURNS 子句寫法
8. 復合語句中 SQL PL 語句分類與 SIGNAL 錯誤處理模式

1. 上一篇文章： DB2 9.5 SQL Procedure Developer 認證考試 735 备考指南：第五部分核心技術深度解析
2. 下一篇文章： DB2 9.7 輪換診斷日誌與管理通知日誌：告別磁盤爆滿與傳輸困境

DB2 教學

DB2命令執行六大通道全解析：從主機控制台到IFI接口的完整體系

前言：命令執行路徑決定了權限邊界與運維效率 DB2命令不僅僅

DB2 9 資料庫優勢深度解析：原生 XML 支援與混合資料模型

XML 的靈活性 XML 允許自訂標籤定義資料類型與屬性名稱

DB2 9 核心認證指南（第二部分）：全方位掌握資料庫安全架構

前言：安全防禦的三道防線 資料庫安全不僅是設定密碼，而是系統

DB2 9.7 分區表全文本搜尋：Net Search Extender 新世代同步架構深度解析

背景：分區表優勢與 NSE 基礎 DB2 V9 引入表分區，