Oracle 9i PL/SQL Control Structures
This tutorial 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.
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 THEN
Statements
END IF;
Example:
IF-THEN-ELSE:
IF THEN
Statements
ELSE
statements
END IF;
Example:
IF-THEN-ELSIF:
IF THEN
Statements
ELSIF THEN
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. <>). The following example demonstrates usage of labels in loops.
{mospagebreak}
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 IN ..
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
Oracle 9i Utilities
Oracle 9i Packages
Oracle 9i Database Triggers
Oracle 9i Procedures and Functions
Oracle 9i PL/SQL Collections
Oracle 9i Exception Handling
Download example SQL Scripts used in Oracle 9i Tutorials
Oracle 9i Cursors
Oracle 9i PL/SQL Control Structures
Building PL/SQL Blocks in Oracle 9i
More Oracle 9i Database Objects
Oracle 9i Tables and Constraints
Oracle 9i Software Installation, SQL, PLSQL and SQL *Plus References
Introduction to Oracle 9i SQL, PLSQL, and SQL *Plus