8.4 在SELECT語句中應用條件表達式

前面簡要介紹了嵌套函數，本節將正式討論這個概念。同時還介紹能夠處理NULL值和支持表達式中的條件邏輯的條件函數。

8.4.1 嵌套函數

嵌套函數使用一個函數的輸出作為另一個函數的輸入。函數總是返回一個結果。因此，當給函數提供輸入參數時，可以像考慮字面值那樣考慮函數調用。可以將單行函數嵌套到任何深度。函數的一般形式如下所示：

        Function1(parameter1, parameter2, ...) = result1

用函數調用替換其他函數的參數會生成一個表達式，如下所示：

        F1( param1.1, F2( param2.1, param2.2, F3( param3.1)), param1.3)

先計算嵌套函數，之后再將它們的返回值作為其他函數的參數輸入。按照由內向外的順序計算嵌套函數。前面的表達式可以像下面這樣計算：

(1) 計算F3(param3.1) ，其返回值作為函數F2的第三個參數，可以稱之為：param2.3.

(2) 計算F2(param2.1, param2.2, param2.3)，其返回值作為函數F1的第二個參數，即param1.2。

(3) 計算F1(param1.1, param1.2, param1.3)，將結果返回到調用程序。

本示例中函數F3嵌套了三層。考察下面的查詢：

        select next_day(last_day(sysdate)-7, 'tue') from dual;

SELECT列表中有三個函數，由內到外分別是SYSDATE、LAST_DAY和NEXT_DAY。查詢的計算如下所述：

(1) 首先計算最里面的函數。SYSDATE返回當前日期。假定當前日期為28-OCT-2009。

(2) 接著計算次內層的函數。LAST_DAY('28-OCT-2009')返回十月最后一天的日期，即31-OCT- 2009。

最后計算NEXT_DAY('24-OCT-2009', 'tue')函數，該查詢返回當月最后一個星期二的日期，在本例中為27-OCT-2009。

8.4.2 條件函數

條件邏輯也稱為if-then-else邏輯，它表示依據滿足某種條件的數據值選擇執行路徑。條件函數依據計算比較條件來返回不同值。這一類別中的函數簡化了NULL值的處理，包括NVL、NVL2、NULLIF和COALESCE函數。通用條件邏輯由DECODE函數和CASE表達式實現。DECODE函數專用于Oracle，而CASE表達式是與ANSI SQL兼容的。

1．NVL函數

NVL函數評估任何數據類型的列或者表達式是不是空值。如果原始項是空值，返回備選的非空值；否則，返回原始項。

NVL函數有兩個強制參數。其語法是NVL(original, ifnull)，其中original表示要測試的項，如果original項計算為空，就返回ifnull。original和ifnull參數的數據類型必須一致。它們必須是相同的類型，或者可能將ifnull隱式轉換為original參數的類型。NVL函數返回值的數據類型與original參數的數據類型相同。考察下面三個查詢：

        查詢1: select nvl(1234) from dual;
        查詢2: select nvl(null, 1234) from dual;
        查詢3: select nvl(substr('abc', 4), 'No substring exists') from dual;

因為NVL函數有兩個強制參數，因此查詢1返回錯誤“ORA-00909: invalid number of arguments”。查詢2在測試null關鍵字并發現它為空值之后，返回1234。查詢3包含一個嵌套的SUBSTR函數，它試圖從三個字符的字符串中提取第四個字符，但函數返回空值，接著執行NVL (null, 'No substring exists')函數，它返回字符串’No substring exists'。

2．NVL2函數

NVL2函數是對NVL函數的增強，但功能非常類似。NVL2函數評估任何數據類型的列或者表達式是不是空值。如果第一項不是空值，那么返回第二個參數，否則返回第三個參數。注意，NVL函數不是這樣，因為如果原始項不是空值，它就返回原始項。

NVL2函數有三個強制參數。其語法是NVL2(original, ifnotnull, ifnull)，其中original表示被測試的項。如果original不是空值，就返回ifnotnull；如果original是空值，就返回ifnull。ifnotnull和ifnull參數的數據類型必須一致，它們不能是LONG數據類型。它們可以是相同的類型，或者可以將ifnull轉換為ifnotnull參數的類型。NVL2函數返回的數據類型與ifnotnull參數的數據類型相同。考察下面三個查詢：

        查詢1: select nvl2(1234, 1, 'a string') from dual;
        查詢2: select nvl2(null, 1234, 5678) from dual;
        查詢3: select nvl2(substr('abc', 2), 'Not bc', 'No substring') from dual;

