Collections

 

Collections in PL/SQL are data structures that allow you to store and manipulate multiple values (similar to arrays in other programming languages).

PL/SQL provides three types of collections:

1. Associative Arrays (Index-by Tables)
2. Nested Tables
3. VARRAYs (Variable-Sized Arrays)

Ø  Associative Arrays in PL/SQL (Index-By Tables)

In PL/SQL, associative arrays (also known as index-by tables) are key-value pairs where data is stored and accessed using unique index values. These arrays are similar to hash tables or dictionaries in other programming languages.

Key Features of Associative Arrays:

1. Unbounded Size: The size is not fixed and grows dynamically.
2. Indexed by BINARY_INTEGER or VARCHAR2: Unlike normal arrays, which use numeric indexes, associative arrays allow both numeric and string indexes.
3. Sparsely Populated: The index values do not need to be consecutive.
4. Efficient for Lookups: Access time is fast as data is stored in a hashed structure.
5. Scoped to PL/SQL Block: They exist only in memory and cannot be stored in the database directly.

Example 1: Associative Array with Numeric Index

DECLARE

    -- Declare an associative array type

    TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

    -- Declare a variable of this type

    employees emp_array;

BEGIN

    -- Insert values using numeric index

    employees(101) := 'John Doe';

    employees(102) := 'Jane Smith';

    employees(103) := 'Mike Johnson';

   

    -- Retrieve and display values

    DBMS_OUTPUT.PUT_LINE('Employee 101: ' || employees(101));

    DBMS_OUTPUT.PUT_LINE('Employee 102: ' || employees(102));

    DBMS_OUTPUT.PUT_LINE('Employee 103: ' || employees(103));

END;

/

Output:

yaml

CopyEdit

Employee 101: John Doe

Employee 102: Jane Smith

Employee 103: Mike Johnson

 

 

 

Example 2: Associative Array with String (VARCHAR2) Index

DECLARE

    -- Declare an associative array type

    TYPE salary_array IS TABLE OF NUMBER INDEX BY VARCHAR2(30);

    -- Declare a variable of this type

    salaries salary_array;

BEGIN

    -- Insert values using string index

    salaries('John') := 50000;

    salaries('Jane') := 60000;

    salaries('Mike') := 70000;

   

    -- Retrieve and display values

    DBMS_OUTPUT.PUT_LINE('Salary of John: ' || salaries('John'));

    DBMS_OUTPUT.PUT_LINE('Salary of Jane: ' || salaries('Jane'));

    DBMS_OUTPUT.PUT_LINE('Salary of Mike: ' || salaries('Mike'));

END;

/

Output:

Salary of John: 50000

Salary of Jane: 60000

Salary of Mike: 70000

Operations on Associative Arrays

1. Checking If a Key Exists (EXISTS Method)

Before accessing a key, you can check whether it exists using the EXISTS function.

IF employees.EXISTS(101) THEN

    DBMS_OUTPUT.PUT_LINE('Employee 101 exists.');

ELSE

    DBMS_OUTPUT.PUT_LINE('Employee 101 does not exist.');

END IF;

2. Deleting an Element (DELETE Method)

employees.DELETE(101);  -- Removes only the element at index 101

To delete all elements:

employees.DELETE;  -- Removes all elements in the array

3. Counting Elements (COUNT Method)

DBMS_OUTPUT.PUT_LINE('Number of employees: ' || employees.COUNT);

4. Iterating Over an Associative Array (FIRST and NEXT Methods)

You can use FIRST and NEXT to traverse an associative array.

DECLARE

    TYPE emp_array IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

    employees emp_array;

    emp_id BINARY_INTEGER;

BEGIN

    employees(101) := 'John Doe';

    employees(102) := 'Jane Smith';

    employees(103) := 'Mike Johnson';

 

    emp_id := employees.FIRST;  -- Get first key

 

    WHILE emp_id IS NOT NULL LOOP

        DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id || ': ' || employees(emp_id));

        emp_id := employees.NEXT(emp_id);  -- Move to next key

    END LOOP;

END;

/

Output:

Employee 101: John Doe

Employee 102: Jane Smith

Employee 103: Mike Johnson

 

Use Cases of Associative Arrays

1. Storing Lookup Tables: Used for in-memory key-value lookups.
2. Session-Level Caching: Store temporary data within a session.
3. Processing Query Results: Fetch query results into an associative array for quick access.
4. Sparse Data Storage: Useful when index values are not sequential.

 

Key Differences: Associative Arrays vs Other Collections

