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

 

 

   
 

Testing the Procedure
Oracle Tips by Burleson
 

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.

           
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