Nested Tables Collection in PL/SQL

Nested Tables Collection in PL/SQL

A nested table in PL/SQL is a collection type that allows you to store multiple elements of the same data type. Unlike arrays (VARRAYs), nested tables do not have a predefined upper limit, making them suitable for situations where the number of elements can vary.

Simple Definition

• Nested Table: A nested table is a collection type that can hold an arbitrary number of elements (of the same data type).
• Unlike VARRAYs, nested tables can be sparse, meaning they can have gaps in their indexing.

A nested table is a collection that can grow dynamically, allowing for non-contiguous indexing. It is stored as a database table, which means you can use SQL operations on nested tables, such as querying and joining.

Syntax for declaring and using a nested table

The general syntax for declaring and using a nested table in PL/SQL involves two main steps:

1. Defining the collection type.

2. Declaring a variable of that collection type.
   

-- Step 1: Define a Nested Table Type
CREATE OR REPLACE TYPE collection_type_name AS TABLE OF element_type;

-- Step 2: Declare a Variable of the Nested Table Type
DECLARE
    collection_variable collection_type_name;
BEGIN
    -- Code to manipulate the nested table
END;

Declaring a Nested Table

In PL/SQL, you can declare a nested table as follows:

• 1. Define the type: You need to define the type of elements that will be stored in the nested table.
• 2. Declare a variable: Use the defined type to declare a variable.

1. Creating a Nested Table Type:

CREATE OR REPLACE TYPE type_name AS TABLE OF element_type;

• type_name: Name of the nested table type.

• element_type: The data type of the elements in the nested table (e.g., VARCHAR2, NUMBER, or an object type).

2. Declaring a Nested Table Variable:

variable_name type_name;

3. Methods for Manipulating Nested Tables:

• EXTEND(n): Increases the size of the nested table by n elements.
• TRIM(n): Reduces the size of the nested table by n elements.
• DELETE(i): Removes the element at index i.
• COUNT: Returns the number of elements in the nested table.

Sample Example: Nested Table of Days of the Week

Here’s a comprehensive example that demonstrates how to create a nested table for the days of the week, populate it, and display the contents.

Step 1: Create a Nested Table Type

First, we define a nested table type to hold the names of the days.

CREATE OR REPLACE TYPE DaysOfWeek AS TABLE OF VARCHAR2(15);

Step 2: Declare and Populate the Nested Table

Next, we declare a variable of this type and populate it with the days of the week.

DECLARE
    days DaysOfWeek; -- Declare a nested table variable
BEGIN
    -- Initialize the nested table
    days := DaysOfWeek();

    -- Add elements to the nested table
    days.EXTEND(7); -- Extend the size by 7 for all days of the week
    days(1) := 'Monday';
    days(2) := 'Tuesday';
    days(3) := 'Wednesday';
    days(4) := 'Thursday';
    days(5) := 'Friday';
    days(6) := 'Saturday';
    days(7) := 'Sunday';

    -- Display the elements in the nested table
    FOR i IN 1 .. days.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Day ' || i || ': ' || days(i));
    END LOOP;
END;
/

Detailed Explanation

1. Creating the Type:

The statement CREATE OR REPLACE TYPE DaysOfWeek AS TABLE OF VARCHAR2(15); defines a new nested table type called DaysOfWeek that can hold strings up to 15 characters long. This type will be used to store the names of the days.

2. Declaring the Nested Table:

In the DECLARE block, days is declared as a variable of type DaysOfWeek. This variable will hold our collection of days.

3. Initializing the Nested Table:

days := DaysOfWeek(); initializes the days variable as an empty nested table of type DaysOfWeek.

4. Adding Elements:

days.EXTEND(7); increases the size of the days nested table to hold 7 elements (one for each day of the week).

Each day is assigned to the corresponding index:

days(1) := 'Monday';

days(2) := 'Tuesday';

days(3) := 'Wednesday';

days(4) := 'Thursday';

days(5) := 'Friday';

days(6) := 'Saturday';

days(7) := 'Sunday';

5. Displaying the Days:

A FOR loop iterates through the days nested table using days.COUNT to determine how many elements are present.

DBMS_OUTPUT.PUT_LINE outputs each day along with its index.

Output

When this block is executed, the output will be:

Day 1: Monday
Day 2: Tuesday
Day 3: Wednesday
Day 4: Thursday
Day 5: Friday
Day 6: Saturday
Day 7: Sunday

Conclusion

This example demonstrates how to define, populate, and manipulate a nested table in PL/SQL. Nested tables are versatile structures that allow you to handle collections of data efficiently, and they can be used in a variety of applications where dynamic data management is required.