PL/SQL CASE Statement

This is an alternative to if… then… else… elsif… end if when you want to use conditional statements in PL/SQL. Here is a simple example, which checks whether a given number is 1 or 2. You start with the word case. Then you add one or more conditions followed by the action to take if that condition is satisfied. Each condition is preceded by the word when. You finish with the words end case:

SQL> declare

  2    one_or_two number := 1;

  3  begin

  4    case

  5    when one_or_two = 1 then

  6      dbms_output.put_line('One');

  7    when one_or_two = 2 then

  8      dbms_output.put_line('Two');

  9    end case;

 10  end;

 11  /

One

 

PL/SQL procedure successfully completed.

 

SQL>

The next example puts the variable name after the word case. This saves you having to include it in every when line:

SQL> declare

  2    one_or_two number := 2;

  3  begin

  4    case one_or_two

  5    when 1 then dbms_output.put_line('One');

  6    when 2 then dbms_output.put_line('Two');

  7    end case;

  8  end;

  9  /

Two

 

PL/SQL procedure successfully completed.

 

SQL>

If none of the when clauses are satisfied, you get an ORA-06592:

SQL> declare

  2    one_or_two number := 3;

  3  begin

  4    case

  5    when one_or_two = 1 then

  6      dbms_output.put_line('One');

  7    when one_or_two = 2 then

  8      dbms_output.put_line('Two');

  9    end case;

 10  end;

 11  /

declare

*

ERROR at line 1:

ORA-06592: CASE not found while executing CASE

statement

ORA-06512: at line 4

 

SQL>

One way to avoid this is to add an else clause before the end case:

SQL> declare

  2    one_or_two number := 3;

  3  begin

  4    case one_or_two

  5    when 1 then dbms_output.put_line('One');

  6    when 2 then dbms_output.put_line('Two');

  7    else dbms_output.put_line('Try again');

  8    end case;

  9  end;

 10  /

Try again

 

PL/SQL procedure successfully completed.

 

SQL>

An alternative is to add an exception section and check for case_not_found:

SQL> declare

  2    one_or_two number := 3;

  3  begin

  4    case

  5    when one_or_two = 1 then

  6      dbms_output.put_line('One');

  7    when one_or_two = 2 then

  8      dbms_output.put_line('Two');

  9    end case;

 10  exception

 11    when case_not_found then

 12      dbms_output.put_line('Try again');

 13  end;

 14  /

Try again

 

PL/SQL procedure successfully completed.

 

SQL>

Once a condition has been satisfied, Oracle jumps to the end case statement, ignoring the remaining conditions:

SQL> declare

  2    one_or_two number := 1;

  3  begin

  4    case

  5    when one_or_two = 1 then

  6      dbms_output.put_line('One');

  7    when one_or_two < 2 then

  8      dbms_output.put_line('Less than two');

  9    end case;

 10  exception

 11    when case_not_found then

 12      dbms_output.put_line('Try again');

 13  end;

 14  /

One

 

PL/SQL procedure successfully completed.

 

SQL>

You can even use a Boolean expression after the word case, as you can see in the two examples below:

SQL> begin

  2  case 1 = 1

  3  when true then

  4    dbms_output.put_line('1 = 1');

  5  when false then

  6    dbms_output.put_line('1 != 1');

  7  end case;

  8  end;

  9  /

1 = 1

 

PL/SQL procedure successfully completed.

 

SQL> begin

  2  case 1 = 2

  3  when true then

  4    dbms_output.put_line('1 = 2');

  5  when false then

  6    dbms_output.put_line('1 != 2');

  7  end case;

  8  end;

  9  /

1 != 2

 

PL/SQL procedure successfully completed.

 

SQL>

These examples were all tested in an Oracle 11 database.

CASE

Tested on an Oracle 11 database. The CASE expression allows you to add conditional tests to SQL statements. First create a table with a few rows of test data:

SQL> create table test_segments as
  2  select *
  3  from dba_segments where 1=2
  4  /

