Previous Table of Contents Next


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.


Previous Table of Contents Next