萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle Cursor 游標完全攻略:顯式/隱式/參數/REF/FOR LOOP/可更新/屬性

游標分類

類型宣告方式適用場景
顯式游標`CURSOR c_name IS SELECT ...`需精確控制 OPEN/FETCH/CLOSE 流程
隱式游標DML / `SELECT INTO` 自動建立單筆查詢、DML 影響行數檢查
參數化游標`CURSOR c(p NUMBER) IS SELECT ... WHERE id=p`重複使用、避免硬編碼
REF CURSOR`TYPE t_ref IS REF CURSOR; c t_ref;`動態 SQL、跨程式傳遞結果集
FOR LOOP 游標`FOR r IN c LOOP ... END LOOP`最簡潔、自動 OPEN/CLOSE、記錄變數隱式宣告

游標四大屬性

屬性含義隱式游標前綴
%FOUND最近 FETCH/DML 影響 ≥1 列SQL%FOUND
%NOTFOUND最近 FETCH/DML 影響 0 列SQL%NOTFOUND
%ISOPEN游標是否開啟 (顯式)SQL%ISOPEN 永遠 FALSE
%ROWCOUNT累計 FETCH/DML 影響列數SQL%ROWCOUNT

注意:屬性回傳 BOOLEAN/數字,**無法直接 `DBMS_OUTPUT.PUT_LINE`**,需用 `CASE WHEN` 或 `IF` 轉字串。

範例 1:標準顯式游標

DECLARE
  CURSOR c_dept IS SELECT * FROM dept;
  r_dept dept%ROWTYPE;
BEGIN
  OPEN c_dept;
  FETCH c_dept INTO r_dept;
  WHILE c_dept%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(r_dept.deptno || ' ' || r_dept.dname || ' ' || r_dept.loc);
    FETCH c_dept INTO r_dept;
  END LOOP;
  CLOSE c_dept;
END;
/

範例 2:參數化游標

DECLARE
  CURSOR c_dept(p_no NUMBER) IS SELECT * FROM dept WHERE deptno = p_no;
  r_dept dept%ROWTYPE;
BEGIN
  OPEN c_dept(10);
  LOOP
    FETCH c_dept INTO r_dept;
    EXIT WHEN c_dept%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Dept=' || r_dept.dname);
  END LOOP;
  CLOSE c_dept;
END;
/

範例 3:FOR LOOP (最推薦)

DECLARE
  CURSOR c_dept(p_no NUMBER) IS SELECT * FROM dept WHERE deptno = p_no;
BEGIN
  FOR r IN c_dept(10) LOOP
    DBMS_OUTPUT.PUT_LINE('Dept=' || r.dname);
  END LOOP;
  -- 自動 OPEN/FETCH/CLOSE,r 隱式為 RECORD
END;
/

範例 4:FETCH INTO 變數 (非 %ROWTYPE)

DECLARE
  v_dno  NUMBER;
  v_dname VARCHAR2(14);
  CURSOR c_dept IS SELECT deptno, dname FROM dept;
BEGIN
  OPEN c_dept;
  FETCH c_dept INTO v_dno, v_dname;
  LOOP
    EXIT WHEN c_dept%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_dno || ' ' || v_dname);
    FETCH c_dept INTO v_dno, v_dname;
  END LOOP;
  CLOSE c_dept;
END;
/

變數個數**必須**與 SELECT 列數一致。

範例 5:%ROWCOUNT 計數器

DECLARE
  CURSOR c_dept IS SELECT dname FROM dept;
  v_name VARCHAR2(14);
BEGIN
  OPEN c_dept;
  LOOP
    FETCH c_dept INTO v_name;
    EXIT WHEN c_dept%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(c_dept%ROWCOUNT);  -- 輸出 1,2,3,4
  END LOOP;
  CLOSE c_dept;
END;
/

範例 6:可更新游標 (FOR UPDATE / WHERE CURRENT OF)

DECLARE
  v_dname VARCHAR2(20);
  CURSOR c_dept IS SELECT dname FROM dept FOR UPDATE;
BEGIN
  OPEN c_dept;
  LOOP
    FETCH c_dept INTO v_dname;
    EXIT WHEN c_dept%NOTFOUND;
    UPDATE dept SET dname = RTRIM(dname, '_t') WHERE CURRENT OF c_dept;
  END LOOP;
  CLOSE c_dept;
END;
/

關鍵:`FOR UPDATE` 鎖定列、`WHERE CURRENT OF` 精準更新游標當前列,避免重新定位。

範例 7:隱式游標 FOR LOOP

BEGIN
  FOR r IN (SELECT dname FROM dept) LOOP
    DBMS_OUTPUT.PUT_LINE(r.dname);
  END LOOP;
END;
/

範例 8:REF CURSOR 動態查詢

DECLARE
  TYPE t_ref IS REF CURSOR;
  c       t_ref;
  r       person%ROWTYPE;
BEGIN
  OPEN c FOR 'SELECT * FROM person';
  LOOP
    FETCH c INTO r;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('name=' || r.name);
  END LOOP;
  CLOSE c;
END;
/

常見錯誤與對策

錯誤原因修正
%FOUND 永遠 FALSEWHILE 前未先 FETCHOPEN 後先 FETCH 一次再進迴圈
FETCH INTO 變數數量不符SELECT 列數 ≠ 變數個數對齊列數或改用 %ROWTYPE
ORA-01001: invalid cursor游標已 CLOSE 再 FETCH檢查邏輯流程,確保單一 OPEN/CLOSE 配對
FOR UPDATE 導致死鎖多 Session 反向處理同組列統一處理順序 (如 ORDER BY PK)、縮短交易

結語

游標是 PL/SQL 處理集合資料的核心。**優先用 FOR LOOP 簡潔安全**;需動態 SQL 用 REF CURSOR;需逐列更新用 `FOR UPDATE + WHERE CURRENT OF`。熟練掌握四大屬性與八種範例模板,覆蓋 95% 以上業務場景。

copyright © 萬盛學電腦網 all rights reserved