Understanding PL/SQL Composite Data Types

In SQL and PL/SQL, composite data types are data structures that allow storing multiple values within a single variable.
They help in managing and processing multiple pieces of data together, just like objects or structures in other programming languages.
Composite data types are classified into two main categories:
1. Records – Similar to a row in a table, where multiple fields are grouped together.
2. Collections – Used to store multiple rows dynamically, like arrays.

Types of Composite Data Types

PL/SQL provides two types of composite data types:

Records
Collections

1. Records

Similar to a row in a table, records store multiple related values of different types in one variable.
Records can be created in two ways:
1. Using an existing table’s structure
2. Defining a custom record type
Records store a single row at a time.

2. Collections

Collections store multiple rows of values, similar to lists or arrays in other programming languages. Collections are of three types:

VARRAYs (Variable-Sized Arrays)
Nested Tables
Associative Arrays (Index-By Tables)

Memory Management

All PL/SQL variables, including composite types, are stored in PGA (Program Global Area).
PGA is in memory, making access much faster than fetching data from a database.

PL/SQL Records

Types of PL/SQL Records

Table-Based Records
Cursor-Based Records
User-Defined Records

1. Table-Based Records

Table-based records derive their structure from an existing database table. Each record variable corresponds to a row in the table.

Declare
    r_emp EMPLOYEES%rowtype;
begin
    select * into r_emp from EMPLOYEES where EMPLOYEES.employee_id ='104';
    DBMS_OUTPUT.PUT_LINE(r_emp.first_name);
end;

2. Cursor-Based Records

Cursor-based records use an explicit cursor to hold multiple rows of data.

DECLARE
    CURSOR emp_cursor IS SELECT employee_id, first_name, last_name FROM employees;
    emp_rec emp_cursor%ROWTYPE;  -- Declares a record based on the cursor
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO emp_rec;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
    END LOOP;
    CLOSE emp_cursor;
END;

3. User-Defined Records

User-defined records allow the creation of custom composite data types.

DECLARE
    type t_emp IS RECORD (
        first_name EMPLOYEES.first_name%TYPE,
        last_name EMPLOYEES.last_name%TYPE,
        salary employees.salary%type  NOT NULL DEFAULT 1000
    );
    r_emp t_emp;
BEGIN
    SELECT first_name,last_name,salary into r_emp from EMPLOYEES where   employee_id = '101';
    DBMS_OUTPUT.PUT_LINE(r_emp.first_name);
END;

DML operation using Record

DECLARE 
    r_emp employees%rowtype;
BEGIN
    select * into r_emp from EMPLOYEES where EMPLOYEE_ID ='110';
    INSERT into NEW_EMPLOYEES VALUES r_emp;
    r_emp.salary := 20000;
    -- UPDATE NEW_EMPLOYEES set row=r_emp where EMPLOYEE_ID = r_emp.employee_id;
    -- DELETE from NEW_EMPLOYEES where EMPLOYEE_ID=r_emp.employee_id;
END;

VARRAYs (Variable-Sized Arrays)

VARRAY (Variable-Size Array) is a collection type in PL/SQL that allows storing a fixed number of elements of the same data type.

Similar to Nested Tables but bounded (fixed size declared at initialization).
Cannot change size after declaration.
Indexed from 1 (not 0).
May lead to unused memory allocation (e.g., declaring 100 elements but using only 80 still occupies space for 100 elements).
Used when a fixed-size collection is required, especially for compatibility with procedures/functions expecting a specific size.

DECLARE 
    TYPE t_varr is Varray(5) of VARCHAR2(10);
    employees t_varr;
    -- employees t_varr:= t_varr('a','b','c','d','e'); -- Declare and initialize at same place
BEGIN
    employees := t_varr('a','b','c','d','e');
    for i in 1..5 loop
        DBMS_OUTPUT.PUT_LINE(employees(i));
    end loop;
END;

VARRAY Functions and Operations

COUNT: Returns the number of elements.
LIMIT: Returns the maximum size.
EXTEND: Adds elements (only in PL/SQL collections, not SQL tables).
DELETE: Removes all elements. Can’t remove specific element form varray.
FIRST : Returns first index(e.g. 1).
LAST : Returns last index(e.g. 5).
EXISTS(key) : Returns true if element present.

