3.3.32 CREATE TYPE

Table of Contents Previous Next


3 The SQL Language : 3.3 SQL Commands : 3.3.32 CREATE TYPE

CREATE TYPE -- define a new user-defined type
( { attribute { datatype | objtype | collecttype } }
[ method_spec ] [, ...]
where method_spec is:
subprogram_spec
and subprogram_spec is:
{ PROCEDURE proc_name
[, argname [ IN | IN OUT | OUT ] argtype
FUNCTION func_name
[, argname [ IN | IN OUT | OUT ] argtype
RETURN rettype
CREATE [ OR REPLACE ] TYPE name { IS | AS } TABLE OF
{ datatype | objtype | collecttype }
{ VARRAY | VARYING ARRAY } (maxsize) OF { datatype | objtype }
CREATE TYPE defines a new user-defined data type. The types that can be created are an object type, a nested table type, or a varray type. (Nested table and varray types belong to the category of types known as collections. See Section 4.10 for information on collections.) CREATE OR REPLACE TYPE will either create a new type definition, or replace an existing type definition.
Note: The OR REPLACE option cannot be currently used to add, delete, or modify the attributes of an existing object type. Use the DROP TYPE command to first delete the existing object type. The OR REPLACE option can be used to add, delete, or modify the methods in an existing object type.
Note: The PostgreSQL form of the ALTER TYPE ALTER ATTRIBUTE command can be used to change the data type of an attribute in an existing object type. However, the ALTER TYPE command cannot add or delete attributes in the object type.
See Section 4.10.2 for more information on nested table types. See Section 4.10.3 for more information on varray types. See Section 8 for more information on object types.
Specifies whether the privileges of the object type owner (DEFINER) or the privileges of the current user executing a method in the object type (CURRENT_USER) are to be used to determine whether or not access is allowed to database objects referenced in the object type. DEFINER is the default.
FINAL
NOT FINAL
For an object type, specifies whether or not a subtype can be derived from the object type. FINAL (subtype cannot be derived from the object type) is the default.
For method_spec, specifies whether or not the method may be overridden in a subtype. NOT FINAL (method may be overridden in a subtype) is the default.
INSTANTIABLE
NOT INSTANTIABLE
For an object type, specifies whether or not an object instance can be created of this object type. INSTANTIABLE (an instance of this object type can be created) is the default. If NOT INSTANTIABLE is specified, then NOT FINAL must be specified as well. If method_spec for any method in the object type contains the NOT INSTANTIABLE qualifier, then the object type, itself, must be defined with NOT INSTANTIABLE and NOT FINAL following the closing parenthesis of the object type specification.
For method_spec, specifies whether or not the object type definition provides an implementation for the method. INSTANTIABLE (the CREATE TYPE BODY command for the object type provides the implementation of the method) is the default. If NOT INSTANTIABLE is specified, then the CREATE TYPE BODY command for the object type must not contain the implementation of the method.
If OVERRIDING is specified, 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
STATIC
Specify MEMBER if the subprogram operates on an object instance. Specify STATIC if the subprogram operates independently of any particular object instance.
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 is currently invoking the method. SELF can be explicitly declared as an IN or IN OUT parameter in the parameter list. If explicitly declared, SELF 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 IN for member functions.
Supplies a default value for an input argument if one is not supplied in the method call. DEFAULT may not be specified for arguments with modes IN OUT or OUT.
The return data type, which may be any of the types listed for argtype. As for argtype, a length must not be specified for rettype.
Create object type addr_obj_typ.
Create object type emp_obj_typ that includes a member method display_emp.
Create object type dept_obj_typ that includes a static method get_dname.
Create a nested table type, budget_tbl_typ, of data type, NUMBER(8,2).

3 The SQL Language : 3.3 SQL Commands : 3.3.32 CREATE TYPE

Table of Contents Previous Next