PL/SQL   «Prev  Next»

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.
The 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 SALE_HEADER table:

Querying Nested Table
The following simulation requires you to build a PL/SQL block that queries a nested table. You will query the SALE_HEADER table and its attributes, including the nested table within it, and display the data by using DBMS_OUTPUT.
PL Sql Block
The next lesson will wrap up this module.