To demonstrate the use of the REF operator, Listing 13.14 shows a simple procedure that, given an employee number and a building name, will assign the employee to a building.

INPUT/
OUTPUT
Listing 13.14. Using the REF operator.

 1: CREATE OR REPLACE PROCEDURE AssignEmpToBldg (
 2:   EmpNumIn IN employee.emp_id%TYPE,
 3:   BldgNameIn IN buildings.BldgName%TYPE
 4:   ) AS
 5: BEGIN
 6:   UPDATE employee
 7:      SET emp_bldg = (SELECT REF(b)
 8:                       FROM buildings B
 9:                      WHERE BldgName = BldgNameIn)
10:    WHERE emp_id = EmpNumIn;
11:
12:   --Raise an error if either the employee number or
13:   --building name is invalid.
14:   IF SQL%NOTFOUND THEN
15:     RAISE_application_error(-20000,'Employee ` || EmpNumIn
16:                            || ` could not be assigned to building `
17:                            || BldgNameIn);
18:   END IF;
19: END;
20: /

Procedure created.

 1: BEGIN
 2:   AssignEmpToBldg (598,'Victor Building');
 3:   AssignEmpToBldg (597,'East Storage Shed');
 4:   AssignEmpToBldg (599,'Software Research');
 5: END;
 6: /

PL/SQL procedure successfully completed.

ANALYSIS
The first part of this listing contains the definition for the AssignEmpToBldg procedure. This procedure takes both an employee ID and a building name as arguments. An UPDATE statement (lines 6_10 of the first segment) uses the building name to retrieve the matching building object and stores a reference to that object in the employee table. The PL/SQL anonymous block in lines 1_6 of the second segment calls this procedure to make some building assignments.

To show the use of the DEREF operator, Listing 13.15 shows a simple function that retrieves the name of the building in which an employee works.

INPUT/
OUTPUT
Listing 13.15. Using the DEREF operator.

 1: CREATE OR REPLACE FUNCTION GetEmpBldgName (
 2:   EmpNumIn IN employee.emp_id%TYPE
 3:   ) RETURN VARCHAR2 AS
 4: TheBldg     building;
 5: BEGIN
 6:   --Select the building object reference from this employee's record.
 7:   SELECT DEREF(emp_bldg) INTO TheBldg
 8:     FROM employee
 9:    WHERE emp_id = EmpNumIn;
10:
11:   IF TheBldg IS NULL THEN
12:     RETURN `No Building Assigned';
13:   ELSE
14:     RETURN TheBldg.BldgName;
15:   END IF;
16: END;
17: /

Function created.

 1: BEGIN
 2:   dbms_output.put_line(GetEmpBldgName(599));
 3:   dbms_output.put_line(GetEmpBldgName(598));
 4:   dbms_output.put_line(GetEmpBldgName(597));
 5: END;
 6: /

Software Research
Victor Building
East Storage Shed

PL/SQL procedure successfully completed.

ANALYSIS
The GetEmpBldgName function takes an employee ID as an argument and returns the name of the building in which the employee works. The SELECT statement, in lines 7_9 of the first segment, retrieves the building information for the selected employee. It does this by using the DEREF operator (line 7 of the first segment) to dereference the emp_bldg pointer. The DEREF operator causes Oracle to automatically retrieve the referenced building object from wherever it is stored, which in this case happens to be the building table.

The SELF Parameter

Each object method you write has a default first parameter named SELF. This SELF parameter, which is normally not specified in the method's declaration, is used to reference the attributes of the object being called. By default, any unqualified attribute reference in a member function or member procedure is automatically qualified by SELF. Listing 13.16 shows how the building object type definition would look if you explicitly defined and used the SELF parameter.

INPUT/
OUTPUT
Listing 13.16. Using the SELF parameter.

 1: CREATE OR REPLACE TYPE building AS OBJECT (
 2:   BldgName          VARCHAR2(40),
 3:   BldgAddress       address,
 4:   BldgMgr           INTEGER,
 5:   MEMBER PROCEDURE  ChangeMgr (SELF IN OUT building,
 6:                                NewMgr IN INTEGER),
 7:   ORDER MEMBER FUNCTION Compare (SELF IN building,
 8:                                  OtherBuilding IN building)
 9:       RETURN INTEGER
10:   );
11:
12: CREATE OR REPLACE TYPE BODY building AS
13:   MEMBER PROCEDURE  ChangeMgr(SELF IN OUT building,
14:                               NewMgr IN INTEGER) IS
15:     BEGIN
16:       SELF.BldgMgr := NewMgr;
17:     END;
18:
19:   ORDER MEMBER FUNCTION Compare (SELF IN building,
20:                                  OtherBuilding IN building)

                                                          continues

Listing 13.16. continued

21:   RETURN INTEGER IS
22:       BldgName1     VARCHAR2(40);
23:       BldgName2     building.BldgName%TYPE;
24:     BEGIN
25:       --Grab the two building names for comparison.
26:       --Make sure that we don't get messed up by leading/trailing
27:       --spaces or by case.
28:       BldgName1 := upper(ltrim(rtrim(SELF.BldgName)));
29:       BldgName2 := upper(ltrim(rtrim(OtherBuilding.BldgName)));
30:
31:       --Return the appropriate value to indicate the order of
32:       --this object vs OtherBuilding.
33:       IF BldgName1 = BldgName2 THEN
34:         RETURN 0;
35:       ELSIF BldgName1 < BldgName2 THEN
36:         RETURN -1;
37:       ELSE
38:         RETURN 1;
39:       END IF;
40:     END;
41: END;
42: /

ANALYSIS
Notice that all the member method definitions now include SELF as the first parameter. All attribute references in the preceding listing are explicitly prefaced with "SELF." Oracle always treats any unqualified attribute references in member methods as if you had really written them this way.

The SELF parameter must always be the object type being defined. By default it is an input (IN) parameter for member functions and an input/output (IN OUT) parameter for member procedures. This is because functions usually return values without altering an object's attributes, whereas procedures frequently do alter an object's attributes.

Although you normally do not specify the SELF parameter, you might do so if you want to specify an input/output mode other than the default. For example, if you wanted a member function to be able to modify an object's attributes, you would explicitly define SELF as an IN OUT parameter.

Overloading

NEW TERM
The term overloading refers to the ability to have more than one function or procedure of the same name, but with a different number and types of parameters. This ability to overload function and procedure names is a key feature of object-oriented languages. It is what allows you to write a Compare function for several different object types. Being able to do this frees you from the burden of remembering a different comparison function name for each object type that you are using.