Database Reference
In-Depth Information
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:
EODA@ORA12CR1> insert into dept_and_emp
2 select dept.*,
3 CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm
4 from SCOTT.EMP
5 where emp.deptno = dept.deptno ) AS emp_tab_type )
6 from SCOTT.DEPT
7 /
4 rows created.
There are two things to notice here:
•
Only four rows were created. There are really only four rows in the
DEPT_AND_EMP
table. The 14
EMP
rows don't exist independently.
The syntax is getting pretty exotic.
•
CAST
and
MULTISET
are syntax most people have never
used. You will find lots of exotic syntax when dealing with object-relational components in
the database. The
MULTISET
keyword is used to tell Oracle the subquery is expected to return
more than one row (subqueries in a
SELECT
list have previously been limited to returning
one row). The
CAST
is used to instruct Oracle to treat the returned set as a collection type. In
this case, we
CAST
the
MULTISET
to be a
EMP_TAB_TYPE.
CAST
is a general-purpose routine not
limited to use in collections. For example, if we 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
VARCHAR2(20) ) e from emp
.
We're now ready to query the data. Let's see what one row might look like this:
EODA@ORA12CR1> select deptno, dname, loc, d.emps AS employees
2 from dept_and_emp d
3 where deptno = 10
4 /
DEPTNO DNAME LOC EMPLOYEES(EMPNO, ENAME, JOB,
---------- -------------- ------------- ----------------------------
10 ACCOUNTING NEW YORK EMP_TAB_TYPE(EMP_TYPE(7782,
'CLARK', 'MANAGER', 7839, '0
9-JUN-81', 2450, NULL), EMP_
TYPE(7839, 'KING', 'PRESIDEN
T', NULL, '17-NOV-81', 5000,
NULL), EMP_TYPE(7934, 'MILL
ER', 'CLERK', 7782, '23-JAN-
82', 1300, NULL))
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 un-nest a collection and treat it much like a relational table:
EODA@ORA12CR1> select d.deptno, d.dname, emp.*
2 from dept_and_emp D, table(d.emps) emp
3 /