We can use count, first, last in loop.

DECLARE 
    TYPE t_varr is Varray(10) of VARCHAR2(10);
    employees t_varr:= t_varr('a','b','c','d','e'); 
BEGIN
    DBMS_OUTPUT.PUT_LINE(employees.first); -- 1
    DBMS_OUTPUT.PUT_LINE(employees.last);  -- 5
    DBMS_OUTPUT.PUT_LINE(employees.count); -- 5
    DBMS_OUTPUT.PUT_LINE(employees.limit); -- 10
    employees.delete; -- Remove all elements
    DBMS_OUTPUT.PUT_LINE(employees.count); -- 0
END;

Use of extend function for post insert in varray.

DECLARE
  TYPE t_list IS VARRAY(15) OF VARCHAR2(50);
  employees t_list := t_list();
  i NUMBER := 1;
BEGIN
  FOR p IN 100..110 LOOP
    employees.extend;
    SELECT first_name INTO   employees(i) FROM   employees WHERE employee_id = p;
    i := i + 1;
  END LOOP;
  FOR x IN 1..employees.count() LOOP
    If employees.exists(x) THEN
      dbms_output.put_line(employees(x));
    END IF;
  END LOOP;
END;

Declaring a VARRAY at schema level

CREATE OR REPLACE TYPE e_list AS VARRAY(20) OF VARCHAR2(100);

DECLARE 
    employees e_list;
BEGIN
    employees := e_list('a','b','c','d','e');
    for i in 1..5 loop
        DBMS_OUTPUT.PUT_LINE(employees(i));
    end loop;
END;

Nested Tables

A list where keys are only numeric, starting from 1.
Unbounded (can grow dynamically without limits).
Keys increment automatically when a new value is added.
Unlike arrays in other languages, PL/SQL collections start at index 1

Similarities & Differences Between Nested Tables and VARRAYs

Both nested tables and VARRAYs are collections with key-value pairs.
Keys can only be positive numbers (negative numbers are invalid).
The keys can use subtypes like binary_integer or pls_integer, which are more efficient than number for arithmetic operations.
Both can hold up to 2GB of values.
Both can be used as schema-level types, meaning they can be stored as columns in tables.

Key Differences

Unlike VARRAYs, nested tables allow deletion of elements after initialization.
Nested tables are not stored consecutively in the database; there can be gaps between elements in memory.
Even if elements are deleted, retrieved keys always start from 1 when queried.
Nested tables are unbounded, meaning their size can grow dynamically.
VARRAYs require a predefined maximum size, but nested tables do not.

Creating a Nested Table

The syntax is similar to VARRAYs but uses TABLE instead of VARRAY.

DECLARE
   TYPE e_list IS TABLE OF VARCHAR2(50);  -- Using TABLE keyword
   emps e_list;
BEGIN
   emps := e_list('Alex', 'Bruce', 'John');  -- Initializing the nested table

   FOR i IN 1..emps.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(emps(i));
   END LOOP;
END;

Nested Tables Functions and Operations

COUNT: Returns the number of elements.
EXTEND: Adds elements (only in PL/SQL collections, not SQL tables).
DELETE: Removes all elements.
DELETE(key): Removes specific elements.
FIRST : Returns first index(e.g. 1).
LAST : Returns last index(e.g. 5).
EXISTS(key) : Returns true if element present.

Adding Elements to a Nested Table

emps.EXTEND;
emps(4) := 'Bob';

Without EXTEND, assigning a value to an out-of-bound index results in "Subscript beyond count" error.(Same for varray)

Fetching Data from a Table into a Nested Table

DECLARE
   TYPE e_list IS TABLE OF employees.first_name%TYPE;  -- Using %TYPE attribute
   emps e_list := e_list();  -- Constructor method to initialize
   idx PLS_INTEGER := 1;
BEGIN
   FOR x IN 100..110 LOOP
      emps.EXTEND;
      SELECT first_name INTO emps(idx) FROM employees WHERE employee_id = x;
      idx := idx + 1;
   END LOOP;
END;

Deleting Elements from a Nested Table

Unlike VARRAYs, you can delete specific elements from nested tables.

