Oracle PL/SQL 存儲過程核心語法與實戰範例

基本結構

CREATE OR REPLACE PROCEDURE proc_name (
    p_in_param  IN  VARCHAR2,
    p_out_param OUT NUMBER
) IS
    v_local_var NUMBER := 0;  -- 宣告區
BEGIN
    -- 執行區
    v_local_var := p_in_param + 10;
    p_out_param := v_local_var;
EXCEPTION
    WHEN OTHERS THEN
        -- 例外處理
        RAISE;
END proc_name;

變數賦值與流程控制

-- 賦值
v_var := 100;

-- IF 條件
IF v_sal > 5000 THEN
    v_bonus := v_sal * 0.1;
ELSIF v_sal > 3000 THEN
    v_bonus := v_sal * 0.05;
ELSE
    v_bonus := 0;
END IF;

-- FOR 迴圈 (游標)
FOR rec IN (SELECT ename FROM emp WHERE deptno = 10) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.ename);
END LOOP;

-- FOR 迴圈 (數值範圍)
FOR i IN 1..10 LOOP
    INSERT INTO log VALUES (i, SYSDATE);
END LOOP;

-- WHILE 迴圈
WHILE v_cnt < 100 LOOP
    v_cnt := v_cnt + 1;
END LOOP;

集合 (集合類型/陣列)

Oracle 無原生陣列，使用 集合 模擬：

-- 1. 定義於 Package 供共用
CREATE OR REPLACE PACKAGE pkg_types IS
    TYPE rec_emp IS RECORD (ename VARCHAR2(20), sal NUMBER);
    TYPE tab_emp IS TABLE OF rec_emp INDEX BY BINARY_INTEGER;
END pkg_types;

-- 2. 存儲過程中使用
CREATE OR REPLACE PROCEDURE proc_array (p_data IN pkg_types.tab_emp) IS
BEGIN
    FOR i IN 1..p_data.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(p_data(i).ename || ':' || p_data(i).sal);
    END LOOP;
END;

集合索引從 1 起算，不同於 Java/C 的 0 起始。

游標操作

隱性游標 (FOR LOOP 自動管理)

FOR rec IN (SELECT * FROM emp WHERE deptno = p_deptno) LOOP
    -- 自動 OPEN/FETCH/CLOSE
    UPDATE emp SET sal = sal * 1.1 WHERE empno = rec.empno;
END LOOP;

顯性游標 (需手動控制)

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;
        DBMS_OUTPUT.PUT_LINE(v_empno || '-' || v_ename);
    END LOOP;
    CLOSE c_emp;
END;

SYS_REFCURSOR (可當參數傳遞)

CREATE OR REPLACE PROCEDURE get_emp_rc (p_rc OUT SYS_REFCURSOR) IS
BEGIN
    OPEN p_rc FOR SELECT * FROM emp WHERE deptno = 20;
END;

-- 呼叫端
DECLARE
    v_rc SYS_REFCURSOR;
    v_rec emp%ROWTYPE;
BEGIN
    get_emp_rc(v_rc);
    LOOP
        FETCH v_rc INTO v_rec;
        EXIT WHEN v_rc%NOTFOUND;
        -- 處理 v_rec
    END LOOP;
    CLOSE v_rc;
END;

完整實戰範例：學生成績自動計算

CREATE OR REPLACE PROCEDURE auto_compute (p_step IN NUMBER) IS
    v_rc       SYS_REFCURSOR;
    v_total    NUMBER;
    v_avg      NUMBER;
    v_std_id   VARCHAR2(30);
    v_math     NUMBER;
    -- 其餘科目變數...
    v_comment  pkg_types.tab_comment;  -- 自定義集合
BEGIN
    -- 1. 取得課外評價
    get_comment(v_comment);

    -- 2. 開啟學生游標
    OPEN v_rc FOR
        SELECT std_id, math, article, language, music, sport
        FROM student WHERE step = p_step;

    LOOP
        FETCH v_rc INTO v_std_id, v_math, v_article, v_lang, v_music, v_sport;
        EXIT WHEN v_rc%NOTFOUND;

        v_total := v_math + v_article + v_lang + v_music + v_sport;

        -- 3. 檢查課外評價 A 加 20 分
        FOR i IN 1..v_comment.COUNT LOOP
            IF v_std_id = v_comment(i).std_id AND v_comment(i).comment = 'A' THEN
                v_total := v_total + 20;
                GOTO calc_avg;  -- 跳出內層迴圈
            END IF;
        END LOOP;

        <>
        v_avg := v_total / 5;

        UPDATE student SET total = v_total, average = v_avg
        WHERE std_id = v_std_id;
    END LOOP;
    CLOSE v_rc;
END auto_compute;

關鍵技巧總結

• 使用 %TYPE/%ROWTYPE 錨定型別，避免結構變更失效
• 游標屬性 %FOUND/%NOTFOUND/%ROWCOUNT 控制流程
• GOTO 配合標籤可跳出多層迴圈 (慎用)
• 集合定義於 Package 實現跨程式共用
• DBMS_OUTPUT.PUT_LINE 除錯輸出，記得 SET SERVEROUTPUT ON