萬盛學電腦網

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

Oracle 游標完整指南:隱式、顯式、REF CURSOR 與效能優化

PL/SQL 透過游標處理多行查詢結果。掌握四種游標型態與最佳實務,是高效 PL/SQL 開發基石。

1. 隱式游標 (Implicit Cursor)

DML (INSERT/UPDATE/DELETE) 與 SELECT INTO 單行查詢自動建立,屬性:

  • SQL%ROWCOUNT:影響/返回行數
  • SQL%FOUND / %NOTFOUND:是否找到資料
  • SQL%ISOPEN:永遠 FALSE (隱式游標即時關閉)

2. 顯式游標 (Explicit Cursor)

-- 宣告
CURSOR c_emp (p_deptno NUMBER) IS
  SELECT empno, ename, sal FROM emp WHERE deptno = p_deptno;

-- 使用
OPEN c_emp(10);
LOOP
  FETCH c_emp INTO v_empno, v_ename, v_sal;
  EXIT WHEN c_emp%NOTFOUND;
  -- 處理邏輯
END LOOP;
CLOSE c_emp;

游標 FOR 迴圈 (自動 OPEN/FETCH/CLOSE)

FOR rec IN c_emp(10) LOOP
  DBMS_OUTPUT.PUT_LINE(rec.ename || ':' || rec.sal);
END LOOP;

3. 參數化游標

宣告時定義參數,開啟時傳值,提升重用性與安全性 (防注入)。

4. REF CURSOR (弱/強型別游標變數)

-- 弱型別 (通用)
TYPE ref_cursor IS REF CURSOR;
v_cur ref_cursor;

-- 強型別 (綁定回傳結構)
TYPE emp_ref_cursor IS REF CURSOR RETURN emp%ROWTYPE;

-- 動態開啟
OPEN v_cur FOR 'SELECT * FROM emp WHERE deptno = :1' USING 10;
-- 或
OPEN v_cur FOR SELECT * FROM emp WHERE deptno = p_deptno;

適用於:動態 SQL、函數回傳結果集給客戶端 (Java/.NET)、管線函數。

%TYPE / %ROWTYPE 錨定型別

v_empno  emp.empno%TYPE;
v_emp    emp%ROWTYPE;  -- 整行記錄型別

結構變更自動適配,免維護程式碼。

效能優化鐵律

  1. BULK COLLECT + FORALL:批次抓取/批次 DML,減少 Context Switch
    FETCH c BULK COLLECT INTO v_array LIMIT 1000;
    FORALL i IN 1..v_array.COUNT
      UPDATE ... VALUES v_array(i);
  2. 避免逐行提交 (Commit in loop) → 批次提交
  3. 游標查詢只選需要欄位,禁用 SELECT *
  4. 大量資料考慮管線函數 (PIPELINED) 串流回傳

常見陷阱

  • 忘記 CLOSE 導致游標洩漏 (超過 OPEN_CURSORS 限制報 ORA-01000)
  • FOR UPDATE 游標未及時 COMMIT/ROLLBACK 造成阻塞
  • 隱式轉換導致索引失效:WHERE TO_CHAR(date_col) = '2024-01-01'
copyright © 萬盛學電腦網 all rights reserved