Oracle顯式游標完整實戰：宣告、開啟、提取與關閉全流程

顯式游標核心概念

顯式游標是處理多行查詢結果的標準機制，提供對結果集的精確控制。本文系統講解游標完整生命週期操作，從宣告到關閉的每個關鍵步驟。

一、游標宣告語法與規範

CURSOR cursor_name IS select_statement;

關鍵規則：

• 游標名稱是未宣告的標識符，不可賦值或用於表達式
• SELECT語句可查詢表、視圖、多表連結，支援*全選
• 建議在DECLARE區塊集中宣告所有游標，提升可讀性

宣告範例

DECLARE
  CURSOR c_emp IS
    SELECT empno, ename, salary
    FROM emp
    WHERE salary > 2000
    ORDER BY ename;
BEGIN
  -- 游標操作邏輯
END;

二、游標開啟：初始化結果集

OPEN cursor_name;

執行查詢、建立結果集、將指標定位在第一行之前。若查詢涉及變數綁定，以OPEN時刻的變數值為準。

三、資料提取：FETCH機制詳解

FETCH cursor_name INTO variable[, variable,...];

核心規則：

• 每次FETCH推進指標到下一行
• 變數列表數量、型別、順序必須與SELECT列表完全對應
• 超出結果集範圍時，變數值不變，需配合%NOTFOUND判斷

基本提取範例

SET SERVEROUTPUT ON
DECLARE
  v_ename  EMP.ENAME%TYPE;
  v_salary EMP.SALARY%TYPE;
  CURSOR c_emp IS SELECT ename, salary FROM emp;
BEGIN
  OPEN c_emp;
  FETCH c_emp INTO v_ename, v_salary;
  DBMS_OUTPUT.PUT_LINE('Salary of Employee ' || v_ename || ' is ' || v_salary);
  -- 重複FETCH獲取後續行...
  CLOSE c_emp;
END;

四、迴圈化提取：標準化處理模式

手工重複FETCH程式碼冗長難維護，標準模式採用LOOP + FETCH + EXIT WHEN：

SET SERVEROUTPUT ON
DECLARE
  v_ename  EMP.ENAME%TYPE;
  v_salary EMP.SALARY%TYPE;
  CURSOR c_emp IS SELECT ename, salary FROM emp;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_ename, v_salary;
    EXIT WHEN c_emp%NOTFOUND;  -- 關鍵：游標屬性判斷結束
    DBMS_OUTPUT.PUT_LINE('Salary of Employee ' || v_ename || ' is ' || v_salary);
  END LOOP;
  CLOSE c_emp;
END;

五、游標關閉：釋放資源

CLOSE cursor_name;

最佳實踐：

• 處理完成後必須顯式關閉，釋放記憶體與鎖資源
• 在EXCEPTION區塊中同樣需要關閉游標，防止資源洩漏
• 可使用cursor_name%ISOPEN檢查狀態後再關閉

六、進階技巧與效能優化

總結

掌握顯式游標完整生命週期操作，是PL/SQL程式設計的基本功。實務中建議：統一採用迴圈化提取模式、嚴格管理游標開關、善用參數化與FOR UPDATE機制、大數據量場景引入BULK COLLECT優化。