Oracle PL/SQL Language Pocket Reference, Second Edition

Conditional and Sequential Control

PL/SQL includes conditional (IF, CASE) structures as well as sequential control (GOTO, NULL) constructs.

Conditional Control Statements

There are several varieties of IF-THEN-ELSE and CASE structures.

IF-THEN combination

IF condition THEN
   executable statement(s)
END IF;

For example:

IF caller_type = 'VIP' THEN
   generate_response('GOLD');
END IF;

IF-THEN-ELSE combination

IF condition THEN
   TRUE sequence_of_executable_statement(s)
ELSE
   FALSE/NULL sequence_of_executable_statement(s) 
END IF;

For example:

IF caller_type = 'VIP' THEN
   generate_response('GOLD');
ELSE
   generate_response('BRONZE');
END IF;

IF-THEN-ELSIF combination

IF condition-1 THEN
   statements-1
ELSIF condition-N THEN
 statements-N
[ELSE
   ELSE statements]
END IF;

For example:

IF caller_type = 'VIP' THEN
   generate_response('GOLD');
ELSIF priority_client THEN
   generate_response('SILVER');
ELSE
   generate_response('BRONZE');
END IF;

CASE statement (Oracle9i)

There are two types of CASE statements: simple and searched.

A simple CASE statement is similar to an IF-THEN-ELSIF structure. The statement has a switch expression immediately after the keyword CASE. The expression is evaluated and compared to the value in each WHEN clause. The first WHEN clause with a matching value is executed and then control passes to the next statement following the END CASE. For example:

CASE region_id
   WHEN 'NE' THEN  
      mgr_name := 'MINER'; 
   WHEN 'SE' THEN
      mgr_name := 'KOOI';
   ELSE mgr_name := 'LANE';
END CASE;

If a switch expression evaluates ...