Database Reference
In-Depth Information
6 mgr number(4),
7 hiredate date,
8 sal number(7, 2),
9 comm number(7, 2)
10 );
11 /
Type created.
EODA@ORA12CR1> create or replace type emp_tab_type
2 as table of emp_type
3 /
Type created.
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:
EODA@ORA12CR1> create table dept_and_emp
2 (deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13),
5 emps emp_tab_type
6 )
7 nested table emps store as emps_nt;
Table created.
EODA@ORA12CR1> alter table emps_nt add constraint
2 emps_empno_unique unique(empno)
3 /
Table altered.
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:
EODA@ORA12CR1> alter table emps_nt add constraint mgr_fk
2 foreign key(mgr) references emps_nt(empno);
alter table emps_nt add constraint mgr_fk
*
ERROR at line 1:
ORA-30730: referential constraint not allowed on nested table column
 
Search WWH ::




Custom Search