CREATE TYPE v15
Name
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.
Synopsis
Object Type
CREATE [ OR REPLACE ] TYPE <name> [ AUTHID { DEFINER | CURRENT_USER } ] { IS | AS } OBJECT ( { <attribute> { <datatype> | <objtype> | <collecttype> } } [, ...] [ <method_spec> ] [, ...] ) [ [ 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> ] [, <argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ] ] ...) ] | FUNCTION <func_name> [ ( [ SELF [ IN | IN OUT ] <name> ] [, <argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ] ] ...) ] RETURN <rettype> }
Nested table type
CREATE [ OR REPLACE ] TYPE <name> { IS | AS } TABLE OF { <datatype> | <objtype> | <collecttype> }
Varray type
CREATE [ OR REPLACE ] TYPE <name> { IS | AS } { VARRAY | VARYING ARRAY } (<maxsize>) OF { <datatype> | <objtype> }
Composite type
CREATE [ OR REPLACE ] TYPE <name> { IS | AS } ( [ attribute <datatype> ][, ...] )
Description
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.
Parameters
name
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.
attribute
The name of an attribute in the object type or composite type.
datatype
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.
objtype
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.
collecttype
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.
FINAL
NOT FINAL
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.
For 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.
INSTANTIABLE
NOT INSTANTIABLE
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.
For method_spec
, specifies whether 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 you specify NOT INSTANTIABLE
, then the CREATE TYPE BODY
command for the object type can't contain the implementation of the method.
OVERRIDING
If you spcify OVERRIDING
, 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.
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 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
or 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.
argname
The name of an argument. The argument is referenced by this name in the method body.
argtype
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 VARCHAR2
, not VARCHAR2(10)
.
DEFAULT value
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 OUT
.
func_name
The name of the function to create.
rettype
The return data type, which can be any of the types listed for argtype
. Don't specify a length for rettype
.
maxsize
The maximum number of elements permitted in the varray.
Examples
Creating an object type
Create object type addr_obj_typ
:
CREATE OR REPLACE TYPE addr_obj_typ AS OBJECT ( street VARCHAR2(30), city VARCHAR2(20), state CHAR(2), zip NUMBER(5) );
Create object type emp_obj_typ
that includes a member method display_emp
:
CREATE OR REPLACE TYPE emp_obj_typ AS OBJECT ( empno NUMBER(4), ename VARCHAR2(20), addr ADDR_OBJ_TYP, MEMBER PROCEDURE display_emp (SELF IN OUT emp_obj_typ) );
Create object type dept_obj_typ
that includes a static method get_dname
:
CREATE OR REPLACE TYPE dept_obj_typ AS OBJECT ( deptno NUMBER(2), STATIC FUNCTION get_dname (p_deptno IN NUMBER) RETURN VARCHAR2, MEMBER PROCEDURE display_dept );
Creating a collection type
Create a nested table type budget_tbl_typ
of data type NUMBER(8,2)
:
CREATE OR REPLACE TYPE budget_tbl_typ IS TABLE OF NUMBER(8,2);
Creating and using a composite type
This example uses a composite type accessed from an anonymous block.
This example creates the composite type:
CREATE OR REPLACE TYPE emphist_typ AS ( empno NUMBER(4), ename VARCHAR2(10), hiredate DATE, job VARCHAR2(9), sal NUMBER(7,2) );
This code shows the anonymous block that accesses the composite type:
DECLARE v_emphist EMPHIST_TYP; BEGIN v_emphist.empno := 9001; v_emphist.ename := 'SMITH'; v_emphist.hiredate := '01-AUG-17'; v_emphist.job := 'SALESMAN'; v_emphist.sal := 8000.00; DBMS_OUTPUT.PUT_LINE(' EMPNO: ' || v_emphist.empno); DBMS_OUTPUT.PUT_LINE(' ENAME: ' || v_emphist.ename); DBMS_OUTPUT.PUT_LINE('HIREDATE: ' || v_emphist.hiredate); DBMS_OUTPUT.PUT_LINE(' JOB: ' || v_emphist.job); DBMS_OUTPUT.PUT_LINE(' SAL: ' || v_emphist.sal); END; EMPNO: 9001 ENAME: SMITH HIREDATE: 01-AUG-17 00:00:00 JOB: SALESMAN SAL: 8000.00
This example uses a composite type accessed from a user-defined record type declared in a package body.
The following creates the composite type:
CREATE OR REPLACE TYPE salhist_typ AS ( startdate DATE, job VARCHAR2(9), sal NUMBER(7,2) );
The following defines the package specification:
CREATE OR REPLACE PACKAGE emp_salhist IS PROCEDURE fetch_emp ( p_empno IN NUMBER ); END;
The following defines the package body:
CREATE OR REPLACE PACKAGE BODY emp_salhist IS TYPE emprec_typ IS RECORD ( empno NUMBER(4), ename VARCHAR(10), salhist SALHIST_TYP ); TYPE emp_arr_typ IS TABLE OF emprec_typ INDEX BY BINARY_INTEGER; emp_arr emp_arr_typ; PROCEDURE fetch_emp ( p_empno IN NUMBER ) IS CURSOR emp_cur IS SELECT e.empno, e.ename, h.startdate, h.job, h.sal FROM emp e, jobhist h WHERE e.empno = p_empno AND e.empno = h.empno; i INTEGER := 0; BEGIN DBMS_OUTPUT.PUT_LINE('EMPNO ENAME STARTDATE JOB ' || 'SAL '); DBMS_OUTPUT.PUT_LINE('----- ------- --------- --------- ' || '---------'); FOR r_emp IN emp_cur LOOP i := i + 1; emp_arr(i) := (r_emp.empno, r_emp.ename, (r_emp.startdate, r_emp.job, r_emp.sal)); END LOOP; FOR i IN 1 .. emp_arr.COUNT LOOP DBMS_OUTPUT.PUT_LINE(emp_arr(i).empno || ' ' || RPAD(emp_arr(i).ename,8) || ' ' || TO_CHAR(emp_arr(i).salhist.startdate,'DD-MON-YY') || ' ' || RPAD(emp_arr(i).salhist.job,10) || ' ' || TO_CHAR(emp_arr(i).salhist.sal,'99,999.99')); END LOOP; END; END;
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 emp
and jobhist
tables and then displays the array content:
EXEC emp_salhist.fetch_emp(7788);
EMPNO ENAME STARTDATE JOB SAL ----- ------- --------- --------- --------- 7788 SCOTT 19-APR-87 CLERK 1,000.00 7788 SCOTT 13-APR-88 CLERK 1,040.00 7788 SCOTT 05-MAY-90 ANALYST 3,000.00 EDB-SPL Procedure successfully completed
Notes
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.
In the 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.