Control Structures
This chapter teaches about how to structure flow of control through a PL/SQL program. The control structures of PL/SQL are simple yet powerful. Control structures in PL/SQL can be divided into selection or conditional, iterative and sequential.
Conditional Control (Selection): This structure tests a condition, depending on the condition is true or false it decides the sequence of statements to be executed. Example
IF-THEN, CASE and searched CASE statements.
Syntax for IF-THEN
IF
Statements
END IF;
Example:
IF-THEN-ELSE:
IF
Statements
ELSE
statements
END IF;
Example:
IF-THEN-ELSIF:
IF
Statements
ELSIF
Statements
ELSE
Statements
END IF;
Iterative Control
LOOP statement executes the body statements multiple times. The statements are placed between LOOP – END LOOP keywords.
The simplest form of LOOP statement is an infinite loop. EXIT statement is used inside LOOP to terminate it.
Syntax for LOOP- END LOOP
LOOP
Statements
END LOOP;
Example:
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE (‘Hello’);
END LOOP;
END;
Labeling Loops
We can label Loops. A Label is undeclared identifier enclosed between double angle brackets( Ex. <
WHILE-LOOP
This is similar to LOOP. A condition placed between WHILE and LOOP is evaluated before each iteration. If the condition evaluates to TRUE the statements are executed and the control resumes at the top of the LOOP. If the condition evaluates to FALSE or NULL then control comes out of the loop.
FOR – LOOP:
The FOR – LOOP is used to repeatedly execute a set of statements for certain number of times specified by a starting number and an ending number. The variable value starts at the starting value given and increments by 1(default and can not be changed) with each iteration. The iteration stops when the variable value reaches end value specified.
Syntax:
FOR
LOOP
Statements
END LOOP;
Sequential Control Statements
The GOTO statement is used for doing unconditional branching to a named label. Its frequent usage is not recommended. We should have at least one executable statement following the label. GOTO statements can some time result in complex, unstructured code making it difficult to understand.
Points To be remembered while working with GOTO:
- A statement, at least NULL statement, must follow every GOTO statement
- A GOTO statement can branch to enclosing block from the current block
- A GOTO statement cannot branch from one IF statement clause to another.
- A GOTO statement cannot branch from an enclosing block into a sub-block.
- A GOTO statement cannot branch out of a subprogram.
- A GOTO cannot branch from an exception handler to current block. But it can branch from the exception handler to an enclosing block
