存儲過程核心架構
Oracle 存儲過程是經過編譯存儲在數據庫中的 PL/SQL 程序單元，由聲明部、執行部、異常處理部三大部分組成，支持參數傳遞、事務控制、動態 SQL 等企業級特性。
基礎語法模板
CREATE OR REPLACE PROCEDURE proc_name (
param_in IN VARCHAR2,
param_out OUT NUMBER
) AS
-- 聲明區
v_var1 VARCHAR2(100);
v_var2 NUMBER := 0;
BEGIN
-- 執行區
SELECT COUNT(*) INTO v_var1 FROM dual;
IF v_var1 > 0 THEN
param_out := 1;
ELSE
param_out := 0;
END IF;
EXCEPTION
-- 異常處理
WHEN OTHERS THEN
param_out := -1;
RAISE;
END proc_name;
參數模式深度解析
| 模式 | 方向 | 特性 | 適用場景 |
|---|
| IN | 只讀入參 | 默認模式，可傳常量/變量/表達式 | 查詢條件、業務參數輸入 |
| OUT | 只寫出參 | 調用前未初始化，返回前必須賦值 | 計算結果、狀態碼返回 |
| IN OUT | 雙向參數 | 調用前需初始化，可讀可寫 | 累加器、狀態機上下文傳遞 |
變量聲明最佳實踐
- 錨定類型：使用
table.column%TYPE 避免結構變更導致失效
- 記錄類型：
TYPE rec_type IS RECORD (col1 TYPE, col2 TYPE); 簡化行數據操作
- 集合類型：關聯數組、嵌套表、VARRAY 滿足批量處理需求
控制結構與流程控制
條件判斷
IF condition1 THEN
-- statements
ELSIF condition2 THEN
-- statements
ELSE
-- statements
END IF;
-- 簡潔寫法
CASE expression
WHEN value1 THEN statements;
WHEN value2 THEN statements;
ELSE statements;
END CASE;
循環構造
-- 基礎循環
LOOP
EXIT WHEN condition;
-- statements
END LOOP;
-- WHILE 循環
WHILE condition LOOP
-- statements
END LOOP;
-- FOR 循環（隱式游標）
FOR rec IN (SELECT * FROM emp WHERE deptno = p_deptno) LOOP
DBMS_OUTPUT.PUT_LINE(rec.ename);
END LOOP;
-- 數值範圍循環
FOR i IN 1..10 LOOP
-- statements
END LOOP;
游標進階應用
顯式游標完整生命周期
CURSOR c_emp (p_deptno NUMBER) IS
SELECT empno, ename, sal FROM emp WHERE deptno = p_deptno;
v_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp(10);
LOOP
FETCH c_emp INTO v_emp;
EXIT WHEN c_emp%NOTFOUND;
-- 處理邏輯
END LOOP;
CLOSE c_emp;
END;
游標變量動態綁定
TYPE ref_cur IS REF CURSOR;
c_var ref_cur;
BEGIN
IF p_type = 'EMP' THEN
OPEN c_var FOR SELECT * FROM emp;
ELSE
OPEN c_var FOR SELECT * FROM dept;
END IF;
-- 統一處理邏輯
END;
游標屬性速查
| 屬性 | 含義 | 適用階段 |
|---|
| %FOUND | 最近 FETCH 返回行 | FETCH 後 |
| %NOTFOUND | 最近 FETCH 無數據 | FETCH 後 |
| %ROWCOUNT | 已提取行數 | OPEN 後累計 |
| %ISOPEN | 游標是否打開 | 任意時刻 |
異常處理體系
預定義異常
NO_DATA_FOUND：SELECT INTO 無數據TOO_MANY_ROWS：SELECT INTO 返回多行DUP_VAL_ON_INDEX：唯一約束違反VALUE_ERROR：數據類型轉換錯誤
自定義異常與重拋
e_insufficient_funds EXCEPTION;
PRAGMA EXCEPTION_INIT(e_insufficient_funds, -20001);
BEGIN
IF v_balance < v_amount THEN
RAISE e_insufficient_funds;
END IF;
EXCEPTION
WHEN e_insufficient_funds THEN
LOG_ERROR('余額不足', SQLERRM);
RAISE_APPLICATION_ERROR(-20001, '賬戶余額不足，當前可用：' || v_balance);
WHEN OTHERS THEN
LOG_ERROR('系統異常', SQLERRM);
RAISE; -- 保留原始堆棧重拋
END;
企業級開發模式
包裝化設計
-- 規範定義
CREATE OR REPLACE PACKAGE pkg_hr AS
PROCEDURE hire_employee(p_data IN employee_rec);
FUNCTION calc_bonus(p_empno NUMBER) RETURN NUMBER;
END pkg_hr;
-- 體實現
CREATE OR REPLACE PACKAGE BODY pkg_hr AS
PROCEDURE hire_employee(p_data IN employee_rec) IS
BEGIN
-- 實現邏輯
END;
FUNCTION calc_bonus(p_empno NUMBER) RETURN NUMBER IS
BEGIN
-- 實現邏輯
RETURN v_bonus;
END;
END pkg_hr;
批量處理優化
-- FORALL 批量 DML
FORALL i IN INDICES OF v_empnos
UPDATE emp SET sal = sal * 1.1 WHERE empno = v_empnos(i);
-- BULK COLLECT 批量查詢
SELECT empno BULK COLLECT INTO v_empnos FROM emp WHERE deptno = 10;
自主事務
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO audit_log VALUES (...);
COMMIT; -- 僅提交審計日志，不影響主事務
END;
性能優化核心原則
- 減少上下文切換：批量綁定、FORALL 替代循環 DML
- 避免硬解析：使用綁定變量，複用游標
- 合理提交頻率：大批量處理每 1000-5000 行提交一次
- 索引覆蓋查詢：WHERE 條件列、JOIN 列建立複合索引
- 監控執行計劃：定期
DBMS_XPLAN.DISPLAY_CURSOR 分析
調試與診斷工具
DBMS_OUTPUT：基礎輸出調試DBMS_DEBUG / PL/SQL Developer 斷點調試DBMS_PROFILER：性能剖析統計DBMS_TRACE：調用鏈路追蹤V$SQL / V$SQL_PLAN：運行時 SQL 分析
