The EXECUTABLE Section

Assignments

a := 45;

Character strings should be set off with single quotes (') as in all expressions. An example follows:

FNAME := 'Clair';

There are other examples of assignments in other parts of this chapter.

Flow Control

• IF statements. These execute a group of one or more statements based on whether a condition is TRUE.

• Basic loops. These repeatedly execute a group of one or more statements until an EXIT statement is reached.

• FOR loops. These repeatedly execute a group of one or more statements a given number of times or until an EXIT statement is reached.

• WHILE loops. These repeatedly execute a group of one or more statements until a particular condition is met or an EXIT statement is reached.

• GOTO statements. These pass execution directly to another point in the code, exiting loops and enclosing blocks as necessary. Use these sparsely, as they make code difficult to read and debug.

You can nest flow control statements within one another to any level of complexity.

IF Statements

The IF statement has the following forms:

1	IF <condition> THEN <statement-list>; 
	END IF;

If the condition following IF is TRUE, PL/SQL executes the statements in the list following THEN. A semicolon terminates this list. END IF (not ENDIF) is mandatory and terminates the entire IF statement. Here is an example:

	IF balance > 500 THEN send_bill(customer); 
	END IF;

We are assuming that send_bill is a procedure taking a single parameter.

2	IF <condition> THEN <statement-list>; 
	ELSE <statement-list>; 
	END IF;

This is the same as the preceding statement, except that, if that condition is FALSE or NULL, PL/SQL executes the statement list following ELSE instead of that following THEN.

3	IF <condition> THEN <statement-list>; 
	ELSIF <condition> THEN <statement-list>; 
	ELSIF <condition> THEN <statement-list>;.....
	ELSE <statement-list>; 
	END IF;

You can include any number of ELSIF (not ELSEIF) conditions. Each is tested only if the IF condition and all preceding ELSIF conditions are FALSE or NULL. As soon as PL/SQL finds an IF or ELSIF condition that is TRUE, it executes the associated THEN statement list and skips ahead to END IF. The ELSE clause is optional, but, if included, must come last. It is executed if all preceding IF and ELSIF conditions are FALSE or NULL.

NULL Statements If you do not want an action to be taken for a given condition, you can use the NULL statement, which is not to be confused with database nulls, Boolean NULLs, or the SQL predicate IS NULL. The syntax of this statement is simply:

NULL;

The statement performs no action, but fulfills the syntax requirement that a statement list must follow every THEN keyword. In some cases, you can also use it to increase the readability of your code. For more information on the NULL statement, see "NULL Statement" in the PL/SQL User's Guide and Reference.

Basic Loops

credit := 0;
 LOOP 
	IF c = 5 THEN EXIT;
	END IF;
	credit := credit + 1;
END LOOP;

This loop keeps incrementing credit until it reaches 5 and then exits. An alternative to placing an exit statement inside an IF statement is to use the EXIT-WHEN syntax, as follows:

EXIT WHEN credit = 5;

This is equivalent to the earlier IF statement.

Note: The EXIT statement cannot be the last statement in a PL/SQL block. If you want to exit a PL/SQL block before its normal end is reached, use the RETURN statement. For more information, see "RETURN Statement" in the PL/SQL User's Guide and Reference.

FOR Loops

FOR credit IN 1..5 LOOP
	interest := interest * 1.2;
END LOOP;

The numbers used to specify the range (in this case, 1 and 5) can be variables, so you can let the number of iterations of the loop be determined at runtime if you wish.

WHILE Loops

credit := 1;
WHILE credit <= 5 LOOP
	interest := interest * 1.2;
	credit := credit + 1;
END LOOP;

Unlike some languages, PL/SQL has no structure, such as REPEAT-UNTIL, that forces a LOOP to execute at least once. You can create this effect, however, using either basic or WHILE loops and setting a variable to a value that will trigger the loop, as in the above example. For more information on loops, see "Iterative Control" in the PL/SQL User's Guide and Reference.

GOTO Statements

<<this_is_a_label>>

You only use the brackets at the target itself, not in the GOTO statement that references it, so a GOTO statement transferring execution to the above label would be:

GOTO this_is_a_label;

Note: An EXIT statement can also take a label, if that label indicates the beginning of a loop enclosing the EXIT statement. You can use this to exit several nested loops at once. See "Loop Labels" in the PL/SQL User's Guide and Reference for more information.

A GOTO statement is subject to the following restrictions:

• It must branch to an executable statement, not, for example, an END.

• It cannot branch to a point within the body of IF or a LOOP statement, unless it is contained in the body of that statement itself.

• It cannot branch to a subprogram or enclosing block of the present block (with one exception, explained shortly).

• It cannot branch from one IF statement clause to another. That is to say, it cannot jump between THEN, ELSIF, and ELSE clauses that are part of the same IF statement.

• It cannot branch from the EXCEPTION section to the EXECUTABLE section of the same block.

• It can, however, branch from the EXCEPTION section of a block to the EXECUTABLE section of an enclosing block, which is the exception to the third rule above.