Nested Tables Collection in PL/SQL

Nested Tables Collection in PL/SQL

Nested Tables in PL/SQL are a type of collection that allows you to store an unbounded number of elements of the same type. They are particularly useful for holding sets of data in a structured format, making them ideal for scenarios where you need to manage multiple rows of related data.

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.

Syntax and Structure

Syntax

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).

1. Declaring a Nested Table Variable:

variable_name type_name;

1. 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.

1. 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.

1. Initializing the Nested Table:

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

1. 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';

1. 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.

The above is the detailed content of Nested Tables Collection in PL/SQL. For more information, please follow other related articles on the PHP Chinese website!