Oracle 預存程序語法全攻略：無參、帶參、IN/OUT/IN OUT 與呼叫方式

預存程序三段式結構

所有 Oracle 預存程序皆包含：

1. 宣告區（DECLARE / AS 之後，BEGIN 之前）：變數、游標、型別、例外宣告。
2. 執行區（BEGIN ... END）：業務邏輯、SQL 敘述、流程控制。
3. 例外處理區（EXCEPTION ... WHEN ... THEN ...）：錯誤捕獲與處理（可選）。

語法範本與實例

1. 無參數程序

CREATE OR REPLACE PROCEDURE NoParPro AS
    -- 宣告區
    v_msg VARCHAR2(100);
BEGIN
    -- 執行區
    v_msg := 'Hello, Oracle Procedure!';
    DBMS_OUTPUT.PUT_LINE(v_msg);
EXCEPTION
    -- 例外區
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END NoParPro;
/
-- 呼叫
BEGIN
    NoParPro;
END;
/
EXEC NoParPro;  -- SQL*Plus 簡寫

2. 單一 IN 參數（查詢員工姓名）

CREATE OR REPLACE PROCEDURE queryempname (
    p_empno IN emp.empno%TYPE
) AS
    v_ename emp.ename%TYPE;
    v_job   emp.job%TYPE;
BEGIN
    SELECT ename, job INTO v_ename, v_job
    FROM emp
    WHERE empno = p_empno;

    DBMS_OUTPUT.PUT_LINE('員工: ' || v_ename || ', 職位: ' || v_job);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('查無此員工編號: ' || p_empno);
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('資料異常：單一編號對應多筆記錄');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('其他錯誤: ' || SQLERRM);
END queryempname;
/
-- 呼叫
EXEC queryempname(7369);

3. 多參數混合模式（IN、OUT、IN OUT）

CREATE OR REPLACE PROCEDURE runbyparameters (
    p_sal   IN     emp.sal%TYPE,      -- 僅輸入
    p_ename OUT    VARCHAR2,          -- 僅輸出
    p_job   IN OUT VARCHAR2           -- 輸入輸出雙向
) AS
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM emp
    WHERE sal > p_sal AND job = p_job;

    IF v_cnt = 1 THEN
        SELECT ename INTO p_ename
        FROM emp
        WHERE sal > p_sal AND job = p_job;
        p_job := 'FOUND_' || p_job;  -- 修改 IN OUT 參數
    ELSE
        p_ename := 'NOT_UNIQUE';
    END IF;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        p_ename := 'MULTI_ROWS';
    WHEN OTHERS THEN
        p_ename := 'ERROR: ' || SQLERRM;
END runbyparameters;
/
-- 呼叫方式一：位置標記法（須依序）
DECLARE
    v_sal   emp.sal%TYPE := 1100;
    v_ename VARCHAR2(40);
    v_job   VARCHAR2(40) := 'CLERK';
BEGIN
    runbyparameters(v_sal, v_ename, v_job);
    DBMS_OUTPUT.PUT_LINE('結果: ' || v_ename || ' | ' || v_job);
END;
/

-- 呼叫方式二：名稱標記法（順序任意，強烈建議）
DECLARE
    v_sal   emp.sal%TYPE := 1100;
    v_ename VARCHAR2(40);
    v_job   VARCHAR2(40) := 'CLERK';
BEGIN
    runbyparameters(p_sal => v_sal, p_ename => v_ename, p_job => v_job);
    DBMS_OUTPUT.PUT_LINE('結果: ' || v_ename || ' | ' || v_job);
END;
/

參數模式深度解析

最佳實踐建議

1. 慣用 %TYPE / %ROWTYPE：參數與變數型別錨定資料表欄位，結構變更自動適配。
2. 優先使用名稱標記法呼叫：可讀性高、免受參數順序變更影響。
3. OUT/IN OUT 參數勿設預設值：語法不允許，且邏輯上無意義。
4. 例外處理要分層：先捕獲具體例外（NO_DATA_FOUND, TOO_MANY_ROWS），最後才用 WHEN OTHERS。
5. 大量資料處理用 BULK COLLECT / FORALL：避免逐行處理效能陷阱。
6. 避免在程序中 COMMIT/ROLLBACK：交易控制權交給呼叫端，保持原子性彈性。

常見錯誤與除錯技巧

• PLS-00306：參數個數/型別不符 → 檢查呼叫端與定義一致性。
• ORA-06502：數值/字串轉換溢位 → 確認參數長度與精度足夠。
• 除錯利器：DBMS_OUTPUT.PUT_LINE 印關鍵變數值；SQL Developer / PL/SQL Developer 內建除錯器可斷點單步。

總結

掌握「三段式結構、四種參數模式、兩種呼叫法、例外分層處理」，即可撰寫健壯、可維護、高效能的 Oracle 預存程序。建議將常用範本存為程式碼片段，加速開發流程。