Creates a user-defined type, which can be an object type, a collection type (a nested table type or a varray type), or a composite type.
Syntax
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
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
The CREATE TYPE command creates a user-defined data type. The types that can be created include object type, nested table type, varray type, and composite type. The nested table type and varray type belong to the collection type.
Composite types are incompatible with Oracle databases. However, composite types can be accessed through SPL programs, which is the same as other types described in this topic.
If you specify a Schema name in the CREATE TYPE command, the type is created in the specified Schema. Otherwise, the type is created in the current Schema. The name of a new type must be different from an existing type in the same Schema. If you want to update the definition of an existing type, you can use the CREATE OR REPLACE TYPE command.
- The OR REPLACE option cannot be used to add, delete, or modify the attributes of an existing object type. However, you can use the DROP TYPE command to delete the existing object type. 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 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.
The user that creates the type is the owner of the type.
Parameters
Parameter | Description |
---|---|
name | The name of the type to be created. The name can be Schema-qualified. |
DEFINER | CURRENT_USER | Specifies the privileges that are used to determine whether access is allowed to database objects referenced in the object type. DEFINER indicates the privileges of the object type owner. CURRENT_USER indicates the privileges of the current user executing a method in the object type. The default value is DEFINER. |
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 is 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 is 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 is being created. |
FINAL | NOT FINAL |
|
INSTANTIABLE | NOT INSTANTIABLE |
|
OVERRIDING | If you specify OVERRIDING, method_spec overrides an identically named method with the same number of identically named method arguments. The arguments have the same data types, the same order, and the same return type (if the method is a function) as defined in a supertype. |
MEMBER | STATIC | If the subprogram runs on an object instance, specify MEMBER. If the subprogram runs independently of a particular object instance, specify STATIC. |
proc_name | The name of the stored procedure to be created. |
SELF [ IN | IN OUT ] name | For a member method, an implicit built-in parameter named SELF is available. The data type of this parameter is the data type of the object type being created. SELF references the object instance that is calling 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 stored 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 arguments. The argument types can be a base data type or a user-defined type such as a nested table type or an object type. You cannot specify the length of a base data type. For example, you can specify VARCHAR2 rather than VARCHAR2(10). |
DEFAULT value | If no default value is specified in the method call, this parameter specifies a default value for an input argument. DEFAULT may not be specified for arguments with the IN OUT or OUT mode. |
func_name | The name of the function to be created. |
rettype | The return data type, which can be one of the types listed for the argtype parameter. For argtype, you cannot specify a length for rettype. |
maxsize | The maximum number of elements in the varray. |
Examples
-
Create an object type
Create an object type named 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 an object type named emp_obj_typ that contains 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 an object type named dept_obj_typ that contains 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 named budget_tbl_typ of data type NUMBER(8,2).
CREATE OR REPLACE TYPE budget_tbl_typ IS TABLE OF NUMBER(8,2);
-
Create and use a composite type
The following example shows how to access a composite type from an anonymous block.
The composite type is created as follows:
CREATE OR REPLACE TYPE emphist_typ AS ( empno NUMBER(4), ename VARCHAR2(10), hiredate DATE, job VARCHAR2(9), sal NUMBER(7,2) );
The following example 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
The following example shows how to access a composite type from a user-defined record type that is declared in a package body.
The composite type is created as follows:
CREATE OR REPLACE TYPE salhist_typ AS ( startdate DATE, job VARCHAR2(9), sal NUMBER(7,2) );
The package specification is defined as follows:
CREATE OR REPLACE PACKAGE emp_salhist IS PROCEDURE fetch_emp ( p_empno IN NUMBER ); END;
The package body is defined as follows:
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;
Note that in the declaration of the TYPE emprec_typ IS RECORD data structure in the package body, the salhist field is of the SALHIST_TYP composite type that is 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. The data structure includes the salhist field of the SALHIST_TYP composite type.
The following example shows how to call the package stored procedure that loads the array from a join of the emp and jobhist tables and 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 polar-spl Procedure successfully completed