Oracle9i中CASE语句的使用介绍

Oracle9i中CASE语句的使用介绍

概述 Starting with Oracle9i, PL/SQL supports the use of case statements and case expressions. These constructs are often convenient to use in place of complex if-then-else logic and function similarly to counterparts in other languages (e.g. the C switch statement). Case statements are standalone statements that can appear anywhere any other PL/SQL statement can appear. Case expressions are similar to case statements, but return a value and can only appear in places where any other PL/SQL expression can appear. There are also some subtle syntax differences between case statements and expressions; the differences will be pointed out where appropriate in this article. 程序说明 以下所有程序均在windows xp 下Oracle 9.2.0.1上测试通过，所有的SQL均可以在SQL*PLUS或plsql dev中测试运行。 参考资料 PL/SQL User's Guide and Reference 程序 Sample 1: A simple example demonstrating the proper syntax for a case statement using a character variable as the selector. See the section entitled 'Restrictions' at the end of this article for details on which PLSQL datatypes may appear as a selector in a case statement or expression. - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - - set serveroutput on declare achar char(1) := '&achar'; begin case achar when 'A' then dbms_output.put_line('The description was Excellent'); when 'B' then dbms_output.put_line('The description was Very Good'); when 'C' then dbms_output.put_line('The description was Good'); when 'D' then dbms_output.put_line('The description was Fair'); when 'F' then dbms_output.put_line('The description was Poor'); else dbms_output.put_line('The description was No such Grade'); end case; end; / - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - - 　 Sample 2: A simple example demonstrating the proper syntax for a case expression using a character variable as the selector. See the section entitled 'Restrictions' at the end of this article for details on which PLSQL datatypes may appear as a selector in a case statement or expression. - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - - set serveroutput on declare achar char(1) := '&achar'; description varchar2(20); begin description := case achar when 'A' then 'Excellent' when 'B' then 'Very Good' when 'C' then 'Good' when 'D' then 'Fair' when 'F' then 'Poor' else 'No such grade' end; dbms_output.put_line('The description was ' || description); end; / - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - - NOTE: The above simple samples demonstrate two subtle differences in the syntax required for case statements and expressions. 1) A case STATEMENT is terminated using the 'end case' keywords; a case EXPRESSION is terminated using only the 'end' keyword. 2) Each item in a case STATEMENT consists of one or more statements, each terminated by a semicolon. Each item in a case expression consists of exactly one expression, not terminated by a semicolon. Sample 3: Sample 1 demonstrates a simple case statement in which the selector is compared for equality with each item in the case statement body. PL/SQL also provides a 'searched' case statement as an alternative; rather than providing a selector and a list of values, each item in the body of the case statement provides its own predicate. This predicate can be any valid boolean expression, but only one case will be selected. - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - - set serveroutput on declare achar char(1) := '&achar'; begin case when achar = 'A' then dbms_output.put_line('The description was Excellent'); when achar = 'B' then dbms_output.put_line('The description was Very Good'); when achar = 'C' then dbms_output.put_line('The description was Good'); when achar = 'D' then dbms_output.put_line('The description was Fair'); when achar = 'F' then dbms_output.put_line('The description was Poor'); else dbms_output.put_line('The description was No such Grade'); end case; end; / - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - - 　 Sample 4: This sample demonstrates the proper syntax for a case expression of the type discussed in Sample 3 above. - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - - set serveroutput on declare achar char(1) := '&achar'; description varchar2(20); begin description := case when achar = 'A' then 'Excellent' when achar = 'B' then 'Very Good' when achar = 'C' then 'Good' when achar = 'D' then 'Fair' when achar = 'F' then 'Poor' else 'No such grade' end; dbms_output.put_line('The description was ' || description); end; / - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - - Sample 5: This sample demonstrates the use of nested case statements. It is also permissable to nest case expressions within a case statement (though it is not demonstrated here), but nesting of case statements within a case expression is not possible since statements do not return any value. - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - - set serveroutput on declare anum1 number := &anum1; anum2 number := &anum2; answer number; begin case anum1 when 1 then case anum2 when 1 then answer := 10; when 2 then answer := 20; when 3 then answer := 30; else answer := 999; end case; when 2 then case anum2 when 1 then answer := 15; when 2 then answer := 25; when 3 then answer := 35; else answer := 777; end case; else answer := 555; end case; dbms_output.put_line('The answer is ' || answer); end; / - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - - Sample 6: This sample demonstrates nesting of case expressions within another case expression. Note again the absence of semicolons to terminate both the nested case expression and the individual cases of those expressions. - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - - set serveroutput on declare anum1 number := &anum1; anum2 number := &anum2; answer number; begin answer :=case anum1 when 1 then case anum2 when 1 then 10 when 2 then 20 when 3 then 30 else 999 end when 2 then case anum2 when 1 then 15 when 2 then 25 when 3 then 35 else 777 end else 555 end; dbms_output.put_line('The answer is ' || answer); end; / - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - - Although PL/SQL anonymous blocks have been used in all of the examples so far,case statements and expressions can also be used in procedures, functions, and packages with no changes to the syntax. The following samples are included for completeness and demonstrate the use of case statements and/or expressions in each of these scenarios. Sample 7: This sample demonstrates use of a case statement in a stored procedure. Note that this sample also demonstrates that it is possible for each of the items in the case body to consist of more than one statement. - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - - set serveroutput on create or replace procedure testcasestmt ( anum IN number ) is begin case when anum = 1 then dbms_output.put_line('The number was One'); dbms_output.put_line('In case 1'); when anum = 2 then dbms_output.put_line('The number was Two'); dbms_output.put_line('In case 2'); when anum = 3 then dbms_output.put_line('The number was Three'); dbms_output.put_line('In case 3'); when anum = 4 then dbms_output.put_line('The number was Four'); dbms_output.put_line('In case 4'); when anum = 5 then dbms_output.put_line('The number was Five'); dbms_output.put_line('In case 5'); else dbms_output.put_line('The description was Invalid input'); dbms_output.put_line('In the else case'); end case; end; / exec testcasestmt(&anum); - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - - Sample 8: This sample demonstrates the use of a case statement in a stored package. - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - - set serveroutput on create or replace package testpkg2 is procedure testcasestmt ( anum IN number ); function testcasestmt_f ( anum IN number ) return number; end testpkg2; / create or replace package body testpkg2 is procedure testcasestmt ( anum IN number ) is begin case when anum = 1 then dbms_output.put_line('The number was One'); dbms_output.put_line('In case 1'); when anum = 2 then dbms_output.put_line('The number was Two'); dbms_output.put_line('In case 2'); when anum = 3 then dbms_output.put_line('The number was Three'); dbms_output.put_line('In case 3'); when anum = 4 then dbms_output.put_line('The number was Four'); dbms_output.put_line('In case 4'); when anum = 5 then dbms_output.put_line('The number was Five'); dbms_output.put_line('In case 5'); else dbms_output.put_line('The description was Invalid input'); dbms_output.put_line('In the else case'); end case; end; function testcasestmt_f ( anum IN number ) return number is begin case when anum = 1 then dbms_output.put_line('The number was One'); dbms_output.put_line('In case 1'); when anum = 2 then dbms_output.put_line('The number was Two'); dbms_output.put_line('In case 2'); when anum = 3 then dbms_output.put_line('The number was Three'); dbms_output.put_line('In case 3'); when anum = 4 then dbms_output.put_line('The number was Four'); dbms_output.put_line('In case 4'); when anum = 5 then dbms_output.put_line('The number was Five'); dbms_output.put_line('In case 5'); else dbms_output.put_line('The description was Invalid input'); dbms_output.put_line('In the else case'); end case; return anum; end; end testpkg2; / exec testpkg2.testcasestmt(&anum); variable numout number exec :numout := testpkg2.testcasestmt_f(&anum); print numout - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - - Sample 9: This sample demonstrates the use of a case expression in a stored package. - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - - set serveroutput on create or replace package testpkg is procedure testcase ( anum IN number ); function testcase_f ( anum IN number ) return number; end testpkg; / create or replace package body testpkg is procedure testcase ( anum IN number ) is anumber number := anum; anothernum number; begin anothernum := case when anumber = 1 then anumber + 1 when anumber = 2 then anumber + 2 when anumber = 3 then anumber + 3 when anumber = 4 then anumber + 4 when anumber = 5 then anumber + 5 else 999 end; dbms_output.put_line('The number was ' || anothernum); end; function testcase_f ( anum IN number ) return number is anumber number := anum; anothernum number; begin anothernum := case when anumber = 1 then anumber + 1 when anumber = 2 then anumber + 2 when anumber = 3 then anumber + 3 when anumber = 4 then anumber + 4 when anumber = 5 then anumber + 5 else 999 end; dbms_output.put_line('The number was ' || anothernum); return anothernum; end; end testpkg; / variable numout number exec testpkg.testcase(&anum); exec :numout := testpkg.testcase_f(&anum); print numout - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - - 限制声明 The following datatypes ARE NOT supported to appear as the selector expression in a case statement or case expression: BLOB BFILE VARRAY Nested Table PL/SQL Record PL/SQL Version 2 tables (index by tables) Object type (user-defined type) All of these types, except for object types, face a similar restriction even for if statements (i.e. they cannot be compared for equality directly), so, this is unlikely to change for these types. Lack of support for object types is simply an implementation restriction which may be relaxed in future releases.

阅读全文