Object type components-PolarDB(PolarDB)-阿里云帮助中心

An object type in Stored Procedure Language (SPL) consists of two components:

Object type specification — the public interface, declaring attributes and method signatures.
Object type body — the implementation, containing the code for each method declared in the specification.

Create the specification first, then create the body to implement its methods. Every instantiable method declared in the specification must have a corresponding implementation in the body.

Object type specification

Use CREATE TYPE to define the public interface of an object type.

Syntax

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:

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

where subprogram_spec is:

{ 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:

  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

Parameters

`name` Identifier for the object type. Optionally schema-qualified.

`AUTHID` Controls which user's privileges are used when a method executes:

DEFINER (default) — uses the rights of the object type owner.
CURRENT_USER — uses the rights of the user currently executing the method.

`attribute` Identifier for an attribute of the object type. At least one attribute is required.

`datatype` A base data type for the attribute.

`objtype` A previously defined object type used as the attribute's data type.

`collecttype` A previously defined collection type used as the attribute's data type.

`FINAL | NOT FINAL` (after the closing parenthesis) Whether a subtype can be derived from this object type:

FINAL (default) — no subtypes can be derived.
NOT FINAL — subtypes are allowed.

SPL accepts NOT FINAL in the CREATE TYPE statement, but does not support creating subtypes.

`INSTANTIABLE | NOT INSTANTIABLE` (after the closing parenthesis) Whether an instance of this object type can be created:

INSTANTIABLE (default) — instances can be created.
NOT INSTANTIABLE — no instances can be created; the type serves only as a parent template. If specified, NOT FINAL must also be specified.

SPL accepts NOT INSTANTIABLE in the CREATE TYPE statement, but does not support creating subtypes.

`method_spec` The specification of a member or static method. Each method_spec consists of optional qualifiers followed by a subprogram_spec.

`FINAL | NOT FINAL` (before a method definition) Whether the method can be overridden in a subtype:

NOT FINAL (default) — the method can be overridden.
FINAL — the method cannot be overridden.

`INSTANTIABLE | NOT INSTANTIABLE` (before a method definition) Whether the object type body provides an implementation for this method:

INSTANTIABLE (default) — the CREATE TYPE BODY statement must include an implementation.
NOT INSTANTIABLE — the CREATE TYPE BODY statement must not include an implementation. A subtype is expected to override the method.

If any method is NOT INSTANTIABLE, the object type itself must be declared NOT INSTANTIABLE and NOT FINAL.

`OVERRIDING` Indicates that this method overrides an identically named method in a supertype. The overriding method must have:

The same number of parameters, with identical names, data types, and modes, in the same order.
The same return type (for functions).

`subprogram_spec` Specifies whether the subprogram is a MEMBER or STATIC method, then defines a procedure or function:

`MEMBER` — must be invoked on a specific object instance.
`STATIC` — invoked without reference to an object instance.

`proc_name` Identifier of a procedure.

`func_name` Identifier of a function.

`SELF` An implicit parameter present in every member method. Its data type is the object type being defined. SELF refers to the object instance invoking the method.

If declared explicitly, SELF must be the first parameter. If omitted from the parameter list, its mode defaults to:

IN OUT for member procedures.
IN for member functions.

`parm1`, `parm2`, ... Formal parameters of the procedure or function.

`datatype1`, `datatype2`, ... Data types of the corresponding formal parameters.

`IN | IN OUT | OUT` Parameter mode. Defaults to IN if not specified.

`value1`, `value2`, ... Default values for IN parameters.

`return_type` The data type returned by a function.

`constructor` Defines a constructor function. Include the CONSTRUCTOR keyword and the function definition. The constructor must end with RETURN self AS RESULT.

Usage notes

An object type specification must include at least one attribute.
An object type can have zero, one, or more methods.
Every member method has an implicit SELF parameter whose type is the object type being defined.
Static methods cannot be overridden. OVERRIDING and STATIC cannot be combined in method_spec.
Static methods must be instantiable. NOT INSTANTIABLE and STATIC cannot be combined in method_spec.

Limitations

Currently, OR REPLACE cannot add, delete, or modify attributes of an existing object type. To change attributes, use DROP TYPE to delete the type, then recreate it. OR REPLACE can add, delete, or modify methods.
The PostgreSQL form of ALTER TYPE ALTER ATTRIBUTE can change the data type of an attribute, but cannot add or delete attributes.

Object type body

Use CREATE TYPE BODY to implement the methods declared in the object type specification.

Syntax

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

where method_spec is:

subprogram_spec

where subprogram_spec is:

{ 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:

  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;

Parameters

`name` Identifier of the object type. Must match the name used in the corresponding CREATE TYPE statement. Optionally schema-qualified.

`method_spec` The implementation of an instantiable method declared in the CREATE TYPE statement.

If the method is declared INSTANTIABLE (or the qualifier is omitted) in CREATE TYPE, a method_spec for that method must appear in CREATE TYPE BODY.
If the method is declared NOT INSTANTIABLE in CREATE TYPE, no method_spec for that method may appear in CREATE TYPE BODY.

`subprogram_spec` The same MEMBER or STATIC qualifier used in the CREATE TYPE statement must be used here. The parameter declarations must match those in CREATE TYPE exactly.

`proc_name` Identifier of a procedure, matching the name declared in CREATE TYPE.

`func_name` Identifier of a function, matching the name declared in CREATE TYPE. The return_type must match the return type declared in CREATE TYPE.

`PRAGMA AUTONOMOUS_TRANSACTION` Sets the procedure or function as an autonomous transaction, running independently of the calling transaction.

`declarations` Variable, cursor, type, or subprogram declarations local to the method. Subprogram declarations must appear after all other declarations.

`statement` An SPL program statement.

`constructor` The implementation of a constructor function. Include the CONSTRUCTOR keyword and the function definition.