Object type components - PolarDB - Alibaba Cloud Documentation Center

You can create and store an object type in a database by using the following two constructs of the Stored Procedure Language (SPL).

Object type specification: This is the public interface which specifies the attributes and method signatures of the object type.
Object type body: This contains the implementation of the methods specified in the object type specification.

The following sections describe the statements used to create the object type specification and the object type body.

Syntax of the object type specification

The syntax of the object type specification is as follows:

CREATE [ OR REPLACE ] TYPE name
  [ AUTHID { DEFINER | CURRENT_USER } ]
  { IS | AS } OBJECT
( { attribute { datatype | objtype | collecttype } }
    [, ...]
  [ method_spec ] [, ...]
  [ constructor ] [, ...]
) [ [ NOT ] { FINAL | INSTANTIABLE } ] ... ;

where, method_spec is as follows:

[ [ NOT ] { FINAL | INSTANTIABLE } ] ...
[ OVERRIDING ]
  subprogram_spec

where, subprogram_spec is as follows:

{ MEMBER | STATIC }
{ PROCEDURE proc_name
    [ ( [  SELF [ IN | IN OUT ] name ]
        [, parm1 [ IN | IN OUT | OUT ] datatype1
                 [ DEFAULT value1 ] ]
        [, parm2 [ IN | IN OUT | OUT ] datatype2
                 [ DEFAULT value2 ]
        ] ...)
    ]
|
  FUNCTION func_name
    [ ( [  SELF [ IN | IN OUT ] name ]
        [, parm1 [ IN | IN OUT | OUT ] datatype1
                 [ DEFAULT value1 ] ]
        [, parm2 [ IN | IN OUT | OUT ] datatype2
                 [ DEFAULT value2 ]
        ] ...)
    ]
  RETURN return_type
}

where, constructor is as follows:

  CONSTRUCTOR func_name
    [ ( [  SELF [ IN | IN OUT ] name ]
        [, parm1 [ IN | IN OUT | OUT ] datatype1
                 [ DEFAULT value1 ] ]
        [, parm2 [ IN | IN OUT | OUT ] datatype2
                 [ DEFAULT value2 ]
        ] ...)
    ]
  RETURN self AS RESULT

Note Currently, the OR REPLACE option cannot be used to add, delete, or modify the attributes of an existing object type. Before you can use this option, you must use the DROP TYPE statement to first delete the existing object type. However, the OR REPLACE option can be used to add, delete, or modify the methods in an existing object type.

The PostgreSQL form of the ALTER TYPE ALTER ATTRIBUTE statement can be used to change the data type of an attribute in an existing object type. However, the ALTER TYPE statement cannot be used to add or delete attributes in the object type.

name is an identifier assigned to the object type. It is optionally schema-qualified.

If the AUTHID clause is omitted or DEFINER is specified, the rights of the object type owner are used to determine access permissions on database objects. If CURRENT_USER is specified, the rights of the current user who is executing a method in the object are used to determine access permissions.

attribute is an identifier assigned to an attribute of the object type.

datatype is a base data type.

objtype is a previously defined object type.

collecttype is a previously defined collection type.

Following the closing parenthesis of the CREATE TYPE definition, [ NOT ] FINAL specifies whether a subtype can be derived from this object type. FINAL is the default value. It means that no subtypes can be derived from this object type. If you want to allow subtypes to be defined under this object type, specify NOT FINAL.

Note Even though the specification of NOT FINAL is accepted in the CREATE TYPE statement, SPL does not support the creation of subtypes.

Following the closing parenthesis of the CREATE TYPE definition, [ NOT ] INSTANTIABLE specifies whether an object instance of this object type can be created. INSTANTIABLE is the default value. It means that an instance of this object type can be created. If this object type is to be used only as a parent template from which other specialized subtypes are to be defined, specify NOT INSTANTIABLE. If NOT INSTANTIABLE is specified, NOTFINAL must also be specified. If a method in the object type contains the NOT INSTANTIABLE qualifier, the object type must be defined with NOT INSTANTIABLE and NOT FINAL.

Note Even though the specification of NOT INSTANTIABLE is accepted in the CREATE TYPE statement, SPL does not support the creation of subtypes.

method_spec denotes the specification of a member method or a static method.

Prior to the definition of a method, [ NOT ] FINAL specifies whether the method can be overridden in a subtype. NOT FINAL is the default value. It means that the method can be overridden in a subtype.

If a method overrides an identically named method in a supertype, specify OVERRIDING prior to the definition of the method. The overriding method must have the same number of identically named method parameters with the same data types and parameter modes, in the same order, and the same return type (if the method is a function) as defined in the supertype.

Prior to the definition of a method, [ NOT ] INSTANTIABLE specifies whether the object type definition provides an implementation for the method. If INSTANTIABLE is specified, the CREATE TYPE BODY statement for the object type must specify the implementation of the method. If NOT INSTANTIABLE is specified, the CREATE TYPE BODY statement for the object type must not contain the implementation of the method. In the latter case, assume that a subtype contains the implementation of the method, which overrides the method in this object type. If there are NOT INSTANTIABLE methods in the object type, the object type definition must specify NOT INSTANTIABLE and NOTFINAL following the closing parenthesis of the object type specification. The default value is INSTANTIABLE.

subprogram_spec denotes the specification of a procedure or function and begins with the specification of MEMBER or STATIC. A member subprogram must be invoked with respect to a particular object instance, while a static subprogram is not invoked with respect to an object instance.

