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.

Next Page: Tutorial 9 : Oracle 9i: PL/SQL Control Structures - Page 2