SQL-Oracle09嵌套表与可变数组

嵌套表（了解）

嵌套表：在一个表中还包含了一个子表

 
//------------------------------------------------------
CREATE TABLE department(
 deptno NUMBER(2) PRIMARY KEY NOT NULL ,
 dname VARCHAR2(50) NOT NULL
) ;
CREATE TABLE project(
 proid NUMBER(4) PRIMATY KEY NOT NULL,
 proname VARCHAR2(50) NOT NULL ,
 prodata DATE  NOT NULL ,
 deptno NUMBER(2) ,
 CONSTRAINT department_project_deptno FOREIGN KEY(deptno) ON DELETE CASCADE
) ;

 但是，这种操作是最通用的操作，而且本身也属于一个最正确的操作，
但是再Oracle中引入了嵌套表的概念，可以直接将项目表作为department表的字段类型，
达到嵌套的功能。
 但是，如果想完成一个嵌套表的制作，则首先要保证一点：
因为数据库在创建数据表的时候都要指定字段的类型，所以嵌套表本身也需要同样指定
类型，那么这种类型就需要单独定义。
CREATE TYPE project_ty AS OBJECT(
 proid NUMBER(4),
 proname VARCHAR2(50),
 prodate DATE
) ;
/

创建类型成功后并不意味着类型可以直接使用，因为此类型为一个完整类型，所以
要为此类型指定一个名称。

CREATE TYPE project_nt AS TABLE OF project_ty ;
/

以上操作表示以后直接使用project_nt表示project_ty类型，就类似于varchar2表示
字符串时一样的，此时可以使用此类型创建department表
CREATE TABLE department(
 deptno NUMBER(2) PRIMARY KEY NOT NULL,
 daname VARCHAR() NOT NULL,
 projects project_nt 
) NESTED TABLE projects STORE AS project_nt_tab_temp ;

对于插入数据来讲，需要指定一个project_ty的类型
INSERT INTO department(deptno,dname,projects)
VALUES (1,'技术部',
 project_nt(
  project_ty(1001,'ERP',SYSDATE) ,
  project_ty(1002,'CRM',SYSDATE) ,
  project_ty(1003,'OA',SYSDATE)
 )
) ;
 此时，查询嵌套表，可以返回多个项目的完整性约束

SELECT * FROM department ;
如果，此时需要查看一个部门的全部项目的话，则需要查询嵌套表
SELECT * FROM TABLE(SELECT projects FROM department WHERE deptno=1) ;

eg：更新项目编号为10001的项目名称，将此项目名称更新为“测试项目”

UPDATE TABLE (SELECT projects FROM department WHERE deptno=1) pro
SET VALUE(pro)=project_ty('1001','测试项目',
 TO_DATE('1998-02-12','yyyy-mm-dd')) WHERE pro.proid=1001 ;

可变数组（了解）

  可变数组就属于嵌套表的升级版，在可变数组中，实际上就是将内部的嵌套表
内容的长度进行了限制。

 例如：一个部门有多个工人，如果按照可变数组的做法，

CREATE TYPE worker_info AS OBJECT(
 id NUMBER ,
 name VARCHAR2(50) ,
 sex VARCHAR2(6)
) ;
/

下面再定义数组类型

CREATE TYPE worker_info_list AS VARRAY(10) OF worker_info ;
/

定义部门表，一个部门中可能存在多个工人。

DROP TABLE department ;
CREATE TABLE department(
 deptno NUMBER(2) PRIMARY KEY NOT NULL ,
 dname VARCHAR2(50) NOT NULL ,
 workers worker_info_list
) ;

插入测试数据：

INSERT INTO department(deptno,dname,projects)
VALUES(
 20,'后勤部'
 worker_info_list(
  worker_info(1,'张三','男') ,
  worker_info(2,'李四','女') ,
  worker_info(3,'王五','男')
 )
);

SELECT * FROM department ;