Oracle 觸發器與視圖實作多語言資料輸出

無標準化翻譯流程下，應用層處理多語言極其繁瑣。透過資料庫層面的基礎表、翻譯表、視圖設計，可自動依 USERENV('LANG') 輸出對應語言，開發者專注業務邏輯。

架構設計

1. 基礎表 (預設語言：英文)

CREATE TABLE base_countries (
  a2      CHAR(2) NOT NULL,   -- 國家代碼 ISO 3166-1 alpha-2
  a3      CHAR(3) NOT NULL,
  num     CHAR(3) NOT NULL,
  name    VARCHAR2(60) NOT NULL,
  CONSTRAINT base_countries_pk PRIMARY KEY (a2)
);

2. 翻譯表

CREATE TABLE tran_countries (
  a2      CHAR(2) NOT NULL,
  lang    VARCHAR2(2) NOT NULL,  -- 語言代碼，如 ZH, FR, DE
  name    VARCHAR2(60) NOT NULL,
  CONSTRAINT tran_countries_pk PRIMARY KEY (a2, lang),
  CONSTRAINT tran_countries_fk FOREIGN KEY (a2) REFERENCES base_countries (a2)
);

3. 多語言視圖 (Oracle 8i/9i 外連接寫法)

CREATE OR REPLACE VIEW nls_countries AS
SELECT b.a2, b.a3, b.num,
       NVL(t.name, b.name) name
FROM base_countries b, tran_countries t
WHERE b.a2 = t.a2 (+)
  AND USERENV('LANG') = t.lang (+);

4. 多語言視圖 (Oracle 9i+ ANSI 外連接)

CREATE OR REPLACE VIEW nls_countries AS
SELECT b.a2, b.a3, b.num,
       NVL(t.name, b.name) name
FROM base_countries b
LEFT OUTER JOIN tran_countries t
  ON b.a2 = t.a2
 AND t.lang = USERENV('LANG');

運作邏輯

• Session 設定 ALTER SESSION SET NLS_LANGUAGE = 'CHINESE' 或客戶端環境變數 NLS_LANG
• USERENV('LANG') 回傳當前 Session 語言代碼
• 視圖自動 LEFT JOIN 翻譯表，找不到對應語言則回退基礎表英文
• 應用端直接 SELECT * FROM nls_countries 即獲當地語言

擴展建議

• 多欄位翻譯：在翻譯表加 column_id 識別欄位
• 支援地區變體：lang 採 zh_CN, zh_TW 格式
• 快取熱門語言：在應用層或 Result Cache 緩存視圖結果
• 審計追蹤：在翻譯表加 created_by, updated_at

此模式符合「資料與邏輯分離」原則，一次部署、多語言自動切換，大幅降低應用層複雜度。