您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

Oracle 存儲過程開發全解析：參數模式、異常處理與事務控制

編輯：Oracle 教學

Oracle 存儲過程開發全解析：參數模式、異常處理與事務控制

存儲過程是 Oracle 業務邏輯封裝的核心單元。本文通過完整代碼示例，系統講解無參數、輸入參數、輸入輸出參數三類過程的創建與調用，並涵蓋DML 操作、事務提交/回滾、異常捕獲、過程互調等實戰技巧。

一、四大類存儲過程代碼範本

1. 無參數過程

CREATE OR REPLACE PROCEDURE TEST_IMIT_PRO1 AS
  P_IMTI_NAME VARCHAR2(200);
  X_MESSAGE   VARCHAR2(200);
BEGIN
  SELECT I.IMTI_NAME INTO P_IMTI_NAME
    FROM SCM_ISS.T_IMTI_TEST I
   WHERE I.IMTI_NO = 1001;

  DBMS_OUTPUT.PUT_LINE('result:' || P_IMTI_NAME);

EXCEPTION
  WHEN OTHERS THEN
    X_MESSAGE := SUBSTR(SQLERRM, 1, 240);
    DBMS_OUTPUT.PUT_LINE('ERR:' || X_MESSAGE);
END TEST_IMIT_PRO1;

2. 輸入參數過程

CREATE OR REPLACE PROCEDURE TEST_IMTI_PRO2 (P_NO IN NUMBER) AS
  P_IMTI_NAME VARCHAR2(200);
BEGIN
  SELECT I.IMTI_NAME INTO P_IMTI_NAME
    FROM SCM_ISS.T_IMTI_TEST I
   WHERE I.IMTI_NO = P_NO;

  DBMS_OUTPUT.PUT_LINE('NAME:' || P_IMTI_NAME);
END TEST_IMTI_PRO2;

-- 調用方式
CALL SCM_ISS.TEST_IMTI_PRO2(1003);
-- 或匿名區塊
BEGIN SCM_ISS.TEST_IMTI_PRO2(1003); END;

3. 輸入輸出參數過程 (核心：OUT 參數回傳值)

CREATE OR REPLACE PROCEDURE TEST_IMTI_PRO3 (P_NO IN NUMBER, P_NAME OUT VARCHAR2) AS
  T_NAME VARCHAR2(200);
BEGIN
  T_NAME := 'HELLO WORLD.';
  DBMS_OUTPUT.PUT_LINE('T_NAME:' || T_NAME);

  SELECT I.IMTI_NAME INTO P_NAME
    FROM SCM_ISS.T_IMTI_TEST I
   WHERE I.IMTI_NO = P_NO;

  DBMS_OUTPUT.PUT_LINE('TEST_IMTI_PRO3 RETURN:' || P_NAME);
END TEST_IMTI_PRO3;

注意：帶 OUT 參數的過程不能直接用 CALL 或 SQL 調用，必須在 PL/SQL 區塊或另一函數/過程中調用以接收返回值。

4. DML 寫入過程 (含事務與異常處理)

CREATE OR REPLACE PROCEDURE TEST_SALT_PRO1 (
  P_SALT_NUM  IN NUMBER,
  P_SALT_NAME IN VARCHAR2,
  P_SALT_DESC IN VARCHAR2
) AS
BEGIN
  INSERT INTO T_SALT_TEST (SALT_NO, SALT_NAME, SALT_DESC)
  VALUES (P_SALT_NUM, P_SALT_NAME, P_SALT_DESC);

  COMMIT; -- 顯式提交

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK; -- 異常回滾，保證數據一致性
    DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 240));
END TEST_SALT_PRO1;

二、函數與過程互調範例

CREATE OR REPLACE FUNCTION TEST_IMTI_FUN1 RETURN VARCHAR2 IS
  P_ITEM_NAME VARCHAR2(200);
  X_MESSAGE   VARCHAR2(200);
BEGIN
  SELECT I.IMTI_NAME INTO P_ITEM_NAME
    FROM SCM_ISS.T_IMTI_TEST I
   WHERE I.IMTI_NO = 1002;

  -- 調用無參過程
  SCM_ISS.TEST_IMIT_PRO1;
  -- 調用輸入參數過程
  SCM_ISS.TEST_IMTI_PRO2(1003);

  RETURN P_ITEM_NAME;

EXCEPTION
  WHEN OTHERS THEN
    X_MESSAGE := SUBSTR(SQLERRM, 1, 240);
    RETURN X_MESSAGE; -- 函數異常時返回錯誤信息字符串
END TEST_IMTI_FUN1;

三、核心最佳實踐總結

維度	建議
參數模式	嚴格區分 `IN` (默認,只讀), `OUT` (寫出), `IN OUT` (雙向)。函數優先用 RETURN，過程用 OUT 返回多值。
事務控制	DML 過程必須顯式 COMMIT/ROLLBACK。若由上層應用控制事務，過程中不要 COMMIT，改由調用者決定。
異常處理	避免 `WHEN OTHERS THEN NULL` 吞掉錯誤。至少記錄 `SQLCODE`/`SQLERRM` 到日志表或 `DBMS_OUTPUT`。
命名規範	過程 `PROC_` / `P_`，函數 `FUNC_` / `F_`，參數 `P_` (IN), `PO_` (OUT), `PIO_` (IN OUT)，局部變量 `V_`。
性能優化	大批量 DML 使用 `FORALL` + `BULK COLLECT`；高頻查詢函數加 `DETERMINISTIC` 或 `RESULT_CACHE`。
安全性	授權 `EXECUTE` 權限而非對象權限；敏感邏輯用 `WRAP` 加密或 `PL/SQL Obfuscation`。

四、常見調用方式速查

-- 1. SQL*Plus / SQLcl / SQL Developer
EXEC proc_name(param1, param2);
CALL proc_name(param1, param2); -- 不支援 OUT 參數接收

-- 2. 匿名 PL/SQL 區塊 (支援 OUT/IN OUT)
DECLARE
  v_out VARCHAR2(100);
BEGIN
  proc_name(100, v_out);
  DBMS_OUTPUT.PUT_LINE(v_out);
END;

-- 3. 函數調用 (SQL 語句中)
SELECT func_name(param) FROM dual;

-- 4. Java / Python 等外部應用 (JDBC / cx_Oracle)
-- 使用 CallableStatement / callproc，註冊 OUT 參數類型。