Oracle 程式物件全攻略：Stored Procedure、Function、Trigger、Package、Job、DB Link

概述

本文為「Oracle 全攻略」系列之程式物件篇，涵蓋 PL/SQL 核心物件建立、管理、除錯、效能調優與最佳實踐。以下為知識脈絡摘要，建議搭配原文圖示閱讀。

物件體系總覽

關鍵技術要點

1. Procedure / Function 進階

• 參數模式：IN (預設, 唯讀)、OUT (唯寫, 需初始化)、IN OUT (讀寫)。
• 預設值：CREATE PROCEDURE p (x NUMBER DEFAULT 1)。
• 過載：同名不同參數列表 (Package 內常用)。
• 編譯選項：PLSQL_OPTIMIZE_LEVEL、PLSQL_CODE_TYPE (NATIVE/INTERPRETED)、PLSQL_WARNINGS。
• 結果快取：RESULT_CACHE 子句 (11g+) 緩存確定性函數結果。

2. Trigger 完整分類

• DML Trigger：BEFORE/AFTER INSERT/UPDATE/DELETE ON table，FOR EACH ROW (行級) 可存取 :OLD/:NEW。
• INSTEAD OF Trigger：針對不可更新視圖，攔截 DML 轉為底表操作。
• System/Schema Trigger：AFTER LOGON/STARTUP/DDL ON DATABASE/SCHEMA，審計、同步、安全控制。
• Compound Trigger (11g+)：單一 Trigger 合併 BEFORE/AFTER 邏輯，解決 ORA-04091 變更表問題。

3. Package 設計模式

• 初始化區塊：Body 中一次性執行，載入設定、快取靜態資料。
• 私有程序/變數：僅在 Body 宣告，Spec 不暴露，實現封裝。
• 會話狀態：Package 變數生命週期 = Session，可跨呼叫共享狀態。
• 序列化封裝：PRAGMA SERIALLY_REUSABLE 釋放 Package 狀態於呼叫結束時 (適用無狀態工具包)。

4. DBMS_SCHEDULER (10g+ 取代 DBMS_JOB)

• 核心物件：Program、Schedule、Job、Window、Window Group、Job Class、Resource Plan。
• 事件驅動：QUEUE_SPEC 監聽 AQ 事件觸發作業。
• 鏈式作業：DEFINE_CHAIN_STEP、DEFINE_CHAIN_RULE 實現複雜工作流。
• 遠程外部作業：EXTERNAL_SCRIPT 在遠端主機執行 Shell/Python 腳本。

5. Database Link 實務

• 建立：CREATE DATABASE LINK dblink CONNECT TO user IDENTIFIED BY pwd USING 'tns_alias'。
• 分散式查詢：SELECT * FROM table@dblink，最佳化器可推演執行計畫 (DRIVING_SITE Hint)。
• 分散式交易：兩階段提交 (2PC) 自動協調，COMMIT 觸發 Prepare/Commit 階段。
• 故障排查：ORA-02019 (連線描述缺失)、ORA-12154 (TNS 解析失敗)、ORA-02068 (嚴重錯誤需回滾)。

圖解索引

原文包含 15 張圖示，涵蓋建立語法範例、編譯流程、Package 結構、Scheduler 架構、DB Link 拓撲、除錯工具使用：

開發規範建議

1. 業務邏輯盡量封裝於 Package，減少獨立 Procedure/Function 散落。
2. Trigger 僅用於資料完整性、審計、同步，避免複雜業務邏輯導致除錯困難。
3. Scheduler 作業啟用日誌 (LOGGING_LEVEL=FULL) 並設定失敗通知。
4. DB Link 採用 CURRENT_USER 模式配合 Proxy Authentication，避免硬編碼密碼。