Well assume that testing the function will be done in a schema other than the schema that owns the final product. Making this assumption allows us to set up each of our test conditions by completely emptying the tables of data and then populating the tables with the data to support each test (which makes predicting the results of the function much easier).

The logic for each test is fairly close to the following:

 Clean out the tables.

 Add needed test data to tables.

 Predict the results.

 Call the function.

 Check the results by querying the tables.

Now that these test steps have been established, we can set up a test of the function using a script to handle each condition that needs to be tested. The first of these conditions is tested using a script like the one in Listing 5.24.

As you can see, creating the test is quite simple. The tests for the other conditions are quite similar in content.

TIP: Creating Test Scripts

Although creating tests like this takes some time, once the test is set up it can be repeated whenever the function is modified in the future. (Who says the university wont someday change the way it assigns professors to classes?) Creating test scripts that handle the condition allows the modified function to be tested against the same criteria and baseline data as the original function. In programming (as in science), this is a highly desirable goal.

Summary

Chapter 5 has discussed the fundamentals of creating stored functions within the Oracle database. At this point you should have an understanding of the PL/SQL needed to create a function and should also have some insight on how to design and test your functions. Chapter 6 will discuss some specifics of using functions within packages.