PL/SQL Composite data type - Collections (Associative array, VARRAY and Nested Tables )

PL/SQL data types has been broadly classified in two category - Scalar data types and Composite datatypes. In previous post we discussed about scalar data type which cover NUMBER , CHAR, VARCHAR2, LONG, etc types. The main agenda of this post is to discuss one of the composite types in PL/SQL - collections and in next post another composite type - Record will be discussed.

A composite data type stores values that have internal components and internal components can be either scalar or composite.Internal components can be of same data type and different data type. PL/SQL allows us to define two kinds of composite data types :

Collection - The internal components must have the same data type and we can access each element of a collection variable by its unique index, with this syntax: variable_name(index).
Record - The internal components can have different data types and we can access each field of a record variable by its name, with this syntax: variable_name.field_name. Detailed discussion of Records in PL/SQL.

Collections in PL/SQL

Oracle provides three types of collections.

Index-by Table(associate array),
Nested Tables, and
VARRAY.

All these collections are like a single dimension array.Syntax of collection declaration is as follows:-

TYPE type IS     -- type is collection variable name, a valid identifier 
   { assoc_array_type_def  
   | varray_type_def
   | nested_table_type_def
   } ;

Collection can be created in following ways :
1. Defines a collection type and then declare a variable of that type.
2. Use %TYPE to declare a collection variable of the same type as a previously declared collection variable.

Associate array(Indexed Tables):- Associative array is a set of key-value pairs and each key should be unique index. The data type of index can be either a string type or PLS_INTEGER. Indexes are stored in sort order, not creation order. Syntax of associative array type creation :

TYPE type IS  {
  --assoc_array_type_def 
  TABLE OF datatype [ NOT NULL ]
     INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR2 ( v_size ) | data_type }
};

On combining both collection declaration and associate table type declaration, we create associative array and store key value pairs in following program and we can perform various operation on it (Collections method).It's first way of creating collection(another way uses %TYPE).In below sample program,we create TYPE of associative array named as address and then create a variable employee_address of TYPE address. Refer in-line comments for more details :-

DECLARE
--Associative array type indexed by BINARY_NUMBER
TYPE address IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER ;
--Associative array variable of type address
employees_address address;
BEGIN
  employees_address('01')  :=  'Hyderabad, INDIA';
  employees_address('02')  :=  'Banglore, INDIA';
  employees_address('03')  := 'NY, USA';
  --Collection operations 
  -- FIRST and NEXT gives firs and next element of collecton
DBMS_OUTPUT.PUT_LINE('FIRST and LAST ELEMENT key of collection are ' || 
  employees_address.FIRST || ' and ' || employees_address.LAST);
  --COUNT()method gives total no of elements in collection 
DBMS_OUTPUT.PUT_LINE('Total no of elements in collection '  
  || employees_address.COUNT);
  --EXISTS check for existence of key 
IF employees_address.EXISTS(02) THEN
  employees_address.DELETE(02);
END IF;
DBMS_OUTPUT.PUT_LINE('Total no of elements in collection after delete ' 
  || employees_address.COUNT);
  
END;

=========Sample output==========
FIRST and LAST ELEMENT key of collection are 1 and 3
Total no of elements in collection 3
Total no of elements in collection after delete 2
=============================

Notes:- Associative array is not persistent, it is not stored on disk as other database tables (including VARRAY and Nested tables).It Can hold an any number of elements, which you can access without knowing their positions. Manipulation of associative array can only be carried out by collections functions not with DML statements.
Associative array type can not be created at schema level,in order to pass an associative array variable as a parameter to a standalone program, we should declare the type of that variable in a package specification.

Where do we use Associative array :- 1. For temporary Look-up creation and 2. For Passing collections to and from the database server.

VARRAY:- It is variable-size array and element counts in it can vary from 0 to declared maximum size.Characteristics of VARRAY:-

