Condition control in PL/SQL

Condition control in PL/SQL

In PL/SQL, IF statement allows you to control the execution of a block of code. In PL/SQL we can use the following IF forms.
  • IF condition THEN statements END IF;
  • IF condition THEN statements
ELSE statements
END IF;
  • IF condition THEN statements
ELSE IF condition THEN
Statements
ELSE statements
END IF
END IF;

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
1.The Loop statement in PL/SQL
A loop repeats a sequence of statements. The format is as follows.
LOOP
Statement
END LOOP;
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.
Ex- LOOP
Cntr := cntr +1;
If cntr >00
EXIT;
END IF;
END LOOP;
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 >
LOOP
<STATEMENTS>
END LOOP;
Ex; DECLARE
Count    NUMBER (2): =0;
BEGIN
WHILE count <= 10
LOOP
Count: = count+1;
Message (‘while loop executes’);
END LOOP;
END;
EXIT and EXIT WHEN statement
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:
Syntax:   EXIT;
EXIT WHEN statements have following syntax
Syntax: EXIT WHEN <condition is true>;
EXIT WHEN statement enables you to specify the condition required to exit the execution of the loop.
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

Condition control in PL/SQL using while loop
Condition control in PL/SQL using while loop
3.The Goto Statement in PL/SQL
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
GOTO failed_stud
END IF;
<<failed_stud>>
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 (;)
4.For Loop
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
END LOOP;
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’);
END LOOP;
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’);
END LOOP;
END LOOP;
In the above example the message ‘nested for loop’ is displayed four times.

Leave a Reply

Your email address will not be published.