Oracle PL/SQL Nested Tables

Oracle PL/SQL Nested Tables

Nested tables are similar to index by table but these can be stored in database columns but index by tables cannot be stored in database columns.

A Nested tablecan be considered as a single-column table that can either be in memory, or as a column in a database table. A nested table is quite similar to a VARRAY with the exception that the order of the elements is not static. Elements can be deleted or added anywhere in the nested table where as a VARRAY can only add or delete elements from the end of the array. Nested Table is known as a sparse collection because a nested table can contain empty elements.

Nested tables are a superior choice when:

• You need to delete or update some elements, but not all the elements at once.
• The index values are not consecutive.
• We don’t have any predefined upper bound for index values.
  

Example 1 of Pl/SQL Nested Table

DECLARE
TYPE n_tab_T IS TABLE OF NUMBER;
nt n_tab_T := n_tab_T();

BEGIN
FOR i IN 1..10 LOOP
nt.EXTEND;
nt(i) := i;
END LOOP;
END;

Example 2 of Pl/SQL Nested Table

Suppose we have a more complex beer type:

create type BeerBrand as object (
                name char(20),
                kind char(10),
                color char(10) );

We may create a type that is a nested table of objects of this type by:

Create type BeerTableBrand as table of BeerBrand;

Define a relation of manufacturers that will nest their beers inside.

create table manfs (
             name char(30),
             addr AddrType,
             beers BeerTableBrand)
nested table beers store as BeerTable;

The last line in the create table statement indicates that the nested table is not stored "in-line" with the rest of the table
(Oracle maintains pointers between tables); you cannot refer to BeerTable in any query!

Inserting into nested table

insert into manfs values
(’Budweiser’,
AddrType(’LoopRoad’,’Boga’,’CA’,56789),
BeerTableBrand(
BeerBrand(’sweet’,’ale’,’yellow’),
BeerBrand(’sour’,’lager’,’pale’)
)
);

Querying the nested table

Example: List the beers made by Budweiser:

select beers from manfs
where name = ’Budweiser’;

This query gives you a single value that looks like this:

BeerTableBrand(
BeerBrand(’sweet’,’ale’,’yellow’),
BeerBrand(’sour’,’lager’,’pale’))


More on difference between Nested Table, Varray & Index-by Tables
(Click on the image to view full size)