Description
PLSQL Case Statement is an alternative to the IF statement and makes the code more readable and compact. The case can be expressed as a statement where one can execute the conditional logic based on the value comparisons or conditions. It can also be written as an expression where values are assigned and returns a variable. This is very useful while executing the conditional code and assigning the values to the same variable.
In simple case statement, the variable values to different cases will be compared and returns the very first match. It starts with a case keyword followed by a variable or an expression. The results are evaluated based on the values in the When clause from top to bottom and the ELSE clause is an alternative one. If the clause is not defined and there is no exact match found, the case statement will raise a CASE_NOT_FOUND exception. The CASE statement ends with END CASE statement.
Conceptual
figure
Syntax
CASE{variable or expression}
WHEN value_1 THEN
statements_1
WHEN value_n THEN
statement_n
[ELSE
statements defaults]
END CASE;
Condition =>Value assigned in the condition.
Statement =>Executes true or false statements.
Examples
The below example illustrates the PLSQL Case Statement.
[c]SQL> DECLARE
2 grade char(1):='A';
3 BEGIN
4 CASE grade
5 when 'A'then dbms_output.put_line('Excellent');
6 when 'B'then dbms_output.put_line('Very good');
7 when 'C'then dbms_output.put_line('Well done');
8 when 'D'then dbms_output.put_line('You passed');
9 when 'F'then dbms_output.put_line('Better try again');
10 else dbms_output.put_line('No such grade');
11 END CASE;
12 END;
13 /
Excellent
PL/SQL procedure successfully completed.[/c]
Key Points
- PLSQL Case Statement - works by comparing the conditional statements, and is alternative to the IF statement.