Transcript Interacting with the Oracle Server

Program with PL/SQL
Lesson 4
Writing Control
Structure
Controlling PL/SQL Flow of
Execution

Change the logical execution of statements
using conditional IF statements and loop
control structures.

Conditional IF statements:



IF – THEN – END IF
IF – THEN – ELSE – END IF
IF – THEN – ELSIF – END IF
IF Statements

Syntax:
IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;
CASE Expressions


A CASE expressions selects a result and returns
it
To select the result. The CASE expressions uses
an expression whose values is used to select
one of several alternatives
CASE selector
WHEN expressions1 THEN result1
WHEN expressions2 THEN result2
…
WHEN expressionsN THEN resultN
[ELSE resultN+1;]
END;
Handling Nulls



Simple comparison involving nulls always
yield NULL
Applying the logical operator NOT to a null
yields NULL
In conditional control statements, if the
condition yields NULL, its associated
sequence of statements is not executed
Logic Tables
AND
TRUE
FALSE
NULL
OR
TRUE FALSE NULL
TRUE
TRUE
FALSE
NULL
TRUE
TRUE TRUE
TRUE
FALSE FALSE FALSE FALSE
FALSE TRUE FALSE NULL
NULL
NULL
NULL
FALSE
NULL
TRUE NULL
NULL
Iterative Control:
LOOP Statements

Loops repeat a statement or sequence of
statements multiple times

There are three loop types



Basic loop
FOR loop
WHILE loop
Basic Loops
Syntax:
LOOP
Statement1;
...
EXIT [WHEN condition];
END LOOP
-- delimiter
-- statements
-- EXIT statements
-- delimiter
WHILE Loops
Syntax:
WHILE condition LOOP
Statement1;
Statement2;
...
END LOOP
Condition is
evaluated at the
beginning of
each iteration
Use the WHILE loop to repeat statements while a
condition is TRUE
FOR Loops
Syntax:
FOR counter IN [REVERSE]
lower_bound .. Upper_bound LOOP
Statement1;
Statement2;
...
END LOOP



Use a FOR loop to shortcut the test for the number of
iterations
Do not declare the counter; it is declared implicitly
‘lower_bound .. Upper_bound’ is required syntax
FOR Loops
Guidelines
 Reference the counter within the loop only; it
is undefined outside the loop
 Do not reference the counter as the targert of
an assignment
Guidelines While Using Loops

Use the basic loop when the statements
inside the loop must execute at least once

Use the WHILE loop if the condition has to be
evaluated at the start of each iteration

Use a FOR loop if the number of iterations is
known
Nested Loops and Labels

Nest loops to multiple levels

Use labels to distinguish between blocks and
loops

Exit the outer loop with the EXIT statement
that references the label
Practice Overview
Practice 1
Create a PL/SQL block that rewards an employee by appending an
asterisk in the STARS column for every $1000 of the employee’s
salary.
a.
Use the DEFINE command to provide the employee_id.
Pass the value to the PL/SQL block through a iSQL*Plus
substitution variable
b.
Initialize a v_asterisk variable that contains a NULL
c.
Append an asterisk to the string for every $1000 of the
salary amount. For example, if the employee has a salary
amount $8000, the string of asterisk should contain eight
asterisk. If the employee has a salary amount of $12500,
the string of asterisks should contains 13 asterisks
d.
Update the STARS column for the employee with the
string of asterisk
e.
COMMIT
Practice 2
In a loop, use a cursor to retrieve the
department number and department name from
the DEPARTMENTS table for those departments
whose DEPARTMENT_ID is less than 100. Pass
the department number to another cursor to
retrieve from the EMPLOYEES table the details
of employee last name, job, hire_date, and
salary of those employees whose
EMPLOYEE_ID is less than 120 and who work
in that department.
Practice 3
In a loop use the iSQL*Plus substitution
parameter created in step 1 and gather the
salaries of the top n people from the
EMPLOYEES table. There should be no
duplication in the salaries. If two employees
earn the same salary, the salary should be
picked up only once and store the salaries in
the TOP_DOGS table