Oracle COALESCE 函數實戰：簡化多實體共用表查詢邏輯

單表存儲多實體類型的查詢挑戰

購買零件與自製零件同屬「零件」，經理與工人同屬「員工」。多實體共用表時，傳統做法用 CASE 逐行判斷實體類型再回傳不同結果。

COALESCE 函數：9i+ 版本的優雅替代方案

語法格式

COALESCE(expression_1, expression_2, ..., expression_n)

回傳第一個非空運算式值；全為空則回傳 NULL。

核心原理：空值傳播特性

大多數含空值運算式結果為空值 (例外：連接運算子 ||)。如：NULL + 值 = NULL、NULL * 值 = NULL。利用此特性構建有序運算式鏈，首個非空即為結果，等同 CASE 邏輯。

實戰案例：PARTS 表成本查詢

表結構

PART_ID       NUMBER(6)     NOT NULL
PART_TYPE     CHAR(1)       NOT NULL  -- 'P'=購買, 'B'=自製
PURCHASE_COST NUMBER(8,2)   -- 購買成本，自製為 NULL
MATERIAL_COST NUMBER(8,2)   -- 材料成本，購買為 NULL
MATERIAL_QTY  NUMBER(6)     -- 材料用量，購買為 NULL

傳統 CASE 寫法

CASE 
  WHEN PART_TYPE = 'P' THEN PURCHASE_COST
  WHEN PART_TYPE = 'B' THEN MATERIAL_QTY * MATERIAL_COST
END

COALESCE 一行解決

COALESCE(PURCHASE_COST, MATERIAL_QTY * MATERIAL_COST)

購買零件：PURCHASE_COST 非空直接回傳。自製零件：第一參數為空，自動計算第二參數乘積回傳。

完整查詢

SELECT PART_ID "Part", PART_TYPE "Type", 
       COALESCE(PURCHASE_COST, MATERIAL_QTY * MATERIAL_COST) "Cost"
FROM PARTS;

延伸應用與提醒

• 可鏈接任意多運算式，處理多實體統一估值
• CASE 優勢：自帶文檔性，邏輯更易閱讀維護
• COALESCE 適用於「同一語意不同來源」的優先級取值場景