5、條件控制之CASE語句

1、條件控制之CASE語句1目標(biāo)CASE語句CASE表達(dá)式NULLIF和COALESCE函數(shù)2CASE語句 18-1CASE語句存在兩種形式: simple CASE和搜索式CASE 。 CASE語句可以設(shè)定選擇器，選擇器會(huì)決定需要執(zhí)行哪組動(dòng)作。而搜索式CASE語句沒有選擇器，搜索條件會(huì)按順序計(jì)算，從而決定采取哪組動(dòng)作Simple CASE語句具有如下結(jié)構(gòu): CASE selector WHEN expression_1 THEN statement 1; WHEN expression_2 THEN statement 2; WHEN expression_n THEN statement n;

2、 ELSE statement n+1; END CASE;3CASE語句 18-2保留字CASE標(biāo)識(shí)CASE語句的開始。選擇器決定哪個(gè)WHEN子句應(yīng)該被執(zhí)行。每個(gè)WHEN子句都包含一個(gè)expression以及與之關(guān)聯(lián)的一個(gè)或者多個(gè)可執(zhí)行語句。ELSE子句是可選的。它的工作方式非常類似于IF-THEN-ELSE語句中所使用的ELSE子句。END CASE是標(biāo)識(shí)CASE語句結(jié)束的保留字注意，選擇器只會(huì)計(jì)算一次，并且會(huì)順序計(jì)算WHEN子句。表達(dá)式的值與選擇器的值進(jìn)行比較。如果兩個(gè)值相等，那么與特定WHEN子句相關(guān)的語句會(huì)執(zhí)行，并且隨后的WHEN子句不會(huì)被計(jì)算。如果任何表達(dá)式的值都不匹配選擇器的值，

3、則ELSE子句會(huì)被選中和執(zhí)行4CASE語句 18-3示例1：使用simple CASE語句判斷用戶輸入的數(shù)字是否為偶數(shù)DECLARE v_num NUMBER := &sv_user_num; v_num_flag NUMBER;BEGIN v_num_flag := MOD(v_num,2); - 測(cè)試用戶輸入的數(shù)字是否為偶數(shù) CASE v_num_flag WHEN 0 THEN DBMS_OUTPUT.PUT_LINE (v_num| is even number); ELSE DBMS_OUTPUT.PUT_LINE (v_num| is odd number); END CASE;

4、DBMS_OUTPUT.PUT_LINE (Done);END;5CASE語句 18-4搜索式CASE語句搜索式CASE語句有個(gè)能夠產(chǎn)生布爾值(TRUE、FALSE或者NULL)的搜索條件。當(dāng)特定搜索條件的計(jì)算結(jié)果為TRUE時(shí)，會(huì)執(zhí)行與該條件相關(guān)的語句組合。搜索式CASE語句的語法如下所示: CASE WHEN search condition_1 THEN statement 1; WHEN search condition_2 THEN statement 2; WHEN search condition_n THEN statement n; ELSE statement n+1; EN

5、D CASE;6CASE語句 18-5當(dāng)搜索條件的計(jì)算結(jié)果為TRUE時(shí)，執(zhí)行控制權(quán)傳遞給與之相關(guān)的語句。如果任何搜索條件都不會(huì)產(chǎn)生TRUE，則就會(huì)執(zhí)行與ELSE子句相關(guān)的語句。注意， ELSE子句是可選的示例2：使用搜索式 CASE語句判斷用戶輸入的數(shù)字是否為偶數(shù)DECLARE v_num NUMBER := &sv_user_num;BEGIN - 測(cè)試用戶輸入的數(shù)字是否為偶數(shù) CASE WHEN MOD(v_num,2) = 0 THEN DBMS_OUTPUT.PUT_LINE (v_num| is even number); ELSE DBMS_OUTPUT.PUT_LINE (v_n

6、um| is odd number); END CASE; DBMS_OUTPUT.PUT_LINE (Done);END;7CASE語句 18-6Simple CASE 語句和搜索式CASE語句之間的差別搜索式CASE語句沒有選擇器。除此之外，它的WHEN子句包含能夠產(chǎn)生布爾值的搜索條件Simple CASE語句具有選擇器，它的WHEN子句表達(dá)式的值要與選擇器的值進(jìn)行比較，因此表達(dá)式返回的數(shù)據(jù)類型必須匹配選擇器的數(shù)據(jù)類型，否則會(huì)出現(xiàn)語法錯(cuò)誤示例3：DECLARE v_num NUMBER := &sv_num; v_num_flag NUMBER;BEGIN CASE v_num_flag

