Use CREATE TYPE BODY to define an object type body - PolarDB

Defines the method implementations for an object type created with CREATE TYPE.

Syntax

CREATE [ OR REPLACE ] TYPE BODY name
  { IS | AS }
 method_spec [...]
END

Where method_spec is:

subprogram_spec

And subprogram_spec is:

  { MEMBER | STATIC }
  { PROCEDURE proc_name
      [ ( [ SELF [ IN | IN OUT ] name ]
          [, argname [ IN | IN OUT | OUT ] argtype
                     [ DEFAULT value ]
          ] ...)
      ]
  { IS | AS }
 program_body
    END;
  |
    FUNCTION func_name
      [ ( [ SELF [ IN | IN OUT ] name ]
          [, argname [ IN | IN OUT | OUT ] argtype
                     [ DEFAULT value ]
          ] ...)
      ]
    RETURN rettype
  { IS | AS }
 program_body
    END;
  }

Description

Use CREATE TYPE BODY to define a new object type body. Use CREATE OR REPLACE TYPE BODY to create a new object type body or replace an existing one.

If a schema name is included, the object type body is created in the specified schema. Otherwise, it is created in the current schema. The name of the new object type body must match an existing object type specification in the same schema, and must not duplicate an existing object type body in that schema—unless you are updating an existing body with CREATE OR REPLACE TYPE BODY.

Parameters

Parameter	Description
`name`	The name of the object type for which a body is to be created. The name may be schema-qualified.
`MEMBER` \| `STATIC`	Specify `MEMBER` if the subprogram runs on an object instance. Specify `STATIC` if the subprogram runs independently of any particular object instance.
`proc_name`	The name of the procedure to create.
`SELF [ IN \| IN OUT ] name`	For a member method, there is an implicit, built-in parameter named `SELF` whose data type is the same as the object type being defined. `SELF` refers to the object instance invoking the method. You can explicitly declare `SELF` as `IN` or `IN OUT` in the parameter list; if declared explicitly, it must be the first parameter. If not declared, the default mode is `IN OUT` for member procedures and `IN` for member functions.
`argname`	The name of an argument, referenced by this name within the method body.
`argtype`	The data type(s) of the method arguments. Argument types can be a base data type or a user-defined type such as a nested table or an object type. Do not specify a length for base types—for example, use `VARCHAR2`, not `VARCHAR2(10)`.
`DEFAULT value`	A default value for an input argument when none is provided in the method call. `DEFAULT` cannot be specified for `IN OUT` or `OUT` arguments.
`program_body`	The pragmas, declarations, and SPL statements that make up the function or procedure body. The pragma can be `PRAGMA AUTONOMOUS_TRANSACTION` to mark the subprogram as an autonomous transaction.
`func_name`	The name of the function to create.
`rettype`	The return data type of the function. Accepts the same types as `argtype`. Do not specify a length for `rettype`.

Examples

Define a member procedure

The following example creates the object type body for emp_obj_typ, implementing a member procedure that prints employee details.

CREATE OR REPLACE TYPE BODY emp_obj_typ AS
    MEMBER PROCEDURE display_emp (SELF IN OUT emp_obj_typ)
    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;

Define a static function and a member procedure

The following example creates the object type body for dept_obj_typ. It implements a static function that returns a department name by number, and a member procedure that calls that function to print department details.

CREATE OR REPLACE TYPE BODY dept_obj_typ 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 := 'ACCOUNING';
            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_typ.get_dname(SELF.deptno));
    END;
END;