嵌套表:在一个表中还包含另外一个子表
首先为嵌套表指定类型,该类型需要单独定义
SQL> CREATE TYPE project_ty AS OBJECT(
priod NUMBER(4),
proname VARCHAR2(30),
prodate DATE
);
/
该类型创建成后,不意味着此类型能够直接使用,要为此类型指定一个名称
SQL> CREATE TYPE project_nt AS TABLE OF project_ty
/
这样就可以使用project_nt表示project_ty类型,现在根据此类型创建department表
SQL> CREATE TABLE department(
deptno NUMBER(2) PRIMARY
KEY,
dname VARCHAR2(50) NOT
NULL,
projects project_nt
)NESTED TABLE projects STORE AS project_nt_tab_temp;
如果要进行数据插入的话
SQL> INSERT INTO department(deptno,dname,projects)
VALUES(
1,'tech',
project_nt(
project_ty(1001,'ERP',SYSDATE),
project_ty(1002,'CRM',SYSDATE)
)
);
查询部门表,可以返回多个项目
SQL> SELECT * FROM department;
如果需要查看一个部门的全部项目的话,查询嵌套表
SQL> SELECT * FROM TABLE
(SELECT projects FROM department WHERE
deptno=1);
更新编号为1001的项目名称
SQL> UPDATE TABLE (SELECT projects FROM department WHERE
deptno=1) pro
SET VALUES(pro)=project_ty('1001','APR',SYSDATE) WHERE
pro.proid=1001;