Oracle 9i: PL/SQL Collections

Introduction

Version 8.0 and higher versions of Oracle are referred to as ORDBMS (Object-Relational Database Management System). The traditional Oracle database management system is extended to include Object-Oriented Concepts and structures such as abstract data types, nested tables, varying arrays, object views and references.

Abstract Datatypes

Also called user-defined datatypes are those that consist one or more subtypes. Apart from using standard oracle datatypes they can also use other abstract datatypes. An abstract datatype created can be reused like other standard datatypes.

Creating Abstract Datatypes

The following screenshots and other code fragments explain about how to create abstract datatypes. Also I explained about how to create tables using abstract datatypes.

SQL > DESC CUSTOMER

Name Null? Type
----------------------------------------- -------- -----------------
CUSTID NUMBER (4)
CUSTNAME VARCHAR2 (20)
ADDRESS CUST_ADDRESS_TY

From the above example it is clear that simple DESC command will not give us any detail about Abstract Data Type. To display the details about an abstract datatype use the following syntax.

SQL > SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS
2 WHERE TABLE_NAME = 'CUSTOMER';

COLUMN_NAME DATA_TYPE
---------------------------------------------------------------
CUSTID NUMBER
CUSTNAME VARCHAR2
ADDRESS CUST_ADDRESS_TY

Inserting Data into tables with Abstract Data Type

For Placing records into a table containing Abstract Data Types, we use a special method called constructor method. A constructor method is created by Oracle when you create an abstract datatype.

Example:

Displaying Records from the table

PL/SQL Collections:

A collection is a data structure that acts like an array. A collection is defined as an ordered group of elements, all of the same type. Individual element in a collection can be accessed by using index like an array in C. There are three types of collections in oracle

Nested Tables

A nested table is defined as table within another table. They can also be called one-column database tables. A nested table is collection of rows, represented as column within the table; nested table may contain many rows. Using nested tables we can store one-to-many relationships within one table. You can have multiple rows in the nested table for each row in the main table A nested table is like one-dimensional array but it has the following differences. Size of an array is fixed but nested table can grow to any size. The subscript of an Array is consecutive but subscript of a nested table may not be consecutive.