Oracle 游標進階：顯式/隱式游標屬性、FOR 循環、動態 REF CURSOR 實戰詳解

顯式游標 (Explicit Cursor) 完整生命周期

DECLARE
  CURSOR c_stu IS SELECT studno, studname FROM studinfo WHERE classid = '20010505';
  v_rec c_stu%ROWTYPE;
BEGIN
  OPEN c_stu;
  LOOP
    FETCH c_stu INTO v_rec;
    EXIT WHEN c_stu%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_rec.studno || ' ' || v_rec.studname);
  END LOOP;
  CLOSE c_stu;
END;

游標 FOR 循環 (隱式 OPEN/FETCH/CLOSE)

BEGIN
  FOR r IN (SELECT studno, studname FROM studinfo WHERE classid = '20010505') LOOP
    DBMS_OUTPUT.PUT_LINE(r.studno || ' ' || r.studname);
  END LOOP;
END;

優點：代碼簡潔、自動處理屬性、無需聲明記錄變量。

游標屬性對比

關鍵差異：顯式游標 %ROWCOUNT 在循環中遞增 (1,2,3...)；隱式游標 SQL%ROWCOUNT 執行結束後直接是總行數。

參數化游標

CURSOR c_dept(p_loc VARCHAR2) IS
  SELECT * FROM dept WHERE loc = p_loc;
BEGIN
  FOR r IN c_dept('NEW YORK') LOOP ... END LOOP;

REF CURSOR (動態游標) —— 跨層傳遞結果集

TYPE t_cur IS REF CURSOR;
PROCEDURE get_emp(p_deptno NUMBER, p_rc OUT t_cur) IS
BEGIN
  OPEN p_rc FOR SELECT * FROM emp WHERE deptno = p_deptno;
END;
-- Java/.NET/PLSQL 均可消費

實戰案例：班級成績排名與極值查找

需求 1：排名輸出

CREATE OR REPLACE PROCEDURE show_avg_desc IS
  CURSOR c_rank IS
    SELECT ROWNUM rn, studno, studname, classname, avg_score
    FROM (
      SELECT s.studno, studname, classname, ROUND(AVG(studscore),2) avg_score
      FROM studinfo s, studscoreinfo ss, classinfo c
      WHERE s.studno=ss.studno AND s.classid=c.classid
        AND s.classid='20010505'
      GROUP BY s.studno, studname, classname
      ORDER BY AVG(studscore) DESC
    );
BEGIN
  FOR r IN c_rank LOOP
    DBMS_OUTPUT.PUT_LINE('學號:'||r.studno||' 姓名:'||r.studname||
                         ' 班級:'||r.classname||' 平均分:'||r.avg_score||' 名次:'||r.rn);
  END LOOP;
END;

需求 2：不使用 MAX/MIN 找最高/最低分 (游標兩遍掃描)

CREATE OR REPLACE PROCEDURE find_max_min_avg IS
  CURSOR c_avg IS
    SELECT s.studno, studname, classname, ROUND(AVG(studscore),2) avg_score
    FROM studinfo s, studscoreinfo ss, classinfo c
    WHERE s.studno=ss.studno AND s.classid=c.classid
      AND s.classid='20010505'
    GROUP BY s.studno, studname, classname
    ORDER BY AVG(studscore) DESC;
  v_cnt NUMBER := 0;
  v_idx NUMBER := 0;
BEGIN
  -- 第一遍計數
  FOR r IN c_avg LOOP v_cnt := v_cnt + 1; END LOOP;
  -- 第二遍取首尾
  FOR r IN c_avg LOOP
    v_idx := v_idx + 1;
    IF v_idx = 1 OR v_idx = v_cnt THEN
      DBMS_OUTPUT.PUT_LINE('學號:'||r.studno||' 姓名:'||r.studname||
                           ' 班級:'||r.classname||' 平均分:'||r.avg_score);
    END IF;
  END LOOP;
END;

性能提示

• 大結果集優先 BULK COLLECT ... LIMIT 分批處理，減少上下文切換。
• 避免在游標循環內執行 DML/查詢，改用集合操作或單一 SQL。
• REF CURSOR 適合異構調用，PL/SQL 內部優先強類型游標。