Oracle 存儲過程核心語法全解析：從變量聲明到游標、數組、異常處理的完整實戰範本

基本結構與參數模式

CREATE OR REPLACE PROCEDURE proc_name (
    p_in_param   IN  VARCHAR2,      -- 輸入參數（只讀）
    p_out_param  OUT NUMBER,        -- 輸出參數（寫入後返回）
    p_inout_param IN OUT DATE       -- 輸入輸出參數
) IS
    -- 變量聲明區
    v_local_var  NUMBER := 0;       -- 區域變量可初始化
BEGIN
    -- 執行體
    NULL;
EXCEPTION
    WHEN OTHERS THEN
        RAISE;                      -- 重拋異常，保持調用棧
END proc_name;
/

流程控制語句

條件判斷

IF v_sal > 10000 THEN
    v_bonus := v_sal * 0.1;
ELSIF v_sal > 5000 THEN
    v_bonus := v_sal * 0.05;
ELSE
    v_bonus := 100;
END IF;

-- CASE 表達式（可用於 SQL 內部）
v_grade := CASE
    WHEN v_score >= 90 THEN 'A'
    WHEN v_score >= 80 THEN 'B'
    ELSE 'C'
END;

循環結構

集合類型：PL/SQL 的「數組」

三大集合類型對比

關聯數組實戰範本（最常用於臨時計算）

-- Package 定義公共類型
CREATE OR REPLACE PACKAGE pkg_types IS
    TYPE rec_emp IS RECORD (
        empno   NUMBER(4),
        ename   VARCHAR2(20),
        sal     NUMBER(7,2)
    );
    TYPE tab_emp IS TABLE OF rec_emp INDEX BY PLS_INTEGER;
END pkg_types;
/

-- 存儲過程使用
CREATE OR REPLACE PROCEDURE proc_batch_update (p_emps IN pkg_types.tab_emp) IS
BEGIN
    FOR i IN 1..p_emps.COUNT LOOP
        UPDATE emp SET sal = p_emps(i).sal WHERE empno = p_emps(i).empno;
    END LOOP;
END;
/

游標進階：隱式 vs 顯式 vs 弱/強類型 REF CURSOR

強類型 REF CURSOR（編譯期檢查）

TYPE ref_emp IS REF CURSOR RETURN emp%ROWTYPE;
v_cur ref_emp;
OPEN v_cur FOR SELECT * FROM emp WHERE deptno = 10;

弱類型 SYS_REFCURSOR（通用，跨模組傳遞首選）

CREATE OR REPLACE PROCEDURE get_emps (p_deptno IN NUMBER, p_rc OUT SYS_REFCURSOR) IS
BEGIN
    OPEN p_rc FOR SELECT * FROM emp WHERE deptno = p_deptno;
END;
/

-- 調用端
DECLARE
    v_rc   SYS_REFCURSOR;
    v_rec  emp%ROWTYPE;
BEGIN
    get_emps(10, v_rc);
    LOOP
        FETCH v_rc INTO v_rec;
        EXIT WHEN v_rc%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_rec.ename);
    END LOOP;
    CLOSE v_rc;
END;
/

游標屬性速查

異常處理最佳實踐

DECLARE
    e_dup_val   EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_dup_val, -00001);  -- 綁定 ORA-00001
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = 7788;
    IF v_cnt > 0 THEN
        RAISE e_dup_val;          -- 業務異常主動拋出
    END IF;
    INSERT INTO emp ...;
EXCEPTION
    WHEN e_dup_val THEN
        DBMS_OUTPUT.PUT_LINE('員工編號已存在');
        -- 記錄業務日誌，不回滾外層事務
    WHEN NO_DATA_FOUND THEN
        NULL;                     -- 預期內無數據，靜默處理
    WHEN OTHERS THEN
        -- 記錄系統錯誤日誌表
        INSERT INTO err_log (err_time, err_code, err_msg, sql_text)
        VALUES (SYSDATE, SQLCODE, SQLERRM, $$PLSQL_UNIT);
        RAISE;                    -- 重拋，讓上層決定是否回滾
END;

綜合實戰：學生成績自動計算（含游標、集合、異常、事務控制）

CREATE OR REPLACE PROCEDURE proc_calc_grade (p_term IN NUMBER) IS
    TYPE rec_score IS RECORD (
        std_id   VARCHAR2(20),
        math     NUMBER,
        eng      NUMBER,
        total    NUMBER,
        avg_scr  NUMBER
    );
    TYPE tab_score IS TABLE OF rec_score INDEX BY PLS_INTEGER;
    v_scores tab_score;
    v_rc     SYS_REFCURSOR;
    v_idx    PLS_INTEGER := 0;
BEGIN
    -- 1. 取原始分數
    OPEN v_rc FOR SELECT std_id, math, eng FROM scores WHERE term = p_term;
    LOOP
        FETCH v_rc INTO v_scores(v_idx).std_id, v_scores(v_idx).math, v_scores(v_idx).eng;
        EXIT WHEN v_rc%NOTFOUND;
        v_idx := v_idx + 1;
    END LOOP;
    CLOSE v_rc;

    -- 2. 取課外加分（關聯數組）
    DECLARE
        TYPE rec_bonus IS RECORD (std_id VARCHAR2(20), bonus NUMBER);
        TYPE tab_bonus IS TABLE OF rec_bonus INDEX BY PLS_INTEGER;
        v_bonus tab_bonus;
        v_brc   SYS_REFCURSOR;
    BEGIN
        OPEN v_brc FOR SELECT std_id, DECODE(grade, 'A', 20, 0) FROM extra WHERE term = p_term;
        LOOP
            FETCH v_brc INTO v_bonus(v_bonus.COUNT).std_id, v_bonus(v_bonus.COUNT).bonus;
            EXIT WHEN v_brc%NOTFOUND;
        END LOOP;
        CLOSE v_brc;

        -- 3. 合併計算並批量更新
        FOR i IN 1..v_scores.COUNT LOOP
            v_scores(i).total := v_scores(i).math + v_scores(i).eng;
            FOR j IN 1..v_bonus.COUNT LOOP
                IF v_scores(i).std_id = v_bonus(j).std_id THEN
                    v_scores(i).total := v_scores(i).total + v_bonus(j).bonus;
                    EXIT;
                END IF;
            END LOOP;
            v_scores(i).avg_scr := v_scores(i).total / 2;
        END LOOP;

        FORALL i IN 1..v_scores.COUNT
            UPDATE scores
            SET total = v_scores(i).total, avg_scr = v_scores(i).avg_scr
            WHERE std_id = v_scores(i).std_id AND term = p_term;
    END;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

總結

掌握 參數模式、集合選型、游標類型、FORALL 批量綁定、異常重拋 五大核心，即能編寫高效、健壯、可維護的企業級存儲過程。記住：業務邏輯儘量用 SQL 集合操作（FORALL/MERGE），流程控制留給 PL/SQL。