[ Team LiB ] Previous Section Next Section

1.6 Conditional and Sequential Control

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

1.6.1 Conditional Control Statements

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

1.6.1.1 IF-THEN combination
IF condition THEN
   executable statement(s)
END IF;

For example:

IF caller_type = 'VIP' THEN
   generate_response('GOLD');
END IF;
1.6.1.2 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;
1.6.1.3 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;
1.6.1.4 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 to NULL, the ELSE case is the only one that can possibly match; WHEN NULL will never match because Oracle performs an equality comparison on the expressions.

Both the CASE statement and the CASE expression (see the next section) should include an ELSE clause that will execute statements if no WHEN clause evaluates TRUE, because PL/SQL's runtime engine will raise an exception if it finds no matching expression.

The searched CASE statement does not have a switch; instead, each WHEN clause has a complete Boolean expression. The first matching WHEN clause is executed and control passes to the next statement following the END CASE. For example: 

CASE
   WHEN region_id = 'EAME' THEN 
      mgr_name := 'SCHMIDT';
   WHEN division = 'SALES' THEN
      mgr_name := 'KENNEDY';
   ELSE mgr_name := 'GUPTA';
END CASE;
1.6.1.5 CASE expression (Oracle9i)

There are also two types of CASE expressions: simple and searched. You can use CASE expressions anywhere that you can use any other type of expressions in PL/SQL programs.

A simple CASE expression lets you choose an expression to evaluate based on a scalar value that you provide as input. The following example shows a simple CASE expression being used with the built-in DBMS_OUTPUT package to output the value of a Boolean variable. DBMS.OUTPUT.PUT_LINE is not overloaded to handle Boolean types, so in this example the CASE expression converts the Boolean value in a character string, which PUT_LINE can then handle: 

DECLARE
   boolean_true BOOLEAN := TRUE;
   boolean_false BOOLEAN := FALSE;
   boolean_null BOOLEAN;

   FUNCTION boolean_to_varchar2 (flag IN BOOLEAN)
      RETURN VARCHAR2 IS
   BEGIN
      RETURN 
      CASE flag
      WHEN TRUE THEN 'True'
      WHEN FALSE THEN 'False'
      ELSE 'NULL' END;
   END;

BEGIN
   DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_true));
   DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_false));
   DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_null));
END;

A searched CASE expression evaluates a list of expressions to find the first one that evaluates to TRUE, and then returns the results of an associated expression. In the following example, a searched CASE expression returns the proper bonus value for any given salary: 

DECLARE
  salary NUMBER := 20000;
  employee_id NUMBER := 36325;

  PROCEDURE give_bonus
    (emp_id IN NUMBER, bonus_amt IN NUMBER) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(emp_id);
    DBMS_OUTPUT.PUT_LINE(bonus_amt);
  END;

BEGIN
   give_bonus(employee_id,
         CASE
         WHEN salary >= 10000 AND salary <=20000 THEN 1500
         WHEN salary > 20000 AND salary <= 40000 THEN 1000
         WHEN salary > 40000 THEN 500
         ELSE 0
         END);
END;

1.6.2 Sequential Control Statements

PL/SQL provides a GOTO statement and a NULL statement to aid in sequential control operations.

1.6.2.1 GOTO

The GOTO statement performs unconditional branching to a named label. You should only rarely use a GOTO. At least one executable statement must follow the label (the NULL statement can be this necessary executable statement). The format of a GOTO statement is: 

GOTO <<label_name>>;

For example:

BEGIN
   GOTO second_output;

   DBMS_OUTPUT.PUT_LINE('This line will never execute.');

   <<second_output>>
   DBMS_OUPUT.PUT_LINE('We are here!);
END

There are a number of scope restrictions on where a GOTO can branch control. A GOTO:

  • Can branch out of an IF statement, LOOP, or sub-block

  • Cannot branch into an IF statement, LOOP, or sub-block

  • Cannot branch from one section of an IF statement to another (from the IF-THEN section to the ELSE section is illegal)

  • Cannot branch into or out of a sub-program

  • Cannot branch from the exception section to the executable section of a PL/SQL block

  • Cannot branch from the executable section to the exception section of a PL/SQL block, although a RAISE does this

1.6.2.2 NULL

The NULL statement is an executable statement that does nothing. It is useful when an executable statement must follow a GOTO label or to aid readability in an IF-THEN-ELSE structure. For example: 

IF :report.selection = 'DETAIL' THEN
   exec_detail_report;
ELSE
   NULL;
END IF;
    [ Team LiB ] Previous Section Next Section