Oracle PL/SQL by Example, Fifth Edition (2015)

---

In Lab 23.1, you learned that object type methods are functions and procedures that specify actions that may be performed on the object type attributes and are defined in the object type specification. Also, you saw how to use the default system-defined constructor methods. A constructor is only one of the method types supported by PL/SQL. Some other method types are member, static, map, and order. The method type is typically determined by the actions that a particular method performs. For example, constructor methods are used to initialize object instances, whereas map and order methods are used for comparing and sorting object instances, respectively.

Oftentimes object type methods use a built-in parameter called SELF. This parameter represents a particular instance of the object type. As such, it is available to the methods that are invoked on that object type instance. You will see various examples of the SELF parameter in the discussions that follow.

Constructor Methods

A constructor method is a default method that is implicitly created by the system whenever a new object type is created. This function has the same name as its object type. Its input parameters have the same names and data types as the object type attributes and are listed in the same order as the object type attributes. A constructor method returns a new instance of the object type. In other words, it initializes the new object instance and assigns values to the object attributes.

Listing 23.2 illustrates calls to the default constructor method for the zipcode_obj_type created in Lab 23.1.

Listing 23.2 Default Constructor Method for Zipcode_Obj_Type

zip_obj1 := ZIPCODE_OBJ_TYPE('00914', 'Santurce', 'PR', USER, SYSDATE, USER, SYSDATE);

or

zip_obj2 := ZIPCODE_OBJ_TYPE(NULL, NULL, NULL, NULL, NULL, NULL NULL);

The first call to the constructor method returns a new instance, zip_obj1, of the zipcode_obj_type with attributes initialized to non-null values. The second call creates a new instance, zip_obj2, with NULL attribute values. Note that both calls produce non-null instances of thezipcode_obj_type. The difference between them lies in the values assigned to the individual attributes.

In Listing 23.2, both calls to the default constructor method use positional notation. Recall that positional notation associates values with corresponding parameters based on their positions in the header of the function, procedure, or (in this case) constructor. Next, consider the call to the default constructor method that uses named notation. In this case, the order of parameters does not correspond to the order of attributes in the zipcode_obj_type, as they are referenced by their names as shown in Listing 23.3.

Listing 23.3 Using Positional Notation with the Default Constructor Method for Zipcode_Obj_Type

zip_obj3 := ZIPCODE_OBJ_TYPE(created_by => USER
,created_date => SYSDATE
,modified_by => USER
,modified_date => SYSDATE
,zip =>'00914'
,city => 'Santurce'
,state => 'PR');

As noted earlier, PL/SQL also provides you with the ability to create your own (user-defined) constructors. User-defined constructors offer flexibility that the default constructors lack. For example, you might want to define a constructor on the zipcode_obj_type that initializes only some of the attributes of the newly created object instance. In this case, any attributes for which you do not specify values will be initialized to NULL by the system. In addition, you can control the number and type of parameters that your constructor may require.

Consider the following example of the user-defined constructors for the zipcode_obj_type.

For Example ch23_8a.sql

CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT
(zip VARCHAR2(5)
,city VARCHAR2(25)
,state VARCHAR2(2)
,created_by VARCHAR2(30)
,created_date DATE
,modified_by VARCHAR2(30)
,modified_date DATE

,CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
,zip VARCHAR2)
RETURN SELF AS RESULT

,CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
,zip VARCHAR2
,city VARCHAR2
,state VARCHAR2)
RETURN SELF AS RESULT);
/

CREATE OR REPLACE TYPE BODY zipcode_obj_type AS

CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
,zip VARCHAR2)
RETURN SELF AS RESULT
IS
BEGIN
SELF.zip := zip;
SELECT city, state
INTO SELF.city, SELF.state
FROM zipcode
WHERE zip = SELF.zip;

RETURN;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
END;

CONSTRUCTOR FUNCTION zipcode_obj_type (SELF IN OUT NOCOPY zipcode_obj_type
,zip VARCHAR2
,city VARCHAR2
,state VARCHAR2)
RETURN SELF AS RESULT
IS
BEGIN
SELF.zip := zip;
SELF.city := city;
SELF.state := state;

RETURN;
END;
END;
/

This script expands the definition of the zipcode_obj_type by providing two versions of the default constructor method. In programming terms, such approach is called overloading. Essentially, overloading allows two methods or subprograms to use the same name as long as their parameters differ in terms of either their data types or their number. In the preceding example, the first constructor method expects two parameters, and the second constructor method expects four parameters.

Both constructors use the default parameter SELF as an IN OUT parameter and as a return data type in the RETURN clause. As stated previously, SELF references a particular object type instance. Note the use of the NOCOPY compiler hint. This hint is typically used with OUT and IN OUT parameters. By default, OUT and IN OUT parameters are passed by value. As a consequence, the values of these parameters are copied prior to the execution of the subprogram or method. Then, during the execution, temporary variables are used to hold values of the OUT parameters. For parameters that represent complex data types such as collections, records, and object type instances, this copying step can add significant processing overhead. By adding the NOCOPY hint, you instruct PL/SQL compiler to pass OUT and IN OUT parameters by reference and eliminate the copying step altogether.

In the type body, both constructor methods populate the city, state, and zip attributes. The first constructor method accomplishes this work via a SELECT INTO statement, and the second constructor method assigns incoming values to the object attributes. Notice how the attributes are referenced via the SELF parameter in the constructor methods.

Member Methods

Member methods provide access to the object instance data. As such, a member method should be defined for each action that an object type must perform. For example, you may need to return city, state, and ZIP code values associated with an object instance to the calling application, as shown in the next example. Note that this example shows only the newly added member method.

