1、条件控制之CASE语句,目标,CASE语句 CASE表达式 NULLIF和COALESCE函数,CASE语句 18-1,CASE语句存在两种形式: simple CASE和搜索式CASE 。 CASE语句可以设定选择器，选择器会决定需要执行哪组动作。而搜索式CASE语句没有选择器，搜索条件会按顺序计算，从而决定采取哪组动作 Simple CASE语句具有如下结构: CASE selector WHEN expression_1 THEN statement 1; WHEN expression_2 THEN statement 2; WHEN expression_n THEN stateme
2、nt n; ELSE statement n+1; END CASE;,CASE语句 18-2,保留字CASE标识CASE语句的开始。选择器决定哪个WHEN子句应该被执行。每个WHEN子句都包含一个expression以及与之关联的一个或者多个可执行语句。ELSE子句是可选的。它的工作方式非常类似于IF-THEN-ELSE语句中所使用的ELSE子句。END CASE是标识CASE语句结束的保留字 注意，选择器只会计算一次，并且会顺序计算WHEN子句。表达式的值与选择器的值进行比较。如果两个值相等，那么与特定WHEN子句相关的语句会执行，并且随后的WHEN子句不会被计算。如果任何表达式的值都不匹
3、配选择器的值，则ELSE子句会被选中和执行,CASE语句 18-3,示例1：使用simple CASE语句判断用户输入的数字是否为偶数 DECLAREv_num NUMBER := ,CASE语句 18-4,搜索式CASE语句 搜索式CASE语句有个能够产生布尔值(TRUE、FALSE或者NULL)的搜索条件。当特定搜索条件的计算结果为TRUE时，会执行与该条件相关的语句组合。搜索式CASE语句的语法如下所示: CASE WHEN search condition_1 THEN statement 1; WHEN search condition_2 THEN statement 2; WHE
4、N search condition_n THEN statement n; ELSE statement n+1; END CASE;,CASE语句 18-5,当搜索条件的计算结果为TRUE时，执行控制权传递给与之相关的语句。如果任何搜索条件都不会产生TRUE，则就会执行与ELSE子句相关的语句。注意， ELSE子句是可选的 示例2：使用搜索式 CASE语句判断用户输入的数字是否为偶数 DECLAREv_num NUMBER := ,CASE语句 18-6,Simple CASE 语句和搜索式CASE语句之间的差别 搜索式CASE语句没有选择器。除此之外，它的WHEN子句包含能够产生布尔值的
5、搜索条件 Simple CASE语句具有选择器，它的WHEN子句表达式的值要与选择器的值进行比较，因此表达式返回的数据类型必须匹配选择器的数据类型，否则会出现语法错误 示例3： DECLAREv_num NUMBER := ,CASE语句 18-7,在这个例子中，变量 v_num_flag已经被定义为NUMBER类型。但是，每个表达式的结果一个Boolean类型。因此，这个范例会产生如下语法错误: ORA-06550: 第 6 行, 第 9 列: PLS-00615: 在 CASE 操作数和 WHEN 操作数之间的 V_NUM_FLAG 处发现不匹配的类型 ORA-06550: 第 6 行,
6、第 4 列: PL/SQL: Statement ignored,CASE语句 18-8,示例4：将上例子中v_num_flag变量的数据类型改为Boolean DECLAREv_num NUMBER := ,CASE语句 18-9,执行上例，给变量v_num赋值7，产生如下输出： 7 is odd number DonePL/SQL procedure successfully completed 再次运行，给变量v_num赋值4，产生如下输出： 4 is odd number DonePL/SQL procedure successfully completed 第二次的结果是错误的。原因
7、是？,CASE语句 18-10,当给变量v_num赋值4时，表达式MOD(v_num,2) = 0会返回TRUE，并且与选择器v_num_flag的值进行比较。但是， v_num_flag变量没有被初始化为任何值，其值为NULL。由于NULL与任何值进行比较，包括NULL本身，其结果都是NULL，所以与ELSE子句相关的语句会执行,CASE语句 18-11,示例5：使用simple CASE语句，在屏幕上显示基于每周中日期编号的当天名称。换句话说，如果某天是一周中的第3天，则显示Tuesday SET SERVEROUTPUT ON DECLAREv_date DATE := TO_DATE(
8、,CASE语句 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_OUTPUT.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 _d
9、ate的值是15-1月-2008，请问屏幕上会输出什么内容?,CASE语句 18-13,应该输出如下结果: Today is TuesdayPL/SQL procedure successfully completed 当变量v _date的输入值是15-3月-2008时，借助于TO_ CHAR函数，可以得到v_day变量的值，也就是一周中的哪天。接下来， CASE语句的每个表达式会顺序地与该选择器的值进行比较。由于选择器的值是3，则与第3个WHEN子句相关的DBMS_OUTPUT.PUT_LINE语句会执行。因此，消息Today is Tuesday会显示在屏幕上。其它表达式不再计算，程序执
10、行权会转到END CASE语句后的第一个可执行语句,CASE语句 18-14,B) CASE选择器v_day被计算多少次？CASE选择器v_day只会被计算一次。但是，会顺序地检查WHEN子句。当WHEN子句中表达式的值等于选择器的值时，会执行与该WHEN子句相关的语句 C）在CASE语句中使用ELSE子句，重新编写这个脚本 只需将最后一个WHEN子句用ELSE子句替代即可 D)使用搜索式CASE语句，重新编写这个脚本,CASE语句 18-15,示例6：使用搜索式CASE语句显示注册到课程25的指定班级的学生的分数（以字母表示） SET SERVEROUTPUT ON DECLAREv_stu
11、dent_id NUMBER := 102; -学生v_section_id NUMBER := 89; -班级v_final_grade NUMBER;v_letter_grade CHAR(1); BEGINSELECT final_gradeINTO v_final_gradeFROM enrollmentWHERE student_id = v_student_idAND section_id = v_section_id;,CASE语句 18-16,CASEWHEN v_final_grade = 90 THEN v_letter_grade := A;WHEN v_final_gr
12、ade = 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 := D;ELSE v_letter_grade := F;END CASE;DBMS_OUTPUT.PUT_LINE (Letter grade is: |v_letter_grade); END; A)屏幕上会显示什么表示分数？1)如果变量v_final_grade的值等于60呢？2)如果v_final_grade的值大于60，小于70呢？
13、3)如果v_final_grade的值是NULL呢？,CASE语句 18-17,B)如何修改上述代码，以便当v_final_grade变量的值是NULL时，显示消息There is no final grade？除此之外，要确保屏幕上不要显示Letter grade is: 1、需要引入一个替代变量来输入不同的v_student_id值 2、为实现期望的效果，应该嵌套CASE语句。外部的CASE语句会计算v_final_grade变量的值。如果v_final_grade的值是NULL，屏幕上会显示消息There is no final grade.。如果v_final_grade的值不是NUL
14、L，则执行外部CASE语句的ELSE部分,CASE语句 18-18,CASE -outer CASEWHEN v_final_grade IS NULL THENDBMS_OUTPUT.PUT_LINE(There is no final grade.); ELSE CASE -inner CASEWHEN v_final_grade = 90 THEN v_letter_grade := A; 3、为保证只有变量v_final_grade的值不是NULL时，才会在屏幕上显示Letter grade.，应该在外部CASE语句的ELSE子句中使用如下语句: DBMS_OUTPUT.PUT_LIN
15、E (Letter grade is: |v_letter_grade);,CASE表达式 12-1,我们知道表达式的计算结果会产生赋予某个变量的一个值。类似地， CASE表达式也可以计算出一个值，这个值也可以被赋值给变量 CASE表达式有个几乎等同于CASE语句的结构。因此，它有两种形式: simple CASE和搜索式CASE 示例1：判断用户输入的数字是奇数还是偶数，并打印出结果。使用simple case语句实现： DECLAREv_num NUMBER := -计算除2余数,CASE表达式 12-2,CASE v_num_flagWHEN 0 THEN DBMS_OUTPUT.PUT
16、_LINE (v_num| is even number);ELSE DBMS_OUTPUT.PUT_LINE (v_num| is odd number);END CASE;DBMS_OUTPUT.PUT_LINE (Done); END; 示例2：使用 simple case表达式改写示例1： DECLAREv_num NUMBER := - -计算除2余数,CASE表达式 12-3,-给结果变量赋值 v_result := CASE v_num_flagWHEN 0 THEN v_num| is even numberELSE v_num| is odd numberEND;DBMS_O
17、UTPUT.PUT_LINE (v_result);DBMS_OUTPUT.PUT_LINE (Done); END; 该例中，使用一个新的变量v_result来保存CASE表达式所返回的值,CASE表达式 12-4,要特别注意CASE语句和CASE表达式之间的语法差别：,CASE表达式 12-5,在CASE语句中，WHEN和ELSE子句都包含一个可执行的语句。每个可执行语句的结尾处都是分号 在CASE表达式中， WHEN和ELSE子句所包含的表达式的结尾处不是分号。分号出现在保留字END的后面，保留字END终止CASE表达式 最后， CASE语句被保留字END CASE所终止 示例3：使用搜
18、索式 case表达式改写示例1： DECLAREv_num NUMBER := ,CASE表达式 12-6,BEGINv_result := CASEWHEN MOD(v_num,2) = 0 THEN v_num| is even numberELSE v_num| is odd numberEND;DBMS_OUTPUT.PUT_LINE (v_result);DBMS_OUTPUT.PUT_LINE (Done); END; 在该例中，没有必要声明变量v_num_flag ，因为搜索式CASE表达式不需要选择器值，并且MOD函数的结果会集成到搜索条件中,CASE表达式 12-7,前面的示
19、例中，case表达式的值是通过:=操作符赋给变量的，使用select into，也可以将case表达式的值赋给变量 示例4： DECLAREv_course_no NUMBER;v_description VARCHAR2(50);v_prereq VARCHAR2(35); BEGIN,CASE表达式 12-8,SELECT course_no, description,CASEWHEN prerequisite IS NULL THEN No prerequisite course requiredELSE TO_CHAR(prerequisite)END prerequisiteINTO
20、 v_course_no, v_description, v_prereqFROM courseWHERE course_no = 20;DBMS_OUTPUT.PUT_LINE (Course: |v_course_no);DBMS_OUTPUT.PUT_LINE (Description: |v_description);DBMS_OUTPUT.PUT_LINE (Prerequisite: |v_prereq); END;,CASE表达式 12-9,分析： 这个例子会在屏幕上显示课程编号、课程名称以及预备课程。除此之外，如果某课程没有预备课程，会在屏幕上显示一个消息。为达到期望的结果，
21、CASE表达式被用作SELECT INTO语句的某个列。它的值会赋予变量v_prereq。注意，CASE表达式中保留字END后面没有分号 因为课程20没有预备课程。因此，搜索式条件 WHEN prerequisite IS NULL THEN 的计算结果为TRUE，并且会把No prerequisite course required赋予变量v_prereq,CASE表达式 12-10,为什么在CASE表达式的ELSE字句中使用函数TO_CHAR? 即 ELSE TO_CHAR(prerequisite) CASE表达式会返回一个值，需要定义一种数据类型。因此，不管CASE表达式的哪部分执行，
22、都会始终返回相同的数据类型，这一点非常重要。在前面的CASE表达式中， WHEN子句会返回VARCHAR2数据类型。ELSE子句返回COURSE表中PREREQUISITE列的值。这个列已经被定义为NUMBER，因此也必须把它转换为字符串类型，否则会有语法错误发生,CASE表达式 12-11,示例5：使用case表达式以及select into来显示课程25的特定班所注册学生的分数(字母表示) SET SERVEROUTPUT ON DECLAREv_student_id NUMBER := 102;v_section_id NUMBER := 89;v_letter_grade CHAR(1
23、); BEGIN,CASE表达式 12-12,SELECT CASEWHEN final_grade = 90 THEN AWHEN final_grade = 80 THEN BWHEN final_grade = 70 THEN CWHEN final_grade = 60 THEN DELSE FENDINTO v_letter_gradeFROM enrollmentWHERE student_id = v_student_idAND section_id = v_section_id;DBMS_OUTPUT.PUT_LINE (Letter grade is: |v_letter_g
24、rade); END;,NULLI F和COALESCE函数 5-1,在ANSI 1999 SQL标准中， NULLIF和COALESCE函数被定义为“CASE缩写词”。两个函数都被视为CASE表达式的变种 NULLIF函数会比较两个表达式。如果两者相同，函数会返回NULL。否则，返回第一个表达式的值。NULLIF函数原型如下: NULLIF (expression1 , expression2) 如果expression1等于expression2，则NULLIF返回NULL。如果expression1不等于expression2 , NULLIF函数返回expression1 。注意， N
25、ULLIF函数的作用与NVL函数相反。 NVL函数是：如果第一个表达式是NULL， NVL函数返回第二个表达式。如果第一个表达式不是NULL， NVL返回第一个表达式,NULLI F和COALESCE函数 5-2,示例1： DECLAREv_num NUMBER := ,NULLI F和COALESCE函数 5-3,NULLIF函数有一个限制：不能把字面值NULL赋给expression1 示例2： DECLAREv_remainder NUMBER; BEGIN- 计算余数，如果余数为0则返回NULLv_remainder := NULLIF(NULL,0);DBMS_OUTPUT.PUT_
26、LINE (v_remainder: |v_remainder); END; 出现以下错误： ORA-06550: 第 6 行, 第 26 列: PLS-00619: NULLIF 表达式中的第一个操作数必须不为 NULL ORA-06550: 第 6 行, 第 4 列: PL/SQL: Statement ignored,NULLI F和COALESCE函数 5-4,COALESCE函数会把表达式列表中每个表达式与NULL比较，并返回第一个非NULL表达式的值。COALESCE函数具有如下结构: COALESCE (expressionl , expression2, ., expressi
27、onN) 如果expression1等于NULL，会计算expression2。如果expression2的计算结果不为NULL，这个函数会返回expression2。如果expression2的计算结果也是NULL，则接下来的一个表达式会被计算。如果所有表达式的计算结果都是NULL，则这个函数返回NULL,NULLI F和COALESCE函数 5-5,示例3： SELECT e.student_id, e.section_id, e.final_grade, g.numeric_grade, COALESCE(e.final_grade, g.numeric_grade, 0) grade
28、FROM enrollment e, grade gWHERE e.student_id = g.student_idAND e.section_id = g.section_idAND e.student_id = 102AND g.grade_type_code = FI;STUDENT_ID SECTION_ID FINAL_GRADE NUMERIC_GRADE GRADE - - - - -102 86 85 85102 89 92 92 92,练习题,执行如下两个SELECT语句，并解释为什么会产生不同的输出: SELECT e.student_id, e.section_id,
29、e.final_grade , g.numeric_grade, COALESCE(g.numeric_grade , e.final_grade) grade FROM enrollment e , grade gWHERE e.student_id = g.student_id AND e.section_id = g.section_id AND e.student_id = 102 AND g.grade_type_code = FI;,练习题,SELECT e.student_id, e.section_id, e.final_grade , g.numeric_grade , NULLIF(g.numeric_grade， e.final_grade) grade FROM enrollment e , grade g WHERE e.student_id = g.student_id AND e.section_id = g.section_id AND e.student_id = 102 AND g.grade_type_code = FI;,
