187-191 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

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.

Listing 5.24 A test script for the Assign_Instructor() function.

 DECLARE BEGIN    --    -- Clean out the local tables before creating baseline data.    -- The CLASS_TIMES table is a lookup table, so no work needs    -- to be done there.    --    TRUNCATE TABLE INSTRUCTORS;    TRUNCATE TABLE SCHEDULED_CLASSES;    --    -- Create a faculty member psychology instructor qualified    -- to teach courses at the 800 level, with no minimum level.    --    INSERT    INTO   INSTRUCTORS           (instructor_number,            last_name,            first_name,            faculty_member_flag,            approved_class_level,            min_class_level,            approved_field,            maximum_classes)    VALUES (1000,            'Williams',            'Bill',            'Y',            800,            NULL,            'PSY',            'Y');    --    -- Create four courses with our instructor.    --    INSERT    INTO   SCHEDULED_CLASSES           (course_number,            course_field,            instructor_number,            semester_id,            class_time,            field_level)    VALUES (2000,            'PSY',            1000,            197,            'A',            100);    INSERT    INTO   SCHEDULED_CLASSES           (course_number,            course_field,            instructor_number,            semester_id,            class_time,            field_level)    VALUES (2001,            'PSY',            1000,            197,            'B',            232);    INSERT    INTO   SCHEDULED_CLASSES           (course_number,            course_field,            instructor_number,            semester_id,            class_time,            field_level)    VALUES (2010,            'PSY',            1000,            197,            'C',            321);    INSERT    INTO   SCHEDULED_CLASSES           (course_number,            course_field,            instructor_number,            semester_id,            class_time,            field_level)    VALUES (2302,            'PSY',            1000,            197,            'G',            810);    --    -- Create a class that this professor could teach.    --    INSERT    INTO   SCHEDULED_CLASSES           (course_number,            course_field,            instructor_number,            semester_id,            class_time,            field_level)    VALUES (2100,            'PSY',            1000,            197,            'E',            201);    --    -- Now call the Assign_Instructor function. Since there is only    -- one professor who can teach psychology courses and that    -- professor is already teaching four courses, the function    -- will return without a value (an exception will be raised when    -- calling the function).    --    nTeacherID := Assign_Instructor (nClassNumber => 2100);    --    -- If execution reaches this point, the function did return    -- a value. This should not have happened.    --    DBMS_Output.Put_Line ('ERROR: Function executed normally!');    DBMS_Output.Put_line ('Instructor ID: '  nTeacherID); EXCEPTION    WHEN OTHERS THEN         DBMS_Output.Put_Line (SQLERRM); END;

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.

Table of Contents