PL/SQL includes conditional (IF, CASE) structures as well as sequential control (GOTO, NULL) constructs.
There are several varieties of IF-THEN-ELSE and CASE structures.
IF condition THEN executable statement(s) END IF;
IF caller_type = 'VIP' THEN generate_response('GOLD'); END IF;
IF condition THEN TRUE sequence_of_executable_statement(s) ELSE FALSE/NULL sequence_of_executable_statement(s) END IF;
IF caller_type = 'VIP' THEN generate_response('GOLD'); ELSE generate_response('BRONZE'); END IF;
IF condition-1 THEN statements-1 ELSIF condition-N THEN statements-N [ELSE ELSE statements] END IF;
IF caller_type = 'VIP' THEN generate_response('GOLD'); ELSIF priority_client THEN generate_response('SILVER'); ELSE generate_response('BRONZE'); END IF;
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;
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;
PL/SQL provides a GOTO statement and a NULL statement to aid in sequential control operations.
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:
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
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;