145-148 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

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.

Testing The Procedure

Our code looks like it will do the trick, and it compiles cleanly, but we re not done yet. The procedure can t go into production until it has been tested thoroughly. After all, these are real dollars we re playing with here!

The unit test script for the procedure can be outlined by breaking the requirements down into both positive and negative tests, as follows:

Create an employee with only one late day and a base salary of $20,000. Because no other factors will come into play, the employee s new salary should be $20,100 after calling Annual_Review() .

Create an employee with five late days (just under 98 percent) and a base salary of $20,000. Because no other factors will come into play, the employee s salary should remain at $20,000 after calling Annual_Review() .

Create an employee with no late days and a base salary of $20,000. Because no other factors will come into play, the employee s new salary should be $20,120.

Create an employee with four warnings and a base salary of $20,000. Because the employee has four warnings, the employee s base salary should remain at $20,000.

Create an employee with a performance rating of 7 and a base salary of $20,000. Because the employee s performance did not rate above 8, there should be no change in the base salary.

Create an employee with a performance rating of 9 and a base salary of $20,000. Because the employee has a performance rating higher than 8, the new base salary should be $20,100.

We ll assume that the procedure won t be tested in a real data environment. This will allow us to set up each test condition by creating the appropriate test data in an empty EMPLOYEES table. Once we have a proper data set, each requirement can be tested. The logic for each test looks something like this:

 clean out the EMPLOYEES table; add test data to the table; predict the results; call the procedure; check the results by querying the table;

Now that we ve isolated the tests that have to be performed, it s a simple matter to write a script that handles each condition. The first of these scripts is shown in Listing 4.29.

Listing 4.29 Part of the unit testing scripts for the Annual_Review() procedure.

 DECLARE   nSalary    number; BEGIN   --   -- Create an employee with only 1 late day and no other raise   -- earning conditions. Base salary will be 20000, the expected   -- raise will be .5% (100 dollars).   --   INSERT   INTO   EMPLOYEES          (employee_num,           first_name,           last_name,           ssn,           home_phone,           eff_hire_date,           base_salary,           eff_termination_date,           middle_name,           late_days,           warnings,           overtime_hours,           performance_rating)   VALUES (999999,           'Joe',           'Schmoe',           999999999,           2065550123,           to_date ('02/02/1982'),           20000,           NULL,           NULL,           1,           0,           0,           8);   Annual_Review;   SELECT base_salary   INTO   nSalary   FROM   EMPLOYEES   WHERE  employee_num = 999999;   DBMS_Output.Put_Line ('Base salary is now: '                          to_char (nSalary));   IF (nSalary != 20100) THEN     DBMS_Output.Put_Line ('ERROR: Incorrect result!');   END IF; END;

Summary

Chapter 4 covers the fundamentals of creating stored procedures within the Oracle database. At this point, you should be familiar with the PL/SQL needed to create a stored procedure and have some insights into designing and testing stored procedures. Now, let s take a look at functions in Chapter 5.

Table of Contents