Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 28, 2006 - 9:41 pm UTC

Last updated: June 29, 2006 - 1:16 pm UTC

Version: 9.2

Viewed 1000+ times

You Asked

Tom,
What is the true distinction between a CASE "Expression" and a CASE "Statement"?

Is it simply that the "Statement" version allows a sequence of statements after each WHEN clause? It appears that an ELSE is not necessarily needed with the "Expression" version, whereas an ELSE or exception handler needs to be included with the "Statement" version?

Is there a preference to use one over the other?

Thanks.

and Tom said...

they are synonymous.

there is a searched case and a non-searched case, but a "case statement" is the same as a "case expression"


1 select user_id,
2 case user_id
3 when 5
4 then 'five'
5 else 'not five'
6 end searched_case,
7 case when user_id <> 5
8 then 'not five'
9 when user_id is not null
10 then 'five'
11 end not_searched_case
12 from all_users
13* where user_id <= 10
ops$tkyte@ORA10GR2> /

USER_ID SEARCHED NOT_SEAR
---------- -------- --------
0 not five not five
5 five five



Neither "mandates" an else:


1 select user_id,
2 case user_id
3 when 5
4 then 'five'
5 end searched_case,
6 case when user_id <> 5
7 then 'not five'
8 when user_id is not null
9 then 'five'
10 end not_searched_case
11 from all_users
12* where user_id <= 10
ops$tkyte@ORA10GR2> /

USER_ID SEAR NOT_SEAR
---------- ---- --------
0 not five
5 five five



Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Variation

A reader, June 29, 2006 - 12:25 pm UTC 

Tom,
Sorry I didn't provide the code previously.  This was the scenario I was interested in.

CASE "Statement"

SQL> declare
  2 v_color    vachar2(20) := 'BLUE';
  3 result    varchar2(20);
  4 begin
  5 case
  6   when v_color = 'RED' then result := 'Code RED';
  7   when v_color = 'WHITE' then result := 'Code WHITE';
  8 end case;
  9   dbms_output.put_line(nvl(result,'Code BLUE'));
 10 end;
 11 /
declare
*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 5


SQL> declare
  2 v_color    vachar2(20) := 'BLUE';
  3 result    varchar2(20);
  4 begin
  5 case
  6   when v_color = 'RED' then result := 'Code RED';
  7   when v_color = 'WHITE' then result := 'Code WHITE';
  8   else NULL;
  9 end case;
 10   dbms_output.put_line(nvl(result,'Code BLUE'));
 11 end;
 12 /
Code BLUE

PL/SQL procedure successfully completed.


Case "Expression"

SQL> declare
  2 v_color    vachar2(20) := 'BLUE';
  3 result    varchar2(20);
  4 begin
  5 result :=
  6 case
  7   when v_color = 'RED' then 'Code RED'
  8   when v_color = 'WHITE' then 'Code WHITE'
  9 end;
 10   dbms_output.put_line(nvl(result,'Code BLUE'));
 11 end;
 12 /
Code BLUE

PL/SQL procedure successfully completed.


Thanks again.

Tom Kyte
June 29, 2006 - 1:16 pm UTC

they are both case statements. call them expressions, doesn't really matter.

In plsql - it would make more "semantic" sense to call them case statements.

A reader, June 29, 2006 - 1:44 pm UTC 

I agree, however I was concerned with the need for an ELSE or an exception handler in one and not the other.

SQL> declare
  2 v_color    vachar2(20) := 'BLUE';
  3 result    varchar2(20);
  4 begin
  5 case
  6   when v_color = 'RED' then result := 'Code RED';
  7   when v_color = 'WHITE' then result := 'Code WHITE';
  8   else NULL;
  9 end case;
 10   dbms_output.put_line(nvl(result,'Code BLUE'));
 11 end;
 12 /
Code BLUE

PL/SQL procedure successfully completed.

ELSE (or an exception handler) is required above, or I receive the following:

SQL> declare
  2 v_color    vachar2(20) := 'BLUE';
  3 result    varchar2(20);
  4 begin
  5 case
  6   when v_color = 'RED' then result := 'Code RED';
  7   when v_color = 'WHITE' then result := 'Code WHITE';
  8 end case;
  9   dbms_output.put_line(nvl(result,'Code BLUE'));
 10 end;
 11 /
declare
*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at line 5


However, it what the documentation labels a CASE "Expression", an ELSE or exception handler isn't required.

SQL> declare
  2 v_color    vachar2(20) := 'BLUE';
  3 result    varchar2(20);
  4 begin
  5 result :=
  6 case
  7   when v_color = 'RED' then 'Code RED'
  8   when v_color = 'WHITE' then 'Code WHITE'
  9 end;
 10   dbms_output.put_line(nvl(result,'Code BLUE'));
 11 end;
 12 /
Code BLUE

PL/SQL procedure successfully completed.

more info on CASE statement versus expression

Donat Callens, September 20, 2007 - 7:42 am UTC

The answer to the above is explained there:
http://awads.net/wp/2007/07/25/what-you-ought-to-know-about-case-in-oracle-plsql
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.