"Nested Table", as a part of Object Relational Extension, in Oracle, has been provided as one of the type of "collection objects" (read array but somewhat different in some sense), the other being "varray". The only points of differences between these two types of collections are, the former is virtually unbounded (with no upper limit to number of elements it may contain) whereas latter is bounded and also while the former may be manipulated easily by "Extended" SQL, the latter may not and requires PL/SQL program to manipulate. So the discussion about Nested Table is considered enough to give a fair idea about the varrays.
As has been said above, the Nested Table is very much similar to a child table in a traditional parent/child pair in the relational model but just has been facilitated with being defined as an object data type to be used to create a column in the normal table. So in a way it gives an illusion that each row in parent table has its own child table. Visually the structure could somewhat resemble as follows -
Table deptemp :
A Live Example -We shall consider the same object (empitax) that we created in our post about Object Table and use it to create our Nested Table type collection (emptaxtab) and its column (empt) in a "deptemp" table.
Step 1 -
Of course creating the object type empitax which we did in out post about Object Table. (not to be repeated here)
Step 2 -
We shall create "Nested Table" type collection
create or replace type emptaxtab as table of empitax;
Step 3 -
Creating the table "deptemp"
create table deptemp (deptno number(3) primary key,
nested table empt store as empnest;
alter table empnest add constraint u_empnest unique (empno);
Observe here that we're creating the column "empt" as type "emptaxtab" of nested table type collection we created in step 2. We are adding "unique" constraint in the statement "alter table ..." to force the "empno" to be unique. The nested table however does not allow to add any foreign key constraint, even the one to refer to its own unique column. The clause "nested table ... store as ..." has been explained at the bottom of this post.
Step 4 -
Data manipulation -
We may insert the rows in this table as -
insert into deptemp
In this same way we may put rows for all the departments and their employees. (observe the constructors "emptaxtab" and "empitax" being called. "constructors" are functions used for initializing (read providing) values for object attributes)
Now suppose we have recruited one more employee in deptno 10, then to add such employee row we require to use an operator "the" or "table" as given below -
Now suppose we want to change salary of Peter working in department 10 from 3000 to 4000 then
Now if George leaves our company, so to delete his row -
The "Table" or "the" operator (in fact a function) may be used to list the data in an un-nest way (similar to relational table join) -
Nested Table storage -Recall the clause "nested table ... store as ...". Actually it implicitly did create a real physical table "empnest" apart from our table "deptemp" and stored it separately. Also look at each "select" statement in "the" or "table" operator. The predicate contains only an equality condition, "select empt from deptemp where deptno = 10". This is virtually selecting the "nested table" for one row in the table, here for department 10. And it must return at least one row or it will result into error "ORA-22908:reference to NULL object value". If this same predicate (for example deptno > 10) returns multiple nested tables, it will return error "ORA-01427:Single row subquery returns more than one row".
Nested Table Limitations -So we may conclude - Oracle considers each row in "deptemp" table to point to a "table" (a nested table virtually only meant for it) and not a set of rows from another table as it happens in parent/child like relational tables. In relational model there may be set of rows in the child table for one parent row. This basic difference has some limitations in the use of nested tables.
The most important limitation is that we can not access the nested table directly, it has to be accessed through the main table (deptemp). So it is not easily possible to answer queries like "what department does peter work in". Also "how do you transfer an employee working in department 10 to department 30" in nested table? In relational table we just may update the "deptno" (a reference column in emp table to point to deptno column of dept table) column of the emp table from 10 to 30.