Nested Tables-Up-Database Tables-1

Nested tables are part of the object-relational extensions to Oracle. A nested table, one of the two collection types in Oracle, is very similar to a child table in a traditional parent/ child table pair in the relational model. It is an unordered set of data elements, all of the same datatype, which could be either a built-in datatype or an object datatype. It goes one step further, however, since it is designed to give the illusion that each row in the parent table has its own child table. If there are 100 rows in the parent table, then there are virtually 100 nested tables. Physically, there is only the single parent and the single child table. There are large syntactic and semantic differences between nested tables and parent/child tables as well, and we’ll look at those in this section.

There are two ways to use nested tables. One is in your PL/SQL code as a way to extend the PL/SQL language. The other is as a physical storage mechanism for persistentstorage of collections. I use them in PL/SQL all of the time, but I have never used them as a permanent storage mechanism.

In this section, I’ll briefly introduce the syntax to create, query, and modify nested tables. Then we’ll look at some of the implementation details and what is important to know about how Oracle really stores nested tables.

Nested Tables Syntax

The creation of a table with a nested table is fairly straightforward—it is the syntax for manipulating them that gets a little complex. Let’s use the simple EMP and DEPT tables to demonstrate. We’re familiar with that little data model that is implemented relationally as follows:

To create a table with a nested table, we need a nested table type. The preceding code creates a complex object type, EMP_TYPE, and a nested table type of that, EMP_TAB_ TYPE. In PL/SQL, this will be treated much like an array would. In SQL, it will cause a physical nested table to be created. Here is the simple CREATE TABLE statement that uses it:

The important part of this CREATE TABLE statement is the inclusion of the column EMPS of EMP_TAB_TYPE and the corresponding NESTED TABLE EMPS STORE AS EMPS_NT. This created a real physical table, EMPS_NT, separate from and in addition to the table DEPT_AND_EMP. We add a constraint on the EMPNO column directly on the nested table to make the EMPNO unique as it was in our original relational model. We cannot implement our full data model; however, there is the self-referencing constraint:

ORA-30730: referential constraint not allowed on nested table column

This will simply not work. Nested tables do not support referential integrity constraints, as they cannot reference any other table—even themselves. So, we’ll just skip that requirement for this demonstration (something you cannot do in real life). Next, we’ll populate this table with the existing EMP and DEPT data:

There are two things to notice here:

•\ Only four rows were created. There are really only four rows in theDEPT_AND_EMP table. The 14 EMP rows don’t exist independently.

•\ The syntax is getting pretty exotic. CAST and MULTISET are syntaxmost people have never used. You will find lots of exotic syntax whendealing with object-relational components in the database. TheMULTISET keyword is used to tell Oracle the subquery is expectedto return more than one row (subqueries in a SELECT list havepreviously been limited to returning one row). The CAST is used toinstruct Oracle to treat the returned set as a collection type. In thiscase, we CAST the MULTISET to be an EMP_TAB_TYPE. CAST is a general-­purpose routine not limited to use in collections. For example, ifwe wanted to fetch the EMPNO column from EMP as a VARCHAR2(20)instead of a NUMBER(4) type, we may use the query select cast(empno as ARCHAR2(20) ) e from emp.

All of the data is there in a single column. Most applications, unless they are specifically written for the object-relational features, will not be able to deal with this particular column. For example, ODBC doesn’t have a way to deal with a nested table (JDBC, OCI, Pro*C, PL/SQL, and most other APIs and languages do). For those cases, Oracle provides a way to unnest a collection and treat it much like a relational table:

We are able to cast the EMPS column as a table, and it naturally did the join for us—no join conditions were needed. In fact, since our EMP type doesn’t have the DEPTNO column, there is nothing for us apparently to join on. Oracle takes care of that nuance for us.

So, how can we update the data? Let’s say we want to give department 10 a $100 bonus. We would code the following: