概述 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.