您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle PL/SQL 游標完整指南：隱式、顯式與 REF CURSOR 實戰

編輯：Oracle 資料庫相關知識

掌握游標，掌控 PL/SQL 資料處理核心

SQL 是存取 Oracle 的語言，PL/SQL 擴充了程序邏輯並支援 DML 與交易控制。DDL 不直接支援於 PL/SQL 區塊，需透過 DBMS_SQL 或 EXECUTE IMMEDIATE 動態執行。本文系統梳理游標類型、屬性與實務應用。

SELECT INTO：單筆查詢的標準作法

SELECT [DISTINCT | ALL] {* | column[, ...]}
INTO {variable[, ...] | record}
FROM {table | (subquery)} [alias]
WHERE ...;

限制：只能返回單筆資料。超過一筆需改用顯式游標。INTO 變量數量與型態必須與 SELECT 列表一致，也可使用記錄型態變數。

%TYPE 與 %ROWTYPE：動態錨定資料型態

v_empno  SCOTT.EMP.EMPNO%TYPE;
v_salary EMP.SALARY%TYPE;
v_emp_rec EMP%ROWTYPE;

優勢：參考欄位/表定義自動繼承型態與長度。DDL 變更時程式碼無需修改，極大降低維護成本。變數、常數、游標、記錄皆可使用 %TYPE。

DML 執行後的隱式游標屬性

每次執行 DML（INSERT/UPDATE/DELETE/SELECT INTO），PL/SQL 自動開啟隱式游標 SQL，完成後關閉。四大屬性：

屬性	型態	含義
`SQL%FOUND`	BOOLEAN	DML 影響至少一筆 / SELECT INTO 返回一筆 = TRUE
`SQL%NOTFOUND`	BOOLEAN	`SQL%FOUND` 邏輯相反
`SQL%ROWCOUNT`	NUMBER	DML 影響筆數 / SELECT INTO 成功=1 失敗=0
`SQL%ISOPEN`	BOOLEAN	隱式游標永遠 FALSE（執行即開啟、結束即關閉）

顯式游標：多筆資料處理的標準流程

DECLARE
    CURSOR c_emp IS SELECT empno, ename FROM emp WHERE deptno = 10;
    v_empno emp.empno%TYPE;
    v_ename emp.ename%TYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO v_empno, v_ename;
        EXIT WHEN c_emp%NOTFOUND;
        -- 處理邏輯
    END LOOP;
    CLOSE c_emp;
END;

顯式游標屬性：%FOUND、%NOTFOUND、%ROWCOUNT、%ISOPEN，用法同隱式但對應顯式游標名稱。

游標 FOR 迴圈：簡化開啟/提取/關閉

FOR rec IN (SELECT empno, ename FROM emp WHERE deptno = 10) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.empno || ' ' || rec.ename);
END LOOP;

隱式處理 OPEN/FETCH/CLOSE，程式碼更簡潔安全。

參數化游標與 REF CURSOR：動態與模組化

CURSOR c_emp(p_deptno NUMBER) IS
    SELECT * FROM emp WHERE deptno = p_deptno;

-- REF CURSOR 傳遞結果集給客戶端/其他程序
TYPE ref_cur_t IS REF CURSOR;
c_ref ref_cur_t;
OPEN c_ref FOR SELECT * FROM emp WHERE deptno = 10;

交易控制：COMMIT、ROLLBACK、SAVEPOINT

交易是邏輯工作單元。SET TRANSACTION READ ONLY 實現交易層級讀一致性；SET TRANSACTION USE ROLLBACK SEGMENT 指定大型回滾段避免 ORA-01555。

實務範例：員工離職處理程序

CREATE OR REPLACE PROCEDURE fire_employee(p_empno IN NUMBER) AS
    v_ename emp.ename%TYPE;
BEGIN
    SELECT ename INTO v_ename FROM emp WHERE empno = p_empno;
    INSERT INTO former_emp(empno, ename) VALUES (p_empno, v_ename);
    DELETE FROM emp WHERE empno = p_empno;
    UPDATE former_emp SET date_deleted = SYSDATE WHERE empno = p_empno;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee Number Not Found!');
END;

游標是 PL/SQL 處理集合資料的核心機制。熟練運用隱式/顯式/參數化/REF CURSOR，配合屬性判斷流程控制，能編寫高效、健壯的資料庫程式邏輯。