Define Structured Types / Overview of Structured Types / What is Abstract Datatype? / How to define Structured types or Abstract datatypes? / Structured type (Abstract datatype) definition in Oracle with example.
The major advantage of using objects is the ability to define new data types (Abstract Data Types). In ORDBMS, the RDBMS extends the usage of objects that can be defined and stored as part of database. Like a CLASS declaration in C++ language, a new type can be defined in an ORDBMS as follows; (the reserved words/keywords are given in UPPERCASE hereafter)
CREATE TYPE type_name AS
Here, data_type can be any of the following;
- It can be one of the valid data types like CHAR, VARCHAR, NUMBER, INTEGER, etc. Or
- It can be another User Defined Type.
We call this kind of new User Defined Types as Structured Types / Abstract Datatypes.
For example, Structured types can be declared and used in SQL:1999 as follows;
CREATE TYPE phone AS
This type can be used in other TYPE definition or TABLE definition as follows;
CREATE TABLE contact
In this TABLE definition, PHONE is the structured type that we have defined through previous example.
Structured Types in Oracle
Let us see some examples of defining and manipulating Structured types in Oracle.
CREATE TYPE Address AS OBJECT
Execution of the above statement will create a new ABSTRACT datatype named ADDRESS and store the definition as part of the database.
This new type can be used to define an attribute in any TABLEs or TYPEs as follows;
CREATE TABLE Person
This table Person will consist of 3 columns where the first one and the third one are of regular datatypes VARCHAR, and NUMBER respectively, and the second one is of the abstract type ADDRESS. The table PERSON will look like as follows;
Table 1 – Person table