Oracle Cursor 游標完整應用指南：隱式、顯式、REF CURSOR 與 %TYPE

一、隱式游標

PL/SQL 中的 SELECT ... INTO 即為隱式游標，僅支援單筆查詢結果。若回傳超過一筆會拋出 TOO_MANY_ROWS；無資料則拋出 NO_DATA_FOUND。

語法

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

範例

DECLARE
    v_empno  SCOTT.EMP.EMPNO%TYPE;
    v_ename  SCOTT.EMP.ENAME%TYPE;
BEGIN
    SELECT empno, ename
    INTO v_empno, v_ename
    FROM scott.emp
    WHERE empno = 7369;
    DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('查無資料');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('資料筆數超過一筆');
END;
/

二、%TYPE 與 %ROWTYPE 屬性

• %TYPE：參考欄位、變數、常數、游標、記錄的資料型別與長度，資料結構變更時自動同步，免修改程式碼。
• %ROWTYPE：參考整個資料表或游標的列結構，宣告記錄型別變數。

範例

DECLARE
    v_a NUMBER(5) := 10;
    v_b v_a%TYPE := 15;      -- 繼承 v_a 型別
    v_c v_a%TYPE;            -- 未初始化為 NULL
    v_emp  SCOTT.EMP%ROWTYPE; -- 參考整張表結構
BEGIN
    DBMS_OUTPUT.PUT_LINE('v_a='||v_a||' v_b='||v_b||' v_c='||v_c);
END;
/

三、顯式游標

適用於多筆查詢結果，需經過宣告、開啟、擷取、關閉四步驟。

基本結構

DECLARE
    CURSOR cur_name IS SELECT ...;
    v_record cur_name%ROWTYPE;
BEGIN
    OPEN cur_name;
    LOOP
        FETCH cur_name INTO v_record;
        EXIT WHEN cur_name%NOTFOUND;
        -- 處理 v_record
    END LOOP;
    CLOSE cur_name;
END;
/

游標屬性

四、游標 FOR 迴圈（簡化寫法）

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

五、帶參數游標

DECLARE
    CURSOR c_emp(p_deptno NUMBER) IS
        SELECT empno, ename FROM scott.emp WHERE deptno = p_deptno;
    v_ename VARCHAR2(20);
BEGIN
    OPEN c_emp(20);
    LOOP
        FETCH c_emp INTO v_empno, v_ename;
        EXIT WHEN c_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
    END LOOP;
    CLOSE c_emp;
END;
/

六、REF CURSOR（動態游標）

適用於回傳給呼叫端（如 Java、.NET）或動態組裝 SQL。

弱型別

TYPE ref_cursor IS REF CURSOR;
v_cur ref_cursor;
OPEN v_cur FOR 'SELECT * FROM emp WHERE deptno = :1' USING 20;

強型別

TYPE emp_ref_cur IS REF CURSOR RETURN scott.emp%ROWTYPE;
v_cur emp_ref_cur;

七、其他 DML 在 PL/SQL 中的使用

INSERT、UPDATE、DELETE、LOCK TABLE 語法與 SQL 相同，可直接引用宣告區變數，注意巢狀區塊的變數作用域。

範例：帶參數預存程序刪除員工

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;
    DELETE FROM emp WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('已刪除員工: ' || v_ename);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('查無該員工編號');
END;
/

八、最佳實務建議

• 單筆查詢優先用 SELECT INTO，多筆用顯式游標或 FOR 迴圈。
• 善用 %TYPE/%ROWTYPE 降低維護成本。
• 游標務必 CLOSE，避免游標洩漏導致 ORA-01000: maximum open cursors exceeded。
• 大量資料處理考慮 BULK COLLECT 與 FORALL 提升效能。
• 動態 SQL 需防範 SQL Injection，使用繫結變數而非字串拼接。