- IF condition THEN statements END IF;
- IF condition THEN statements
- IF condition THEN statements
Iterative control in PL/SQL
PL/SQL provides iterative control and execution of PL/SQL statements in the block. This is the ability to repeat or skip sections of a code block. Following are the four types of iterative statements provided by the PL/SQL.
- 1.The Loop statement
- 2.The WHILE Loop statement
- 3.The GOTO statement
- 4.FOR Loop
A loop repeats a sequence of statements. The format is as follows.
The one or more PL/SQL statements can be written between the key words LOOP and END LOOP. Once a LOOP begins to run, it will go on forever. Hence loops are always accompanied by conditional statements that keeps control on the number of items it is executed. We can also build user defined exits from a loop, where required.
Cntr := cntr +1;
If cntr >00
EXIT statement brings the control out of loop if the condition is satisfied.
2.While Loop in PL/SQL
The WHILE loop enables you to evaluate a condition before a sequence of statements would be executed. If condition is TRUE then sequence of statements are executed. This is different from the FOR loop where you must execute the loop at least once. The syntax for the WHILE loop is as follows.
Syntax: WHILE < Condition is TRUE >
Count NUMBER (2): =0;
WHILE count <= 10
Count: = count+1;
Message (‘while loop executes’);
EXIT and EXIT WHEN statements enable you to escape out of the control of a loop. The format of the EXIT statement is as follows:
EXIT WHEN statements have following syntax
Syntax: EXIT WHEN <condition is true>;
In this case no IF statement is required.
Ex-1: IF count >= 10 EXIT;
EX-2: EXIT WHEN count >= 10;
Condition control in PL/SQL using while loop
The GOTO statement allows you to change the flow of control within a PL/SQL block. The syntax is as follows
Syntax: GOTO <label name>;
The label is surrounded by double brackets (<<>>) and label must not have a semi colon after the label name. The label name does not contain a semi colon because it is not a PL/SQL statement. But rather an identifier of a block of PL/SQL code. You must have at least one statement after the label otherwise an error will result. The GOTO destination must be in the same block, at the same level as or higher than the GOTO statement itself.
Ex: IF result = ‘fail’ THEN
Message (‘student is failed’);
The entry point the destination block is defined within << >> as shown above, i.e. labels are written within the symbol << >>. Notice that <<failed_stud>> is a label and is not ended with semicolon (;)
FOR loop will allow you to execute a block of code repeatedly until some condition occurs. The syntax of FOR loop is as follows.
Syntax: FOR loop_index IN [ REVERSE] low_value…HIGH_value LOOP
Statements to execute
The loop_index is defined by oracle as a local variable of type integer. REVERSE allows you to execute the loop in reverse order. The low_value.High_value is the range to execute the loop. These can be constants or variables. The line must be terminated with loop with no semicolon at the end of this line. You can list the statements to executed until the loop is executed is evaluated to false.
Ex: FOR v_count 1N 1..5 LOOP
MESSAGE (‘FOR LOOP EXECUTES’);
In the above example the message ‘FOR LOOP EXECUTES’ is displayed five times.
We can terminate the FOR loop permanently using EXIT statement based on some BOOLEAN condition. Nesting of FOR loop can also be allowed in PL/SQL. The outer loop is executed once, then the inner loop is executed as many times as the range indicates, and then the control is returned to the outer loop until its range expires.
Ex: FOR out_count IN 1.2 LOOP
FOR in_count IN 1.2 LOOP
Message (‘nested for loop’);