Oracle 游標語法大全：顯式、隱式與 REF CURSOR 實戰模式

一、游標核心概念

游標是指向查詢結果集的指針，讓程式能逐行處理 SELECT 返回的數據。分類：

• 靜態游標：編譯時確定查詢，含顯式（命名）與隱式（匿名）；
• REF CURSOR：運行時動態綁定查詢的引用類型，類似指針，支援動態 SQL 與模塊間傳遞結果集。

二、顯式游標完整生命週期

CURSOR 游標名(參數列表) [RETURN 返回類型] IS
  SELECT 語句;

三階段操作

1. OPEN：解析、綁定變量，不取數據；
2. FETCH INTO：執行查詢，將當前行載入變量（通常用 %ROWTYPE 記錄型變量）；
3. CLOSE：釋放資源，可重新 OPEN。

三、參數化游標實戰範例

SET SERVEROUTPUT ON
DECLARE
  CURSOR emp_cur (p_deptid IN NUMBER) IS
    SELECT * FROM employees WHERE department_id = p_deptid;
  l_emp employees%ROWTYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Getting employees from department 30');
  OPEN emp_cur(30);
  LOOP
    FETCH emp_cur INTO l_emp;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee id ' || l_emp.employee_id || ' is ' || l_emp.first_name || ' ' || l_emp.last_name);
  END LOOP;
  CLOSE emp_cur;

  DBMS_OUTPUT.PUT_LINE('Getting employees from department 90');
  OPEN emp_cur(90);
  LOOP
    FETCH emp_cur INTO l_emp;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee id ' || l_emp.employee_id || ' is ' || l_emp.first_name || ' ' || l_emp.last_name);
  END LOOP;
  CLOSE emp_cur;
END;
/

四、關鍵屬性速查

五、REF CURSOR 典型用法

TYPE ref_cur_t IS REF CURSOR RETURN employees%ROWTYPE;
PROCEDURE get_emps(p_deptid IN NUMBER, p_rc OUT ref_cur_t) IS
BEGIN
  OPEN p_rc FOR SELECT * FROM employees WHERE department_id = p_deptid;
END;

優勢：解耦查詢邏輯與調用端，支援動態 WHERE 子句、跨會話/進程傳遞結果集、Java/.NET 等中間層直接消費。

六、最佳實踐提醒

• 循環中務必 EXIT WHEN cursor%NOTFOUND，避免無限循環；
• 異常處理塊中 CLOSE 游標，防止游標洩漏；
• 大結果集考慮 BULK COLLECT ... LIMIT 批量獲取，減少上下文切換；
• 靜態 SQL 優先用顯式游標，動態 SQL 必須用 REF CURSOR 或 EXECUTE IMMEDIATE。