Oracle 存儲過程開發入門：從無參數到帶異常處理的完整實例

前言

存儲過程是 Oracle PL/SQL 核心編程單元，將業務邏輯封裝在資料庫層可提升性能、安全性與可維護性。本文通過兩個漸進式實例，詳細講解創建、調用、參數傳遞、異常處理全流程。

一、存儲過程基礎語法結構

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [, parameter]...) ]
IS | AS
    -- 聲明區：變量、游標、異常、嵌套子程序
BEGIN
    -- 執行區：SQL/PLSQL 語句
    [EXCEPTION
        -- 異常處理區
    ]
END [procedure_name];

二、實例 1：無參數過程 - 輸出系統日期

1. 代碼實現

CREATE OR REPLACE PROCEDURE output_date
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END output_date;
/
SHOW ERRORS

2. 關鍵點解析

• CREATE OR REPLACE：存在則覆蓋，便於開發迭代
• IS ... BEGIN ... END：PL/SQL 塊標準結構
• DBMS_OUTPUT.PUT_LINE：標準輸出包，需客戶端啟用 `SET SERVEROUTPUT ON`
• TO_CHAR(SYSDATE, ...)：格式化日期輸出，避免隱式轉換

3. 編譯與執行

-- SQL*Plus 中編譯
SQL> @output_date.sql
Procedure created.

-- 啟用輸出並執行
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE output_date;
2024-01-15 14:30:25
PL/SQL procedure successfully completed.

-- 或在匿名塊中調用
SQL> BEGIN
  2    output_date;
  3  END;
  4  /

三、實例 2：帶 IN/OUT 參數及異常處理 - 根據 ID 查用戶名

1. 測試表準備

CREATE TABLE tab_user (
    id       NUMBER(10) PRIMARY KEY,
    username VARCHAR2(50) NOT NULL
);

INSERT INTO tab_user VALUES (1, 'zhangsan');
INSERT INTO tab_user VALUES (2, 'lisi');
COMMIT;

2. 存儲過程代碼

CREATE OR REPLACE PROCEDURE get_username (
    v_id       IN  NUMBER,
    v_username OUT VARCHAR2
)
AS
BEGIN
    SELECT username
    INTO   v_username
    FROM   tab_user
    WHERE  id = v_id;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'ID 不存在: ' || v_id);
    WHEN TOO_MANY_ROWS THEN
        RAISE_APPLICATION_ERROR(-20002, 'ID 返回多行數據: ' || v_id);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20000, '查詢異常: ' || SQLERRM);
END get_username;
/
SHOW ERRORS

3. 參數模式詳解

4. 異常處理最佳實踐

• 預定義異常：`NO_DATA_FOUND`、`TOO_MANY_ROWS`、`DUP_VAL_ON_INDEX` 等
• 自定義錯誤碼範圍：`-20000` 到 `-20999` 保留給應用使用
• RAISE_APPLICATION_ERROR：拋出用戶定義錯誤，可被客戶端捕獲
• WHEN OTHERS：兜底處理，記錄 `SQLCODE`/`SQLERRM` 後重新拋出或轉換

5. 調用測試

-- 正常調用
SQL> VARIABLE v_name VARCHAR2(50)
SQL> EXECUTE get_username(1, :v_name);
PL/SQL procedure successfully completed.
SQL> PRINT v_name
V_NAME
--------------------------------------------------
zhangsan

-- 異常調用：ID 不存在
SQL> EXECUTE get_username(99, :v_name);
BEGIN get_username(99, :v_name); END;
*
ERROR at line 1:
ORA-20001: ID 不存在: 99
ORA-06512: at "SCOTT.GET_USERNAME", line 10
ORA-06512: at line 1

四、開發調試技巧

1. 編譯錯誤查看

SHOW ERRORS PROCEDURE get_username;
-- 或查詢視圖
SELECT * FROM USER_ERRORS WHERE NAME = 'GET_USERNAME' ORDER BY SEQUENCE;

2. 執行追蹤

-- 會話級開啟 SQL 追蹤
ALTER SESSION SET SQL_TRACE = TRUE;
-- 或使用 DBMS_MONITOR
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => TRUE, binds => TRUE);

3. 除錯輸出

-- 關鍵節點打印調試信息
DBMS_OUTPUT.PUT_LINE('DEBUG: v_id=' || v_id || ', v_username=' || v_username);

五、進階擴展方向

• 函數：有返回值，可直接用於 SQL 語句中
• 包：封裝相關過程/函數/類型/變量，支持初始化、狀態保持
• 自治事務：`PRAGMA AUTONOMOUS_TRANSACTION` 實現獨立提交/回滾
• 動態 SQL：`EXECUTE IMMEDIATE` / `DBMS_SQL` 處理運行時構建的 SQL

結語

存儲過程是將業務邏輯下沉資料庫層的關鍵手段。從無參數的簡單過程開始，逐步掌握參數傳遞、異常處理、事務控制，再進階至包設計與動態 SQL，構建健壯、高性能、可維護的資料庫程序體系。