PL/SQL Nested Tables

PL/SQL supports a nested table. It is a type of data structure like an array in a programming language. Multiple values are stored in a single column. They are stored in a database. Nested tables in PL/SQL are dynamic i.e. the amount of memory utilized can increase or decrease as per use. They allow you to work on variable amounts of data dynamically. They are used when an unknown number of elements are to be stored. Elements of any datatype can be stored in a nested table.

A nested table is used instead of Varray as they are dynamic, they can grow and shrink as per the scenario. Two types of nested tables are unbounded and bounded. Bounded have a fixed size and unbounded are dynamic. If the pl/sql nested table becomes too large, it may lead to performance issues. For loop is used to iterate through the nested table. Oracle database manages the memory related to the PL/SQL nested table.

Syntax of PL/SQL nested tables:

DECLARE

–DECLARE THE NESTED TABLE TYPE

TYPE nested_table_type IS TABLE OF data_type;

–Declare variable of the nested table type

variable nested_table_type;

BEGIN

–initialize the variable

variable := nested_table_type( element1,element2 …..elementn)

— Perform the required operations

__ free up the allocated memory

variable : =NULL;

END;

• The nested table type is declared using the TYPE keyword.
• The variable is created using IS TABLE OF.

Declaring a Nested Table Variable

A nested table is declared in the declaration section like cursors or procedure. The datatype is declared along with the name of the nested table.

TYPE nested_table_type IS TABLE OF data_type;

variable nested_table_type;

Initializing a Nested Table

When a variable is declared using the nested table type it is initialized to NULL. Constructor initializes the value to the variable.

Syntax:

variable_of_nested_table := nested_table_type();

Add Elements to a Nested Table

Elements can be added to nested tables using the EXTEND keyword or the constructor. Elements are added in the execution section of the pl/sql block.

Syntax:

–using EXTEND keyword

BEGIN

nested_table_type_variable.EXTEND;

nested_table_type_variable(1) := value1;

nested_table_type_variable(2) := value2;

Syntax:

–using parameterized constructor

BEGIN

nested_table_type_variable := nested_table_type(value1,value2 …..);

PL/SQL stands for Procedural Language/ Structured Query Language. It has block structure programming features.PL/SQL supports SQL queries. It also supports the declaration of the variables, control statements, Functions, Records, Cursor, Procedure, and Triggers.PL/SQL contains a declaration section, execution section, and exception-handling section. Declare and exception handling sections are optional. Every PL/SQL query contains BEGIN, and END keywords. We can nest blocks in PL/SQL. It supports anonymous blocks and named blocks. Anonymous blocks are not stored in the database while named blocks are stored.

In this article, we’ll explore nested tables in PL/SQL. We’ll look at what they are, how they work, and what they can be used for.PL/SQL is a procedural version of Oracle’s SQL. It’s designed to support complex data structures. Nested tables are a great example of this.

Syntax of PL/SQL block:

Declaration section

BEGIN

Execution section

EXCEPTION

Exception section

END;