如何使用Oracle的COALESCE函數

資料庫應用軟體很多時候將多重的、相關的實體資訊儲存在同一個表格中。例如，購買的零件和本地生產的零件都是零件，經理和工人都是員工，儘管多重的實體類型在資料存放區上略有不同，但是它們有太多的相同之處，因此通常使用一個表格而不是兩個。

處理這樣的表格通常意味著對每一行進行條件測試來檢查哪個實體類型正在被處理，然後返回每種類型的不同結果集。CASE語句可以用來完成這一工作。

從Oracle 9i版開始，COALESCE函數在很多情況下就成為替代CASE語句的一條捷徑，COALESCE的格式如下：

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

列表中第一個非空的運算式是函數的傳回值，如果所有的運算式都是空值，最終將返回一個空值。

使用COALESCE的秘密在於大部分包含空值的運算式最終將返回空值（串連操作符“||”是一個值得注意的例外）。例如，空值加任何值都是空值，空值乘任何值也都是空值，依此類推。

這樣您就可以構建一系列運算式，每個運算式的結果是空值或非空，這就像一個CASE語句，運算式按照順序進行測試，第一個滿足條件的值就確定了結果。

列表A展示了名為PARTS的表格的一部分，該Table Store了購買的零件資料和生產的零件資料，如果是購買的零件，那麼part_type列的值為‘P’，如果是本地生產或組裝的則是‘B’；此外，對於購買的零件，purchase_cost 列會顯示購買成本，而本地生產的零件則是空值；而且，本地生產的零件還有material_qty和material_cost兩列的資訊，對於購買的零件則是空值。

您可以使用一個CASE語句來測試part_type列的值並返回either purchase_cost和material_qty列與material_cost列的乘積；不過COALESCE可以用一行語句解決這個問題：

COALESCE(purchase_cost, material_qty * material_cost)

如果資料行中儲存的是一個購買的零件，那麼purchase_cost就不是空值，將返回purchase_cost的值；然而，對於本地生產的零件，purchase_cost是空值，COALESCE會忽略它，然後將material_qty和material_cost相乘，並將乘積作為結果返回。

SELECT part_id "Part", part_type "Type",

   COALESCE(purchase_cost, material_qty * material_cost) "Cost"

   FROM parts;

您可以對任何數量的運算式重複使用這個模式，COALESCE是一個非常便捷的方法對統一表格中的多重實體求值。

最後，還要說一點CASE語句的優點，就是CASE是自動進行文檔記錄的，這便於理解和解讀正在發生的事情。