 |
|
Oracle
Code for Annual_Review() Procedure
Oracle Tips by
Burleson
|
Now that the pseudocode for the procedure has been written and
looked over for logic errors, the code can be written using the
pseudocode as a base. The final draft of the Annual_Review()
procedure is shown in Listing 4.28.
Listing 4.28 The code for the Annual_Review()
procedure.
PROCEDURE Annual_Review
IS
xCONTINUE_LOOP EXCEPTION;
iLateDays integer;
iPerforanceRating integer;
iWarningsIssued integer;
nBaseSalary number;
nOntimeRating number;
nTotalRaisePercent number;
--
-- The total number of working days in the year. This is
-- calculated as follows:
--
-- 104 weekend days
-- 10 paid holidays (11 in leap year)
-- 10 sick days
--
TOTAL_WORKING_DAYS CONSTANT integer := 241;
--
-- Any employee working for the company for over one year.
--
CURSOR All_Employees_cur
IS
SELECT employee_num, eff_hire_date, base_salary,
late_days, warnings, performance_rating
FROM EMPLOYEES
WHERE (to_char (SYSDATE, 'YYYY')
- to_char (eff_hire_date, 'YYYY')) > 1;
FUNCTION Raise_Salary (nBaseSalary IN number,
nRaiseAmount IN number)
RETURN number
IS
BEGIN
RETURN ( nBaseSalary
+ (nBaseSalary * nRaiseAmount));
END;
BEGIN
FOR All_Employees_rec IN All_Employees_cur LOOP
BEGIN
iLateDays := All_Employees_rec.late_days;
iPerformanceRating := All_Employees_rec.performance_rating;
iWarningsIssued := All_Employees_rec.warnings;
nBaseSalary := All_Employees_rec.base_salary;
nOntimeRating := 0;
nTotalRaisePercent := 0.0;
nIncreasedSalary := 0.0;
--
-- If the employee has 4 or more warnings issued, go to
-- the next employee.
--
IF (iWarningsIssued > 3) THEN
RAISE xCONTINUE_LOOP;
END IF;
nOntimeRating := ( TOTAL_WORKING_DAYS
- iLateDays);
nOntimeRating := (nOntimeRating / TOTAL_WORKING_DAYS) * 100;
IF (nOntimeRating > 98) THEN
nTotalRaisePercent := nTotalRaisePercent + 0.005;
IF (iLateDays = 0) THEN
nTotalRaisePercent := nTotalRaisePercent + 0.001;
END IF;
END IF;
IF (iPerformanceRating > 8) THEN
nTotalRaisePercent := nTotalRaisePercent + .005;
END IF;
nIncreasedSalary := Raise_Salary
(nBaseSalary => nBaseSalary,
nRaiseAmount => nTotalRaisePercent);
UPDATE EMPLOYEES
SET base_salary = nIncreasedSalary
WHERE CURRENT OF All_Employees_cur;
EXCEPTION
WHEN xCONTINUE_LOOP THEN
NULL;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
The xCONTINUE_LOOP user-defined exception is raised when the
employee has four or more warnings. Using this exception allows us to
avoid using a GOTO statement inside the loop to skip to the
next iteration.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
