How To Use CASE Expression In Oracle PL/SQL

This help is based on examples so it would be easier to understand. Oracle Case expression allows to compare values and return the first matching option. Also the same result can be achieved with the IF expression that does the comparison and returns the first matching option. It depends on you with whom you want to go with. There are two types of syntax for Oracle Case expression and they are following.

The first is a simple case expression that does as the name says. It does let you to do a simple conditions to find a match.

CASE <value_to_compare>
     WHEN <value_to_compare_with> THEN <return_value>
   [ WHEN <value_to_compare_with_2> THEN <return_value_2>
     ....
     ELSE <other_value> ] 
END

The second Oracle Case expression type is searched case expression and looks almost the same as the simple case expression but you can use this style to write more complicated conditions and use different values to find a match.

CASE WHEN <the_conditions>   THEN <return_value>
   [ WHEN <the_conditions_2> THEN <return_value_2>
     ....
     ELSE <other_value> ] 
END

The first Oracle Case expression PL/SQL block is written with the simple case expression and the V_NUMBER variable has set value number “3“. The Oracle Case expression needs to find from the list condition “WHEN 3” and to return text “three“.

DECLARE
    v_number PLS_INTEGER := 3;
BEGIN
 DBMS_OUTPUT.PUT_LINE ('v_number is ' ||
   (CASE v_number
      WHEN 0 THEN 'zero'
      WHEN 1 THEN 'one'
      WHEN 2 THEN 'two'
      WHEN 3 THEN 'three'
      WHEN 4 THEN 'four'
    END)
   ) ;
END;
/

The Oracle Dbms_Output has returned text “v_number is three” and this string has been set by the DBMS_OUTPUT.PUT_LINE procedure in the beginning of the Case expression.

The second Oracle Case expression is with the searched case expression and the condition is containing different value pairs. For example when the V_NUMBER is 1 then the V_TEXT should be “MySQL” to become a match. The example below has set the V_NUMBER to 3 and the V_TEXT is “Oracle” and the match should return a text like “The number is x and the text is y“. For the V_NUMBER is “3” and V_TEXT is “Oracle” this Oracle Case expression should return text “The number is three and the text is Oracle” and it should match with the second condition (WHEN v_number = 3 AND v_text = ‘Oracle’).

DECLARE
    v_number PLS_INTEGER := 3;
    v_text VARCHAR2(10) := 'Oracle';
BEGIN
 DBMS_OUTPUT.PUT_LINE (
   CASE
       WHEN v_number = 1 AND v_text = 'MySQL'
       THEN 'The number is one and the text is MySQL'
       WHEN v_number = 3 AND v_text = 'Oracle'
       THEN 'The number is three and the text is Oracle'
       WHEN v_number = 10 AND v_text = 'MsSQL'
       THEN 'The number is ten and the text is MsSQL'
       ELSE 'Unknown number and text'
   END ) ;
END;
/

Once again the DBMS_OUTPUT returned the expected text but what will return the Oracle Case when we have declared two matching conditions. To keep this example simpler we will use the simple case expression but the same also goes to the searched case expression. We have set to the V_NUMBER variable a value “3” as we did on the first Oracle Case example but we do have set two “WHEN 3” conditions in this Case expression. The first match returns text “one” and the second “three“. Take a look at the script below.

DECLARE
    v_number PLS_INTEGER := 3;
BEGIN
 DBMS_OUTPUT.PUT_LINE ('The v_number is '||v_number||' and CASE returned: '||
  CASE v_number
        WHEN 0
        THEN 'zero'
        WHEN 3
        THEN 'one'
        WHEN 2
        THEN 'two'
        WHEN 3
        THEN 'three'
        WHEN 4
        THEN 'four'
        ELSE 'unknown'
    END  ) ;
END;
/

The DBMS_OUTPUT returned the text of the first condition (with text “one“) and ignored the second match with text “three“. The reason is in the Oracle Case expression that takes the first matching condition from up to down and ignores rest of them. The output text show text “The v_number is 3 and CASE returned: one“.

See Also:
Home