Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

EnterpriseDB: CASE STATEMENT
Oracle Tips by Burleson
 

There are two versions of the CASE statement.  The first that I will cover is the "simple case" or "selector case".  The syntax for a simple case statement is:

CASE <expression>
WHEN <value>
  <executable code>
[WHEN <value>
  <executable code>]
.
.
.
[ELSE
  <executable code>]
END CASE;

The second version of a CASE statement is the "complex case" or "searched case".  The syntax for the complex case is:

CASE

WHEN <expression>
  <executable code>
[WHEN <expression>
  <executable code>]
.
.
.
[ELSE
  <executable code>]
END CASE;

Here is an example that shows the same functional results using both types of the CASE statement as well as an IF statement.

DECLARE
  v_var1 NUMBER := 3;
BEGIN

  -- IF statement

  IF v_var1 = 1
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 1');
  ELSIF v_var1 = 2
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 2');
  ELSIF v_var1 = 3

 
THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 3');
  ELSIF v_var1 = 4
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 4');
  ELSE
    DBMS_OUTPUT.PUT_LINE( 'v_var1 NOT IN (1,2,3,4)');
  END IF;

  -- Simple case

  CASE v_var1
  WHEN 1
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 1');
  WHEN 2
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 2');
  WHEN 3
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 3');
  WHEN 4
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 4');

 
ELSE
    DBMS_OUTPUT.PUT_LINE( 'v_var1 NOT IN (1,2,3,4)');
  END CASE;

  -- Complex Case

  CASE
  WHEN v_var1 = 1
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 1');
  WHEN v_var1 = 2
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 2');
  WHEN v_var1 = 3
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 3');

 
WHEN v_var1 = 4
  THEN
    DBMS_OUTPUT.PUT_LINE( 'v_var1 = 4');
  ELSE
    DBMS_OUTPUT.PUT_LINE( 'v_var1 NOT IN (1,2,3,4)');
  END CASE;

END; 

INFO:  v_var1 = 3
INFO:  v_var1 = 3
INFO:  v_var1 = 3

Both types of case can also be used an as expression.  An expression is a statement that evaluates to a result.  To show a simple example:

DECLARE
  v_var1 NUMBER := 3;
  v_var2 VARCHAR2(50);
BEGIN
  v_var2 :=
    CASE v_var1
    WHEN 1
    THEN
      'v_var1 = 1'
    WHEN 2

   
THEN
      'v_var1 = 2'
    WHEN 3
    THEN
      'v_var1 = 3'
    WHEN 4
    THEN
      'v_var1 = 4'
    ELSE
      'v_var1 NOT IN (1,2,3,4)'
    END; 

  DBMS_OUTPUT.PUT_LINE( v_var2 );

  v_var2 :=
    CASE
    WHEN v_var1 = 1
    THEN
      'v_var1 = 1'
    WHEN v_var1 = 2
    THEN
      'v_var1 = 2'
    WHEN v_var1 = 3
    THEN
      'v_var1 = 3'
    WHEN v_var1 = 4
    THEN
      'v_var1 = 4'
    ELSE
      'v_var1 NOT IN (1,2,3,4)'
    END; 

  DBMS_OUTPUT.PUT_LINE( v_var2 ); 

END;

The main differences between a case statement and a case expression are:

* The case statement ends with the END CASE keywords and the case expression ends with an END keyword

* The when value of a case statement is an assignment or action (contains executable code), the case expression can only return a value expression

* The executable code of a case statement is terminated with a semi-colon, while the value expression of a case expression is not.

* A case expression can be used in a SQL statement, a case statement cannot

If you have an expression that can only resolve to TRUE, FALSE or Unknown (NULL), a best practice would dictate the use of an IF statement.  If you need to compare against multiple value sets, you should use a case statement.

If you need to use a case statement in SQL, use the case expression format of the CASE keyword.

             

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter