PL/SQL Control Structures are used to control flow of execution. PL/SQL provides different kinds of statements to provide such type of procedural capabilities.These statements are almost same as that of provided by other languages.
The flow of control statements can be classified into the following categories:
IF < Condition > THEN < Action > ELSIF <Condition> THEN < Action > ELSE < Action > END IF;
DECLARE a Number := 30; b Number; BEGIN IF a > 40 THEN b := a - 40; DBMS_OUTPUT.PUT_LINE('b=' || b); elseif a = 30 then b := a + 40; DBMS_OUTPUT.PUT_LINE('b=' || b); ELSE b := 0; DBMS_OUTPUT.PUT_LINE('b=' || b); END IF; END; /
Iterative control indicates the ability to repeat or skip sections of a code block.
A loop marks a sequence of statements that has to be repeated. The keyword loop has to be placed before the first statement in the sequence of statements to be repeated, while the keyword end loop is placed immediately after the last statement in the sequence.
Once a loop begins to execute, it will go on forever. Hence a conditional statement that controls the number of times a loop is executed always accompanies loops.
PL/SQL supports the following structures for iterative control:
In simple loop, the key word loop should be placed before the first statement in the sequence and the keyword end loop should be written at the end of the sequence to end the loop.
Loop < Sequence of statements > End loop;
DECLARE i number := 0; BEGIN LOOP dbms_output.put_line ('i = '||i); i:=i+1; EXIT WHEN i>=11; END LOOP; END; /
The while loop executes commands in its body as long as the condtion remains true
WHILE < condition > LOOP < Action > END LOOP
find reverse of given number using while loop
DECLARE num Number(3) :=123; ans Number(3) :=0; i Number(3) :=0; BEGIN WHILE num != 0 LOOP i:=mod(num,10); ans:=(ans * 10 ) + i; num:=floor(num/10); END LOOP; dbms_output.put_line('reverse of given number is: ' || ans); END; /
The FOR loop can be used when the number of iterations to be executed are known.
FOR variable IN [REVERSE] start..end LOOP < Action > END LOOP;
The variable in the For Loop need not be declared. Also the increment value cannot be specified. The For Loop variable is always incremented by 1.
DECLARE i number ; BEGIN FOR i IN 1 .. 10 LOOP dbms_output.put_line ('i = '||i); END LOOP; END; /
The GOTO statement changes the flow of control within a PL/SQL block. This statement allows execution of a section of code, which is not in the normal flow of control. The entry point into such a block of code is marked using the tags «userdefined name». The GOTO statement can then make use of this user-defined name to jump into that block of code for execution.
GOTO jump; .... <<jump>>
DECLARE BEGIN dbms_output.put_line ('code starts'); dbms_output.put_line ('before GOTO statement'); GOTO down; dbms_output.put_line ('statement will not get executed..'); <<down>> dbms_output.put_line ('flow of execution jumped here.'); END; /