基本結構
CREATE [OR REPLACE] PROCEDURE proc_name
(param1 [IN|OUT|IN OUT] datatype, ...)
IS | AS
-- 宣告區 (無 DECLARE 關鍵字)
BEGIN
-- 執行區
EXCEPTION
-- 異常區
END [proc_name];
三種參數模式
| 模式 | 說明 | 預設值 | 可讀/可寫 |
| IN | 唯讀,呼叫端傳入 | 可指定 DEFAULT | R / - |
| OUT | 唯寫,回傳值給呼叫端 | 不可指定 | - / W |
| IN OUT | 可讀可寫 | 不可指定 | R / W |
建立範例
1. 無參數
CREATE OR REPLACE PROCEDURE get_dept_count AS
v_cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO v_cnt FROM DEPT;
DBMS_OUTPUT.PUT_LINE('DEPT 筆數: '||v_cnt);
END;
2. IN 參數
CREATE OR REPLACE PROCEDURE get_sal_by_eno (p_eno IN NUMBER) AS
v_sal EMP.SAL%TYPE;
BEGIN
SELECT SAL INTO v_sal FROM EMP WHERE EMPNO = p_eno;
DBMS_OUTPUT.PUT_LINE('員工 '||p_eno||' 薪資: '||v_sal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('查無此員工');
END;
3. OUT 參數
CREATE OR REPLACE PROCEDURE get_ename_job (
p_eno IN NUMBER,
p_ename OUT VARCHAR2,
p_job OUT VARCHAR2) AS
BEGIN
SELECT ENAME, JOB INTO p_ename, p_job FROM EMP WHERE EMPNO = p_eno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_ename := 'NOT FOUND'; p_job := 'N/A';
END;
4. IN OUT 參數
CREATE OR REPLACE PROCEDURE increase_sal (
p_eno IN NUMBER,
p_sal IN OUT NUMBER) AS
BEGIN
SELECT SAL INTO p_sal FROM EMP WHERE EMPNO = p_eno;
p_sal := p_sal * 1.1;
UPDATE EMP SET SAL = p_sal WHERE EMPNO = p_eno;
END;
四種調用方式
| 方式 | 語法 | 適用場景 |
| 匿名區塊 | BEGIN proc_name(args); END;測試、腳本 |
| EXECUTE | EXEC proc_name(args);SQL*Plus/SQL Developer 互動 |
| CALL | CALL proc_name(args);SQL 標準、需 OUT 參數時必加 () |
| 應用程式 (JDBC/ODP.NET) | CallableStatement cs = conn.prepareCall("{call proc_name(?,?)}");生產環境 |
常見錯誤與除錯
- 建立時加
() 於無參過程名後 → PLS-00103。
CALL 呼叫無參過程必須加 ()。
- 編譯錯誤查看:
SHOW ERRORS PROCEDURE proc_name 或查 USER_ERRORS。
- 權限:
GRANT EXECUTE ON proc_name TO user;