查詢1中的ifnotnull項是一個數字，而ifnull參數是一個字符串。因為它們之間的數據類型不兼容，因此會返回錯誤“ORA-01722: invalid number”。查詢2返回ifnull參數，即5678。查詢3使用SUBSTR函數提取字符“bc”，并計算NVL2('bc', 'Not bc', 'No Substring')函數，返回ifnotnull參數，即字符串’Not bc'。

3．NULLIF函數

NULLIF函數測試兩項的相等性。如果它們相等，函數就返回空值，否則返回這兩個測試項的第一項。

NULLIF函數有兩個可以是任何數據類型的強制參數。其語法是NULLIF(ifunequal, comparison_ term)，其中比較參數ifunequal和comparison_term。如果它們相同，返回NULL。如果它們不同，就返回ifunequal參數。考察下面的查詢：

        查詢1: select nullif(1234, 1234) from dual;
        查詢2: select nullif('24-JUL-2009', '24-JUL-09') from dual;

查詢1返回空值，因為這兩個參數相同。查詢2中的字符字面值沒有隱式轉換為DATE項，NULLIF函數將它們作為兩個字符串進行比較。由于字符串的長度不同，因此返回ifunequal參數，即24-JUL-2009。

圖8-4說明了如何將NULLIF嵌套為NVL2函數的參數。NULLIF函數本身將嵌套在表達式里的SUBSTR和UPPER字符函數作為它的ifunequal參數。為名字為4個字符長的員工比較EMAIL列和一個表達式，該表達式的形式是將FIRST_NAME的第一個字符連接到LAST_NAME列的大寫字母形式。如果這兩項相等，NULLIF返回空值，否則返回計算后的ifunequal參數。這個返回值用作NVL2的參數。NVL2函數提供描述性文本，該文本依據是否匹配模式對行進行分類。

4．COALESCE函數

COALESCE函數從參數列表中返回第一個非空值。如果所有參數為空，那么返回空值。

COALESCE函數有兩個強制參數和任何數量的可選參數。其語法是COALESCE(expr1, expr2, …, exprn)，如果expr1不是空值，就返回它，否則，如果expr2不是空值，就返回它，以此類推。COALESCE是NVL函數的一般形式，如下面兩個等式所示：

        COALESCE(expr1, expr2) = NVL(expr1, expr2)
        COALESCE(expr1, expr2, expr3) = NVL(expr1, NVL(expr2, expr3))

如果找到非空值，COALESCE返回的數據類型與第一個非空參數的數據類型相同。為了避免出現“ORA-00932: inconsistent data types”錯誤，所有非空參數的數據類型必須與第一個非空參數的數據類型一致。考察下面三個查詢：

        查詢1: select coalesce(null, null, null, 'a string') from dual;
        查詢2: select coalesce(null, null, null) from dual;
        查詢3: select coalesce(substr('abc', 4), 'Not bc', 'No substring') from dual

查詢1返回第四個參數：一個字符串，因為這是出現的第一個非空參數。查詢2返回空值，因為所有參數都是空值。查詢3計算它的第一個參數，即嵌套的SUBSTR函數，發現它是空的。第二個參數為非空，因此返回字符串’Not bc'。

5．DECODE函數

DECODE函數通過測試前兩項的相等性來實現if-then-else條件邏輯，如果它們相等，則返回第三個參數；如果它們不相等，則可能返回另一項。

DECODE函數至少使用三個強制參數，但可以使用更多參數。該函數的語法是DECODE (expr1, comp1, iftrue1, [comp2, iftrue2...[ compN, iftrueN]], [iffalse])。這些參數的計算如下面的偽代碼示例所示：

        If expr1 = comp1 then return iftrue1
              else if expr1 = comp2 then return iftrue2
                  ...
                  ...
                  else if expr1 = compN then return iftrueN
              else return null | iffalse;

