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
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
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.
The following illustrates the 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 clauses. If the selector value is equal to
expression in the
WHEN clause, the corresponding sequence of statement after the
THEN keyword is executed.
If the selector’s value is not one of the choices covered by
WHEN clause, the sequence of statements in the
ELSE clause will be executed. The
ELSE clause is optional so if you omit it. PL/SQL will add the following implicit
ELSE RAISE CASE_NOT_FOUND;
If you use implicit
ELSE clause in the PL/SQL
CASE statement, an
CASE_NOT_FOUND exception is raised and can be handled in the exception handling section of the PL/SQL block as usual.
END CASE clause are used to terminate the
Example of Using PL/SQL CASE Statement
The following example demonstrates the PL/SQL
CASE statement. We’ll use
employees table in HR sample data provided by Oracle for the demonstration.
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:
[<<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];
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 evaluates to TRUE, the sequence of statements in the corresponding
WHEN clause is executed and the control is passed 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.
The following is an example of using PL/SQL searched
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; /
In this tutorial, you have learned how to use PL/SQL CASE statement and searched CASE to execute a sequence of statements based on conditions.