Oracle 11g 虛擬列特性詳解：無需存儲即可索引、約束與 PL/SQL 函數計算的衍生欄位

什麼是虛擬列

Oracle 11g 引入 Virtual Column，允許在表定義中直接聲明衍生列，其值由表達式或函數實時計算，不佔用磁盤存儲。語法：

COLUMN_NAME [datatype] GENERATED ALWAYS AS (expression) [VIRTUAL]

VIRTUAL 關鍵字可選。

基本示例

CREATE TABLE emp (
  empno   NUMBER(6),
  sal     NUMBER(8,2),
  comm    NUMBER(8,2),
  sal_pack GENERATED ALWAYS AS (sal + NVL(comm,0)) VIRTUAL
);

• sal_pack 由 sal + NVL(comm,0) 即時計算。
• 不可直接 INSERT/UPDATE 虛擬列（報 ORA-54013）。
• 插入時僅指定物理列：INSERT INTO emp (empno, sal, comm) VALUES (10, 1500, 500);
• 查詢自動展開：SELECT * FROM emp; 顯示計算後的 sal_pack=2000。

索引與約束支持

• 可直接創建索引：CREATE INDEX sal_pack_idx ON emp(sal_pack);
• 可定義外鍵引用虛擬列。
• 等效於「函數索引 + 視圖」但更輕量、無需額外對象維護。

調用 PL/SQL 函數

函數必須聲明 DETERMINISTIC：

CREATE OR REPLACE FUNCTION sum_num (n1 NUMBER, n2 NUMBER) RETURN NUMBER DETERMINISTIC AS
BEGIN RETURN n1 + n2; END;

ALTER TABLE emp ADD sal_pack_temp GENERATED ALWAYS AS (sum_num(sal, comm));

註釋與文檔化

COMMENT ON COLUMN emp.sal_pack IS 'Virtual column [sal + comm]';

適用場景與限制

結語

虛擬列是 Oracle 11g 為「計算列」場景提供的原生輕量級方案，尤其適合報表查詢、衍生指標、業務規則固化等高頻只讀、低頻寫入的列，值得在新系統設計中優先考慮。