Loading article…
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.
In order to define a custom structure type using Oracle Database one could use statements such as these:
CREATETYPEPerson_TypeASOBJECT(person_titleVARCHAR2(10),person_first_nameVARCHAR2(20),person_last_nameVARCHAR2(20),)NOTFINAL;Such structure type can be then used to create a table that would also hold all columns defined in Person_Type:
CREATETABLEPerson_TableOFPerson_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.
CREATETYPEStudent_TypeUNDERPerson_Type(matriculation_numberNUMBER(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.
CREATETABLEStudent_TableOFStudent_Type(matriculation_numberPRIMARYKEY,CONSTRAINTperson_title_not_null_constraintNOTNULL(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 );