PL/SQL   «Prev  Next»
Lesson 1

Querying Nested Tables and Varrays

As you saw in previous lessons in this course, a collection is an ordered group of elements that are all of the same type. The two types of collections that are defined within Oracle 8i are varrays and nested tables.
Collections can store instances of an object type, be attributes of an object type, and also be parameters to other collections. You can use collections to move data in and out of the database or between client-side applications and stored subprograms. In addition, you can define collection types within a PL/SQL package and use them programmatically within your applications.
A collection consists of an inner table and an outer table. The inner table refers to the enclosing table in which you use a nested table or varray as a data type for a column. The outer table, also known as a nested table, refers to the collection that is implemented as a column within a table. For example, within the schema, DETAIL_TABLE is the inner table, whereas the DETAIL_NEST column within the SALE_HEADER table is the outer table.

Module objectives
When you have completed this module, you will be able to:
  1. Explain the concept of flattening a nested table
  2. Describe the SQL enhancements for nested tables such as THE and TABLE
  3. Query nested tables and varrays
  4. Create a PL/SQL block to query nested tables and varrays
In the next lesson, we will define the concept of flattening a nested table.