proc_name is an identifier of a procedure. If the SELF parameter is specified, name is the object type name given in the CREATE TYPE statement. In this situation, parm1, parm2, ... are the formal parameters of the procedure. datatype1, datatype2, ... are the data types of parm1, parm2, ... respectively. IN, IN OUT, and OUT are possible parameter modes for each formal parameter. If none of them are specified, the default value is IN. value1, value2, ... are default values that may be specified for IN parameters.

You must include the CONSTRUCTOR keyword and function definition to define a constructor.

func_name is an identifier of a function. If the SELF parameter is specified, name is the object type name given in the CREATE TYPE statement. In this situation, parm1, parm2, ... are the formal parameters of the function. datatype1, datatype2, ... are the data types of parm1, parm2, ... respectively. IN, IN OUT, and OUT are possible parameter modes for each formal parameter. If none of them are specified, the default value is IN. value1, value2, ... are default values that may be specified for IN parameters. return_type is the data type of the value that the function returns.

Note the following points about an object type specification:

There must be at least one attribute defined in the object type.
There may be none, one, or more methods defined in the object type.
For each member method, there is an implicit, built-in parameter named SELF, whose data type is that of the object type being defined.
SELF refers to the object instance that is invoking the method. SELF can be explicitly declared as an IN or IN OUT parameter in the parameter list, for example, as MEMBERFUNCTION (SELF IN OUT object_type ...).

If SELF is explicitly declared, it must be the first parameter in the parameter list. If SELF is not explicitly declared, its parameter mode defaults to IN OUT for member procedures and to IN for member functions.
A static method cannot be overridden. OVERRIDING and STATIC cannot be specified together in method_spec.
A static method must be instantiable. NOT INSTANTIABLE and STATIC cannot be specified together in method_spec.

Syntax of the object type body

The syntax of the object type body is as follows:

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

where, method_spec is as follows:

subprogram_spec

where, subprogram_spec is as follows:

{ MEMBER | STATIC }
{ PROCEDURE proc_name
    [ ( [  SELF [ IN | IN OUT ] name ]
        [, parm1 [ IN | IN OUT | OUT ] datatype1
                 [ DEFAULT value1 ] ]
        [, parm2 [ IN | IN OUT | OUT ] datatype2
                 [ DEFAULT value2 ]
        ] ...)
    ]
{ IS | AS }
  [ PRAGMA AUTONOMOUS_TRANSACTION; ]
  [ declarations ]
  BEGIN
    statement; ...
[ EXCEPTION
    WHEN ... THEN
      statement; ...]
  END;
|
  FUNCTION func_name
    [ ( [  SELF [ IN | IN OUT ] name ]
        [, parm1 [ IN | IN OUT | OUT ] datatype1
                 [ DEFAULT value1 ] ]
        [, parm2 [ IN | IN OUT | OUT ] datatype2
                 [ DEFAULT value2 ]
        ] ...)
    ]
  RETURN return_type
{ IS | AS }
  [ PRAGMA AUTONOMOUS_TRANSACTION; ]
  [ declarations ]
  BEGIN
    statement; ...
[ EXCEPTION
    WHEN ... THEN
      statement; ...]
  END;

where, constructor is as follows:

  CONSTRUCTOR func_name
    [ ( [  SELF [ IN | IN OUT ] name ]
        [, parm1 [ IN | IN OUT | OUT ] datatype1
                 [ DEFAULT value1 ] ]
        [, parm2 [ IN | IN OUT | OUT ] datatype2
                 [ DEFAULT value2 ]
        ] ...)
    ]
  RETURN self AS RESULT
{ IS | AS }
  [ declarations ]
  BEGIN
    statement; ...
[ EXCEPTION
    WHEN ... THEN
      statement; ...]
  END;

name is an identifier assigned to the object type. It is optionally schema-qualified.

method_spec denotes the implementation of an instantiable method that is specified in the CREATE TYPE statement.

If INSTANTIABLE is specified or omitted in method_spec of the CREATE TYPE statement, there must be a method_spec for this method in the CREATE TYPE BODY statement.

If NOT INSTANTIABLE is specified in method_spec of the CREATE TYPE statement, there must be no method_spec for this method in the CREATE TYPE BODY statement.

subprogram_spec denotes the specification of a procedure or function and begins with the specification of MEMBER or STATIC. The same qualifier as that specified in subprogram_spec of the CREATE TYPE statement must be used.

proc_name is an identifier of a procedure specified in the CREATE TYPE statement. The parameter declarations have the same meaning as described for the CREATE TYPE statement, and must be specified in the CREATE TYPE BODY statement in the same manner as specified in the CREATE TYPE statement.

You must include the CONSTRUCTOR keyword and function definition to define a constructor.

func_name is an identifier of a function specified in the CREATE TYPE statement. The parameter declarations have the same meaning as described for the CREATETYPE statement, and must be specified in the CREATETYPE BODY statement in the same manner as specified in the CREATE TYPE statement. return_type is the data type of the value that the function returns and must match return_type given in the CREATE TYPE statement.

PRAGMA AUTONOMOUS_TRANSACTION is the directive that sets the procedure or function as an autonomous transaction.

declarations are variable, cursor, type, or subprogram declarations. If subprogram declarations are included, they must be declared after all other variable, cursor, and type declarations.

statement is an SPL program statement.