Feature	Associative Arrays	Nested Tables	VARRAYs
Size Limit	No	No	Yes (fixed)
Persistent in DB?	No	Yes	Yes
Can be Sparse?	Yes	No	No
Indexed By	BINARY_INTEGER / VARCHAR2	Sequential (1,2,3,...)	Sequential (1,2,3,...)
Suitable for Sparse Data?	Yes	No	No

 

Conclusion

Associative arrays in PL/SQL are highly flexible and useful for in-memory data storage and lookups. Their ability to use both numeric and string indexes, coupled with fast access and dynamic sizing, makes them ideal for applications needing quick, efficient data retrieval.

 

 

 

 

 

 

 

 

Ø  Nested Tables in PL/SQL – Detailed Explanation:

What is a Nested Table in PL/SQL?

A nested table is a collection type in PL/SQL that behaves like a one-dimensional array but can be stored in database columns or used in PL/SQL blocks. It allows storing a set of elements, which can be manipulated as a unit.

Nested tables are more flexible than VARRAYs because they do not have a fixed size and can be sparse (i.e., elements can be deleted, leaving gaps).

Key Features of Nested Tables:

1. Variable Size: Unlike VARRAYs, nested tables can grow dynamically.
2. Persistent Storage: Can be stored in database columns.
3. Sparse Structure: Elements can be deleted, leaving gaps.
4. Manipulated with SQL: Nested tables can be queried like normal tables using TABLE() function.
5. Indexed Sequentially: Indexes start at 1 and are sequential.

Example 1: Nested Table in PL/SQL Block

DECLARE

    -- Define a nested table type

    TYPE emp_names IS TABLE OF VARCHAR2(50);

    -- Declare a variable of the nested table type

    employees emp_names := emp_names();

BEGIN

    -- Add elements using EXTEND

    employees.EXTEND;

    employees(1) := 'John Doe';

 

    employees.EXTEND;

    employees(2) := 'Jane Smith';

 

    employees.EXTEND;

    employees(3) := 'Mike Johnson';

 

    -- Display elements

    FOR i IN 1..employees.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Employee ' || i || ': ' || employees(i));

    END LOOP;

END;

/

Output:

yaml

CopyEdit

Employee 1: John Doe

Employee 2: Jane Smith

Employee 3: Mike Johnson

 

 

 

 

 

Operations on Nested Tables

1. Adding Elements (EXTEND)

Nested tables do not allocate memory automatically. You need to use EXTEND to increase the size before assigning values.

employees.EXTEND;

employees(1) := 'John Doe';

2. Deleting Elements (DELETE)

• Remove a specific element:

employees.DELETE(2);  -- Deletes the second element

employees.DELETE;  -- Clears all the entire nested table

3. Counting Elements (COUNT)

DBMS_OUTPUT.PUT_LINE('Total Employees: ' || employees.COUNT);

4. Checking for Empty Table (EXISTS)

IF employees.EXISTS(2) THEN

    DBMS_OUTPUT.PUT_LINE('Element exists');

ELSE

    DBMS_OUTPUT.PUT_LINE('Element does not exist');

END IF;

 

Example 2: Nested Table with SQL Operations

Nested tables allow storage in database columns and manipulation with SQL.

Step 1: Create a Nested Table Type

CREATE OR REPLACE TYPE num_list AS TABLE OF NUMBER;

Step 2: Use Nested Table in a PL/SQL Block

DECLARE

    -- Declare a variable of the nested table type

    numbers num_list := num_list(10, 20, 30, 40, 50);

BEGIN

    -- Loop through and display elements

    FOR i IN 1..numbers.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Number ' || i || ': ' || numbers(i));

    END LOOP;

END;

/

Output:

Number 1: 10

Number 2: 20

Number 3: 30

Number 4: 40

Number 5: 50

 

Storing and Querying Nested Tables in Database

Step 1: Create a Table with a Nested Table Column

CREATE TABLE employees (

    emp_id NUMBER PRIMARY KEY,

    emp_name VARCHAR2(100),

    skills num_list  -- Nested table column

) NESTED TABLE skills STORE AS skill_table;

Step 2: Insert Data into Nested Table Column

INSERT INTO employees VALUES (101, 'John Doe', num_list(1, 2, 3));

INSERT INTO employees VALUES (102, 'Jane Smith', num_list(4, 5, 6));

Step 3: Querying a Nested Table

To retrieve data from a nested table column, use the TABLE() function.

SELECT e.emp_name, t.COLUMN_VALUE AS skill

FROM employees e, TABLE(e.skills) t;

Output:

John Doe   1

John Doe   2

John Doe   3

Jane Smith 4

Jane Smith 5

Jane Smith 6

 

Comparison: Nested Tables vs. Other Collection Types

