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:

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:

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:

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.

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;