PL-SQL Nested Tables

PL-SQL Nested Tables -Learn how to use Nested tables in PL-SQL. Also learn the similarities and difference between Index By Table, VARRAY, Nested Tables with explanation and examples.

A Nested Table is similar to one dimensional array(VARRAY) which can store elements random number of elements.

For an array only declared number of elements are stored, but nested table can store elements any number of elements.

There is no limit on the size of nested tables. The size of a nested table increases dynamically.

Elements can be deleted or added from anywhere in the nested table.

For VARRAY you can add or delete the elements at the end of the array.

An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.

Syntax

TYPE type_name IS TABLE OF Data_type [NOT NULL]; 
 
table_name type_name;

This declaration is similar to the declaration of an index-by table, but there is no INDEX BY clause.

A Nested table can store in a database column. It can further use for simplifying SQL operations where you join a single-column table with a larger table.

An Associative array cannot be stored in the database.

Example program to print the Names in the Nested Table

DECLARE 
   TYPE PRINTARRAY IS TABLE OF VARCHAR2(20); 
  PRINTNAMES PRINTARRAY;
BEGIN 
  PRINTNAMES :=PRINTARRAY('JOHN FORD','LUCY ROY','DANE BRECK','STEPHEN KING','STOT COT');
  FOR I      IN 1..PRINTNAMES.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE(PRINTNAMES(I) );
  END LOOP;
END;

Output

JOHN FORD
LUCY ROY
DANE BRECK
STEPHEN KING
STOT COT

Example Program to initialize values and to extend the size of the nested tables.

DECLARE
TYPE PRINTARRAY IS TABLE OF  VARCHAR2(20);
PRINTNAMES PRINTARRAY:=PRINTARRAY();
BEGIN
  -- PRINTNAMES :=PRINTARRAY('JOHN FORD','LUCY ROY','DANE BRECK','STEPHEN KING','STOT COT');
  --intialize the array size using extend key word
  PRINTNAMES.extend(5);
  --initialize the values to index
  PRINTNAMES(1) := 'JOHN FORD';
  PRINTNAMES(2) := 'LUCY ROY';
  PRINTNAMES(3) := 'DANE BRECK';
  PRINTNAMES(4) := 'STEPHEN KING';
  PRINTNAMES(5) := 'STOT COT';
  PRINTNAMES.extend(10);
   PRINTNAMES(6) := 'BUTTLER COT';
  FOR I         IN 1..PRINTNAMES.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE(PRINTNAMES(I) );
  END LOOP;
END;

Output

JOHN FORD
LUCY ROY
DANE BRECK
STEPHEN KING
STOT COT
BUTTLER COT

Example to program Declare Nested table with %TYPE

DECLARE
  CURSOR e_employees
  IS
    SELECT name FROM employee_tbl1;
TYPE e_list
IS
  TABLE OF employee_tbl1.Name%type ;
  name_list e_list :=e_list();
  counter INTEGER :=0;
BEGIN
  FOR n IN e_employees
  LOOP
    counter            := counter +1;
    name_list.extend;
    name_list(counter)  := n.name; 
    dbms_output.put_line('employee('||counter||'):'||name_list(counter));
  END LOOP;
END;

OUTPUT

employee(1):Tom
employee(2):Pam
employee(3):John
employee(4):Sam
employee(5):Todd
employee(6):Ben
employee(7):Sara
employee(8):Valarie
employee(9):James
employee(10):Russell