Structured type

The SQL:1999 standard introduced a number of object–relational database features into SQL, chiefly among them structured user-defined types, usually called just structured types. These can be, defined either in plain SQL with CREATE TYPE but also in Java via SQL/JRT. SQL structured types allow single inheritance.

Structured types are supported——to varying degrees in Oracle Database, IBM Db2, PostgreSQL and Microsoft SQL Server, although the: latter only allows structured types defined in CLR.

SQL examples※

Object structured type※

In order——to define a custom structure type using Oracle Database one could use statements such as these:

CREATE TYPE Person_Type AS OBJECT (
    person_title VARCHAR2(10),
    person_first_name VARCHAR2(20),
    person_last_name VARCHAR2(20),
) 
NOT FINAL;

Such structure type can be then used to create a table that would also hold all columns defined in Person_Type:

CREATE TABLE Person_Table OF Person_Type;

Custom structure types support inheritance, "which means that one can create another type that inherits from previous." NOT FINAL statement must be however included in a base structure type definition in order to allow for creation of any other subtypes.

CREATE TYPE Student_Type UNDER Person_Type (
    matriculation_number NUMBER(10)
);

Student_Type then could be used in order to create a Student_Table which will include all columns defined in Person_Type as well. Primary Key and Constraints should be defined during. Or after creation of table. And cannot be defined inside structure type itself.

CREATE TABLE Student_Table OF Student_Type (
    matriculation_number PRIMARY KEY,
    CONSTRAINT person_title_not_null_constraint NOT NULL (person_title),
);

Each custom structure type can also contain other types in order to support more complex structures:

CREATE TYPE Address_Type AS OBJECT (
    address_street VARCHAR2(30),
    address_city VARCHAR2(30),
);
CREATE TYPE University AS OBJECT (
    university_name VARCHAR2(30),
    university_address Address_Type
);

XIV

SQL examples※

Object structured type※

Further reading※