Summary: In this tutorial, you will learn how to use PL/SQL CASE statement to execute a sequence of statements based on a selector.
Introduction to 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 combination of multiple Boolean expressions. The following illustrates the PL/SQL CASE statement syntax:
[codesyntax lang="plsql" title="PL/SQL CASE Statement Syntax" bookmarkname="PL/SQL CASE Statement Syntax"]
[<<label_name>>] CASE [TRUE | selector] WHEN expression1 THEN sequence_of_statements1; WHEN expression2 THEN sequence_of_statements2; ... WHEN expressionN THEN sequence_of_statementsN; [ELSE sequence_of_statementsN+1;] END CASE [label_name];
Followed by the keyword CASE is a selector. The PL/SQL CASE statement evaluates the selector only once to decide which sequence of statements to execute. Followed by the selector is any number of the WHEN clause. If the selector value is equal to expression in the WHEN clause, the corresponding sequence of statement after the THEN keyword will be executed. If the selector’s value is not one of the choices covered by WHEN
ELSE RAISE CASE_NOT_FOUND;
If you use implicit ELSE clause in the PL/SQL CASE statement, exception CASE_NOT_FOUND is raised and can be handled in the exception handling part of the PL/SQL block as usual.
The keywords END CASE are used to terminate the CASE statement.
Example of Using PL/SQL CASE Statement
The following code snippet demonstrates the PL/SQL CASE statement. We’ll use table employees in the sample data HR in Oracle for demonstration.
[codesyntax lang="plsql" title="PL/SQL CASE Statement Example" bookmarkname="PL/SQL CASE Statement Example" highlight_lines="14,15,17,19,21,23"]
SET SERVEROUTPUT ON SIZE 1000000; DECLARE n_pct employees.commission_pct%TYPE; v_eval varchar2(10); n_emp_id employees.employee_id%TYPE := 145; BEGIN -- get commission percentage SELECT commission_pct INTO n_pct FROM employees WHERE employee_id = n_emp_id; -- evalutate commission percentage CASE n_pct WHEN 0 THEN v_eval := 'N/A'; WHEN 0.1 THEN v_eval := 'Low'; WHEN 0.4 THEN v_eval := 'High'; ELSE v_eval := 'Fair'; END CASE; -- print commission evaluation DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id || ' commission ' || TO_CHAR(n_pct) || ' which is ' || v_eval); END; /
PL/SQL Searched CASE Statement
PL/SQL provides a special CASE statement called searched CASE statement. The syntax of the PL/SQL searched CASE statement is as follows:
[codesyntax lang="plsql" title="PL/SQL Searched CASE Statement Syntax" bookmarkname="PL/SQL Searched CASE Statement Syntax"]
[<<label_name>>] CASE WHEN search_condition_1 THEN sequence_of_statements_1; WHEN search_condition_2 THEN sequence_of_statements_2; ... WHEN search_condition_N THEN sequence_of_statements_N; [ELSE sequence_of_statements_N+1;] END CASE [label_name];
The searched CASE statement has no selector. Each WHEN clause in the searched CASE statement contains a search condition that returns a Boolean value. The search condition is evaluated sequentially from top to bottom. If a search condition return TRUE, the sequence of statements in the corresponding WHERE clause is executed and control passes to the next statement therefore the subsequent search conditions are ignored. If no search condition evaluates to TRUE, the sequence of statements in the ELSE clause will be executed.
Here is an example of using PL/SQL searched CASE statement:
[codesyntax lang="plsql" title="PL/SQL Searched CASSE Statement Example" bookmarkname="PL/SQL Searched CASSE Statement Example" highlight_lines="12,13,17,19"]
SET SERVEROUTPUT ON SIZE 1000000; DECLARE n_salary EMPLOYEES.SALARY%TYPE; n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := 200; v_msg VARCHAR(20); BEGIN SELECT salary INTO n_salary FROM employees WHERE employee_id = n_emp_id; CASE WHEN n_salary < 2000 THEN v_msg := 'Low'; WHEN n_salary >= 2000 and n_salary <=3000 THEN v_msg := 'Fair'; WHEN n_salary >= 3000 THEN v_msg := 'High'; END CASE; DBMS_OUTPUT.PUT_LINE(v_msg); END; /
You’ve learned how to use PL/SQL CASE statement to execute a sequence of statements based on a selector. In addition, you learned how to use PL/SQL searched CASE statement.