核心概念區分
| 特性 | Procedure（存儲過程） | Function（函數） |
|---|
| 返回值 | 無返回值（可透過 OUT/IN OUT 參數回傳） | 必須有 RETURN 值 |
| 調用方式 | 獨立語句：EXEC proc_name(...); 或 CALL proc_name(...); | 嵌入 SQL 表達式：SELECT func_name(...) FROM dual; |
| 事務控制 | 可包含 COMMIT/ROLLBACK | 通常不建議包含 DML 提交 |
| 主要用途 | 執行業務邏輯、批次作業、資料維護 | 計算、轉換、查詢輔助、可用於 SQL 子句 |
PL/SQL 區塊結構
DECLARE
-- 宣告區（選填）：變數、游標、例外、型別定義
v_sal NUMBER;
BEGIN
-- 執行區（必填）：業務邏輯、SQL 操作、流程控制
SELECT sal INTO v_sal FROM emp WHERE empno = 7369;
DBMS_OUTPUT.PUT_LINE('薪資: ' || v_sal);
EXCEPTION
-- 例外處理區（選填）：錯誤捕獲與處理
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('查無此員工！');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('系統錯誤: ' || SQLERRM);
END;
/
建立與管理存儲過程
最簡過程
CREATE OR REPLACE PROCEDURE myproc AS
BEGIN
NULL; -- 空操作
END;
/
帶參數過程
CREATE OR REPLACE PROCEDURE get_emp_sal (p_empno IN NUMBER) AS
v_sal NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
DBMS_OUTPUT.PUT_LINE('員工 ' || p_empno || ' 薪資: ' || v_sal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('員工編號 ' || p_empno || ' 不存在');
END;
/
參數模式詳細解析
| 模式 | 方向 | 特性 | 典型用途 |
|---|
| IN | 呼叫端 → 過程 | 預設、唯讀、可傳常數/變數/運算式 | 輸入條件、查詢參數 |
| OUT | 過程 → 呼叫端 | 初始為 NULL、需指派值、僅能傳變數 | 回傳計算結果、狀態碼 |
| IN OUT | 雙向 | 傳入初始值、可修改並回傳、按地址傳遞 | 累計器、狀態物件、大物件操作 |
OUT/IN OUT 參數實戰範例
CREATE OR REPLACE PROCEDURE calc_bonus (
p_base_sal IN NUMBER,
p_bonus_rate IN NUMBER,
p_bonus_amt OUT NUMBER,
p_total_sal IN OUT NUMBER
) AS
BEGIN
p_bonus_amt := p_base_sal * p_bonus_rate;
p_total_sal := p_total_sal + p_bonus_amt;
END;
/
-- 呼叫測試
DECLARE
v_bonus NUMBER;
v_total NUMBER := 50000; -- 原總薪資
BEGIN
calc_bonus(50000, 0.15, v_bonus, v_total);
DBMS_OUTPUT.PUT_LINE('獎金: ' || v_bonus || ', 新總薪: ' || v_total);
END;
/
函數開發範例
標量函數
CREATE OR REPLACE FUNCTION get_emp_name (p_empno IN NUMBER) RETURN VARCHAR2 AS
v_ename VARCHAR2(50);
BEGIN
SELECT ename INTO v_ename FROM emp WHERE empno = p_empno;
RETURN v_ename;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'UNKNOWN';
END;
/
-- SQL 中調用
SELECT get_emp_name(7369) FROM dual;
表值函數
CREATE OR REPLACE TYPE emp_rec AS OBJECT (
empno NUMBER, ename VARCHAR2(50), sal NUMBER
);
CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_rec;
CREATE OR REPLACE FUNCTION get_dept_emps (p_deptno IN NUMBER) RETURN emp_tab PIPELINED AS
BEGIN
FOR r IN (SELECT empno, ename, sal FROM emp WHERE deptno = p_deptno) LOOP
PIPE ROW (emp_rec(r.empno, r.ename, r.sal));
END LOOP;
RETURN;
END;
/
-- 當作資料表使用
SELECT * FROM TABLE(get_dept_emps(10));
實務應用：用戶驗證過程
CREATE TABLE app_users (
user_id NUMBER PRIMARY KEY,
username VARCHAR2(30) UNIQUE,
password VARCHAR2(100),
role_id NUMBER
);
CREATE OR REPLACE PROCEDURE check_login (
p_username IN VARCHAR2,
p_password IN VARCHAR2,
p_result OUT VARCHAR2,
p_user_id OUT NUMBER,
p_role OUT NUMBER
) AS
v_cnt NUMBER;
BEGIN
SELECT COUNT(*), user_id, role_id
INTO v_cnt, p_user_id, p_role
FROM app_users
WHERE username = p_username AND password = p_password;
IF v_cnt = 1 THEN
p_result := 'SUCCESS';
ELSE
p_result := 'INVALID_CREDENTIALS';
p_user_id := NULL;
p_role := NULL;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_result := 'INVALID_CREDENTIALS';
WHEN OTHERS THEN
p_result := 'SYSTEM_ERROR: ' || SQLERRM;
END;
/
最佳實踐建議
- 命名規範：過程用動詞前綴（
proc_, sp_），函數用名詞/動詞（fn_, get_, calc_）
- 參數預設值：
CREATE PROCEDURE p(p_param IN NUMBER DEFAULT 0) ...
- 例外處理：盡量捕獲具體例外，避免只用
WHEN OTHERS 吞沒錯誤
- 效能優化：使用
BULK COLLECT、FORALL 處理大量資料；適當加入 PRAGMA AUTONOMOUS_TRANSACTION 處理日誌記錄
- 版本控管：所有 DDL 腳本納入 Git 管理，部署前進行 Code Review
