| Lesson 6 || Nested table queries with PL/SQL |
| Objective || Write a PL/SQL procedure that retrieves nested table data. |
Nested Table Queries with PL/SQL
In this lesson, we will write a PL/SQL block to query an object table that has a nested table.
Let us look at the
SALE_HEADER table closely. This table includes two basic parts, the
SALE_HEADER object type and the
SALE_DETAIL object type.
SALE_DETAIL object type is a nested table, which is referred as a column within the
SALE_HEADER object table. A parent-child (one-to-many) relationship exists between these two object types.
Querying an object table with a nested table within it, the
SALE_HEADER table, for instance, requires a simple cursor and a
FOR LOOP structure.
However, to retrieve the data from the nested table, we must define another
FOR LOOP because the nested table has more than one record for every instance of the object table.
The following SlideShow explains the results of applying this to the example of querying the
Querying Nested Table
The following simulation requires you to build a PL/SQL block that queries a nested table. You will query the
table and its attributes, including the nested table within it, and display the
data by using
PL Sql Block
The next lesson will wrap up this module.