PL/SQL Table and Nested Table

                                                    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 anarray 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 N Tab_List1 is TABLE OF TYPE INTEGER;       --create a Collection typeTYPENTab_List1

TAB NTab_List1;                                                                    --create a variable of NTab_List1 Type

 Begin

--

End;