Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter