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.
Structured
Types
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
(Attribute1_name
data_type(size),
Attribute2_name
data_type(size),
Attribute3_name data_type(size),
…….
AttributeN_name
data+_type(size));
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
(Country_code
NUMBER(4),
STD_Code NUMBER(5),
Phone_Number
NUMBER(10))
This type can be used in other TYPE definition
or TABLE definition as follows;
CREATE TABLE
contact
(Contact_name VARCHAR(25),
Street VARCHAR(25),
City VARCHAR(25),
Ph PHONE);
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
(Street VARCHAR(35),
City VARCHAR(30),
State VARCHAR(30),
Pincode NUMBER(10));
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
(Person_name VARCHAR(25),
Addr ADDRESS,
Phone NUMBER(10));
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;
Person_name
|
Addr
|
Phone
|
Street
|
City
|
State
|
Pincode
|
|
|
|
|
|
|
Table 1 – Person table
