PL/SQL CASE Statement

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 using a combination of multiple Boolean expressions.

The following illustrates the PL/SQL CASE statement syntax:

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 clause:

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.

The END CASE clause are used to terminate the CASE statement.

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.

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:

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 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 CASE statement:

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.