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 allows you to choose one sequence of statements to execute out of many possible sequences.

PL/SQL supports two types of CASE statements:

• Simple CASE statement.
• Searched CASE statement.

Both types of CASE statements support an optional ELSE clause.

Simple PL/SQL 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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

1) selector

The selector is an expression that 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.

Since 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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 false, PL/SQL raises an error.

Simple CASE statement example #

The following example uses a simple CASE statement to compare a single value (c_grade) with many possible values ‘A’, ‘B’,’C’, ‘D’, and ‘F’:

SET SERVEROUTPUT ON;

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Very Good

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The searched CASE statement follows the rules below:

• The conditions in the WHEN clauses 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 uses the searched CASE statement to calculate sales commission based on sales revenue.

SET SERVEROUTPUT ON;

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Commission is 15%

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

n_sales > 200000Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

n_commission := 0.15;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 or searched CASE statement #

In general, use a searched CASE statement when you want to execute a sequence of statements based on the results of multiple Boolean expressions, and 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.

Summary #

• Use a simple CASE statement to evaluate a single expression against multiple values.
• Use a searched CASE statement to evaluate multiple, independent Boolean conditions.
• Use the ELSE clause to provide a default block to execute when no condition is true.