| |
 |
|
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. |
