3-4. Driving from an Expression Having Multiple Outcomes
Problem
You have a single expression that yields multiple outcomes. You are interested in evaluating the expression and performing a different set of statements depending upon the outcome.
Solution
Use a CASE statement to evaluate your expression, and decide which set of statements to execute depending upon the outcome. In the following example, a SQL*Plus script accepts a region entry, which is being evaluated to determine the set of statements to be executed. Based upon the value of the region, the corresponding set of statements is executed, and once those statements have been executed, then the control is passed to the statement immediately following the CASE statement.
DECLARE
region regions.region_name%TYPE := '®ion';
country_count number := 0;
BEGIN
CASE upper(region)
WHEN 'EUROPE' THEN
SELECT count(*)
INTO country_count
FROM countries
WHERE region_id = 1;
DBMS_OUTPUT.PUT_LINE('There are ' || country_count || ' countries in ' ||
'the Europe region.'),
WHEN 'AMERICAS' THEN
SELECT count(*)
INTO country_count
FROM countries
WHERE region_id = 2;
DBMS_OUTPUT.PUT_LINE('There are ' || country_count || ' countries in ' ||
'the Americas region.'),
WHEN 'ASIA' THEN
SELECT count(*)
INTO country_count
FROM countries
WHERE region_id = 3;
DBMS_OUTPUT.PUT_LINE('There are ' || country_count || ' countries in ' ||
'the Asia region.'),
WHEN 'MIDDLE EAST AND AFRICA' THEN
SELECT count(*)
INTO country_count
FROM countries
WHERE region_id = 4;
DBMS_OUTPUT.PUT_LINE('There are ' || country_count || ' countries in ' ||
'the Middle East and Africa region.'),
ELSE
DBMS_OUTPUT.PUT_LINE('You have entered an invaid region, please try again'),
END CASE;
END;
How It Works
There are two different types of CASE statements that can be used—those being the searched CASE and the simple CASE statement. The solution to this recipe demonstrates the simple CASE. For an example of a searched CASE statement, please see Recipe 3-3.
The simple CASE statement begins with the keyword CASE followed by a single expression called a selector. The selector is evaluated one time, and it can evaluate to any PL/SQL type other than BLOB, BFILE, an object type, a record, or a collection type. The selector is followed by a series of WHEN clauses. The WHEN clauses are evaluated sequentially to determine whether the value of the selector equals the result from any of the WHEN clause expressions. If a match is found, then the corresponding WHEN clause is executed.
The CASE statement can include any number of WHEN clauses, and much like an IF statement, it can be followed with a trailing ELSE clause that will be executed if none of the WHEN expressions matches. If the ELSE clause is omitted, a predefined exception will be raised if the CASE statement does not match any of the WHEN clauses. The END CASE keywords end the statement.