PL/SQL Table and Nested Table

 Introduction:

As you know, a datatype is used to classify the data to be stored as number, varchar, boolean etc. But a varible declared using a dataype can hold only a single data value at a time, i.e a single memory location.

 

What if we need to store multiple rows of data in a single variable? There comes the use of collections in Oracle. A variable declared as a collection could store an

 array of data of the same TYPE or ROWTYPE. This is beneficial when we need to pass multiple rows of data between Procedures or need to return multiple records

from functions especially to other languages like Java, C etc.

 

These are the main classifications:

 

1.      PL/SQL TABLE (index-by tables) or NESTED TABLE

2.      VARRAY

3.      RECORD

4.      OBJECT

 

Here I am discussing PL/SQL TABLE (index-by tables) and NESTED TABLE in detail

 

1. PL/SQL TABLE (index-by tables) and NESTED TABLE

 

            These are single-column tables and could be considered as an Array of data but which is unbounded; i.e we cannot set a limit on number of rows like an array

 and that s sometimes an added advantage.

 

Both PL/SQL TABLE and NESTED TABLE has got the same structure and all, but the main difference is, nested tables could be stored in a database

column whereas PL/SQL tables could not be. To explain further,  Nested table Dataypes can be created standalone they could be used for columns in normal database Tables.

 

Declaration:

 

a. NESTED TABLE

 

  a.1 Delcare and use in PL/SQL code:

 

TYPE NTab_List1 is TABLE OF TYPE INTEGER;

 

      Eg:

 CREATE OR REPLACE PROCEDURE PRC_NT

 IS

 

 TYPE NTab_List1 is TABLE OF TYPE INTEGER;                 --create a Collection type TYPE NTab_List1

 TAB NTab_List1;                                                                    --create a variable of NTab_List1 Type

 

 Begin

 

--<code here>

 

 End;

 

 a.2 Declare Outside PL/SQL:

 

CREATE TYPE NTab_List2 AS TABLE OF NUMBER;                -- in SQL Prompt

 

  And use inside PL/SQL:

 

  CREATE OR REPLACE PROCEDURE PRC_NT

  IS

  TAB NTab_List1;                                                                -- Just create a variable of NTab_List1 Type since

                                                                                                --NTab_List1 Collection type already exists

  Begin

 

  --<code here>

 

  End;

 

 

a.3 Declare and use outside PL/SQL code: (Used as Column Datatypes in Tables)

 

 

CREATE TYPE NTab_List2 AS TABLE OF VARCHAR2;

 

CREATE TABLE  NS_TEST

(

 KEY               NUMBER;

 NAME            VARCHAR2(50);

              ADDRESS     NTab_List

            )

             NESTED TABLE VAR3 STORE AS List_Tab;

 

I.  Inserting data into a Nested Table in a column

The address of a customer goes into the single column NTab_List. As I said earlier, here comes the advantage of an unbouded array. The Nested table in the
 column provides the flexibility of inserting varying number of address lines into the column. The column ADDRESS could have any number of records in a single cell.

BEGIN

   INSERT INTO NS_TEST

      VALUES(1001,'Lynn Saunders',

        NTab_List ( 1027 ,

                 North Avenue ,

                 Regent Street ,

                 Nt Valley ,

                 Berligton 

                 74839 )

        );

END;

II. Retrieving data

Get address of customer with customer id=1001

DECLARE

   NTAB NTAB_LIST;

BEGIN

   SELECT NTAB_LIST INTO NTAB FROM NS_TEST

      WHERE ID = 1001;

END;

III. Updation

Suppose the customer with id=1001 needs to change his address. Then

DECLARE

   NEW_NTAB NTAB_LIST:=

     NTAB_LIST ( 892 ,

                 SOUTH CIRCLE ,

                 MALLEY STREET ,

                 89399 );

BEGIN

   UPDATE DEPT

      SET  NTAB_LIST = NEW_NTAB WHERE ID=1001;

END;

 

 

b. PL/SQL TABLE:

             

            The difference is PL/SQL table should always be indexed any of the below types.

 

BINARY_INTEGER, BOOLEAN, LONG, LONG RAW, NATURAL, NATURALN, PLS_INTEGER, POSITIVE, POSITIVEN, SIGNTYPE, and STRING.

 

Declaration:

 

TYPE tab is TABLE OF TYPE INTEGER INDEX BY BINARY_INTEGER

Also, we can use the %TYPE to make use of the dynamic datatype functionality.

For eg:

TYPE TAB_EMP IS TABLE OF EMP%TYPE INDEX BY BINARY_INTEGER;

Usage is similar to that of Nested Table.

Some other key differences between PL/SQL and Nested Tables:

To extend a Nested table, you need to use a built-in procedure EXTEND, but to extend a PL/SQL table, you just increment the subscripts.

The value of an uninitialized Nested table is NULL, but an uninitialized PL/SQL table is empty. So, you can compare nested tables using IS NULL operator.

PL/SQL tables are initially sparse. They can store reference data using a numeric primary key as the index.
PL/SQL supports implicit (automatic) datatype conversion between Arrays and PL/SQL tables.