DB2 程式設計技巧（四）：結果集回傳、動態 SQL、型別轉換、SP 互呼、C SP 參數、FENCED/UNFENCED

DB2 存儲過程進階開發模式

存儲過程回傳結果集

-- 被呼叫端：宣告 RESULT SETS 2，游標 WITH RETURN TO CALLER
CREATE PROCEDURE DB2INST1.Proc1 ()
LANGUAGE SQL
RESULT SETS 2
P1: BEGIN
  DECLARE c1 CURSOR WITH RETURN TO CALLER FOR SELECT market_code FROM tb_market_code;
  DECLARE c2 CURSOR WITH RETURN TO CALLER FOR SELECT market_code FROM tb_market_code;
  OPEN c1; OPEN c2;
END P1

-- 呼叫端：使用 RESULT SET LOCATOR
CREATE PROCEDURE DB2INST1.Proc2 (OUT out_market_code CHAR(1))
LANGUAGE SQL
P1: BEGIN
  DECLARE loc1, loc2 RESULT_SET_LOCATOR VARYING;
  CALL Proc1;
  ASSOCIATE RESULT SET LOCATOR (loc1, loc2) WITH PROCEDURE Proc1;
  ALLOCATE cursor1 CURSOR FOR RESULT SET loc1;
  ALLOCATE cursor2 CURSOR FOR RESULT SET loc2;
  FETCH cursor1 INTO out_market_code;
  CLOSE cursor1;
END P1

動態 SQL 分配游標

DECLARE CURSOR C1 FOR STMT1;
PREPARE STMT1 FROM 'ALLOCATE C2 CURSOR FOR RESULT SET ?';

注意事項

• SP 多次呼叫，僅能取得最近一次結果集
• ALLOCATE 游標不可再次 OPEN，可 CLOSE (關閉 SP 內對應游標)

型別轉換

SELECT CAST(CURRENT TIME AS CHAR(8)) FROM tb_market_code

SP 互呼規則

• C SP 可互呼
• SQL SP 可互呼
• SQL SP 可呼叫 C SP
• C SP 呼叫 SQL SP：舊版不支援，新版已開放

C 存儲過程參數關鍵：PARAMETER STYLE GENERAL WITH NULLS

CREATE PROCEDURE pr_clear_task_ctrl (
  IN IN_BRANCH_CODE CHAR(4),
  IN IN_TRADEDATE CHAR(8),
  IN IN_TASK_ID CHAR(2),
  IN IN_SUB_TASK_ID CHAR(4),
  OUT OUT_SUCCESS_FLAG INTEGER
)
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE GENERAL WITH NULLS  -- 關鍵：否則 SQL SP 呼叫會保護錯誤
NO DBINFO
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'pr_clear_task_ctrl!pr_clear_task_ctrl'

FENCED vs UNFENCED

• FENCED：獨立位址空間，較安全
• UNFENCED：共用呼叫者位址空間，可取得呼叫者 PID 等特殊需求

一般推薦 FENCED，特殊需求才用 UNFENCED。