Create object types and member static and constructor methods - PolarDB

Use CREATE TYPE to define an object type specification and CREATE TYPE BODY to implement its methods. Object types let you group related attributes and methods into a single composite unit, which you can then reference in other type definitions or table columns.

Object type structure

An object type has two parts:

Part	Statement	Purpose
Object type specification	`CREATE TYPE`	Defines attributes and method signatures
Object type body	`CREATE TYPE BODY`	Implements the methods

The object type body is required only when the object type includes methods. If you define attributes only, no body is needed.

Method types

Object types support three method types:

Method type	Invoked by	Can access object attributes
Member method	An instance of the object type	Yes
Static method	The object type name	No
Constructor method	Direct call (name matches the object type)	Yes (via `SELF`)

Create an object type with attributes only

An object type with no methods requires only the specification.

CREATE OR REPLACE TYPE addr_object_type AS OBJECT (
    street    VARCHAR2(30),
    city      VARCHAR2(20),
    state     CHAR(2),
    zip       NUMBER(5)
);

addr_object_type groups four address attributes so they can be treated as a single unit in other type definitions or table columns. Because the type has no methods, no object type body is needed.

Member methods

A member method is a function or procedure defined within an object type that operates on a specific object instance. Member methods can access and modify the attributes of the instance they operate on.

Define a member method

Declare the method signature in CREATE TYPE, then implement it in CREATE TYPE BODY.

Object type specification:

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

Object type body:

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;

Inside a member method body, you can reference attributes directly by name (empno, ename) or prefixed with SELF. (SELF.empno, SELF.ename). Both forms are equivalent:

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;

SELF parameter behavior

Behavior	Detail
Implicit parameter	Every member method has an implicit `SELF` parameter. Its data type matches the object type being defined.
First parameter	`SELF` is always the first parameter in a member procedure or function, whether or not it is explicitly declared in the parameter list.
Default mode (procedures)	`IN OUT`

Static methods

A static method belongs to an object type but is not tied to any instance. Invoke it using the object type name:

emp_obj_type.get_count();

Static methods cannot access or modify object instance attributes.

Define a static method

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
);

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;

Static method constraints

Inside a static function, references to SELF are not allowed. The function has no implicit access to any object instance.
Inside a member procedure (such as display_dept), SELF.deptno is accessible without explicitly declaring SELF in the parameter list.
To call a static function from within a member procedure, qualify it with the object type name: dept_obj_type.get_dname(SELF.deptno).

Constructor methods

A constructor method creates an instance of an object type. Its name always matches the object type name. Constructor methods can be overloaded: you can define multiple constructors with the same name but with different parameter types.

Default constructor

The SPL compiler automatically generates a default constructor for every object type. Its parameter list matches the object type attributes in declaration order, and it initializes each attribute to NULL.

For the following object type:

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

The SPL compiler generates this default constructor signature:

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

Custom constructors

Define a custom constructor when you need to compute attribute values from partial input or enforce business rules at object creation time. Common use cases:

Use case	Example
Computing derived attributes	Derive `city` and `state` from a `postal_code`, so callers supply two fields instead of four.
Enforcing business rules	Prevent objects from being created in an invalid state — for example, a payment object with a `NULL` or zero amount.

Declare the constructor in CREATE TYPE and implement it in CREATE TYPE BODY:

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;

To create an instance using the custom constructor:

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;

To override the default constructor — for example, to block NULL amounts in a payment type — declare a custom constructor whose signature matches the default constructor.