emps.DELETE(3);  -- Deletes the third element
emps.delete; -- Delete all the element

Accessing the deleted element later will cause a "No data found" error.
To avoid errors, use the EXISTS function before accessing an element:

IF emps.EXISTS(i) THEN
   DBMS_OUTPUT.PUT_LINE(emps(i));
END IF;

Declaring a Nested Table at schema level :

CREATE OR REPLACE TYPE e_list AS TABLE OF VARCHAR2(100);

DECLARE 
    employees e_list;
BEGIN
    employees := e_list('a','b','c','d','e');
    for i in 1..5 loop
        DBMS_OUTPUT.PUT_LINE(employees(i));
    end loop;
END;

Associative Arrays

They consist of two columns:
- Key (PLS_INTEGER, BINARY_INTEGER, or VARCHAR2).
- Value (any data type).
Best suited for real-life applications where keys (e.g., employee ID, identity numbers) are unique and meaningful.
Associative arrays are also known as index-by tables.
Key characteristics:
Keys must be unique.
Keys do not need to be sequential.
Negative numbers can be used as keys.
Values can be scalar or record types.
No need to initialize the array.
Size is unbounded (limited only by memory).
No upper limit like 2GB for other collections.
Associative arrays are indexed in memory, making them faster than database tables.
Cannot be created at the schema level (only in memory).

Indexing Methods

Indexing depends on the key type:
- PLS_INTEGER → Direct indexing (fast for direct access, slow for traversing).
- VARCHAR2 → B-TREE indexing (efficient for frequent traversals).
For frequent back-and-forth access, use VARCHAR2 keys, even for numbers.

Creating an Associative Arrays

DECLARE
  TYPE e_list IS TABLE OF VARCHAR2(5) INDEX BY PLS_INTEGER;
  emps e_list;
BEGIN
  emps(100) := 'Bob';
  emps(120) := 'Sue';
  dbms_output.put_line(emps(100));
  dbms_output.put_line(emps(120));
END;

Assigning Values to an Associative Array

emps(100) := 'Alice';
emps(120) := 'Bob';

No need for .EXTEND method

Fetching Data from a Table

plsqlCopyEditFOR x IN 100..110 LOOP
  SELECT first_name INTO emps(x) FROM employees WHERE employee_id = x;
END LOOP;

Associative Arrays Functions and Operations

COUNT: Returns the number of elements.
DELETE: Removes all elements.
DELETE(key): Removes specific elements.
FIRST : Returns first index(e.g. 1).
LAST : Returns last index(e.g. 5).
EXISTS(key) : Returns true if element present. Functions and Operations
NEXT(key) : Return next key after num if present , else null.
PRIOR(key) : Return previous key before num if present , else null.

DECLARE
  TYPE e_list IS TABLE OF VARCHAR2(5) INDEX BY PLS_INTEGER;
  emps e_list;
BEGIN
  emps(100) := 'Bob';
  emps(120) := 'Sue';
  DBMS_OUTPUT.PUT_LINE(emps.first); -- 100
  DBMS_OUTPUT.PUT_LINE(emps.last); -- 120
  DBMS_OUTPUT.PUT_LINE(emps.count); -- 2
  emps.delete(130); -- Nothing will happen because 130 key is not present.
  DBMS_OUTPUT.PUT_LINE(emps.count); --2
  DBMS_OUTPUT.PUT_LINE(emps.next(100)); -- 120
  DBMS_OUTPUT.PUT_LINE(emps.prior(120)); -- 100
  if emps.exists(120) then 
    DBMS_OUTPUT.PUT_LINE('aaaa');
  end if;
END;

Iterating Over an Associative Array

Using FIRST and LAST (Sequential Keys Only)

FOR i IN emps.FIRST..emps.LAST LOOP
  DBMS_OUTPUT.PUT_LINE(emps(i));
END LOOP;

Handling Non-Sequential Keys (Preferred Approach)

DECLARE
  idx PLS_INTEGER;
BEGIN
  idx := emps.FIRST;
  WHILE idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(emps(idx));
    idx := emps.NEXT(idx);
  END LOOP;
END;

Using VARCHAR2 as Keys

