標準創建語法模板
CREATE OR REPLACE PROCEDURE proc_name (
p_in_param IN VARCHAR2, -- 只讀入參
p_out_param OUT NUMBER, -- 只寫出參
p_io_param IN OUT DATE -- 雙向參數
) AS
-- 區域變量聲明 (帶長度/精度/範圍)
v_count NUMBER(10) := 0;
v_name VARCHAR2(100);
v_max_sal NUMBER(12,2);
e_custom_ex EXCEPTION; -- 自定義異常
PRAGMA EXCEPTION_INIT(e_custom_ex, -20001);
BEGIN
-- 1. 先檢查存在性 (避免NO_DATA_FOUND/TOO_MANY_ROWS)
SELECT COUNT(*) INTO v_count FROM table_a WHERE col = p_in_param;
-- 2. 條件分支
IF v_count > 0 THEN
SELECT col2 INTO v_name FROM table_a WHERE col = p_in_param;
p_out_param := v_count;
DBMS_OUTPUT.PUT_LINE('Found: ' || v_name);
ELSIF v_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('Not found');
ELSE
RAISE e_custom_ex; -- 拋出自定義異常
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data');
p_out_param := -1;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple rows');
p_out_param := -2;
WHEN OTHERS THEN
ROLLBACK; -- 回滾未提交變更
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
RAISE; -- 重新拋出供調用者感知
END;
/
五大核心注意事項
| # | 規則 | 錯誤示例 | 正確示例 | 原理 |
|---|
| 1 | 參數不帶長度/精度 | p_name IN VARCHAR2(50) | p_name IN VARCHAR2 | 參數模式(IN/OUT/IN OUT)決定傳遞語義，長度由調用端實際值決定 |
| 2 | 變量必須帶長度/精度，以分號結尾 | v_name VARCHAR2; | v_name VARCHAR2(100); | PL/SQL編譯期需確定內存佈局 |
| 3 | 查詢賦值強制用 SELECT ... INTO | v_name := SELECT col FROM tab; | SELECT col INTO v_name FROM tab; | INTO子句指定目標變量，單行單列 |
| 4 | 先COUNT(*)判斷存在性，再查詳情 | 直接SELECT ... INTO無記錄報NO_DATA_FOUND | SELECT COUNT(*) INTO v_cnt ...; IF v_cnt>0 THEN SELECT ... INTO ... | 避免異常開銷，邏輯更清晰 |
| 5 | 異常處理RAISE + 異常名，OTHER最後捕獲 | RAISE 'my error'; | RAISE e_custom_ex; 或 RAISE_APPLICATION_ERROR(-20001,'msg'); | 預定義異常/自定義異常/應用錯誤碼三層體系 |
參數模式深度解析
| 模式 | 可讀 | 可寫 | 調用端傳遞 | 默認值支援 | 典型用途 |
|---|
| IN | ✓ | ✗ | 值/表達式 | ✓ | 輸入過濾條件、業務參數 |
| OUT | ✗ | ✓ | 變量(需預聲明) | ✗ | 返回計算結果、狀態碼 |
| IN OUT | ✓ | ✓ | 變量(需預聲明) | ✓ | 修改傳入對象、累加器 |
調試輸出規範
-- 僅開發/測試環境開啟
SET SERVEROUTPUT ON SIZE 1000000;
DBMS_OUTPUT.PUT_LINE('INFO: ' || var);
DBMS_OUTPUT.PUT_LINE('DEBUG: step=' || step || ', val=' || val);
-- 生產環境：自主事務寫日志表
CREATE OR REPLACE PROCEDURE write_log(p_module VARCHAR2, p_msg VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO sys_debug_log(module, msg, log_time) VALUES (p_module, p_msg, SYSTIMESTAMP);
COMMIT;
END;
部署驗證清單
SHOW ERRORS PROCEDURE proc_name 無編譯錯誤EXEC proc_name(...) 運行無異常SELECT * FROM user_procedures WHERE object_name = 'PROC_NAME' 狀態=VALID- 權限授予：
GRANT EXECUTE ON proc_name TO app_role;
- 納入版本控制：文件名
proc_name_v1.2.pks(spec) / proc_name_v1.2.pkb(body)
命名規範建議：
• 過程：proc_<業務域>_<動詞>_<名詞> 如 proc_hr_calc_salary
• 函數：func_<業務域>_<名詞> 如 func_hr_get_annual_leave
• 參數前綴：p_ (參數)、v_ (變量)、c_ (常量)、e_ (異常)、r_ (記錄/游標)
• 異常碼範圍：-20000 ~ -20999 保留給應用自定義，避免與Oracle內部錯誤衝突。
