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;