在代碼中expr1與comp1比較。如果它們相等，那么返回iftrue1。如果expr1不等于comp1，那么接下來會出現什么情況取決于可選參數comp2和iftrue2是否存在。如果它們存在，就比較expr1和comp2。如果這兩者相等，那么返回iftrue2。如果不相等，則接下來發生的情況取決于compN、iftrueN對是否存在，繼續這個循環，直到沒有比較項存在為止。如果沒有匹配項，并且如果定義了iffalse參數，那么返回iffalse。如果iffalse參數不存在，并且沒有匹配，那么返回空值。

DECODE函數的所有參數都可以是表達式。返回的數據類型與第一個匹配比較選項的數據類型相同。表達式expr1被隱式轉換為第一個比較參數comp1的數據類型。計算其他比較參數comp2…compn時，也會將它們隱式轉換為與comp1相同的數據類型。DECODE認為兩個空值相等，因此如果expr1是空值，并且comp3是出現的第一個空值比較參數，那么就會返回對應的結果參數iftrue3。考察下面三個查詢：

        查詢1: select decode(1234, 123, '123 is a match') from dual;
        查詢2: select decode(1234, 123, '123 is a match', 'No match') from dual;
        查詢3: select decode('search', 'comp1', 'true1', 'comp2', 'true2',
        'search', 'true3', substr('2search', 2, 6), 'true4', 'false') from dual;

查詢1比較數字1234與第一個比較項123。因為它們不相等，因此無法返回第一個結果項。而且，因為沒有定義默認的iffalse參數，所以返回空值。查詢2與查詢1相同，除了定義iffalse參數之外。因為1234不等于123，所以返回字符串’No match'。查詢3搜索比較參數查找匹配項。字符項’comp1’和’comp2’不匹配 search，因此不會返回結果’true1’和’true2'。在第三個比較項’comp3' (參數6)中找到匹配項，它包含字符串search。因此，返回包含字符串'true3’的第三個結果項iftrue3 (參數7)。注意，因為發現了匹配，所以不需要再進行搜索。因此，雖然第四個比較項(參數8)也匹配expr1，但不會計算這個表達式，因為在前面的比較項中已經找到了匹配項。

6．CASE表達式

其實所有第三和第四代編程語言都可實現case語句。和DECODE函數一樣，CASE表達式使用if-then-else條件邏輯。CASE表達式有兩個變體。簡單的CASE表達式列出條件搜索項一次，由每個比較表達式來測試與搜索項的相等性。搜索的CASE表達式列出每個比較表達式的單獨條件。

CASE表達式至少使用三個強制參數，但可以使用更多參數。其語法取決于是使用簡單CASE表達式還是使用搜索的CASE表達式。簡單CASE表達式的語法如下所示：

        CASE search_expr
         WHEN comparison_expr1 THEN iftrue1
         [WHEN comparison_expr2 THEN iftrue2
         ...
         WHEN comparison_exprN THEN iftrueN
         ELSE iffalse]
        END

簡單CASE表達式包含在CASE…END代碼塊內，由至少一個WHEN…THEN語句組成。在最簡單的情況下——只有一個WHEN…THEN語句，search_expr與comparison_expr1進行比較。如果它們相等，那么返回結果iftrue1。如果不相等，那么返回一個空值，除非定義了ELSE組件，這種情況下，返回默認的iffalse值。當CASE表達式中有多個WHEN…THEN語句時，就會不斷搜索匹配的比較表達式，直到找到匹配項為止。

搜索、比較和結果參數可以是列值、表達式或者字面值，但必須都是相同的數據類型。考察下面的查詢：

        select
         case substr(1234, 1, 3)
        when '134' then '1234 is a match'
        when '1235' then '1235 is a match'
        when concat('1', '23') then concat('1', '23')||' is a match'
        else 'no match'
       end
      from dual;

派生自SUBSTR(1234,1,3)的搜索表達式是字符串123。第一個WHEN...THEN語句比較字符串134和123。因為它們不相等，所以沒有計算結果表達式。第二個WHEN…THEN語句比較字符串1235和123，它們也不相等。第三個WHEN…THEN語句將來自CONCAT('1', '23')表達式的結果(即123)與搜索表達式進行比較。因為它們相同，因此返回第三個結果表達式'123 is a match'。

檢索DEPARTMENT_ID值為100的員工的LAST_NAME和HIRE_DATE列，以及兩個數字表達式和一個CASE表達式，如圖8-5所示。

