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...

<<update_row>>
BEGIN
UPDATE s_warehouse...
...
END update_row;
...
GOTO update_row;
...
END;
Controlling Flow in PL/SQL Blocks 23Ć5
Overview
You can change the logical flow of statements within the PL/SQL block with a
number of control structures. This lesson addresses two types of PL/SQL control
structures:
D
Conditional constructs with the IF statement
D
Looping constructs
D
Basic loop to provide repetitive actions without overall conditions
D
FOR loops to provide for iterative control of actions based upon a count
D
WHILE loops to provide iterative control of actions based on a true statement
D
EXIT statement to terminate loops
For more information, see
PL/SQL User’s Guide and Reference, Release 2.3, Chapter 3 “Control Structures.”
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder23Ć6
Class Management Note:

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

There can be at most one ELSE clause.
D
Indent the conditionally executed statements for clarity.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder23Ć8
Controlling Flow in PL/SQL Blocks 23Ć9
The IF Statement
continued
Simple IF Statements
PL/SQL executes the conditional statements only if the condition is TRUE. If the
condition is FALSE or NULL, then PL/SQL ignores the conditional statements. In
either case, control resumes at the next statement in the program following END IF.
Example
Set the job title to Sales Representative and the region number to 35 if the last name
is Dumas.
. . .
IF v_last_name = ’Dumas’ THEN
v_job := ’Sales Representative’;
v_region_id := 35;
END IF;
. . .
IFĆTHENĆELSE Statements
If the condition is FALSE or NULL, you can use the ELSE clause to carry out other
actions. As with the simple IF statement, control resumes in the program from the
END IF.
Example
Set a flag for orders where there are fewer than five days between order date and ship
date.
. . .
IF v_date_shipped - v_date_ordered < 5 THEN
v_ship_flag := ’Acceptable’;

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

IFs at the end of each further set of conditions and actions.
Example
For a given value entered, return a calculated value. If the entered value is over 100,
then the calculated value is two times the entered value. If the entered value is
between 50 and 100, then the calculated value is 50% of the starting value. If the
entered value is less than 50, then the calculated value is 10% of the starting value.
FUNCTION calc_val
(v_start IN NUMBER)
RETURN NUMBER
IS
BEGIN
IF v_start > 100 THEN
RETURN (2 * v_start);
ELSIF v_start >= 50 THEN
RETURN (.5 * v_start);
ELSE
RETURN (.1 * v_start);
END IF;
END calc_val;
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder23Ć12
Controlling Flow in PL/SQL Blocks 23Ć13
Building Logical Conditions
Build a simple Boolean condition by combining number, character, or date
expressions with a comparison operator. In general, handle null values with the IS
NULL operator.
Null Within Expressions and Comparisons
D
Any expression containing a null value evaluates to NULL, with the exception of
a concatenated expression, which treats the null value as the empty string.
D

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.