Elements of VARRAY can be accessed by variable_name(index).VARRY index starts from 1 (lowest_index = 1) and it can go up to maximum size of VARRAY.
As contrast to associative array, it can be persisted in database table and order of elements (indexes and element order) remain stable.
VARRAY has constructor support as contrast to Associative array that does not support collection constructor. A collection constructor is a system-defined function with the same name as a collection type,which returns a collection of that type. Syntax of a constructor invocation is:
collection_type ([values,...]), values are optional. If no value is passed constructor returns emplty collection.
VARRAY is stored as a single object in a column in database table.(if size of object is more than 4KB then it is stored separately but in same namespace). Following diagram depicts how VARRAY is stored in database table: Highlighted column refers to VARRAY type and stored in database column as other scalar type.

VARRAY creation and its initialization:-
Syntax of VARRAY creation is as follows - varray_type_def with collection

-- size_limit: upper limit of VARRAY(maximum that many elements can be stored)
TYPE type IS  { VARRAY | [ VARYING ] ARRAY } ( size_limit ) 
  OF datatype [ NOT NULL ]

Consider following sample program which creates a VARRY to store address information of employees and initialize it with constructor. Here ADDRESS is VARRAY type with upper limit of container 3 and using constructor collection of type ADDRESS created is returned to emp_address.

DECLARE
  -- VARRAY type declaration of type VARCHAR, upperlimit 3
  TYPE ADDRESS IS VARRAY(3) OF VARCHAR2(45);  
  -- varray variable initialized with constructor of type ADDRESS
  emp_address ADDRESS := ADDRESS('HYD,IND', 'NY,USA','BANG,IND');
 BEGIN
    DBMS_OUTPUT.PUT_LINE('VARRAY elements count is '
        || emp_address.COUNT);
    DBMS_OUTPUT.PUT_LINE('Address display - Iteration over VARRAY');
    --emp_address.FIRST= 1 and emp_address.LAST = 3
    FOR i IN emp_address.FIRST..emp_address.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(i || '. address is ' || emp_address(i));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Modify emp_address VARRAY ');
    emp_address(1) := 'Sydeny, AUS';
    DBMS_OUTPUT.PUT_LINE('Accessing VARRAY based on index,modified address is  '
        ||emp_address(1)); -- notice modified value here. 
    --emp_address.DELETE(2);--Delete operation on VARRAY is not allowed.
    
END;

==============Sample output==================
VARRAY elements count is 3
Address display - Iteration over VARRAY
1. address is HYD,IND
2. address is NY,USA
3. address is BANG,IND
Modify emp_address VARRAY
Accessing VARRAY based on index,modified address is Sydeny, AUS
===========================================
Where do we use VARRAY:- If we have prior info of maximum number of elements and we want sequential access of collection. It is not not good idea to use VARRAY when collection size is very large, because VARRAY is retrieved at once from database.

Nested Tables:- It is a table (with rows and columns) that is stored in database table as data of a column in no particular order.When that table is retrieved form database in PL/SQl context, PL/SQL indexes all rows starting from 1 and based on index we can access each row of nested table using method: nested_table_var(index). Following diagram shows how Nested tables is stored in database table. Highlighted inner table in CUSTOMER_DETAILS column refers to Nested table type and stored as part of column data.

Nested table creation and its initialization:- Syntax of Nested table creation is as follows, (nested_table_type_def with collection ) :

TYPE type IS {TABLE OF datatype [ NOT NULL ] }

Consider following scenario to understand how Nested table type is created in and stored in database.
Lets say we have an Customer_detail_object is a Object TYPE and it stores customer details and nested table is collection of that object- each row of nested table is customer_detail_object.
If you do no understand what is this Object, do not worry we will revisit it again, for the time being just assume it is a container which can store different data types. Follow following steps and execute query in sequence :
Step 1: Create Object type having fields CustID, cust_name, cust_address, execute below query to create Object named Customer_detail_object.

--create  Object Customer_detail_object : Created in schema level.
create type Customer_detail_object as object  
(
 CustID NUMBER(14),
 cust_name varchar2(25),
 cust_address varchar2(100)
);

