Oracle數(shù)據(jù)庫中COALESCE函數(shù)使用詳解

數(shù)據(jù)庫應(yīng)用軟件很多時候?qū)⒍嘀氐�、相關(guān)的實(shí)體信息保存在同一個表格中。例如，購買的零件和本地生產(chǎn)的零件都是零件，經(jīng)理和工人都是員工，盡管多重的實(shí)體類型在數(shù)據(jù)存儲上略有不同，但是它們有太多的相同之處，因此通常使用一個表格而不是兩個。

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

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

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

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

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

　　這樣您就可以構(gòu)建一系列表達(dá)式，每個表達(dá)式的結(jié)果是空值或非空，這就像一個CASE語句，表達(dá)式按照順序進(jìn)行測試，第一個滿足條件的值就確定了結(jié)果。

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

QUOTE:
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)

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

QUOTE:
COALESCE(purchase_cost, material_qty * material_cost)

　　如果數(shù)據(jù)行中存儲的是一個購買的零件，那么purchase_cost就不是空值，將返回purchase_cost的值;然而，對于本地生產(chǎn)的零件，purchase_cost是空值，COALESCE會忽略它，然后將material_qty和material_cost相乘，并將乘積作為結(jié)果返回。

QUOTE:
SELECT part_id "Part", part_type "Type",
COALESCE(purchase_cost, material_qty * material_cost) "Cost"
FROM parts;

　　您可以對任何數(shù)量的表達(dá)式重復(fù)使用這個模式，COALESCE是一個非常便捷的方法對統(tǒng)一表格中的多重實(shí)體求值。

　　最后，還要說一點(diǎn)CASE語句的優(yōu)點(diǎn)，就是CASE是自動進(jìn)行文檔記錄的，這便于理解和解讀正在發(fā)生的事情。

服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]