Structured type

<h2 id="sql-examples">SQL examples</h2>
<h3>Object structured type</h3><p>
In order to define a custom structure type using <a href="/facts/Oracle_Database/FHv5g732">Oracle Database</a> one could use statements such as these:</p>CREATE TYPE Person_Type AS OBJECT (
    person_title VARCHAR2(10),
    person_first_name VARCHAR2(20),
    person_last_name VARCHAR2(20),
) 
NOT FINAL;
<p>Such structure type can be then used to create a table that would also hold all columns defined in <i>Person_Type</i>:</p>CREATE TABLE Person_Table OF Person_Type;
<p>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.</p>CREATE TYPE Student_Type UNDER Person_Type (
    matriculation_number NUMBER(10)
);
<p><i>Student_Type</i> then could be used in order to create a <i>Student_Table</i> which will include all columns defined in <i>Person_Type</i> as well. <a href="/facts/Unique_key/NKGBxUHR">Primary Key</a> and <a href="/facts/Database_constraint/pEKGmJLy">Constraints</a> should be defined during or after creation of table and cannot be defined inside structure type itself.</p>CREATE TABLE Student_Table OF Student_Type (
    matriculation_number PRIMARY KEY,
    CONSTRAINT person_title_not_null_constraint NOT NULL (person_title),
);
<p>Each custom structure type can also contain other types in order to support more complex structures:</p>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
);

<h2 id="further-reading">Further reading</h2>
<ul><li>Jim Melton (2003). <i>Advanced SQL: 1999</i>. Morgan Kaufmann. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 978-1-55860-677-7. Chapters 2-4.</li>
<li>Suzanne W. Dietrich; Susan D. Urban (2011). <i>Fundamentals of Object Databases: Object-Oriented and Object-Relational Design</i>. Morgan & Claypool Publishers. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 978-1-60845-476-1. Chapter 3.</li>
<li>Catherine Ricardo (2011). <a href="https://archive.org/details/databasesillumin0000rica_2ed"><i>Databases Illuminated</i></a> (2nd ed.). Jones & Bartlett Publishers. <a href="/facts/ISBN_(identifier)/15AdSPa9">ISBN</a> 978-1-4496-0600-8. Chapter 8.</li></ul>

Structured type open-in-new

Structured type