|Lesson 6|| Nested tables |
|Objective|| Determine when to use nested tables in querying.|
Querying Nested Tables using PL/SQL
A nested table is a single-dimension, unbounded collection of homogenous elements. It is initially dense and can become sparse through deletions. The association of a nested table with another table is similar to a parent-child relationship. You have the option of adding or deleting records from the nested table.
Therefore, when you define a nested table, you define its attributes and associate it with an object table.
When you add records to the nested table, it is considered dense. When you delete records from the nested table, it becomes sparse. The primary difference between a nested table and a varray is that a nested table is unbounded. In other words, there is no preset limit on the maximum number of elements that the table can hold. On the other hand, a varray is bounded.
You must specify an upper limit that indicates how large the varray can be. After you define the upper limit, the varray is always considered dense.
The following example defines
DETAIL_TABLE as a nested table, each row of which contains an object of type SALE_DETAIL. To represent the sale details, a nested table is a better choice than a varray for the following reasons:
- Querying the contents of sale details is likely to be a requirement for most applications. This is an inefficient operation for a varray because a varray has a fixed number of elements.
On the other hand, a table can have unlimited number of elements (that is, records).
- Indexing on sale details data may be a requirement for some applications. A nested table allows indexing. However, this is not possible with a varray.
- There is no practical upper bound on the number of sale details on a sale. Using a nested table does not require specifying an upper bound; using a varray does.
The following Slide Show explains the three steps used in creating a nested table and associating it with a different table:
Most of the time, our programming tasks are fairly straightforward and very specific to the requirement at hand. At other times, however, we write more generic kinds of code. For those situations, the Any datatypes might come in very handy. The Any types were introduced in Oracle9i Database and are very different from any other kind of datatype available in an Oracle database. They let you dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type. You can use these types (and the methods defined for them, as they are object types) to do things like determine the type of data stored in a particular nested table without having access to the actual declaration of that table type. The Any datatypes include AnyType, AnyData, and AnyDataSet.
- First, create an object SALE_DETAIL with its attributes as defined
- After creating the object, create a nested table using the following DDL statement:
- After creating the nested table, create a different table and associate the nested table with it, as shown within the DDL statement.
Netsted Oracle Tables
We will look in more detail at querying varrays and nested tables later in the course.
In the next lesson, you will learn how to write SQL to query an object table.
Nested Table - Quiz
Click the Quiz link below to test your knowledge of object-relational concepts.
Nested Table - Quiz