There are two categories of user-defined datatypes:
- Collection types
- Object types
User-defined datatypes use the built-in datatypes and other user-defined datatypes as the building blocks for datatypes that model the structure and behavior of data in applications.
Collection Types
Each collection type describes a data unit made up of an indefinite number of elements, all of the same datatype. The collection types are array types and table types.
Array types and table types are schema objects. The corresponding data units are called VARRAYs and nested tables. When there is no danger of confusion, we often refer to the collection types as VARRAYs and nested tables.
Collection types have constructor methods. The name of the constructor method is the name of the type, and its argument is a comma-separated list of the new collection's elements. The constructor method is a function. It returns the new collection as its value.
An expression consisting of the type name followed by empty parentheses represents a call to the constructor method to create an empty collection of that type. An empty collection is different from a null collection.
VARRAYs
An array is an ordered set of data elements. All elements of a given array are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the array.
The number of elements in an array is the size of the array. Oracle allows arrays to be of variable size, which is why they are called VARRAYs. You must specify a maximum size when you declare the array type.
For example, the following statement declares an array type:
CREATE TYPE prices AS VARRAY(10) OF NUMBER(12,2);
The VARRAYs of type PRICES have no more than 10 elements, each of datatype NUMBER(12,2).
Creating an array type does not allocate space. It defines a datatype, which you can use as:
- The datatype of a column of a relational table
- An object type attribute
- A PL/SQL variable, parameter, or function return type.
A VARRAY is normally stored in line; that is, in the same tablespace as the other data in its row. If it is sufficiently large, however, Oracle stores it as a BLOB.
Nested Tables
A nested table is an unordered set of data elements, all of the same datatype. It has a single column, and the type of that column is a built-in type or an object type. If an object type, the table can also be viewed as a multicolumn table, with a column for each attribute of the object type. If compatibility is set to Oracle9i, Release 1 (9.0.1) or higher, nested tables can contain other nested tables.
For example, in the purchase order example, the following statement declares the table type used for the nested tables of line items:
CREATE TYPE lineitem_table AS TABLE OF lineitem;
A table type definition does not allocate space. It defines a type, which you can use as:
- The datatype of a column of a relational table
- An object type attribute
- A PL/SQL variable, parameter, or function return type
When a table type appears as the type of a column in a relational table or as an attribute of the underlying object type of an object table, Oracle stores all of the nested table data in a single table, which it associates with the enclosing relational or object table. For example, the following statement defines an object table for the object type PURCHASE_ORDER:
CREATE TABLE purchase_order_table OF purchase_order
NESTED TABLE lineitems STORE AS lineitems_table;
The second line specifies LINEITEMS_TABLE as the storage table for the LINEITEMS attributes of all of the PURCHASE_ORDER objects in PURCHASE_ORDER_TABLE.
A convenient way to access the elements of a nested table individually is to use a nested cursor.
Object Types
Object types are abstractions of the real-world entities--for example, purchase orders--that application programs deal with. An object type is a schema object with three kinds of components:
- A name, which serves to identify the object type uniquely within that schema
- Attributes, which model the structure and state of the real-world entity. Attributes are built-in types or other user-defined types.
- Methods, which are functions or procedures written in PL/SQL or Java and stored in the database, or written in a language such as C and stored externally. Methods implement operations the application can perform on the real-world entity.
An object type is a template. A structured data unit that matches the template is called an object.
Here is an example of how you can define object types called EXTERNAL_PERSON, LINEITEM, and PURCHASE_ORDER.
The object types EXTERNAL_PERSON and LINEITEM have attributes of built-in types. The object type PURCHASE_ORDER has a more complex structure, which closely matches the structure of real purchase orders.
The attributes of PURCHASE_ORDER are ID, CONTACT, and LINEITEMS. The attribute CONTACT is an object, and the attribute LINEITEMS is a nested table.
CREATE TYPE external_person AS OBJECT (
name VARCHAR2(30),
phone VARCHAR2(20) );
CREATE TYPE lineitem AS OBJECT (
item_name VARCHAR2(30),
quantity NUMBER,
unit_price NUMBER(12,2) );
CREATE TYPE lineitem_table AS TABLE OF lineitem;
CREATE TYPE purchase_order AS OBJECT (
id NUMBER,
contact external_person,
lineitems lineitem_table,
MEMBER FUNCTION
get_value RETURN NUMBER );
Type Inheritance
An object type can be created as a subtype of an existing object type. A single inheritance model is supported: the subtype can be derived from only one parent type. A type inherits all the attributes and methods of its direct supertype. It can add new attributes and methods, and it can override any of the inherited methods.
The following figure illustrates two subtypes, Student_t and Employee_t, created under Person_t.
Furthermore, a subtype can itself be refined by defining another subtype under it, thus building up type hierarchies. In the diagram above, PartTimeStudent_t is derived from subtype Student_t.
FINAL and NOT FINAL Types
A type declaration must have the NOT FINAL keyword, if you want it to have subtypes. The default is that the type is FINAL; that is, no subtypes can be created for the type. This allows for backward compatibility.
CREATE TYPE Person_t AS OBJECT
( ssn NUMBER,
name VARCHAR2(30),
address VARCHAR2(100)) NOT FINAL;
Person_t is declared to be a NOT FINAL type. This enables definition of subtypes of Person_t.
FINAL types can be altered to be NOT FINAL. In addition, NOT FINAL types with no subtypes can be altered to be FINAL.
NOT INSTANTIABLE Types and Methods
A type can be declared to be NOT INSTANTIABLE. This implies that there is no constructor (default or user-defined) for the type. Thus, it will not be possible to construct instances of this type. The typical usage would be define instantiable subtypes for such a type, as follows:
CREATE TYPE Address_t AS OBJECT(...) NOT INSTANTIABLE NOT FINAL;
CREATE TYPE USAddress_t UNDER Address_t(...);
CREATE TYPE IntlAddress_t UNDER Address_t(...);
A method of a type can be declared to be NOT INSTANTIABLE. Declaring a method as NOT INSTANTIABLE means that the type is not providing an implementation for that method. Furthermore, a type that contains any non-instantiable methods must necessarily be declared NOT INSTANTIABLE.
For example:
CREATE TYPE T AS OBJECT
(
x NUMBER,
NOT INSTANTIABLE MEMBER FUNCTION func1() RETURN NUMBER
) NOT INSTANTIABLE;
A subtype of a NOT INSTANTIABLE type can override any of the non-instantiable methods of the supertype and provide concrete implementations. If there are any non-instantiable methods remaining, the subtype must also necessarily be declared NOT INSTANTIABLE.
A non-instantiable subtype can be defined under an instantiable supertype. Declaring a non-instantiable type to be FINAL is not allowed.