[Oracle] 第17章 集合-嵌套表

---

• 嵌套表是一种类似于索引表的结构，也可以用于保存多个数据，而且也可以保存复合类型的数据。

• 嵌套表指的是在一个数据表定义时同时加入其它内部表的定义，这一概念是在 。 可以使用 SQL 进行访问，也可以进行动态扩展。

• 创建嵌套表类型

CREATE [OR REPLACE] TYPE TYPE_NAME 
AS | IS TABLE OF DATA_TYPE [NOT NULL]; 

• 创建表指定嵌套表存储空间名称

定义

CREATE TABLE TABLE_NAME(
    COL_NAME1 COL_TYPE,
    COL_NAME2 COL_TYPE,
    ...
    NESTED_TABLE_COL NESTED_TABLE_TYPE
)
NESTEDED TABLE NESTED_TABLE_COL 
AS STORAGE_TABLESPACE_NAME;

• 简单嵌套表

创建嵌套表类型

 CREATE OR REPLACE TYPE PROJECT_NESTED 
 IS TABLE OF VARCHAR2(50) NOT NULL;

创建嵌套表

CREATE TABLE DEPARTMENT(
    DID         NUMBER,
    DEPTNAME    VARCHAR2(200) NOT NULL,
    PROJECTS    PROJECT_TYPE,
    CONSTRAINT OK_DID IS PRIMARY KEY(DID)
)
NESTED TABLE PROJECTS 
AS PROJECT_NESTED_TABLE;

插入数据

INSERT INTO DEPARTMENT
(DID, DEPTNAME, PROJECTS)
VALUES
(10,'MLKJ',PROJECT_NESTED('JAVA','Andriod'));
COMMIT;

INSERT INTO DEPARTMENT
(DID, DEPTNAME, PROJECTS)
VALUES
(20,'MLKJ出版社',PROJECT_NESTED('JAVA','JAVA WEB'));
COMMIT;

查看数据

SELECT * FROM TABLE(
SELECT PROJECTS FROM DEPARMENT WHERE DID = 20)

更新数据

UPDATE TABLE(SELECT PROJECTS FROM DEPARMENT WHERE DID = 20) PRO
   SET VALUE(PRO) = 'ORACLE'
 WHERE PRO.COLUMN_VALUE = 'JAVA';
COMMIT;

删除数据

DELETE FROM 
       TABLE(SELECT PROJECTS FROM DEPARMENT WHERE DID = 20) PRO
 WHERE PRO.COLUMN_VALUE = 'JAVA WEB';
COMMIT;

• 复杂嵌套表

创建对象

CREATE OR REPLACE TYPE PROJECT_TYPE AS OBJECT
(
    PROJECTID       NUMBER,
    PROJECTNAME     VARCHAR2(50),
    PROJECTFUNDS    NUMBER(18,6),
    PUBDATE         DATE
);

创建嵌套表类型

CREATE OR REPLACE TYPE PROJECT_NESTED 
 IS TABLE OF PROJECT_TYPE NOT NULL;

创建嵌套表

CREATE TABLE DEPARTMENT(
    DID         NUMBER,
    DEPTNAME    VARCHAR2(200) NOT NULL,
    PROJECTS    PROJECT_TYPE,
    CONSTRAINT OK_DID IS PRIMARY KEY(DID)
)
NESTED TABLE PROJECTS 
AS PROJECT_NESTED_TABLE;

插入数据

INSERT INTO DEPARTMENT
(DID, DEPTNAME, PROJECTS)
VALUES
(10,'MLKJ',
PROJECT_NESTED(
    PROJECT_TYPE(1,'java',1000,date'2016-01-01'),
    PROJECT_TYPE(2,'java web',1011,date'2016-02-01'),
    PROJECT_TYPE(3,'oracle,4500,date'2016-03-01')));
COMMIT;

查看数据

SELECT * FROM TABLE(
SELECT PROJECTS FROM DEPARMENT WHERE DID = 10)

修改数据

UPDATE TABLE(SELECT PROJECTS FROM DEPARMENT WHERE DID = 10) PRO
   SET VALUE(PRO) = PROJECT_TYPE(1,'java',5000,date'2016-01-01')
 WHERE PRO.PROJECT_ID = 1;
COMMIT;

删除数据

DELETE FROM 
       TABLE(SELECT PROJECTS FROM DEPARMENT WHERE DID = 10) PRO
 WHERE PRO.PROJECT_ID = 1;
COMMIT;