PolarDB:Create an object type

Member methods

A member method is a function or procedure that is defined within an object type and only can be invoked by using an instance of that type. Member methods have access to and can change the attributes of the object instance on which they are operating.

The following example creates the emp_obj_type object type:

CREATE OR REPLACE TYPE emp_obj_type AS OBJECT (
    empno           NUMBER(4),
    ename           VARCHAR2(20),
    addr            ADDR_OBJ_TYPE,
    MEMBER PROCEDURE display_emp(SELF IN OUT emp_obj_type)
);

The object type emp_obj_type contains a member method named display_emp. display_emp uses a SELF parameter, which passes the object instance on which the method is invoked.

The data type of a SELF parameter is the same as that of the object type being defined. A SELF parameter always references the instance that is invoking the method. A SELF parameter is the first parameter in a member procedure or function regardless of whether it is explicitly declared in the parameter list.

The following example defines an object type body for emp_obj_type:

CREATE OR REPLACE TYPE BODY emp_obj_type AS
    MEMBER PROCEDURE display_emp (SELF IN OUT emp_obj_type)
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Employee No   : ' || empno);
        DBMS_OUTPUT.PUT_LINE('Name          : ' || ename);
        DBMS_OUTPUT.PUT_LINE('Street        : ' || addr.street);
        DBMS_OUTPUT.PUT_LINE('City/State/Zip: ' || addr.city || ', ' ||
            addr.state || ' ' || LPAD(addr.zip,5,'0'));
    END;
END;

You can also use the SELF parameter in an object type body. To illustrate how the SELF parameter is used in the CREATE TYPE BODY statement, you can rewrite the preceding object type body as follows:

CREATE OR REPLACE TYPE BODY emp_obj_type AS
    MEMBER PROCEDURE display_emp (SELF IN OUT emp_obj_type)
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Employee No   : ' || SELF.empno);
        DBMS_OUTPUT.PUT_LINE('Name          : ' || SELF.ename);
        DBMS_OUTPUT.PUT_LINE('Street        : ' || SELF.addr.street);
        DBMS_OUTPUT.PUT_LINE('City/State/Zip: ' || SELF.addr.city || ', ' ||
            SELF.addr.state || ' ' || LPAD(SELF.addr.zip,5,'0'));
    END;
END;

Both versions of the emp_obj_type body are equivalent.

Static methods

Like a member method, a static method belongs to an object type. A static method, however, is not invoked by an instance of the object type but by using the name of the object type. For example, to invoke a static function named get_count and defined within the emp_obj_type object type, you can write as follows:

emp_obj_type.get_count();

A static method does not have access to and cannot change the attributes of an object instance. It does not typically work with an instance of the object type.

The following object type specification includes a static function get_dname and a member procedure display_dept:

CREATE OR REPLACE TYPE dept_obj_type AS OBJECT (
    deptno          NUMBER(2),
    STATIC FUNCTION get_dname(p_deptno IN NUMBER) RETURN VARCHAR2,
    MEMBER PROCEDURE display_dept
);

The object type body for dept_obj_type defines a static function named get_dname and a member procedure named display_dept.

CREATE OR REPLACE TYPE BODY dept_obj_type AS
    STATIC FUNCTION get_dname(p_deptno IN NUMBER) RETURN VARCHAR2
    IS
        v_dname     VARCHAR2(14);
    BEGIN
        CASE p_deptno
            WHEN 10 THEN v_dname := 'ACCOUNTING';
            WHEN 20 THEN v_dname := 'RESEARCH';
            WHEN 30 THEN v_dname := 'SALES';
            WHEN 40 THEN v_dname := 'OPERATIONS';
            ELSE v_dname := 'UNKNOWN';
        END CASE;
        RETURN v_dname;
    END;
    MEMBER PROCEDURE display_dept
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Dept No    : ' || SELF.deptno);
        DBMS_OUTPUT.PUT_LINE('Dept Name  : ' ||
            dept_obj_type.get_dname(SELF.deptno));
    END;
END;

Within the static function get_dname, references to SELF are not allowed. Since a static function is invoked independently of an object instance, it has no implicit access to any object attribute.

The member procedure display_dept can access the deptno attribute of the object instance passed in the SELF parameter. You do not need to explicitly declare the SELF parameter in the display_dept parameter list.

The last DBMS_OUTPUT.PUT_LINE statement in the display_dept procedure includes a call to the static function get_dname, which is qualified by its object type name dept_obj_type.

Constructor methods

A constructor method is a function that creates an instance of an object type, typically by assigning values to the members of the object. An object type may define several constructors to accomplish different tasks. A constructor method is a member function invoked with a SELF parameter and its name matches the name of the object type.

For example, if you define an object type named address, each constructor is named address. You may overload a constructor by creating one or more different constructor functions with the same name but with different parameter types.

The SPL compiler provides a default constructor for each object type. The default constructor is a member function. Its name matches the name of the object type and its parameter list matches the object type members in order. The following example creates an object type named address:

CREATE TYPE address AS OBJECT
(
  street_address VARCHAR2(40),
  postal_code    VARCHAR2(10),
  city           VARCHAR2(40),
  state          VARCHAR2(2)
)

The SPL compiler provides a default constructor with the following signature:

CONSTRUCTOR FUNCTION address
(
  street_address VARCHAR2(40),
  postal_code    VARCHAR2(10),
  city           VARCHAR2(40),
  state          VARCHAR2(2)
)

The body of the default constructor sets each member to NULL.

If you want to create a custom constructor, declare the constructor by using the keyword constructor in the CREATE TYPE statement and define it in the CREATE TYPE BODY statement. For example, if you want to create a custom constructor for the address object type that computes the city and state given a street_address and postal_code, write as follows:

CREATE TYPE address AS OBJECT
(
  street_address VARCHAR2(40),
  postal_code    VARCHAR2(10),
  city           VARCHAR2(40),
  state          VARCHAR2(2),

  CONSTRUCTOR FUNCTION address
   (
     street_address VARCHAR2,
     postal_code VARCHAR2
    ) RETURN self AS RESULT
)
 CREATE TYPE BODY address AS
  CONSTRUCTOR FUNCTION address
   (
     street_address VARCHAR2,
     postal_code VARCHAR2
    ) RETURN self AS RESULT
  IS
    BEGIN
      self.street_address := street_address;
      self.postal_code := postal_code;
      self.city := postal_code_to_city(postal_code);
      self.state := postal_code_to_state(postal_code);
      RETURN;
    END;
END;

If you want to create an instance of an object type, you can invoke one of the constructor methods for that object type. For example:

DECLARE
  cust_addr address := address('100 Main Street', 02203');
BEGIN
  DBMS_OUTPUT.PUT_LINE(cust_addr.city);  -- displays Boston
  DBMS_OUTPUT.PUT_LINE(cust_addr.state); -- displays MA
END;

Custom constructors are typically used to compute member values when they are given incomplete information. The preceding example computes the values for city and state when a postal code is provided.

Custom constructors are also used to enforce business rules that restrict the state of an object. For example, if you define an object type to represent a payment, you can use a custom constructor to ensure that no object of the object type payment can be created with an amount that is NULL, negative, or zero. The default constructor sets payment.amount to NULL. Therefore, you must create a custom constructor whose signature matches the default constructor to prohibit NULL amounts.