Using the WHILE ... LOOP
Controlling Program Flow
4-26 Oracle Database 2 Day Developer's Guide
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
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;
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;
WHEN 'MK_REP' THEN sal_raise := 0.04;
ELSE NULL; -- job type does not match ones that should consider increases
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 over 5 years, it would be ');
FOR loop_c IN 1..5 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;
Compile the
emp_eval
Body.
