PL/SQL

Control Flow in PL/SQL

PL/SQL allows you to branch and create loops in a fairly familiar way.

An IF statement looks like:

IF <condition> THEN <statement_list> ELSE <statement_list> END IF; The ELSE part is optional. If you want a multiway branch, use: IF <condition_1> THEN ... ELSIF <condition_2> THEN ... ... ... ELSIF <condition_n> THEN ... ELSE ... END IF;

The following is an example, slightly modified from the previous one, where now we only do the insertion if the second component is 1. If not, we first add 10 to each component and then insert:

DECLARE

a NUMBER; b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1 WHERE e>1; IF b=1 THEN INSERT INTO T1 VALUES(b,a); ELSE INSERT INTO T1 VALUES(b+10,a+10); END IF; END; .

run;

Loops are created with the following:

LOOP <loop_body> /* A list of statements. */ END LOOP;

At least one of the statements in <loop_body> should be an EXIT statement of the form

EXIT WHEN <condition>;

The loop breaks if <condition> is true. For example, here is a way to insert each of the pairs (1, 1) through (100, 100) into T1 of the above two examples:

DECLARE

i NUMBER := 1;

BEGIN

LOOP INSERT INTO T1 VALUES(i,i); i := i+1; EXIT WHEN i>100; END LOOP; END; .

run;

Some other useful loop-forming statements are:

• EXIT by itself is an unconditional loop break. Use it inside a conditional if you like.

• A WHILE loop can be formed with

• WHILE <condition> LOOP

• <loop_body>

• END LOOP;

• A simple FOR loop can be formed with:

• FOR <var> IN <start>..<finish> LOOP

• <loop_body>

• END LOOP;

Here, <var> can be any variable; it is local to the for-loop and need not be declared. Also, <start> and <finish> are constants.