假設SYSDATE是27-JAN-2015。別名為YEARS的數字表達式返回用工作的月數除以12之后的截取值。依據工作的年數定義5類忠誠等級，這形成了CASE語句中的搜索表達式。數據集中沒有行匹配第一個WHEN…THEN語句中的比較表達式，如圖8-5所示，有4行匹配剩下的WHEN…THEN語句，且ELSE語句也捕獲了兩行。

搜索的CASE表達式的語法如下所示：

        CASE
         WHEN condition1 THEN iftrue1
         [WHEN condition2 THEN iftrue2
         ...
         WHEN conditionN THEN iftrueN
         ELSE iffalse]
        END

搜索的CASE表達式包含在CASE…END代碼塊內，由至少一個WHEN…THEN語句組成。在最簡單的情況下——只有一個WHEN…THEN語句，計算condition1；如果它為true，那么返回結果iftrue1。如果不是，那么就返回一個空值，除非定義了ELSE組件，這種情況下，返回默認的iffalse值。當CASE表達式中有多個WHEN…THEN語句時，就會不斷搜索匹配的比較表達式，直到找到匹配項為止。下面的查詢使用搜索的CASE表達式，檢索到的結果集與在圖8-5中獲得的結果集相同：

        select last_name, hire_date, sysdate,
        trunc(months_between(sysdate, hire_date)) months,
        trunc(months_between(sysdate, hire_date)/12) years,
        case
         when trunc(months_between(sysdate, hire_date)/12) < 7 then 'Intern'
         when trunc(months_between(sysdate, hire_date)/12) < 8 then 'Junior'
         when trunc(months_between(sysdate, hire_date)/12) < 9 then 'Intermediate'
         when trunc(months_between(sysdate, hire_date)/12) < 10 then 'Senior'
         else 'Furniture'
        end Loyalty
        from employees
        where department_id = 100
        order by years;

練習8-3 使用DECODE函數

要求查詢HR.LOCATIONS表中的一些行，這些行的COUNTRY_ID列為值US。需要別名為LOCATION_INFO的表達式計算STATE_PROVINCE列值，返回不同的信息，如表8-9所示。依據LOCATION_INFO表達式排序輸出。

(1) 啟動SQL Developer或SQL*Plus，連接到HR模式。

(2) 有幾種方法計算LOCATION_ID表達式，可以使用CASE表達式或者DECODE函數。下面的解決方案使用CASE表達式。

        select case state_province
         when 'Washington' then 'Headquarters'
         when'Texas' then 'Oil Wells'
         when 'California' then city
         when 'New Jersey' then street_address
        end location_info, state_province, city, street_address, country_id
        from locations where country_id='US' order by location_info;

8.5 本章知識點回顧

描述SQL中可用的各種類型的轉換函數

● 當調用像TO_CHAR這樣的函數改變值的數據類型時，就會發生顯式轉換。TO_CHAR函數實現日期到字符和數字到字符數據類型的轉換。

● 使用TO_DATE轉換函數可以將字符項顯式轉換為日期值。

● 使用TO_NUMBER轉換函數可以將字符項轉換為數值。

使用TO_CHAR、TO_NUMBER和TO_DATE轉換函數

● TO_CHAR函數返回VARCHAR2類型的項。

● 格式模型或者格式掩碼描述一些模式，字符串必須匹配這些模式以便于準確和一致地轉換為數字或者日期項。

● 使用TO_CHAR函數從日期中提取的字符項(如月和日名稱)會自動添加空格，在格式掩碼前面添加fm修飾符可以刪除這些空格。

● TO_DATE函數有一個 fx 修飾符，它指定要轉換的字符串和日期格式掩碼必須準確匹配。

在SELECT語句中應用條件表達式

● 嵌套函數使用一個函數的輸出作為另一個函數的輸入。

● NVL函數原封不動地返回首項，或者如果首項為空值，則返回備選項。

● 如果首項是空值，NVL2函數會返回新的if-null項；如果首項不是空值，則返回可代替的if-not-null項。

● NULLIF函數測試兩項的相等性。如果它們相等，則函數返回空值，否則返回被測試的兩項的第一項。

● COALESCE函數從參數列表中返回第一個非空值。如果所有參數都為空值，那么返回空值。

● DECODE函數以及簡單的CASE表達式和搜索的CASE表達式用于實現if-then-else條件邏輯。