程序 vs 函數
| 特性 | 程序 | 函數 |
|---|
| 回傳值 | 無 (透過 OUT/INOUT 參數回傳) | 必須有單一回傳值 |
| 呼叫方式 | 獨立陳述式 proc(...); | 表達式一部分 v := func(...); |
| SQL 中使用 | 不可直接用於 SELECT/WHERE | 可用於 SELECT/WHERE/VALUES |
建立程序語法
CREATE [OR REPLACE] PROCEDURE [schema.]proc_name
[(param_name [IN|OUT|IN OUT] datatype [DEFAULT expr] [, ...])]
{AS|IS}
[declarations]
BEGIN
executable_statements
[EXCEPTION handlers]
END [proc_name];
參數模式
IN：唯讀，呼叫端傳入，預設模式。OUT：唯寫，呼叫端變數接收回傳值。IN OUT：可讀可寫，傳入並回傳。
建立函數語法
CREATE [OR REPLACE] FUNCTION [schema.]func_name
[(param_name [IN|OUT|IN OUT] datatype [DEFAULT expr] [, ...])]
RETURN return_datatype
{AS|IS}
[declarations]
BEGIN
executable_statements -- 至少一個 RETURN
[EXCEPTION handlers]
END [func_name];
範例：自訂 SIN 函數 (度轉徑度)
CREATE OR REPLACE FUNCTION my_sin(p_degrees IN NUMBER) RETURN NUMBER IS
v_pi CONSTANT NUMBER := ACOS(-1);
v_rad_per_deg CONSTANT NUMBER := v_pi / 180;
BEGIN
RETURN SIN(p_degrees * v_rad_per_deg);
END my_sin;
套件 結構
-- 規格
CREATE OR REPLACE PACKAGE pkg_name IS
PROCEDURE proc1(...);
FUNCTION func1(...) RETURN datatype;
var_name CONSTANT datatype := value;
END pkg_name;
-- 主體
CREATE OR REPLACE PACKAGE BODY pkg_name IS
PROCEDURE proc1(...) IS BEGIN ... END;
FUNCTION func1(...) RETURN datatype IS BEGIN ... END;
[初始化區塊 BEGIN ... END;]
END pkg_name;
觸發器類型與時機
| 分類 | 範例 |
|---|
| DML 列級 | BEFORE INSERT OR UPDATE ON emp FOR EACH ROW |
| DML 陳述式級 | AFTER DELETE ON emp |
| DDL/系統事件 | AFTER CREATE ON DATABASE, ON LOGON |
| INSTEAD OF | 針對不可更新 View 的 DML 重導 |
最佳實務
- 盡量用套件封裝相關程序/函數，減少名稱衝突、利用封裝體隱藏實作。
- 函數若為純查詢加
DETERMINISTIC、RESULT_CACHE 提升效能。
- 觸發器避免複雜邏輯，改呼叫套件程序，便於除錯與版控。
- 使用
PRAGMA AUTONOMOUS_TRANSACTION 處理觸發器中需獨立提交的日誌寫入。
