Oracle 資料庫資料物件深度解析（下）：程序、函數、套件、觸發器與資料字典

程序與函數

程序與函數均以編譯後形式儲存於資料庫。函數可有多參數並必須有回傳值；程序有零或多參數，無回傳值。兩者皆可透過參數列表傳入/傳出多值，主要差異在於呼叫方式：

• 程序：獨立陳述式呼叫 PAY_INVOICE(INVOICE_NBR,30,DUEDATE);
• 函數：運算式呼叫 ORDER_VOLUME := OPEN_ORDERS(SYSDATE,30);

建立程序語法

CREATE [OR REPLACE] PROCEDURE [SCHEMA.]PROCEDURE_NAME
[PARAMETER_LIST]
{AS|IS}
DECLARATION_SECTION
BEGIN
  EXECUTABLE_SECTION
[EXCEPTION
  EXCEPTION_SECTION]
END [PROCEDURE_NAME];

參數模式

• IN：唯讀，呼叫時傳值，結束不回傳
• OUT：唯寫，忽略輸入，結束時回傳值
• IN OUT：可讀寫，呼叫時傳入，結束時回傳

建立函數語法 (含 RETURN 子句)

CREATE [OR REPLACE] FUNCTION [SCHEMA.]FUNCTION_NAME
[PARAMETER_LIST]
RETURN RETURN_DATATYPE
{AS|IS}
DECLARATION_SECTION
BEGIN
  EXECUTABLE_SECTION
[EXCEPTION
  EXCEPTION_SECTION]
END [FUNCTION_NAME];

函數執行部需至少一 RETURN 陳述式。可呼叫單列函數與群組函數。

套件

套件將程序、函數與資料結構封裝為單一單位，包含：

• 套件規格：公開介面 (函數/程序標頭、公用變數、游標、例外)
• 套件主體：完整實作與私有定義

優點：

• 會話層級資料持久化
• 授權粒度為整個套件
• 支援多載

建立套件規格

CREATE [OR REPLACE] PACKAGE PACKAGE_NAME {AS|IS}
  PUBLIC_DECLARATIONS
END [PACKAGE_NAME];

建立套件主體

CREATE [OR REPLACE] PACKAGE BODY PACKAGE_NAME {AS|IS}
  PRIVATE_DECLARATIONS | IMPLEMENTATIONS
END [PACKAGE_NAME];

觸發器

自動回應資料庫事件執行的程式。事件類型：

• DML 事件 (INSERT/UPDATE/DELETE) — 可為陳述式或列級
• DDL 事件 (CREATE/ALTER/DROP)
• 資料庫事件 (STARTUP/SHUTDOWN/LOGON/LOGOFF/SERVERERROR)

建立觸發器語法

CREATE [OR REPLACE] TRIGGER TRIGGER_NAME
{BEFORE|AFTER|INSTEAD OF} EVENT
ON {TABLE|VIEW|DATABASE}
[FOR EACH ROW [WHEN CONDITION]]
TRIGGER_BODY

只有 DML 觸發器可用 INSTEAD OF；只有表 DML 觸發器可用 BEFORE/AFTER。

啟用/停用/刪除

ALTER TRIGGER TRIGGER_NAME ENABLE|DISABLE;
ALTER TABLE TABLE_NAME ENABLE|DISABLE ALL TRIGGERS;
DROP TRIGGER TRIGGER_NAME;

資料字典

儲存中繼資料。核心表 (OBJ$、UET$、SOURCE$) 由 SQL.BSQ 建立，CATALOG.SQL 建立視圖。視圖分三大前綴家族：

• USER_*：當前用戶擁有的物件
• ALL_*：當前用戶可存取的物件
• DBA_*：全資料庫物件 (需 DBA 權限)

常用視圖家族速查

特殊單一視圖

• USER_COL_PRIVS_MADE/RECD：授出/獲授欄位權限
• USER_TAB_PRIVS_MADE/RECD：授出/獲授表權限

動態效能視圖 (V$/GV$)

基於 X$ 視圖，屬 SYS，提供 Instance 層級資訊 (非資料庫層級)。無 DBA 權限僅可見 USER/ALL 視圖。

以 SQL 產生 SQL 批次維運範例

SELECT 'ALTER USER '||USERNAME||' TEMPORARY TABLESPACE TEMP;'
FROM DBA_USERS
WHERE USERNAME <> 'SYS'
  AND TEMPORARY_TABLESPACE <> 'TEMP';
-- 輸出導向檔案再執行，分鐘級完成大量變更