Oracle 預存程序與觸發器完整教學：從入門到實戰

什麼是預存程序？

在 Oracle 資料庫中，預存程序 是儲存在資料字典中的預先編譯程式區塊。它們可以被不同使用者與應用程式共享，實現程式碼重用與效能優化。預存程序在伺服器端執行，減少網路傳輸開銷，執行速度遠快於客戶端 SQL 敘述。

預存程序四大核心優勢

• 效能卓越：程式碼在伺服器端執行，首次執行後駐留高速快取，後續呼叫直接使用快取版本
• 安全性強：可授權使用者執行程序而不直接存取底層資料表，實現細粒度權限控管
• 自動化執行：可設定在系統啟動時自動執行預定義任務
• 維護容易：邏輯集中在資料庫層，修改程序即可更新所有呼叫端

建立與執行預存程序

語法結構

CREATE PROCEDURE 程序名
[(參數 參數模式 資料型態, ...)]
IS | AS
BEGIN
  SQL_敘述
END 程序名;

參數模式說明

呼叫方式

EXECUTE 程序名 [參數1, 參數2, ...];

實戰範例

範例 1：統計某專業學生人數

CREATE PROCEDURE count_grade
(zym IN CHAR, person_num OUT NUMBER)
AS
BEGIN
  SELECT COUNT(zym) INTO person_num FROM announce;
END count_grade;

範例 2：依總學分自動產生評語

CREATE OR REPLACE PROCEDURE update_info
(xm IN CHAR)
AS
  xf NUMBER;  -- 定義變數
BEGIN
  SELECT zxf INTO xf FROM xs WHERE xm = xm;
  IF xf > 60 THEN
    UPDATE xs SET bz = '三好學生' WHERE xm = xm;
  END IF;
  IF xf < 35 THEN
    UPDATE xs SET bz = '學分沒修滿' WHERE xm = xm;
  END IF;
END update_info;

執行：EXEC update_info('丁吉祥');

修改與刪除預存程序

• 修改：建議使用 CREATE OR REPLACE 重建，ALTER PROCEDURE 僅用於重新編譯驗證
• 刪除：DROP PROCEDURE 程序名; 釋放記憶體資源

觸發器詳解

觸發器 是與資料表緊密關聯的特殊程序，當基礎表發生 INSERT、UPDATE、DELETE 操作時自動執行，用於維護資料完整性與一致性。每張表最多可建立 12 種觸發器：

建立觸發器語法

CREATE OR REPLACE TRIGGER 觸發器名
{BEFORE | AFTER | INSTEAD OF}
{DELETE | INSERT | UPDATE [OF 欄位...]}
ON 表名 | 檢視名
[FOR EACH ROW] [WHEN 條件]
PL/SQL 區塊

重要限制

• 觸發器內只能使用 DML 敘述（SELECT、INSERT、UPDATE、DELETE）
• 禁止 使用 DDL、COMMIT、ROLLBACK、SAVEPOINT 等交易控制敘述

掌握預存程序與觸發器，能大幅提升 Oracle 應用開發的效能、安全性與維護性。