Oracle PL/SQL - Nested Table Type

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Nested Tables

Introduction

Oracle nested tables can hold an arbitrary number of elements and use sequential numbers as sub-scripts.

You can define equivalent SQL types, and store nested tables in database tables and manipulate them through SQL: 

declare
     type <NestedTable> is table of <ElementType>;
  ...

create or replace type <NestedTable>
is table of <ElementType>;

Demo

SQL>
SQL> declare--   w  w w .  ja  va  2 s .c  o m
  2         type month_nt is table of VARCHAR2(20);
  3         v_month_nt month_nt:=month_nt();
  4         i number;
  5  begin
  6         v_month_nt.extend(3);
  7         v_month_nt(1):='January';
  8         v_month_nt(2):='February';
  9         v_month_nt(3):='March';
 10
 11         v_month_nt.delete(2);
 12         DBMS_OUTPUT.put_line('Count:'||v_month_nt.count);
 13         DBMS_OUTPUT.put_line('Last:'||v_month_nt.last);
 14
 15         i:=v_month_nt.first;
 16         loop
 17              DBMS_OUTPUT.put_line(v_month_nt(i));
 18              i:=v_month_nt.next(i);
 19              if i is null
 20              then
 21                    exit;
 22              end if;
 23         end loop;
 24  end;
 25  /
Count:2
Last:3
January
March

PL/SQL procedure successfully completed.
SQL>

Related Topic