7、WHEN MOD(v_num,2) = 0 THEN DBMS_OUTPUT.PUT_LINE (v_num| is even number); ELSE DBMS_OUTPUT.PUT_LINE (v_num| is odd number); END CASE; DBMS_OUTPUT.PUT_LINE (Done);END;8CASE語句 18-7在這個(gè)例子中，變量 v_num_flag已經(jīng)被定義為NUMBER類型。但是，每個(gè)表達(dá)式的結(jié)果一個(gè)Boolean類型。因此，這個(gè)范例會(huì)產(chǎn)生如下語法錯(cuò)誤:ORA-06550: 第 6 行, 第 9 列: PLS-00615: 在 CASE 操作數(shù)和

8、WHEN 操作數(shù)之間的 V_NUM_FLAG 處發(fā)現(xiàn)不匹配的類型ORA-06550: 第 6 行, 第 4 列: PL/SQL: Statement ignored9CASE語句 18-8示例4：將上例子中v_num_flag變量的數(shù)據(jù)類型改為BooleanDECLARE v_num NUMBER := &sv_num; v_num_flag Boolean;BEGIN CASE v_num_flag WHEN MOD(v_num,2) = 0 THEN DBMS_OUTPUT.PUT_LINE (v_num| is even number); ELSE DBMS_OUTPUT.PUT_LIN

9、E (v_num| is odd number); END CASE; DBMS_OUTPUT.PUT_LINE (Done);END;10CASE語句 18-9執(zhí)行上例，給變量v_num賦值7，產(chǎn)生如下輸出：7 is odd numberDone PL/SQL procedure successfully completed再次運(yùn)行，給變量v_num賦值4，產(chǎn)生如下輸出：4 is odd numberDone PL/SQL procedure successfully completed第二次的結(jié)果是錯(cuò)誤的。原因是？11CASE語句 18-10當(dāng)給變量v_num賦值4時(shí)，表達(dá)式MOD(v_n

10、um,2) = 0會(huì)返回TRUE，并且與選擇器v_num_flag的值進(jìn)行比較。但是， v_num_flag變量沒有被初始化為任何值，其值為NULL。由于NULL與任何值進(jìn)行比較，包括NULL本身，其結(jié)果都是NULL，所以與ELSE子句相關(guān)的語句會(huì)執(zhí)行12CASE語句 18-11示例5：使用simple CASE語句，在屏幕上顯示基于每周中日期編號(hào)的當(dāng)天名稱。換句話說，如果某天是一周中的第3天，則顯示TuesdaySET SERVEROUTPUT ONDECLARE v_date DATE := TO_DATE(&sv_user_date, DD-MON-YYYY); v_day VARCHA

11、R2(1);BEGIN v_day := TO_CHAR(v_date, D); CASE v_day WHEN 1 THEN DBMS_OUTPUT.PUT_LINE (Today is Sunday); WHEN 2 THEN DBMS_OUTPUT.PUT_LINE (Today is Monday); 13CASE語句 18-12 WHEN 3 THEN DBMS_OUTPUT.PUT_LINE (Today is Tuesday); WHEN 4 THEN DBMS_OUTPUT.PUT_LINE (Today is Wednesday); WHEN 5 THEN DBMS_OUTP

12、UT.PUT_LINE (Today is Thursday); WHEN 6 THEN DBMS_OUTPUT.PUT_LINE (Today is Friday); WHEN 7 THEN DBMS_OUTPUT.PUT_LINE (Today is Saturday); END CASE;END;A)如果v _date的值是15-1月-2008，請(qǐng)問屏幕上會(huì)輸出什么內(nèi)容?14CASE語句 18-13應(yīng)該輸出如下結(jié)果: Today is Tuesday PL/SQL procedure successfully completed當(dāng)變量v _date的輸入值是15-3月-2008時(shí)，借助于

