如何使用Oracle中的 COALESCE 函數

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

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

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

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

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

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

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

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

列表A：

SQL> desc parts
Name                                      Null?    Type
PART_ID                                   NOT NULL NUMBER(6)
PART_TYPE                                 NOT NULL CHAR(1)

(other columns)

PURCHASE_COST                                      NUMBER(8,2)
MATERIAL_COST                                      NUMBER(8,2)
MATERIAL_QTY                                       NUMBER(6)
LAST_UPDATED                                       TIMESTAMP(6)
UPDATED_BY                                         VARCHAR2(30)

您可以使用一個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是自動進行文檔記錄的，這便於理解和解讀正在發生的事情。