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.
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
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:
More on difference between Nested Table, Varray & Index-by Tables
(Click on the image to view full size)