Controlling Flow in PL/SQL
Blocks
23
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder23Ć2
Schedule: Timing Topic
40 minutes Lecture
60 minutes Practice
100 minutes Total
Class Management Note:
Files required for lesson.
Demonstration: l23calc.pls, l23iters.pls
Practice: None
Controlling Flow in PL/SQL Blocks 23Ć3
Objectives
You can control the flow of your PL/SQL block by using conditional statements
and loops.
At the end of this lesson, you should be able to
D
Conditionally control processing in a PL/SQL block.
D
Iterate statements by using various types of loops.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder23Ć4
Class Management Note:
The GOTO statement is not addressed in this course because unconditional
branching goes against the procedural structure of a top-down language
such as PL/SQL. You can mention the GOTO statement. It unconditionally
transfers control to a different sequence of statements. Branch to a label
within the same block or to a sequence of statements, or to a label within an
outer block or enclosing sequence of statements.
Example:
BEGIN...

PowerPoint: This slide uses the build feature.
Controlling Flow in PL/SQL Blocks 23Ć7
The IF Statement
The structure of the PL/SQL IF statement is similar to the structure of IF statements
in other procedural languages. It allows PL/SQL to perform actions selectively based
upon conditions.
Syntax
IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;
where: condition is a Boolean variable or expression (TRUE,
FALSE, or NULL).
Guidelines
D
When writing code, remember the spelling of the keywords.
D
ELSIF is one word.
D
END IF is two words.
D
If the controlling Boolean condition is TRUE, the associated sequence of
statements is executed; if the controlling Boolean condition is FALSE or NULL,
the associated sequence of statements is passed over.
D
Any number of ELSIF clauses are permitted.
D

ELSE
v_ship_flag := ’Unacceptable’;
END IF;
. . .
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder23Ć10
Class Management Note:
DEMO: l23calc.pls
PURPOSE: Use a function to return a calculated value based on an input
value.
1.Select from the menu File—>Load, and load the l23calc.pls.
2.Show the code to the students.
3.Create a variable to hold the returned calculated number.
Enter: .CREATE NUMBER x PRECISION 5
4.Execute the function at the interpreter.
Enter: :x := calc_val(100);
5.View the resulting value of the variable.
Enter: TEXT_IO.PUT_LINE(TO_CHAR(:x));
6.Repeat steps 4 and 5 using values 150 and 10.
Controlling Flow in PL/SQL Blocks 23Ć11
The IF Statement
continued
Nested IF Statements
Either set of actions of the result of the first IF statement can include further IF
statements before specific actions are performed. Each nested IF statement must be
terminated with a corresponding END IF.
IFĆTHENĆELSIF Statements
When possible, however, use the ELSIF clause instead of nesting IF statements. The
code is easier to read and understand. The logic is clearly identified. If the action in
the ELSE clause consists purely of another IF statement, it is more convenient to use
the ELSIF clause. This makes the code clearer by removing the need for nested END

Any simple comparison containing a null value evaluates to NULL.
D
An IS NULL comparison evaluates to TRUE or FALSE.
Boolean Conditions with Logical Operators
Build a complex Boolean condition by combining simple Boolean conditions with the
logical operators AND, OR, and NOT. In the accompanying logic tables, FALSE
takes precedence for an AND condition and TRUE takes precedence in an OR
condition.
Class Management Note:
The negation of NULL (NOT NULL) results in a null value because null
values are indeterminate.