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

DB2 SQL 預存程序語法權威指南：CREATE PROCEDURE 完整解析與範例

編輯：DB2 教學

語法圖總覽

CREATE PROCEDURE procedure-name
  ( [ IN | OUT | INOUT parameter-name data-type ] , ... )
  [ SPECIFIC specific-name ]
  [ DYNAMIC RESULT SETS integer ]
  [ MODIFIES SQL DATA | CONTAINS SQL | READS SQL DATA ]
  [ DETERMINISTIC | NOT DETERMINISTIC ]
  [ CALLED ON NULL INPUT ]
  [ INHERIT SPECIAL REGISTERS ]
  [ OLD SAVEPOINT LEVEL | NEW SAVEPOINT LEVEL ]
  [ LANGUAGE SQL ]
  [ EXTERNAL ACTION | NO EXTERNAL ACTION ]
  [ PARAMETER CCSID ASCII | UNICODE ]
  SQL-procedure-body

關鍵子句詳細說明

子句	說明
`procedure-name`	同一 Schema 下名稱+參數個數唯一，型別不同亦視為衝突。
`IN/OUT/INOUT`	輸入/輸出/雙向參數。`parameter-name` 程序內唯一。`data-type` 支援 SQL 型別與表類型，不支援 LONG VARCHAR/VARGRAPHIC、DATALINK、REFERENCE、UDT。
`SPECIFIC specific-name`	唯一別名，用於 DROP、COMMENT、GRANT，不可用於 CALL。未指定則自動生成時間戳名稱，建議自訂。
`DYNAMIC RESULT SETS n`	宣告可傳回結果集最大數 (預設 0)。雖無 RETURN 陳述式，但可透過游標 WITH RETURN 傳回。
SQL 資料存取級別	`CONTAINS SQL`：不讀寫資料表。`READS SQL DATA`：唯讀。`MODIFIES SQL DATA`：可增刪改 (預設)。
`DETERMINISTIC`	相同輸入必得相同輸出，優化器可快取結果。`NOT DETERMINISTIC` 為預設。
`CALLED ON NULL INPUT`	允許傳入 NULL 參數呼叫，NULL 處理邏輯由程序內部實作。
`INHERIT SPECIAL REGISTERS`	繼承呼叫端的特殊暫存器 (如 CURRENT DATE、USER)。
Savepoint Level	`OLD` (預設)：儲存點僅在程序內可見。`NEW`：程序建立新層級儲存點。
`LANGUAGE SQL`	指定程序主體為 SQL PL。
External Action	`EXTERNAL ACTION` (預設)：可能執行資料庫外部動作。`NO EXTERNAL ACTION`：純資料庫操作，優化器可更積極優化。
`PARAMETER CCSID`	輸出字串編碼。Unicode 資料庫預設 UNICODE，其他預設 ASCII。

完整範例：查詢薪資中位數並傳回超過中位數的員工

CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
  RESULT SETS 1
  LANGUAGE SQL
BEGIN
  DECLARE v_numRecords INT DEFAULT 1;
  DECLARE v_counter INT DEFAULT 0;
  
  -- 有序薪資游標
  DECLARE c1 CURSOR FOR
    SELECT CAST(salary AS DOUBLE) FROM staff ORDER BY salary;
  
  -- 回傳結果集游標
  DECLARE c2 CURSOR WITH RETURN FOR
    SELECT name, job, CAST(salary AS INTEGER)
    FROM staff WHERE salary > medianSalary ORDER BY salary;
  
  DECLARE EXIT HANDLER FOR NOT FOUND
    SET medianSalary = 6666;  -- 錯誤預設值
  
  SET medianSalary = 0;
  SELECT COUNT(*) INTO v_numRecords FROM staff;
  
  OPEN c1;
  WHILE v_counter < (v_numRecords / 2 + 1) DO
    FETCH c1 INTO medianSalary;
    SET v_counter = v_counter + 1;
  END WHILE;
  CLOSE c1;
  
  OPEN c2;  -- 結果集留給呼叫端提取
END

呼叫方式

-- CLP
CALL MEDIAN_RESULT_SET(?)

-- JDBC
CallableStatement cs = conn.prepareCall("{CALL MEDIAN_RESULT_SET(?)}");
cs.registerOutParameter(1, Types.DOUBLE);
cs.execute();
double median = cs.getDouble(1);
ResultSet rs = cs.getResultSet();  // 取得 c2 結果集
while (rs.next()) { ... }