Figure 16.12.
Viewing the nodes
belonging to the
flugle Oracle
account.

Viewing and Editing a Stored Program Unit

Let's look at an example of how you view or modify a stored program unit. Suppose you want to modify the Student_GPA stored function which is owned by the flugle Oracle account. As you may recall, the Student_GPA function is used to translate the letter grades that a student has received—A, B, C, and so on—and return a numeric grade point average. From the Object Navigator, expand the Database Objects node and also expand the Flugle node. Select the Student_GPA node (see Figure 16.13).

Double-click the icon to the left of the Student_GPA node. You should now see the PL/SQL code for the Student_GPA function, displayed by the Stored Program Unit Editor (see Figure 16.14).

Let's look at some of the user interface elements of the Stored Program Unit Editor. At the top of the window, there are two poplists: Owner and Name. If you click on Owner, the poplist will display a list of all the Oracle accounts that exist in the database to which Procedure Builder is connected, whether or not that account owns any stored program units (see Figure 16.15). Similarly, the Name poplist will display a list of all of the stored program units—package specifications, package bodies, procedures, and functions—that are owned by the Oracle account shown in the Owner field (see Figure 16.16).

Figure 16.13.
Selecting a stored
program unit with the
Object Navigator.

Figure 16.14.
Viewing a stored
function with the
Stored Program Unit
Editor.

Page 450

Figure 16.15.
Owner poplist displays
all Oracle accounts.

Figure 16.16.
Name poplist displays
all stored program
units owned by the
Oracle account
in Owner.

Also, there are six buttons at the top of the window:

If you add a line to the Student_GPA function that isn't valid and click Save, the Stored Program Unit Editor will try to compile the function and display any errors in the bottom of the window (see Figure 16.17). If you look at the line after begin, you'll see an invalid statement: not a valid line.

Let's remove the offending line. In addition, let's modify the function by removing F grades from the calculation of a GPA (see Listing 16.1). Click Save to compile the changes and save the function. Click Close to exit the Stored Program Unit Editor.

Figure 16.17.
Stored Program
Unit Editor displays
PL/SQL errors.

Listing 16.1. Modifying a stored function.

function student_GPA (arg_student_ID IN varchar2)
   return number is

GPA number;

begin

select avg(decode(grade, `A+', 4.25, `A', 4, `A-', 3.75,
                         `B+', 3.25, `B', 3, `B-', 2.75,
                         `C+', 2.25, `C', 2, `C-', 1.75,
                         `D+', 1.25, `D', 1, `D-', 0.75))
into GPA
from student_schedule
where
student_id = arg_student_id;

return GPA;

end;

Creating a Database Trigger with the Database Trigger Editor

You can use the Database Trigger Editor to create, view, modify, or drop a database trigger. Let's work through an example. Suppose that a requirement of the Flugle College Information System is that a journal record be kept of every grade that is assigned to a student, including any changes that are made to an assigned grade. As you recall, the Student_Schedule table contains each class that a student has completed, including the grade that the student received and the date that the grade was assigned.

To satisfy this requirement, you will create another table named Student_Schedule_Journal that contains all the columns of the Student_Schedule table and three additional columns:

You will use the Database Trigger Editor to create a database trigger on the Student_Schedule table that will insert a record into the Student_Schedule_Journal table that records all information about the row in the Student_Schedule table that is being either inserted, updated, or deleted.

1. Invoke the Database Trigger Editor by selecting Tools | Database Trigger Editor from the menu.
2. Select FLUGLE from the poplist under the Table Owner label (see Figure 16.19).
3. Select STUDENT_SCHEDULE from the poplist under the Table label (see Figure 16.18).
4. Click New to create a database trigger. By default, this trigger has been given a name that you'll want to change.
5. In the upper-right corner of the window, beneath the Name field, change the name of the trigger to AIUD_STUDENT_SCHEDULE, which is shorthand notation to signify that this trigger is an After Insert Update Delete trigger for the Student_Schedule table.
6. Set the Triggering radio button to After.
7. In the Statement group, check all three checkboxes, UPDATE, INSERT, and DELETE.
8. Check the checkbox labeled For Each Row.

Figure 16.19 illustrates what you should see in the Database Trigger Editor when you are finished.

Figure 16.18.
Preparing to create a
database trigger on the
Student_Schedule table.

Figure 16.19.
Setting the options for
the new database
trigger.

Type Listing 16.2 into the list box labeled Trigger Body, and click Save to compile and save the trigger.