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;
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 …..);
Examples of PL/SQL Nested Table
Example 1: Accessing Elements by Their Indexes
Indexes in PL/SQL nested tables start from 1. Elements are accessed by specifying the index.
Syntax:
SET SERVEROUTPUT ON;
DECLARE
— DECALRE NESTED TABLE AND THE VARIABLE
BEGIN
DBMS_OUTPUT.PUTLINE(nested_table_variable(1 ));
END;
The first element of the nested table is printed by specifying the index with the variable.
After putting all the mentioned things together we can perform operation on nested table.
Query:
SET SERVEROUTPUT ON;
DECLARE
TYPE First_nested_table IS TABLE OF NUMBER;
example_one First_nested_table;
BEGIN
-- Initialize the nested table
example_one := First_nested_table(1, 2, 3, 4, 5);
FOR i IN example_one.FIRST .. example_one.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || example_one(i));
END LOOP;
-- Free up memory
example_one := NULL;
END;
Output:
Nested Table in PLSQL
Explanation:
The output confirms the successful initialization of the nested table with numeric elements, the correct iteration over its elements, and the subsequent freeing up of memory. Each element is displayed with its corresponding index in the output.
Example 2: Initializing and Printing Elements of a Nested Table in PL/SQL
Query
SET SERVEROUTPUT ON;
DECLARE
TYPE First_nested_table IS TABLE OF VARCHAR2(20);
example_one First_nested_table;
BEGIN
-- Initialize the nested table
example_one := First_nested_table('ONE','TWO','THREE','FOUR');
FOR i IN example_one.FIRST .. example_one.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || example_one(i));
END LOOP;
-- Free up memory
example_one := NULL;
END;
/
Output:
Nested TAbles in PLSQL
Explanation:
The output confirms the successful initialization of the nested table, the correct iteration over its elements, and the subsequent freeing up of memory. Each element is displayed with its corresponding index in the output.
Example 3: Addition of the Elements of the Nested Tables
Query:
SET SERVEROUTPUT ON;
DECLARE
TYPE NST_TBL IS TABLE OF NUMBER;
-- Declare two nested tables
var1 NST_TBL := NST_TBL(1, 2);
var2 NST_TBL := NST_TBL(3, 4);
-- result nested table
result NST_TBL;
BEGIN
result := NST_TBL();
result.EXTEND(var1.COUNT);
--addition of the elements
FOR i IN 1..var1.LAST LOOP
result(i) := var1(i) + var2(i);
END LOOP;
-- Display result of addition
DBMS_OUTPUT.PUT_LINE('Result(1): ' || result(1) || ' Result(2): ' || result(2));
END;
/
Output:
PLSQL NESTED Tables
Output:
Elements of the two nested table are added and are stored in new nested table.Element is added with element of the same index.
Conclusion
In conclusion, PL/SQL is a procedural version of Oracle’s SQL. Nested Tables is dynamic arrays, they can grow or shrink as per the scenario. It is used to manage complex data. Variables of nested table type are initialized using the constructor. Elements from the nested tables are accessed using an index. In this article, we have learned about the nested tables in pl/sql.