PL/SQL CASE Statement

Summary: in this tutorial, you will learn how to use the PL/SQL CASE statement to control the flow of a program.

The CASE statement chooses one sequence of statements to execute out of many possible sequences.

The CASE statement has two types: simple CASE statement and searched CASE statement. Both types of the CASE statements support an optional ELSE clause.

Simple CASE statement

A simple CASE statement evaluates a single expression and compares the result with some values.

The simple CASE statement has the following structure:

CASE selector WHEN selector_value_1 THEN statements_1 WHEN selector_value_1 THEN statement_2 ... ELSE else_statements END CASE;
Code language: SQL (Structured Query Language) (sql)

Let’s examine the syntax of the simple CASE statement in detail:

1) selector

The selector is an expression which is evaluated once. The result of the selector is used to select one of the several alternatives e.g., selector_value_1 and selector_value_2.

2) WHEN selector_value THEN statements

The selector values i.e., selector_value_1, selector_value_2, etc., are evaluated sequentially. If the result of a selector value equals the result of the selector, then the associated sequence of statements executes and the CASE statement ends. In addition, the subsequent selector values are not evaluated.

3) ELSE else_statements

If no values in WHERE clauses match the result of the selector in the CASE clause, the sequence of statements in the ELSE clause executes.

Because the ELSE clause is optional, you can skip it. However, if you do so, PL/SQL will implicitly use the following:

ELSE RAISE CASE_NOT_FOUND;
Code language: SQL (Structured Query Language) (sql)

In other words, PL/SQL raises a CASE_NOT_FOUND error if you don’t specify an ELSE clause and the result of the CASE expression does not match any value in the WHEN clauses.

Note that this behavior of the CASE statement is different from the IF THEN statement. When the IF THEN statement has no ELSE clause and the condition is not met, PL/SQL does nothing instead raising an error.

Simple CASE statement example

The following example compares single value (c_grade) with many possible values ‘A’, ‘B’,’C’,’D’, and ‘F’:

DECLARE c_grade CHAR( 1 ); c_rank VARCHAR2( 20 ); BEGIN c_grade := 'B'; CASE c_grade WHEN 'A' THEN c_rank := 'Excellent' ; WHEN 'B' THEN c_rank := 'Very Good' ; WHEN 'C' THEN c_rank := 'Good' ; WHEN 'D' THEN c_rank := 'Fair' ; WHEN 'F' THEN c_rank := 'Poor' ; ELSE c_rank := 'No such grade' ; END CASE; DBMS_OUTPUT.PUT_LINE( c_rank ); END;
Code language: SQL (Structured Query Language) (sql)

Searched CASE statement

The searched CASE statement evaluates multiple Boolean expressions and executes the sequence of statements associated with the first condition that evaluates to TRUE.

The searched CASE statement has the following structure:

CASE WHEN condition_1 THEN statements_1 WHEN condition_2 THEN statements_2 ... WHEN condition_n THEN statements_n [ ELSE else_statements ] END CASE;]
Code language: SQL (Structured Query Language) (sql)

The searched CASE statement follows the rules below:

  • The conditions in the WHEN clauses in are evaluated in order, from top to bottom.
  • The sequence of statements associated with the WHEN clause whose condition evaluates to TRUE is executed. If more than one condition evaluates to TRUE, only the first one executes.
  • If no condition evaluates to TRUE, the else_statements in the ELSE clause executes. If you skip the ELSE clause and no expressions are TRUE, a CASE_NOT_FOUND exception is raised.

Searched CASE statement example

The following example illustrates how to use the searched CASE statement to calculate sales commission based on sales revenue.

DECLARE n_sales NUMBER; n_commission NUMBER; BEGIN n_sales := 150000; CASE WHEN n_sales > 200000 THEN n_commission := 0.2; WHEN n_sales >= 100000 AND n_sales < 200000 THEN n_commission := 0.15; WHEN n_sales >= 50000 AND n_sales < 100000 THEN n_commission := 0.1; WHEN n_sales > 30000 THEN n_commission := 0.05; ELSE n_commission := 0; END CASE; DBMS_OUTPUT.PUT_LINE( 'Commission is ' || n_commission * 100 || '%' ); END;
Code language: SQL (Structured Query Language) (sql)

In this example, the sales revenue was set to 150,000. The first expression evaluated to FALSE:

n_sales > 200000
Code language: SQL (Structured Query Language) (sql)

But the second expression evaluates to TRUE and the sale commission was set to 15%:

n_commission := 0.15;
Code language: SQL (Structured Query Language) (sql)

PL/SQL stops evaluating the subsequent condition once it finds the first condition that evaluates to TRUE. Therefore, in this example, PL/SQL will never evaluate the last two conditions in the CASE statement. The ELSE statement clause will also never execute.

Simple CASE or searched CASE statement

As a rule of thumb, use a searched CASE statement when you want to execute a sequence of statements based on the results of multiple Boolean expressions and use a simple CASE statement when you want to execute a sequence of statements based on the result of a single expression.

PL/SQL CASE statement vs. CASE expression

PL/SQL also has CASE expression which is similar to the CASE statement.

A CASE expression evaluates a list of conditions and returns one of multiple possible result expressions.

The result of a CASE expression is a single value whereas the result of a CASE statement is the execution of a sequence of statements.

In this tutorial, you have learned how to use the PL/SQL CASE statement to control the flow of a program.

Was this tutorial helpful?