This tutorial is based on examples to be easier to follow. The Oracle CASE expression allows to do comparison with values as for example IF expression does but Oracle Case would not need to be execute inside an additional procedure as it needs to be done with IF expression. The Oracle Case expression syntax are:
-- 1. simple case expression 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 -- 2. searched case expression CASE WHEN <the_conditions> THEN <return_value> [ WHEN <the_conditions_2> THEN <return_value_2> .... ELSE <other_value> ] END
As you see we got two Case expressions and first of them the simple case expression lets you to do a simple conditions to find a match. The searched case expression looks almost the same as the first expression but you can use this style to write more complicated conditions and use different values to find matching.
The first Oracle Case query is written with simple case expression and the source query (SELECT 3 AS a_number FROM dual) returns only 1 row with value 3. The Case expression needs to find in the list “WHEN 3” and return text “three“.
SELECT CASE a_number WHEN 0 THEN 'zero' WHEN 1 THEN 'one' WHEN 2 THEN 'two' WHEN 3 THEN 'three' WHEN 4 THEN 'four' END AS number_in_a_word FROM (SELECT 3 AS a_number FROM dual );
The second Oracle Case query is with searched case expression and the condition is containing different value pairs as for example a_number is 1 and text should be “MySQL“. In this time the source query (SELECT 3 AS a_number, ‘Oracle’ AS text FROM dual) returns two value a number and a text and they are 3 and “Oracle“. The Case expression returns “The number is three and the text is Oracle” as this is the returning value for the second condition (WHEN a_number = 3 AND text = ‘Oracle’).
SELECT CASE WHEN a_number = 1 AND text = 'MySQL' THEN 'The number is one and the text is MySQL' WHEN a_number = 3 AND text = 'Oracle' THEN 'The number is three and the text is Oracle' WHEN a_number = 10 AND text = 'MsSQL' THEN 'The number is ten and the text is MsSQL' ELSE 'Unknown number and text' END AS number_in_a_word FROM (SELECT 3 AS a_number, 'Oracle' AS text FROM dual );
In this following example we would like to show what happens when two values are correct in the Case conditions. The “source” query returns the same value (number 3) as in the first example but we do have set two “WHEN 3” conditions. First of them returns text “one” and the second “three“. Take a look at the example below output returns on the “one” text and ignores the “three” text. The reason is in the Oracle Case expression that takes first matching value from up to down and ignores rest of values.
SELECT CASE a_number WHEN 0 THEN 'zero' WHEN 3 THEN 'one' WHEN 2 THEN 'two' WHEN 3 THEN 'three' WHEN 4 THEN 'four' ELSE 'unknown' END AS number_in_a_word FROM (SELECT 3 AS a_number FROM dual );
As you can see on the last output Oracle Case takes only the first matching condition and ignores the rest of values, so when you did a mistake in the value declaration the Case would not raise an error.