13、TO_ CHAR函數(shù)，可以得到v_day變量的值，也就是一周中的哪天。接下來， CASE語句的每個(gè)表達(dá)式會(huì)順序地與該選擇器的值進(jìn)行比較。由于選擇器的值是3，則與第3個(gè)WHEN子句相關(guān)的DBMS_OUTPUT.PUT_LINE語句會(huì)執(zhí)行。因此，消息Today is Tuesday會(huì)顯示在屏幕上。其它表達(dá)式不再計(jì)算，程序執(zhí)行權(quán)會(huì)轉(zhuǎn)到END CASE語句后的第一個(gè)可執(zhí)行語句15CASE語句 18-14B) CASE選擇器v_day被計(jì)算多少次？ CASE選擇器v_day只會(huì)被計(jì)算一次。但是，會(huì)順序地檢查WHEN子句。當(dāng)WHEN子句中表達(dá)式的值等于選擇器的值時(shí)，會(huì)執(zhí)行與該WHEN子句相關(guān)的語句C）在C

14、ASE語句中使用ELSE子句，重新編寫這個(gè)腳本只需將最后一個(gè)WHEN子句用ELSE子句替代即可D)使用搜索式CASE語句，重新編寫這個(gè)腳本16CASE語句 18-15示例6：使用搜索式CASE語句顯示注冊(cè)到課程25的指定班級(jí)的學(xué)生的分?jǐn)?shù)（以字母表示）SET SERVEROUTPUT ONDECLARE v_student_id NUMBER := 102; -學(xué)生 v_section_id NUMBER := 89; -班級(jí) v_final_grade NUMBER; v_letter_grade CHAR(1); BEGIN SELECT final_grade INTO v_final_g

15、rade FROM enrollment WHERE student_id = v_student_id AND section_id = v_section_id; 17CASE語句 18-16 CASE WHEN v_final_grade = 90 THEN v_letter_grade := A; WHEN v_final_grade = 80 THEN v_letter_grade := B; WHEN v_final_grade = 70 THEN v_letter_grade := C; WHEN v_final_grade = 60 THEN v_letter_grade :=

16、 D; ELSE v_letter_grade := F; END CASE; DBMS_OUTPUT.PUT_LINE (Letter grade is: |v_letter_grade);END;A)屏幕上會(huì)顯示什么表示分?jǐn)?shù)？ 1)如果變量v_final_grade的值等于60呢？ 2)如果v_final_grade的值大于60，小于70呢？ 3)如果v_final_grade的值是NULL呢？18CASE語句 18-17B)如何修改上述代碼，以便當(dāng)v_final_grade變量的值是NULL時(shí)，顯示消息There is no final grade？除此之外，要確保屏幕上不要顯示Lett

17、er grade is: 1、需要引入一個(gè)替代變量來輸入不同的v_student_id值2、為實(shí)現(xiàn)期望的效果，應(yīng)該嵌套CASE語句。外部的CASE語句會(huì)計(jì)算v_final_grade變量的值。如果v_final_grade的值是NULL，屏幕上會(huì)顯示消息There is no final grade.。如果v_final_grade的值不是NULL，則執(zhí)行外部CASE語句的ELSE部分19CASE語句 18-18CASE -outer CASE WHEN v_final_grade IS NULL THEN DBMS_OUTPUT.PUT_LINE(There is no final grad

18、e.); ELSE CASE -inner CASE WHEN v_final_grade = 90 THEN v_letter_grade := A;3、為保證只有變量v_final_grade的值不是NULL時(shí)，才會(huì)在屏幕上顯示Letter grade.，應(yīng)該在外部CASE語句的ELSE子句中使用如下語句: DBMS_OUTPUT.PUT_LINE (Letter grade is: |v_letter_grade);20CASE表達(dá)式 12-1我們知道表達(dá)式的計(jì)算結(jié)果會(huì)產(chǎn)生賦予某個(gè)變量的一個(gè)值。類似地， CASE表達(dá)式也可以計(jì)算出一個(gè)值，這個(gè)值也可以被賦值給變量CASE表達(dá)式有個(gè)幾乎等同

