Oracle 預存程序入門與進階：參數模式、OUT 回傳、動態 SQL、權限控管

基本語法結構

CREATE OR REPLACE PROCEDURE proc_name (
    p_in_param   IN  VARCHAR2,      -- 預設 IN，唯讀
    p_out_param  OUT NUMBER,        -- 唯寫，需在程式內賦值
    p_inout_param IN OUT DATE       -- 可讀可寫
) IS
    v_local_var NUMBER := 0;
BEGIN
    -- 程式邏輯
    p_out_param := v_local_var + 1;
END proc_name;

參數模式詳解

實戰範例集

1. 無參數查詢

CREATE PROCEDURE dept_emp IS
BEGIN
    FOR r IN (SELECT dname, ename FROM dept JOIN emp USING (deptno) WHERE dname = 'ACCOUNTING') LOOP
        DBMS_OUTPUT.PUT_LINE(r.dname || ' - ' || r.ename);
    END LOOP;
END;

2. IN 參數過濾

CREATE PROCEDURE dept_emp_param (p_dept VARCHAR2) IS
BEGIN
    FOR r IN (SELECT ename, job FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = p_dept)) LOOP
        DBMS_OUTPUT.PUT_LINE(r.ename || ' - ' || r.job);
    END LOOP;
END;

3. OUT 參數回傳統計值

CREATE PROCEDURE avg_sal (p_deptno NUMBER, p_avg OUT NUMBER) IS
BEGIN
    SELECT AVG(sal) INTO p_avg FROM emp WHERE deptno = p_deptno;
EXCEPTION
    WHEN NO_DATA_FOUND THEN p_avg := 0;
END;

-- 呼叫
VARIABLE v_avg NUMBER;
EXEC avg_sal(10, :v_avg);
PRINT v_avg;

4. 預設參數值

CREATE PROCEDURE hire_emp (
    p_ename VARCHAR2,
    p_job   VARCHAR2 DEFAULT 'CLERK',
    p_sal   NUMBER  DEFAULT 1000
) IS
BEGIN
    INSERT INTO emp (empno, ename, job, sal, hiredate, deptno)
    VALUES (emp_seq.NEXTVAL, p_ename, p_job, p_sal, SYSDATE, 10);
END;

5. 動態 SQL (EXECUTE IMMEDIATE)

CREATE PROCEDURE trunc_table (p_table VARCHAR2) IS
BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_table;
END;

DDL、未知表名/欄位名時使用動態 SQL；注意 SQL 注入風險，嚴格驗證輸入。

權限與安全

• DEFINER RIGHTS (預設)：以建立者權限執行，呼叫者只需 EXECUTE 權限
• INVOKER RIGHTS：CREATE PROCEDURE ... AUTHID CURRENT_USER，以呼叫者權限執行，需對底表有直接權限
• 敏感操作（TRUNCATE、DROP）建議包裝在 Definer Rights 程序中，最小化授權

除錯與管理

-- 查看程序原始碼
SELECT text FROM user_source WHERE name = 'PROC_NAME' ORDER BY line;

-- 刪除程序
DROP PROCEDURE proc_name;

-- 編譯錯誤查看
SHOW ERRORS PROCEDURE proc_name;
-- 或
SELECT * FROM user_errors WHERE name = 'PROC_NAME';