Step 2: Now nested table type CUSTOMER_DETAILS of object type Customer_detail_object.888888

--Create TABLE of object Customer_detail_object: Created in schema level
create type CUSTOMER_DETAILS as Table of Customer_detail_object;

Step 3: Create a table, PRODUCTS_CUSTOMRS_DETAILS, in database with a fields of type CUSTOMER_DETAILS (while creating table we specify about CUSTOMER_DETAILS as nested table).

--create table in database , NESTED TABLE clause is mandatory to append
create table PRODUCTS_CUSTOMRS_DETAILS
( 
 product_id number(5),
 product_name varchar2(30),
 CUSTOMER_DETAILS HR.CUSTOMER_DETAILS
) NESTED TABLE CUSTOMER_DETAILS STORE AS CUSTOMRS_OBJECTS;

Step 4: Insert rows in table. We have created two rows and each row has CUSTOMER_DETAILS table with two rows. If constructor used is empty, nested table will be empty not NULL.

 --insert data into table 
insert into PRODUCTS_CUSTOMRS_DETAILS
 values(1,'P1',
CUSTOMER_DETAILS(
       Customer_detail_object(1,'RSQ','BANG,INDIA'),
       Customer_detail_object(2,'RTA','AUSTIN,USA')      
 )); 
insert into PRODUCTS_CUSTOMRS_DETAILS
 values(2,'P2',
CUSTOMER_DETAILS(
       Customer_detail_object(1,'RSQ','BANG,INDIA'),
       Customer_detail_object(2,'BAC','NY,USA')      
 )); 
 commit;

Now we have completed set-up to query database and see the stored result from PL/SQL program.

declare
customerDetails_Tab CUSTOMER_DETAILS;
begin
--insert a record in database table with nested table data
 insert into products_CUSTOMRS_DETAILS
 values(3,'P3',
CUSTOMER_DETAILS(
       Customer_detail_object(1,'ACV','HYD,INDIA'),
       Customer_detail_object(2,'ERT','AUSTIN,USA')      
 ));
 commit;
 --select record and store nested table value in customerDetails_Tab
 select CUSTOMER_DETAILS into customerDetails_Tab
 from products_CUSTOMRS_DETAILS
 where product_id = 1;
 --update nested table column in database
 update products_CUSTOMRS_DETAILS set CUSTOMER_DETAILS = customerDetails_Tab
 where product_id = 3;
 commit;
 
end;

Here we played around with DML statements and treating inner table as atomic value(Insert, select or update nested table in column).
We can deal with individual row of nested table using TABLE command as follows:

select * from table ( select CUSTOMER_DETAILS from 
    products_CUSTOMRS_DETAILS where product_id = 1);

Above query executes and it displays nested tables corresponding to row with product_id = 1, as follows :

Where do we use Nested tables:- Nested table finds it's usage when index values are not consecutive, maximum number of elements storage is not fixed (as contrast to VARRAY).
- Nested table finds extensive use when we want to access refcursor output in SQL and PL/SQL table structure cannot be directly be used SQL. So, a table of SQL object is created at schema level.

create or replace type t_emptype as table of emptype;    -- emptype is SQL Object not plsql record

How internal storage of VARRAY and Nested table type are different ?VARRY type are stored as part of database table(as column data) until its size reaches 4KB(then it is stored separately from database table), however in Nested table data is stored in a separate store table, a system-generated database table.When we access Nested table database joins this system table with Nested table that's why nested table is suitable for querying and updating(restricted to part of it at a time).

It is all about collections - a composite data types. In following post we will be discussing about another composite data structure in PL/SQL - Record.

Previous: PL/SQL- Control statements(IF-ELSE, LOOP,WHILE) Next:PL/SQL Record

PL/SQL Composite data type - Collections (Associative array, VARRAY and Nested Tables )

Collections in PL/SQL

2 Comments

Contact Form