19、于CASE語句的結(jié)構(gòu)。因此，它有兩種形式: simple CASE和搜索式CASE 示例1：判斷用戶輸入的數(shù)字是奇數(shù)還是偶數(shù)，并打印出結(jié)果。使用simple case語句實(shí)現(xiàn)：DECLARE v_num NUMBER := &sv_user_num; v_num_flag NUMBER;BEGIN v_num_flag := MOD(v_num,2); -計(jì)算除2余數(shù)21CASE表達(dá)式 12-2CASE v_num_flag WHEN 0 THEN DBMS_OUTPUT.PUT_LINE (v_num| is even number); ELSE DBMS_OUTPUT.PUT_LINE (

20、v_num| is odd number); END CASE; DBMS_OUTPUT.PUT_LINE (Done);END;示例2：使用 simple case表達(dá)式改寫示例1：DECLARE v_num NUMBER := &sv_user_num; v_num_flag NUMBER; v_result VARCHAR2(30); -添加一個(gè)變量，保存輸出結(jié)果BEGIN v_num_flag := MOD(v_num,2); - -計(jì)算除2余數(shù)22CASE表達(dá)式 12-3-給結(jié)果變量賦值v_result := CASE v_num_flag WHEN 0 THEN v_num| is

21、 even number ELSE v_num| is odd number END; DBMS_OUTPUT.PUT_LINE (v_result); DBMS_OUTPUT.PUT_LINE (Done);END;該例中，使用一個(gè)新的變量v_result來保存CASE表達(dá)式所返回的值23CASE表達(dá)式 12-4要特別注意CASE語句和CASE表達(dá)式之間的語法差別：24CASE表達(dá)式 12-5在CASE語句中，WHEN和ELSE子句都包含一個(gè)可執(zhí)行的語句。每個(gè)可執(zhí)行語句的結(jié)尾處都是分號(hào)在CASE表達(dá)式中， WHEN和ELSE子句所包含的表達(dá)式的結(jié)尾處不是分號(hào)。分號(hào)出現(xiàn)在保留字END的后面，保

22、留字END終止CASE表達(dá)式最后， CASE語句被保留字END CASE所終止示例3：使用搜索式 case表達(dá)式改寫示例1：DECLARE v_num NUMBER := &sv_user_num; v_result VARCHAR2(30);25CASE表達(dá)式 12-6BEGIN v_result := CASE WHEN MOD(v_num,2) = 0 THEN v_num| is even number ELSE v_num| is odd number END; DBMS_OUTPUT.PUT_LINE (v_result); DBMS_OUTPUT.PUT_LINE (Done);

23、END;在該例中，沒有必要聲明變量v_num_flag ，因?yàn)樗阉魇紺ASE表達(dá)式不需要選擇器值，并且MOD函數(shù)的結(jié)果會(huì)集成到搜索條件中26CASE表達(dá)式 12-7前面的示例中，case表達(dá)式的值是通過:=操作符賦給變量的，使用select into，也可以將case表達(dá)式的值賦給變量示例4：DECLARE v_course_no NUMBER; v_description VARCHAR2(50); v_prereq VARCHAR2(35);BEGIN27CASE表達(dá)式 12-8SELECT course_no, description, CASE WHEN prerequisite IS

24、 NULL THEN No prerequisite course required ELSE TO_CHAR(prerequisite) END prerequisite INTO v_course_no, v_description, v_prereq FROM course WHERE course_no = 20; DBMS_OUTPUT.PUT_LINE (Course: |v_course_no); DBMS_OUTPUT.PUT_LINE (Description: |v_description); DBMS_OUTPUT.PUT_LINE (Prerequisite: |v_p

25、rereq);END;28CASE表達(dá)式 12-9分析：這個(gè)例子會(huì)在屏幕上顯示課程編號(hào)、課程名稱以及預(yù)備課程。除此之外，如果某課程沒有預(yù)備課程，會(huì)在屏幕上顯示一個(gè)消息。為達(dá)到期望的結(jié)果， CASE表達(dá)式被用作SELECT INTO語句的某個(gè)列。它的值會(huì)賦予變量v_prereq。注意，CASE表達(dá)式中保留字END后面沒有分號(hào)因?yàn)檎n程20沒有預(yù)備課程。因此，搜索式條件 WHEN prerequisite IS NULL THEN 的計(jì)算結(jié)果為TRUE，并且會(huì)把No prerequisite course required賦予變量v_prereq29CASE表達(dá)式 12-10為什么在CASE表達(dá)式的