Table created.

SQL> insert into test_segments
  2  select * from dba_segments
  3  where bytes < 100000
  4  and owner = 'SYS'
  5  and rownum < 6
  6  /

5 rows created.

SQL> insert into test_segments
  2  select * from dba_segments
  3  where bytes >= 100000
  4  and bytes < 150000
  5  and owner = 'SYS'
  6  and rownum < 6
  7  /

5 rows created.

SQL> insert into test_segments
  2  select * from dba_segments
  3  where bytes >= 150000
  4  and owner = 'SYS'
  5  and rownum < 6
  6  /

5 rows created.

SQL>

A very simple CASE expression might have a couple of WHEN conditions. If none of these are satisfied, the output will be blank:

SQL> select segment_name,
  2  case
  3   when bytes < 100000 then 'SMALL'
  4   when bytes < 150000 then 'MEDIUM'
  5  end as object_size
  6  from test_segments
  7  order by 1
  8  /

SEGMENT_NAME                   OBJECT_SIZE
------------------------------ -----------
C_USER#
I_CCOL1
I_CDEF3                        SMALL
I_COBJ#                        MEDIUM
I_COL3
I_FILE1                        SMALL
I_IND1                         MEDIUM
I_OBJ1
I_OBJ2
I_TAB1                         SMALL
I_TS#                          SMALL
I_USER#                        MEDIUM
I_USER2                        MEDIUM
I_VIEW1                        MEDIUM
UNDO$                          SMALL

15 rows selected.

SQL> 

To get round this, you can add an ELSE at the end, if you wish:

SQL> select segment_name,
  2  case
  3   when bytes < 100000 then 'SMALL'
  4   when bytes < 150000 then 'MEDIUM'
  5   else 'LARGE'
  6  end as object_size
  7  from test_segments
  8  order by 1
  9  /

SEGMENT_NAME                   OBJECT_SIZE
------------------------------ -----------
C_USER#                        LARGE
I_CCOL1                        LARGE
I_CDEF3                        SMALL
I_COBJ#                        MEDIUM
I_COL3                         LARGE
I_FILE1                        SMALL
I_IND1                         MEDIUM
I_OBJ1                         LARGE
I_OBJ2                         LARGE
I_TAB1                         SMALL
I_TS#                          SMALL
I_USER#                        MEDIUM
I_USER2                        MEDIUM
I_VIEW1                        MEDIUM
UNDO$                          SMALL

15 rows selected.

SQL>

And you can even have nested CASE expressions. Also note how the CASE may, or may not, be followed immediately by a column name:

SQL> select segment_name,
  2  case segment_type
  3   when 'TABLE' then
  4   case
  5    when bytes < 100000 then 'SMALL TABLE'
  6    when bytes < 150000 then 'MEDIUM TABLE'
  7    else 'LARGE TABLE'
  8   end
  9   when 'INDEX' then
 10   case
 11    when bytes < 100000 then 'SMALL INDEX'
 12    when bytes < 150000 then 'MEDIUM INDEX'
 13    else 'LARGE INDEX'
 14   end
 15  end as size_and_type
 16  from
 17  test_segments
 18  order by 1
 19  /

SEGMENT_NAME                   SIZE_AND_TYPE
------------------------------ -------------
C_USER#
I_CCOL1                        LARGE INDEX
I_CDEF3                        SMALL INDEX
I_COBJ#                        MEDIUM INDEX
I_COL3                         LARGE INDEX
I_FILE1                        SMALL INDEX
I_IND1                         MEDIUM INDEX
I_OBJ1                         LARGE INDEX
I_OBJ2                         LARGE INDEX
I_TAB1                         SMALL INDEX
I_TS#                          SMALL INDEX
I_USER#                        MEDIUM INDEX
I_USER2                        MEDIUM INDEX
I_VIEW1                        MEDIUM INDEX
UNDO$                          SMALL TABLE

15 rows selected.

SQL>