CREATE TYPE v14
CREATE TYPE — Define a new user-defined type, The new type can be an object type, a collection type (a nested table type or a varray type), or a composite type.
CREATE TYPE defines a new data type. The types that you can create are an object type, a nested table type, a varray type, or a composite type. Nested table and varray types belong to the category of types known as collections.
Composite types aren't compatible with Oracle databases. However, SPL programs can access composite types along with other types.
The name (optionally schema-qualified) of the type to create.
DEFINER | CURRENT_USER
Specifies whether to use the privileges of the object type owner (
DEFINER) or of the current user executing a method in the object type (
CURRENT_USER) to determine whether access is allowed to database objects referenced in the object type.
DEFINER is the default.
The name of an attribute in the object type or composite type.
The data type that defines an attribute of the object type or composite type, or the elements of the collection type that's being created.
The name of an object type that defines an attribute of the object type or the elements of the collection type that's being created.
The name of a collection type that defines an attribute of the object type or the elements of the collection type that's being created.
For an object type, specifies whether you can derive a subtype from the object type.
FINAL (you can't derive a subtype from the object type) is the default.
method_spec, specifies whether you can override the method in a subtype.
NOT FINAL (you can override the method in a subtype) is the default.
For an object type, specifies whether you can create an object instance of this object type.
INSTANTIABLE (you can create an instance of this object type) is the default. If you specify
NOT INSTANTIABLE, then you must specify
NOT FINAL as well. If
method_spec for any method in the object type contains the
NOT INSTANTIABLE qualifier, then the object type must be defined with
NOT INSTANTIABLE and
NOT FINAL following the closing parenthesis of the object type specification.
method_spec, specifies whether the object type definition provides an implementation for the method.
CREATE TYPE BODY command for the object type provides the implementation of the method) is the default. If you specify
NOT INSTANTIABLE, then the
CREATE TYPE BODY command for the object type can't contain the implementation of the method.
If you spcify
method_spec overrides an identically named method with the same number of identically named method arguments with the same data types, in the same order, and the same return type (if the method is a function) as defined in a supertype.
MEMBER if the subprogram operates on an object instance. Specify
STATIC if the subprogram operates independently of any particular object instance.
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 that of the object type being defined.
SELF refers to the object instance that's currently invoking the method. You can explicitly declare
SELF as an
IN OUT parameter in the parameter list. If explicitly declared,
SELF must be the first parameter in the parameter list. If you don't explicitly declare
SELF, its parameter mode defaults to
IN OUT for member procedures and
IN for member functions.
The name of an argument. The argument is referenced by this name in the method body.
The data types of the method’s arguments. The argument types can be a base data type or a user-defined type such as a nested table or an object type. Don't specify a length for any base type. For example, specify
Supplies a default value for an input argument if you don't supply one in the method call. You can't specify
DEFAULT for arguments with modes
IN OUT or
The name of the function to create.
The return data type, which can be any of the types listed for
argtype. Don't specify a length for
The maximum number of elements permitted in the varray.
Create object type
Create object type
emp_obj_typ that includes a member method
Create object type
dept_obj_typ that includes a static method
Create a nested table type
budget_tbl_typ of data type
This example uses a composite type accessed from an anonymous block.
This example creates the composite type:
This code shows the anonymous block that accesses the composite type:
This example uses a composite type accessed from a user-defined record type declared in a package body.
The following creates the composite type:
The following defines the package specification:
The following defines the package body:
In the declaration of the
TYPE emprec_typ IS RECORD data structure in the package body, the
salhist field is defined with the
SALHIST_TYP composite type created by the
CREATE TYPE salhist_typ statement.
The associative array definition
TYPE emp_arr_typ IS TABLE OF emprec_typ references the record type data structure
emprec_typ that includes the field
salhist defined with the
SALHIST_TYP composite type.
This example invokes the package procedure that loads the array from a join of the
jobhist tables and then displays the array content:
For packages only, you can include a composite type in a user-defined record type declared with the
TYPE IS RECORD statement in the package specification or package body. This nested structure isn't permitted in other SPL programs such as functions, procedures, and triggers.
CREATE TYPE command, if you include a schema name, then the type is created in the specified schema. Otherwise it's created in the current schema. The name of the new type can't match any existing type in the same schema unless you want to update the definition of an existing type. In that case, use
CREATE OR REPLACE TYPE.
You can't use the
OR REPLACE option to add, delete, or modify the attributes of an existing object type. Use the
DROP TYPE command to first delete the existing object type. You can use the
OR REPLACE option to add, delete, or modify the methods in an existing object type.
You can use the PostgreSQL form of the
ALTER TYPE ALTER ATTRIBUTE command to change the data type of an attribute in an existing object type. However, the
ALTER TYPE command can't add or delete attributes in the object type.
The user that creates the type becomes the owner of the type.