Nested table

When programming in PL/SQL we could use SQL nested tables, that behave like (STL C++) vectors of the underlying data.

Being nested tables SQL type, we could store them in a database schema and use them in any PL/SQL block having SELECT access to that type.

Nested tables could be seen as set, an we can apply on them set operators like MULTISET EXCEPT.

As an example, let's create a nested table type of VARCHAR2, declare a few variables ot this type and use them:

declare
  type nt_names_t is table of varchar2(20); // 1.
  family nt_names_t := nt_names_t(); // 2.
  children nt_names_t := nt_names_t();
  parents nt_names_t := nt_names_t();
begin
  family.extend(4); // 3.
  family(1) := 'Mother'; // 4.
  family(2) := 'Father';
  family(3) := 'Son';
  family(4) := 'Daughter';

  children.extend(); // 5.
  children(1) := 'Son';
  children.extend();
  children(2) := 'Daughter';

  parents := family multiset except children; // 6.

  dbms_output.put_line('Parents:');
  for l_row in parents.first() .. parents.last() // 7.
  loop
    dbms_output.put_line(parents(l_row));
  end loop;
  
  dbms_output.put_line('Childrens:');
  for l_row in children.first() .. children.last()
  loop
    dbms_output.put_line(children(l_row));
  end loop;
  
  dbms_output.put_line('Family:');
  for l_row in family.first() .. family.last()
  loop
    dbms_output.put_line(family(l_row));
  end loop;
end;

1. We define locally a nested table type. To create a nested table type in the current schema we would have written:

create or replace type nt_names_t is table of varchar2(20);

2. Before usage, a nested type variable has to be initialized calling the constructor (C++ style).
3. We have to reserve explicitely memory for new elements.
4. In this way we add a new element to the nested table.
5. By default extend() reserves room for one more element.
6. With MULTISET EXCEPT we get a set that is the difference from the left hand to the right hand passed nested tables.
7. An interesting property of nested tables is that we can loop on them using a for loop delimited by its first() and last() element.

Chapter 12 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about collections.