Data Structures   «Prev  Next»
Lesson 3Abstract data types
ObjectiveCreate an abstract data type.

Oracle Abstract Data Types

Oracle was not the first RDBMS to introduce abstract data types (ADTs) for a relational database. While Oracle was a significant early adopter and popularizer of ADTs, here's a timeline of earlier developments:
Early Pioneers:
  • Postgres (1986): Incorporated user-defined types (UDTs) in its initial release, paving the way for ADTs in other systems.
  • INGRES (early 1990s): Introduced a form of ADTs, allowing users to create custom data types with associated functions.

Oracle's Adoption:
  • Oracle Database 8 (1997): Introduced ADTs more prominently, expanding their capabilities and making them more accessible to developers.
  • Oracle's Influence: Played a key role in popularizing ADTs in the relational database world, demonstrating their potential for modeling complex data structures.

Other Systems:
  • IBM DB2 (1999): Added support for ADTs in its Version 7 release.
  • Microsoft SQL Server (2005): Introduced ADTs in its SQL Server 2005 version.
Key Points:
  • ADTs allow users to create custom data types tailored to specific application needs, enhancing data modeling capabilities.
  • They often include associated functions (methods) for data manipulation and behavior, extending the database's functionality.
  • By encapsulating data and behavior together, ADTs promote code reusability and maintainability.
  • Oracle's early adoption and significant contributions helped shape the evolution of ADTs in relational databases.

Limitations of Oracle 7

One of the shortcomings of the Oracle 7 database was the limited number of intrinsic data types.
A column in an Oracle 7 table could be CHAR, VARCHAR, INT, or NUMBER data types, but there was no way to extend the data types which existed in a table. Another limitation of Oracle 7 was that all entities had to be modeled at their smallest level. For example, to select all of the address information from a table, we have to specify all of the columns in the group.
To illustrate, consider a customer table:

Consider the customer table above. Many individual columns in an SQL query are listed
1) 
CREATE TABLE customer
(
    customer_first_name CHAR(40),
    customer_last_name CHAR(40),
    customer_street_address CHAR(40),
    customer_city CHAR(40),
    customer_state CHAR(2),
    customer_zip_code NUMBER(9)
);
Consider the customer table above. Many individual columns in an SQL query are listed

In Oracle, these data columns can be grouped together by data type
2) 
CREATE TYPE name_type
(
    first_name CHAR(40),
    last_name CHAR(40)
);

CREATE TYPE full_address_type
(
    street_address CHAR(40),
    city CHAR(40),
    state CHAR(2),
    zip_code NUMBER(9)
);

In Oracle, these data columns can be grouped together by data type

This script creates two user-defined types: `name_type` for storing first and last names, and `full_address_type` for storing address details including street address, city, state, and zip code.
3) 
CREATE TYPE name_type
(
    first_name CHAR(40),
    last_name CHAR(40)
);

CREATE TYPE full_address_type
(
    street_address CHAR(40),
    city CHAR(40),
    state CHAR(2),
    zip_code NUMBER(9)
);

This script creates two user-defined types: `name_type` for storing first and last names, and `full_address_type` for storing address details including street address, city, state, and zip code.

This script creates a table named `customer` with two columns:
4) 
CREATE TABLE customer
(
    cust_name name_type,
    cust_address full_address_type
);

This script creates a table named `customer` with two columns: `cust_name` of the `name_type` user-defined type and `cust_address` of the `full_address_type` user-defined type.

In the code above, we have used the name_type and full_addrress type just as if they were an intrinsic Oracle data type such as CHAR or INT.
5) 
CREATE TYPE name_type
(
    first_name CHAR(40),
    last_name CHAR(40)
);

CREATE TYPE full_address_type
(
    street_address CHAR(40),
    city CHAR(40),
    state CHAR(2),
    zip_code NUMBER(9)
);

CREATE TABLE customer
(
    cust_name name_type,
    cust_address full_address_type
);

This script defines two user-defined types: name_type and full_address_type. It then creates a customer table that uses these types for the cust_name and cust_address columns.

Now that the Oracle table is defined, we can reference cust_address in our SQL just as if it were a primitive data type
6) 
CREATE TYPE name_type
(
    first_name CHAR(40),
    last_name CHAR(40)
);

CREATE TYPE full_address_type
(
    street_address CHAR(40),
    city CHAR(40),
    state CHAR(2),
    zip_code NUMBER(9)
);

CREATE TABLE customer
(
    cust_name name_type,
    cust_address full_address_type
);

SELECT cust_name, cust_address FROM customer;

This script defines two user-defined types, `name_type` and `full_address_type`, creates a `customer` table using these types, and includes a `SELECT` statement to retrieve the `cust_name` and `cust_address` columns from the `customer` table.

    Oracle DBA Cloud

    Advantage to Grouping Values with Abastract Data Types

    The option to group values is an advantage, because the many individual columns in an SQL query no longer have to be listed. Creating Abstract Data Types (ADTs) also allows us to embed new constructs such as object ID (OIDs) and VARRAYs in table columns. Embedded constructs have a huge impact on Oracle performance, since expensive JOINS can be avoided.
    An Abstract Data Type (ADT) consists of a data structure and subprograms that manipulate the data. The variables that form the data structure are called attributes. The subprograms that manipulate the attributes are called methods. ADTs are stored in the database and instances of ADTs can be stored in tables and used as PL/SQL variables. ADTs let you reduce complexity by separating a large system into logical components, which you can reuse.
    • In the static data dictionary view *_OBJECTS, the OBJECT_TYPE of an ADT is TYPE.
    • In the static data dictionary view *_TYPES, the TYPECODE of an ADT is OBJECT.

    CREATE TYPE Statement

    The CREATE TYPE statement creates or replaces the specification of one of these:
    1. Abstract Data Type (ADT) (including a SQLJ object type)
    2. Standalone stored varying array (varray) type
    3. Standalone stored nested table type
    4. Incomplete object type

    An incomplete type is a type created by a forward type definition. It is called incomplete because it has a name but no attributes or methods. It can be referenced by other types, allowing you define types that refer to each other. However, you must fully specify the type before you can use it to create a table or an object column or a column of a nested table type. The CREATE TYPE statement specifies the name of the type and its attributes, methods, and other properties. The CREATE TYPE BODY statement contains the code for the methods that implement the type.
    Notes:
    1. If you create a type whose specification declares only attributes but no methods, then you need not specify a type body.
    2. If you create a SQLJ object type, then you cannot specify a type body. The implementation of the type is specified as a Java class.
    3. A standalone stored type that you create with the CREATE TYPE statement differs from a type that you define in a PL/SQL block or package.
    4. With the CREATE TYPE statement, you can create nested table and VARRAY types, but not associative arrays. In a PL/SQL block or package, you can define all three collection types.

      5. The next lesson shows how to imbed an OID in a table.

    ADT - Exercise

    Before you continue, click the Exercise link below to try creating an ADT.
    ADT - Exercise

    SEMrush Software 3 SEMrush Banner 3