Using the LOOP...EXIT WHEN
Controlling Program Flow
4-28 Oracle Database 2 Day Developer's Guide
WHEN 'MK_REP' THEN sal_raise := 0.04;
ELSE NULL;
END CASE;
/* If a salary raise is not zero, print the salary schedule */
IF (sal_raise != 0) THEN -- start code for salary schedule printout
BEGIN
DBMS_OUTPUT.PUT_LINE('If the salary ' || salary || ' increases by ' ||
ROUND((sal_raise * 100),0) ||
'% each year, it would be ');
WHILE salary <= sal_max LOOP
salary := salary * (1 + sal_raise);
DBMS_OUTPUT.PUT (ROUND(salary,2) ||', ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('in successive years.');
END;
END IF;
ELSE
eval_freq := 2;
END IF;
RETURN eval_freq;
END eval_frequency;
Using the LOOP...EXIT WHEN
The
LOOP...EXIT WHEN
structure enables you to exit the loop if further processing is
undesirable. If the
EXIT WHEN
condition evaluates to
TRUE
, the loop exits and control
passes to the next statement.
The
eval_frequency
function in
WHILE...LOOP
. Note that the last computed value may (and typically does) exceed
the maximum possible value for a salary in the last iteration of the loop. If you use the
LOOP_EXIT WHEN
construct instead of the
WHILE...LOOP,
you can have finer
control for terminating the loop.
Example 410 Using LOOP...EXIT WHEN 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
See Also:
Oracle Database PL/SQL Language Reference for more information
on
WHILE...
LOOP statements
