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

DB2 游標完整教學：宣告、開啟、擷取、關閉與靜態/動態範例

編輯：DB2 教學

靜態游標四步驟


-- 1. 宣告
DECLARE C1 CURSOR FOR
  SELECT CAST(SALARY AS DOUBLE)
  FROM STAFF
  WHERE DEPT = :DEPT_NUMBER
  ORDER BY SALARY;

-- 2. 開啟
OPEN C1;

-- 3. 擷取 (迴圈)
WHILE (SQLCODE = 0) DO
  FETCH C1 INTO :MEDIAN_SALARY;
  -- 處理邏輯
END WHILE;

-- 4. 關閉
CLOSE C1;

動態游標（SQL PL）


DECLARE STMT1 STATEMENT;
DECLARE C1 CURSOR FOR STMT1;
SET SQL_STR = 'SELECT ... FROM ... WHERE ...';
PREPARE STMT1 FROM SQL_STR;
OPEN C1 USING :PARAM1, :PARAM2;
-- FETCH 迴圈同靜態
CLOSE C1;

關鍵選項

選項	效果
`WITH HOLD`	Commit 後保持游標開啟
`WITHOUT HOLD`	Commit 後自動關閉 (預設)
`FOR READ ONLY`	明確唯讀，避免模糊游標升級鎖
`FOR UPDATE OF COL1, COL2`	可更新游標，限定可更新欄位
`INSENSITIVE`	結果集快照，隔離並發變更
`SCROLL`	支援 FETCH ABSOLUTE/RELATIVE/PRIOR/NEXT

完整範例：中位數薪資計算


DECLARE C1 CURSOR FOR
  SELECT CAST(SALARY AS DOUBLE)
  FROM STAFF
  WHERE DEPT = DEPT_NUMBER
  ORDER BY SALARY;

DECLARE EXIT HANDLER FOR NOT FOUND
  SET MEDIAN_SALARY = 6666;

SET MEDIAN_SALARY = 0;
SELECT COUNT(*) INTO V_NUM_RECORDS FROM STAFF WHERE DEPT = DEPT_NUMBER;
OPEN C1;

WHILE V_COUNTER < (V_NUM_RECORDS / 2 + 1) DO
  FETCH C1 INTO MEDIAN_SALARY;
  SET V_COUNTER = V_COUNTER + 1;
END WHILE;

CLOSE C1;