DECLARE
  type e_list is table of employees.first_name%TYPE index by employees.email%TYPE;
  v_email employees.email%TYPE;
  v_first_name employees.first_name%TYPE;
  emps e_list;
BEGIN
  SELECT first_name, email INTO v_first_name, v_email FROM employees where employee_id = 103;
  emps(v_email) := v_first_name;
  DBMS_OUTPUT.PUT_LINE(emps(v_email));
END;

Associative Arrays with Records (%ROWTYPE)

DECLARE
  TYPE e_list IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  emps e_list;
BEGIN
  FOR x IN 100..110 LOOP
    SELECT * INTO emps(x) FROM employees WHERE employee_id = x;
  END LOOP;
END;

Storing Collections in Table Columns

Collections: Varrays and Nested Tables can be stored in table columns, but associative arrays cannot be stored at the schema level.
Performance Consideration: Using collections reduces the cost of joins, especially for large tables.

Creating and Using Varrays

Create an Object Type (since records cannot be stored directly):

 CREATE OR REPLACE TYPE t_phone_number AS OBJECT (
     p_type VARCHAR2(10),
     p_number VARCHAR2(50)
 );

Create a Varray Type:

 CREATE OR REPLACE TYPE v_phone_numbers AS VARRAY(3) OF t_phone_number;

Create a Table with Varray Column:

 CREATE TABLE emps_with_phones (
     employee_id NUMBER,
     first_name VARCHAR2(50),
     last_name VARCHAR2(50),
     phone_number v_phone_numbers
 );

Insert Data into the Table:

 INSERT INTO emps_with_phones VALUES (
     10, 'Alex', 'Brown',
     v_phone_numbers(t_phone_number('HOME', '111.111.1111'),
                    t_phone_number('WORK', '222.222.2222'),
                    t_phone_number('MOBILE', '333.333.3333'))
 );

Query the Table:
```
 SELECT * FROM emps_with_phones;
```

Flattening the Varray for Readability:

 SELECT e.first_name, e.last_name, p.p_type, p.p_number
 FROM emps_with_phones e, TABLE(e.phone_number) p;

Creating and Using Nested Tables

Oracle cannot store an unbounded collection inside a single column efficiently. It always stored nested table in a system-generated storage table that automatically managed by the database. This storage table is linked to the main table. Since the data is stored separately, Oracle can optimize queries and joins. The storage table resides in the same tablespace and segment as the main table, making joins fast.

Create a Nested Table Type:

 CREATE OR REPLACE TYPE n_phone_numbers AS TABLE OF t_phone_number;

Create a Table with Nested Table Column:

 CREATE TABLE emps_with_phones2 (
     employee_id NUMBER,
     first_name VARCHAR2(50),
     last_name VARCHAR2(50),
     phone_number n_phone_numbers
 ) NESTED TABLE phone_number STORE AS phone_numbers_table;

 -- NESTED TABLE phone_number STORE AS phone_numbers_table --is required because 
 -- nested table data is stored in a separate table

Insert Data (Similar to Varray Insertion):

 INSERT INTO emps_with_phones2 VALUES (
     11, 'John', 'Doe',
     n_phone_numbers(t_phone_number('HOME', '444.444.4444'),
                    t_phone_number('WORK', '555.555.5555'))
 );

Query Nested Table Data:

 SELECT e.first_name, e.last_name, p.p_type, p.p_number
 FROM emps_with_phones2 e, TABLE(e.phone_number) p;

Updating a Nested Table Column:

 UPDATE emps_with_phones2
 SET phone_number = n_phone_numbers(
     t_phone_number('HOME', '666.666.6666'),
     t_phone_number('WORK', '777.777.7777')
 )
 WHERE employee_id = 11;

Appending a New Value Without Replacing Existing Data (PL/SQL):

 DECLARE
     p_num n_phone_numbers;
 BEGIN
     SELECT phone_number INTO p_num FROM emps_with_phones2 WHERE employee_id = 11;
     p_num.EXTEND;
     p_num(3) := t_phone_number('FAX', '999.999.9999');
     UPDATE emps_with_phones2 SET phone_number = p_num WHERE employee_id = 11;
 END;

Composite Data Types in PL/SQL : 3

Table of contents