For Example ch23_8b.sql

CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT
...

,MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2
,out_city OUT VARCHAR2
,out_state OUT VARCHAR2));
/

CREATE OR REPLACE TYPE BODY zipcode_obj_type AS

...

MEMBER PROCEDURE get_zipcode_info (out_zip OUT VARCHAR2
,out_city OUT VARCHAR2
,out_state OUT VARCHAR2)
IS
BEGIN
out_zip := SELF.zip;
out_city := SELF.city;
out_state := SELF.state;
END;
END;
/

This version of the object type definition contains a new member procedure that returns the ZIP code, city, and state values associated with a particular instance of the zipcode_obj_type object type. The reference to the SELF parameter in this procedure is optional, however, so the assignment statements can be modified as follows:

out_zip := zip;
out_city := city;
out_state := state;

These statements initialize OUT parameters associated with individual attributes of a particular object instance, just like the statements that include the reference to the SELF parameter.

Static Methods

Static methods are created for actions that do not need to access data associated with a particular object instance. As such, these methods are created for the object type itself and describe actions that are global to that object type. Because static methods do not have access to the data associated with a particular object type instance, they may not reference the default parameter SELF.

Consider the following example of a static method that displays ZIP code information. Note that this example shows only the newly added static method.

For Example ch23_8c.sql

CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT
...

,STATIC PROCEDURE display_zipcode_info (in_zip_obj IN zipcode_obj_type));
/

CREATE OR REPLACE TYPE BODY zipcode_obj_type AS

...

STATIC PROCEDURE display_zipcode_info (in_zip_obj IN zipcode_obj_type)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Zip: ' ||in_zip_obj.zip);
DBMS_OUTPUT.PUT_LINE ('City: ' ||in_zip_obj.city);
DBMS_OUTPUT.PUT_LINE ('State: '||in_zip_obj.state);
END;
END;
/

In this version of the script, the static method display_zipcode_info displays the values of the individual attributes for the ZIP code object on the screen. Even though this method references data associated with some object instance, the object instance is created elsewhere (i.e., another PL/SQL script, function, or procedure) and then passed into this method as an input parameter.

Comparing Objects

In PL/SQL, element data types such VARCHAR2, NUMBER, or DATE have a predefined order that enables them to be compared to each other or sorted. For example, the comparison operator (>) determines which variable contains a greater value and the IF-THEN-ELSE statement evaluates toTRUE, FALSE, or NULL accordingly:

IF v_num1 > v_num2 THEN
-- Do something
ELSE
-- Do something else
END IF;

In contrast, an object type may contain multiple attributes of different data types and, as a result, does not have a predefined order. Thus, to be able to compare and sort object instances of the same object type, you must specify how these object instances should be compared and ordered. This can be accomplished via two types of optional member methods, map and order.

Map Methods

Map methods compare and order object instances essentially by mapping an object instance to an element (scalar) data type such as DATE, NUMBER, or VARCHAR2. This mapping is then used to position object instance on the axis (DATE, NUMBER, or VARCHAR2) used for the comparison.

A map method is a member function that does not accept any parameters and returns an element data type, as demonstrated in the next example. Note that this example shows only the newly added map method.

For Example ch23_8d.sql

CREATE OR REPLACE TYPE zipcode_obj_type AS OBJECT
...

,MAP MEMBER FUNCTION zipcode RETURN VARCHAR2);
/

CREATE OR REPLACE TYPE BODY zipcode_obj_type AS

...

MAP MEMBER FUNCTION zipcode RETURN VARCHAR2
IS
BEGIN
RETURN (zip);
END;
END;
/

In this version of the script, the map member function returns the value of the zip attribute that has been defined as VARCHAR2.

Once a map method is added to the object type, object type instances may be compared or ordered similarly to the element data types. For example, if zip_obj1 and zip_obj2 are two instances of the zipcode_obj_type, they can be compared as follows:

zip_obj1 > zip_obj2

or

zip_obj1.zipcode() > zip_obj2.zipcode()

The second statement uses dot notation to reference the map function.

The next example demonstrates how the various object type methods created so far may be used.

For Example ch23_9a.sql

DECLARE
zip_obj1 zipcode_obj_type;
zip_obj2 zipcode_obj_type;
BEGIN
-- Initialize object instances with user-defined constructor methods
zip_obj1 := zipcode_obj_type (zip => '12345'
,city => 'Some City'
,state => 'AB');

zip_obj2 := zipcode_obj_type (zip => '48104');

-- Compare object instances via map methods
IF zip_obj1 > zip_obj2
THEN
DBMS_OUTPUT.PUT_LINE ('zip_obj1 is greater than zip_obj2');
ELSE
DBMS_OUTPUT.PUT_LINE
('zip_obj1 is not greater than zip_obj2');
END IF;
END;

When the user-defined constructors are invoked, there is no reference to the SELF default parameter in the call statements.

When run, this script produces the following output:

v_zip_obj1 is not greater than v_zip_obj2

Order Methods

Order methods use a different technique for comparing and ordering object instances. They do not map object instances to an external axis such as NUMBER or DATE. Instead, an order method compares the current object instance with another object instance of the same object type based on some criteria specified in the method.

An order method is a member function with a single IN parameter of the same object type that returns INTEGER as its return type. Furthermore, the method must return a negative number, zero, or a positive number, which indicates that the object instance referenced by the SELFparameter is less than, equal to, or greater than the object instance referenced by the IN parameter, respectively.