您现在的位置：程式碼之家 >> 數據庫知識 > >> DB2 資料庫 >> DB2 教學

DB2 儲存程序開發最佳實踐：參數設計、游標優化與異常處理全攻略

編輯：DB2 教學

前言

DB2 強大的 SQL PL (SQL Procedural Language) 讓開發人員能建立高效、穩定的儲存程序。本文針對具備基礎經驗的開發者，總結初始化參數、游標、異常處理、暫存表使用、非法程序重綁定等五大核心領域的最佳實踐，附完整範例代碼可直接套用。

最佳實踐 1：CREATE PROCEDURE 必備參數完整宣告

雖然語法上非強制，但建議在建立陳述式中明確指定以下參數，提升執行效能與可維護性：

LANGUAGE SQL：宣告為 SQL 語言程序。
DETERMINISTIC / NOT DETERMINISTIC：相同輸入是否保證相同輸出，影響優化器快取決策。
READS SQL DATA / MODIFIES SQL DATA / CONTAINS SQL：精確描述資料存取分類，決定能否在函數/觸發器中調用。
SPECIFIC <名稱>：提供唯一特定名稱，支援多載與精確權限管理。
DYNAMIC RESULT SETS <N>：宣告回傳結果集數量，供呼叫端 (JDBC/ODBC/CLI) 擷取。

最佳實踐 2：游標管理鐵律

明確宣告游標類型：DECLARE c1 CURSOR FOR SELECT ...；需更新時加 FOR UPDATE OF col1, col2。
儘用 WITH HOLD：跨 Commit 保持游標開啟，避免重複開啟開銷 (需評估鎖持有時間)。
分頁游標：大量資料採用 FETCH FIRST n ROWS ONLY 配合 OFFSET 或鍵值分頁，避免一次性物化巨大結果集。
關閉游標：CLOSE c1; 於邏輯結束處顯式關閉，釋放鎖與資源。

最佳實踐 3：結構化異常處理

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    -- 記錄錯誤資訊
    INSERT INTO error_log(proc_name, sqlcode, sqlstate, err_msg, ts)
    VALUES ('MY_PROC', SQLCODE, SQLSTATE, 'Custom context', CURRENT TIMESTAMP);
    -- 視業務決定 RESIGNAL 或回傳自定義狀態
    RESIGNAL; -- 向呼叫端拋出原始錯誤
END;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_done = 1; -- 控制迴圈結束

原則：EXIT HANDLER 處理嚴重錯誤並記錄/重拋，CONTINUE HANDLER 處理預期情況 (如 NOT FOUND) 讓流程繼續。

最佳實踐 4：暫存表善用策略

在程序內部建立 DECLARE GLOBAL TEMPORARY TABLE SESSION.tmp_x ... 儲存中間結果，避免永久表鎖爭用與日誌開銷。
指定 ON COMMIT PRESERVE ROWS 跨交易保留資料 (需在 USER TEMP TABLESPACE 建立)。
使用後顯式 DROP TABLE SESSION.tmp_x 或依賴連線結束自動清理。

最佳實踐 5：尋找並重綁定非法儲存程序

DDL 變更 (如索引刪除、表結構修改) 會導致相依儲存程序標記為非法 (INVALID)。定期執行：

SELECT ROUTINENAME, ROUTINESCHEMA
FROM SYSCAT.ROUTINES
WHERE ROUTINETYPE = 'P' AND VALID = 'N';

對查出的程序執行 REBIND_ROUTINE_PACKAGE 或重新 CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'schema.proc', 'ANY') 恢復有效狀態，防止運行時首次呼叫觸發隱性重綁導致延遲尖峰。

完整範例：帶參數、游標、異常處理、結果集的標準模板

CREATE PROCEDURE get_emp_by_dept (
    IN  p_deptno   SMALLINT,
    OUT p_count    INTEGER
) DYNAMIC RESULT SETS 1
LANGUAGE SQL
READS SQL DATA
SPECIFIC get_emp_by_dept_v1
BEGIN
    DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
        SELECT empno, firstnme, lastname, salary
        FROM employee
        WHERE workdept = p_deptno;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        INSERT INTO proc_error_log VALUES ('get_emp_by_dept', SQLCODE, SQLSTATE, CURRENT TIMESTAMP);
        RESIGNAL;
    END;

    OPEN c1;
    SELECT COUNT(*) INTO p_count FROM employee WHERE workdept = p_deptno;
    -- 游標結果集自動回傳給呼叫端 (JDBC getMoreResults 等)
END