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.