顯式游標 vs 隱式游標
| 特性 | 顯式游標 | 隱式游標 |
|---|
| 控制權 | 程式設計師完全控制 | PL/SQL 引擎自動管理 |
| 適用場景 | 多列查詢、需逐列處理、複雜邏輯 | 單列查詢、DML 操作 |
| 生命週期 | OPEN → FETCH → CLOSE | 自動開啟關閉 |
| 參數支援 | 支援參數化 | 不支援 |
| 屬性存取 | cursor_name%ROWCOUNT 等 | SQL%ROWCOUNT 等 |
語法結構
CURSOR cursor_name [(parameter_list)] [RETURN return_type] IS
select_statement;
參數化游標——核心優勢
參數化游標允許同一游標定義以不同參數重複開啟，避免重複撰寫相似查詢。
完整實戰範例
SET SERVEROUTPUT ON
DECLARE
-- 1. 宣告參數化游標
CURSOR emp_cur(p_dept_id IN NUMBER) IS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = p_dept_id;
-- 2. 定義接收變數（使用 %ROWTYPE 自適應）
l_emp employees%ROWTYPE;
BEGIN
-- 3. 第一次開啟：部門 30
DBMS_OUTPUT.PUT_LINE('=== 部門 30 員工 ===');
OPEN emp_cur(30);
LOOP
FETCH emp_cur INTO l_emp;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || l_emp.employee_id ||
' Name: ' || l_emp.first_name || ' ' || l_emp.last_name ||
' Salary: ' || l_emp.salary);
END LOOP;
CLOSE emp_cur;
-- 4. 第二次開啟：部門 90（重用同一游標定義）
DBMS_OUTPUT.PUT_LINE('=== 部門 90 員工 ===');
OPEN emp_cur(90);
LOOP
FETCH emp_cur INTO l_emp;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || l_emp.employee_id ||
' Name: ' || l_emp.first_name || ' ' || l_emp.last_name);
END LOOP;
CLOSE emp_cur;
END;
/
進階模式
1. 游標 FOR 迴圈（自動 OPEN/FETCH/CLOSE）
FOR emp_rec IN emp_cur(30) LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ': ' || emp_rec.first_name);
END LOOP;
-- 等同於隱式游標，但保有參數化優勢
2. 帶 RETURN 型別的強型別游標
CREATE TYPE emp_rec_type AS OBJECT (
emp_id NUMBER, emp_name VARCHAR2(100), dept_name VARCHAR2(50)
);
CREATE TYPE emp_tab_type AS TABLE OF emp_rec_type;
CURSOR emp_cur(p_dept_id NUMBER) RETURN emp_rec_type IS
SELECT emp_obj(e.employee_id, e.first_name||' '||e.last_name, d.department_name)
FROM employees e JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id = p_dept_id;
3. 多參數游標
CURSOR emp_cur(p_dept_id NUMBER, p_min_sal NUMBER, p_max_sal NUMBER) IS
SELECT * FROM employees
WHERE department_id = p_dept_id
AND salary BETWEEN p_min_sal AND p_max_sal;
4. 預設參數值
CURSOR emp_cur(p_dept_id NUMBER := 10) IS ... -- 未傳參數時預設 10
游標屬性完整對照
| 屬性 | 意義 | 範例用法 |
|---|
%FOUND | 最近 FETCH 找到資料 | IF emp_cur%FOUND THEN ... |
%NOTFOUND | 最近 FETCH 未找到資料 | EXIT WHEN emp_cur%NOTFOUND; |
%ROWCOUNT | 目前已擷取列數 | DBMS_OUTPUT.PUT_LINE('Fetched ' || emp_cur%ROWCOUNT); |
%ISOPEN | 游標是否開啟中 | IF emp_cur%ISOPEN THEN CLOSE emp_cur; END IF; |
常見錯誤與除錯
| 錯誤 | 原因 | 解決 |
|---|
ORA-06511: cursor already open | 重複 OPEN 未 CLOSE | 檢查邏輯路徑，確保每個 OPEN 有對應 CLOSE；或用 FOR 迴圈 |
ORA-01001: invalid cursor | 操作未開啟或已關閉的游標 | 檢查 %ISOPEN；避免在 CLOSE 後 FETCH |
ORA-06504: rowtype mismatch | FETCH 目標變數與 SELECT 列不符 | 使用 %ROWTYPE 或明確宣告對應 RECORD 型別 |
| 無限迴圈 | 忘記 FETCH 或 EXIT 條件 | 確保迴圈內有 FETCH 且有 EXIT WHEN %NOTFOUND |
效能最佳實務
- 使用 BULK COLLECT + LIMIT：大量資料分批提取，減少 Context Switch
OPEN emp_cur(30);
LOOP
FETCH emp_cur BULK COLLECT INTO l_emp_tab LIMIT 1000;
EXIT WHEN l_emp_tab.COUNT = 0;
FORALL i IN 1..l_emp_tab.COUNT
UPDATE ...; -- 批次 DML
END LOOP;
CLOSE emp_cur;
- 避免在迴圈內 COMMIT**：除非業務要求，否則批次處理完再 COMMIT
- 參數化游標重用**：同一邏輯不同參數，優於動態 SQL
- 關閉游標**：例外處理區塊務必確保 CLOSE
完整範本：安全的參數化游標處理
DECLARE
CURSOR c_data(p_param NUMBER) IS SELECT * FROM big_table WHERE id > p_param;
TYPE t_tab IS TABLE OF big_table%ROWTYPE;
l_tab t_tab;
BEGIN
OPEN c_data(1000);
<>
LOOP
BEGIN
FETCH c_data BULK COLLECT INTO l_tab LIMIT 500;
EXIT WHEN l_tab.COUNT = 0;
-- 處理邏輯
FORALL i IN 1..l_tab.COUNT
INSERT INTO target_table VALUES l_tab(i);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
RAISE;
END;
END LOOP fetch_loop;
CLOSE c_data;
EXCEPTION
WHEN OTHERS THEN
IF c_data%ISOPEN THEN CLOSE c_data; END IF;
RAISE;
END;
/
