PL/SQL CASE Statement
The PL/SQL CASE statement allows you to execute a sequence of statements based on a selector. A selector can be anything such as variable, function, or expression that the CASE statement evaluates to a Boolean value.
You can use almost any PL/SQL data types as a selector except BLOB, BFILE and composite types.
Unlike the PL/SQL IF statement, PL/SQL CASE statement uses a selector instead of using a combination of multiple Boolean expressions.
CASE statement are following two types,
- Simple CASE Statement
Syntax:
CASE [ expression ]
WHEN expression_1 THEN result_1
WHEN expression_2 THEN result_2
...
WHEN expression_n THEN result_n
ELSE result
END
Example:
DECLARE
a number := 7;
BEGIN
CASE a
WHEN 1 THEN
DBMS_OUTPUT.PUT_LINE('value 1');
WHEN 2 THEN
DBMS_OUTPUT.PUT_LINE('value 2');
WHEN 3 THEN
DBMS_OUTPUT.PUT_LINE('value 3');
ELSE
DBMS_OUTPUT.PUT_LINE('no matching CASE found');
END CASE;
END;
/
Result:
no matching CASE found
PL/SQL procedure successfully operation
2.Searched CASE Statement
syntax:
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
Example:
DECLARE
a number := 3;
BEGIN
CASE
WHEN a = 1 THEN
DBMS_OUTPUT.PUT_LINE('value 1');
WHEN a = 2 THEN
DBMS_OUTPUT.PUT_LINE('value 2');
WHEN a = 3 THEN
DBMS_OUTPUT.PUT_LINE('value 3');
ELSE
DBMS_OUTPUT.PUT_LINE('no matching CASE found');
END CASE;
END;
/
Result:
value 3
PL/SQL procedure successfully operation