Feature	Nested Tables	Associative Arrays	VARRAYs
Size Limit	No	No	Yes (fixed)
Persistent in DB?	Yes	No	Yes
Sparse?	Yes	Yes	No
Indexed By	Sequential (1,2,3,...)	BINARY_INTEGER/VARCHAR2	Sequential (1,2,3,...)
SQL Query Support	Yes	No	No
Storage in Tables	Yes	No	Yes

 

 

 

 

Key Use Cases of Nested Tables

Storing Lists in Database Columns: Store multiple values in a single column.

Querying Structured Data: Retrieve multiple values using SQL joins.

Sparse Data Handling: Deleting elements creates gaps, making them useful for non-contiguous data.

 

Conclusion

Nested tables are a powerful collection type in PL/SQL, offering dynamic sizing, database storage, and SQL query capabilities. They are best suited for handling multiple values in database columns and performing bulk operations efficiently.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

VARRAY

To use a VARRAY in PL/SQL, you must first define a VARRAY type before declaring a variable of that type.

Example 1: Declaring and Using VARRAY in PL/SQL Block

DECLARE

    -- Define a VARRAY type

    TYPE emp_varray IS VARRAY(5) OF VARCHAR2(50);   

    -- Declare a variable of the VARRAY type

    employees emp_varray := emp_varray();

BEGIN

    -- Extend and assign values

    employees.EXTEND(3);  -- Increase size to hold 3 elements

    employees(1) := 'John Doe';

    employees(2) := 'Jane Smith';

    employees(3) := 'Mike Johnson';

 

    -- Display elements

    FOR i IN 1..employees.COUNT LOOP

        DBMS_OUTPUT.PUT_LINE('Employee ' || i || ': ' || employees(i));

    END LOOP;

END;

/

Output:

Employee 1: John Doe

Employee 2: Jane Smith

Employee 3: Mike Johnson

Operations on VARRAY

1. Adding Elements (EXTEND)

Since VARRAYs have a fixed size, you must extend them before assigning values.

employees.EXTEND(2); -- Adds space for 2 more elements

2. Accessing Elements

DBMS_OUTPUT.PUT_LINE('First Employee: ' || employees(1));

3. Counting Elements (COUNT)

DBMS_OUTPUT.PUT_LINE('Total Employees: ' || employees.COUNT);

4. Trimming Elements (TRIM)

TRIM removes elements from the end.

employees.TRIM;  -- Removes last element

5. Deleting All Elements (DELETE)

Unlike nested tables, VARRAY does not support deleting individual elements.

employees.DELETE;  -- Deletes all elements in the array

 

 

Example 2: Using VARRAY with SQL

VARRAYs can be stored in database tables and queried using SQL.

Step 1: Create a VARRAY Type

CREATE OR REPLACE TYPE num_varray AS VARRAY(10) OF NUMBER;

Step 2: Create a Table with VARRAY Column

CREATE TABLE employees (

    emp_id NUMBER PRIMARY KEY,

    emp_name VARCHAR2(100),

    salaries num_varray  -- VARRAY column

);

Step 3: Insert Data into VARRAY Column

INSERT INTO employees VALUES (101, 'John Doe', num_varray(50000, 55000, 60000));

INSERT INTO employees VALUES (102, 'Jane Smith', num_varray(60000, 65000));

Step 4: Query Data from VARRAY Column

To retrieve elements from a VARRAY, use the TABLE() function.

SELECT e.emp_name, t.COLUMN_VALUE AS salary

FROM employees e, TABLE(e.salaries) t;

Output:

John Doe   50000

John Doe   55000

John Doe   60000

Jane Smith 60000

Jane Smith 65000

Comparison: VARRAY vs Nested Tables vs Associative Arrays

Feature	VARRAY	Nested Table	Associative Array
Size Limit	Yes (fixed)	No	No
Persistent?	Yes (Stored in DB)	Yes (Stored in DB)	No (PL/SQL only)
Sparse?	No (Dense)	Yes (Sparse)	Yes (Sparse)
Indexed By	Sequential (1,2..)	Sequential (1,2..)	BINARY_INTEGER/VARCHAR2
SQL Query Support	Yes (TABLE())	Yes (TABLE())	No
Deletion of Elements	Only TRIM	DELETE(index) available	DELETE(index) available

Key Use Cases of VARRAY

1. Storing Small Fixed-Sized Collections: Example: A list of last 5 salaries.
2. Ordered Data Storage: VARRAYs maintain the sequence of elements.
3. Efficient Memory Allocation: Ideal for scenarios where the number of elements is known.

 

 

Conclusion

VARRAYs in PL/SQL are useful when you need a fixed-size, ordered collection that can be stored in the database. They are best suited for cases where the number of elements is known in advance and operations on the entire set are needed.