Using WHILE ... LOOP Iterative Control
Controlling Program Flow
Developing and Using Stored Procedures 4-27
Using the WHILE...LOOP
The
WHILE...LOOP
repeats as long as a condition holds
TRUE
. The condition
evaluates at the top of each loop and if
TRUE
, the statements in the body of the loop
run. If the condition is
FALSE
or
NULL
, the control passes to the next statement after
the loop. The general form of the
WHILE...LOOP
control structure follows.
WHILE
condition
LOOP
...
END LOOP;
Note that the
WHILE...LOOP
may run indefinatelly, so use it with care.
Suppose that the
EVAL_FREQUENCY
uses the
WHILE...LOOP
instead of the
FOR...LOOP
, and terminates after the
proposed salary reaches the upper salary limit for the
job_id
.
Example 49 Using WHILE...LOOP Iterative Control
In the
emp_eval
Body pane, edit
eval_frequency
function so that it uses the
proposed salary increase (
sal_raise
) that is assigned in the
CASE
block to print the
proposed salary over a number of years and stops when it reaches the maximum level
possible for the
job_id
. The new code is in bold font.
FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE)
RETURN PLS_INTEGER AS
hire_date employees.hire_date%TYPE; -- start of employment
today employees.hire_date%TYPE; -- today's date
eval_freq PLS_INTEGER; -- frequency of evaluations
job_id employees.job_id%TYPE; -- category of the job
salary employees.salary%TYPE; -- current salary
sal_raise NUMBER(3,3) := 0; -- proposed % salary increase
sal_max jobs.max_salary%TYPE; -- maximum salary for a job
BEGIN
SELECT SYSDATE INTO today FROM DUAL; -- set today's date
SELECT e.hire_date INTO hire_date -- determine when employee started
FROM employees e
WHERE employee_id = e.employee_id;
IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
eval_freq := 1;
/* Suggesting salary increase based on position */
SELECT e.job_id INTO job_id FROM employees e
WHERE employee_id = e.employee_id;
SELECT e.salary INTO salary FROM employees e
WHERE employee_id = e.employee_id;
SELECT j.max_salary INTO sal_max FROM jobs j
WHERE job_id = j.job_id;
CASE job_id
WHEN 'PU_CLERK' THEN sal_raise := 0.08;
WHEN 'SH_CLERK' THEN sal_raise := 0.07;
WHEN 'ST_CLERK' THEN sal_raise := 0.06;
WHEN 'HR_REP' THEN sal_raise := 0.05;
WHEN 'PR_REP' THEN sal_raise := 0.05;
See Also:
Oracle Database PL/SQL Language Reference for information on the
syntax of
LOOP
statements