26、ELSE字句中使用函數(shù)TO_CHAR? 即 ELSE TO_CHAR(prerequisite)CASE表達(dá)式會(huì)返回一個(gè)值，需要定義一種數(shù)據(jù)類型。因此，不管CASE表達(dá)式的哪部分執(zhí)行，都會(huì)始終返回相同的數(shù)據(jù)類型，這一點(diǎn)非常重要。在前面的CASE表達(dá)式中， WHEN子句會(huì)返回VARCHAR2數(shù)據(jù)類型。ELSE子句返回COURSE表中PREREQUISITE列的值。這個(gè)列已經(jīng)被定義為NUMBER，因此也必須把它轉(zhuǎn)換為字符串類型，否則會(huì)有語法錯(cuò)誤發(fā)生30CASE表達(dá)式 12-11示例5：使用case表達(dá)式以及select into來顯示課程25的特定班所注冊(cè)學(xué)生的分?jǐn)?shù)(字母表示)SET SERVER

27、OUTPUT ONDECLARE v_student_id NUMBER := 102; v_section_id NUMBER := 89; v_letter_grade CHAR(1); BEGIN31CASE表達(dá)式 12-12SELECT CASE WHEN final_grade = 90 THEN A WHEN final_grade = 80 THEN B WHEN final_grade = 70 THEN C WHEN final_grade = 60 THEN D ELSE F END INTO v_letter_grade FROM enrollment WHERE stu

28、dent_id = v_student_id AND section_id = v_section_id; DBMS_OUTPUT.PUT_LINE (Letter grade is: | v_letter_grade);END;32NULLI F和COALESCE函數(shù) 5-1在ANSI 1999 SQL標(biāo)準(zhǔn)中， NULLIF和COALESCE函數(shù)被定義為“CASE縮寫詞”。兩個(gè)函數(shù)都被視為CASE表達(dá)式的變種NULLIF函數(shù)會(huì)比較兩個(gè)表達(dá)式。如果兩者相同，函數(shù)會(huì)返回NULL。否則，返回第一個(gè)表達(dá)式的值。NULLIF函數(shù)原型如下: NULLIF (expression1 , expressio

29、n2)如果expression1等于expression2，則NULLIF返回NULL。如果expression1不等于expression2 , NULLIF函數(shù)返回expression1 。注意， NULLIF函數(shù)的作用與NVL函數(shù)相反。 NVL函數(shù)是：如果第一個(gè)表達(dá)式是NULL， NVL函數(shù)返回第二個(gè)表達(dá)式。如果第一個(gè)表達(dá)式不是NULL， NVL返回第一個(gè)表達(dá)式33NULLI F和COALESCE函數(shù) 5-2示例1：DECLARE v_num NUMBER := &sv_user_num; v_remainder NUMBER;BEGIN - 計(jì)算除2余數(shù)，如果余數(shù)為0則返回NULL v

30、_remainder := NULLIF(MOD(v_num,2),0); DBMS_OUTPUT.PUT_LINE (v_remainder: |v_remainder);END;34NULLI F和COALESCE函數(shù) 5-3NULLIF函數(shù)有一個(gè)限制：不能把字面值NULL賦給expression1示例2：DECLARE v_remainder NUMBER;BEGIN - 計(jì)算余數(shù)，如果余數(shù)為0則返回NULL v_remainder := NULLIF(NULL,0); DBMS_OUTPUT.PUT_LINE (v_remainder: |v_remainder);END;出現(xiàn)以下錯(cuò)誤

31、：ORA-06550: 第 6 行, 第 26 列: PLS-00619: NULLIF 表達(dá)式中的第一個(gè)操作數(shù)必須不為 NULLORA-06550: 第 6 行, 第 4 列: PL/SQL: Statement ignored35NULLI F和COALESCE函數(shù) 5-4COALESCE函數(shù)會(huì)把表達(dá)式列表中每個(gè)表達(dá)式與NULL比較，并返回第一個(gè)非NULL表達(dá)式的值。COALESCE函數(shù)具有如下結(jié)構(gòu): COALESCE (expressionl , expression2, ., expressionN)如果expression1等于NULL，會(huì)計(jì)算expression2。如果expression2的計(jì)算結(jié)果