PL/SQL   «Prev 

Updating rows within a Nested Table Example

Advice on Oracle Nested Tables

The new object-relational database contains an interesting pointer structure that permits a single cell in an entity to encapsulate a separate entity. In this way structures can be created where tables can be nested within other tables. This means that values in a single column inside a table can contain an entire table in an object-relational database. In turn, these child tables can have single column values that point to other tables.
This new data structure presents exciting possibilities for modeling aggregate objects, even though applications for it may not be obvious. Database designers can create a structure in C++ object-oriented databases, such as Objectivity, where an object contains a list of pointers. Each of these pointers refers to a separate list of pointers and these pointers point to other objects in the database. This structure is known as **char in the C programming language, which is known as a pointer to a pointer to a character.
This structure is implemented (beginning with Oracle 9i) with a store table. A store table is an internal table that is tightly linked to the parent table and the data storage characteristics of the parent table are inherited by the store table. These characteristics include the initial extent of the table as well as the size of any new extent.
A cell is defined as a pointer to a table in the highest level table and each column value within the column pointing to a whole table must contain a pointer to a table with exactly the same definition. In other words, every pointer within the column is restricted to pointing to tables with an identical definition.
In practice, it may appear that each cell points to a whole table, but the object/relational databases actually implement this structure with the special store table.
A store table is essentially nothing more than an internal table with a fixed set of columns that is subordinate to the parent table. A simple example will illustrate the use of this data structure. Returning to the university database, the database has a many-to-many relationship between courses and student entities. A course has many students, and a student can take many courses. This relationship between students and courses would be implemented in a traditional relational system by creating a junction table between the student and course entities. The primary keys from the student and course tables would then be copied into the junction table. This table is called grade in our example, and the grade entity contains the student_ID and course_ID columns as foreign keys. Let us see how this could be implemented using pointers to whole tables. In a traditional relational implementation, to generate a class schedule for a student we would need to select the student row, join with the GRADE table, and finally join with the CLASS table, as follows:

       student_last_name = 'Burleeze'
       STUDENT.student_ID = GRADE.student_ID
       GRADE.course_ID = COURSE.course_ID;

We can avoid the three-way SQL join of the tables by choosing to create a store table that is subordinate to the STUDENT table.
This table would contain the course_name, course_date, and grade for each student:
 student_list (student_full_name full_name,
 student_full_address   full_address,
 grade CHAR(1)); 

CREATE TYPE student_list_type AS TABLE OF student_list;

  course_name           VARCHAR(20),
  dept_ID               NUMBER(4),
  credit_hrs            NUMBER(2),
  student_roster        student_list_type);

We see here that the student_roster column of the COURSE table contains a pointer to a table of TYPE student_list_type. While it appears that each distinct column value points to a whole table, the column actually points to a set of rows within the store table. The store table is common to all of the columns that contain this pointer structure. The store table also contains a special OID that points to the owner of the row in the parent table.

Example of update syntax

Location 1 The UPDATE statement with the TABLE keyword
Location 2 The SELECT statement for selecting a particular record from the parent table
Location 3 The SET clause to update the value within a nested table
Location 4 The WHERE clause to select a particular row of the nested table