pl/sql 嵌套表

嵌套表指的是在一个数据表定义同时加入其他内部表的定义
语法

CREATE [OR REPLACE] TYPE 类型名称 AS|IS TABLE OF 数据类型 [NOT NULL]; /

例:

SQL> CREATE OR REPLACE TYPE project_nested IS TABLE OF VARCHAR2(50) NOT NULL;
  2  /

类型已创建。
--创建表指定嵌套表并存储空间名称
CREATE TABLE 表名称
字段名称   类型,
....
--嵌套表字段名称 嵌套表类型
) NESTED TABLE 嵌套表字段名称 STORE AS 储存空间名称;


SQL> CREATE TABLE department (
  2  did  NUMBER,
  3  deptname VARCHAR2(30) NOT NULL,
  4  projects  project_nested ,
  5  CONSTRAINT pk_did PRIMARY KEY(did)
  6  )NESTED TABLE projects STORE AS projecs_nestd_table;

表已创建。

增加数据

INSERT INTO DEPARTMENT(DID,DEPTNAME,PROJECTS)VALUES(10,'RIAN',project_nested('python','c++'));
INSERT INTO DEPARTMENT(DID,DEPTNAME,PROJECTS)VALUES(20,'WIND',project_nested('java','c'));
commit;

将嵌套表变成数据表查询

 SELECT *FROM TABLE     (SELECT  projects FROM DEPARTMENT WHERE did=20);

COLUMN_VALUE
----------------------------------------------------------------------------------------------------
java
c

更新嵌套表

update table (select PROJECTS from DEPARTMENT where did=10) pro
    SET value(pro)='upload';

更新后的嵌套表

SQL> SELECT *FROM TABLE
  2  (SELECT  projects FROM DEPARTMENT WHERE did=10);

COLUMN_VALUE
----------------------------------------------------------------------------------------------------
upload
upload

删除嵌套表中数据

SQL>     DELETE FROM TABLE (
  2      SELECT projects FROM department where did=10
  3      );

定义复合类型的嵌套表
语法:

CREATE [OR REPLACE] TYPE 类型名称 AS OJBECT(
列名称  数据类型,
列名称  数据类型,
...
);
/

SQL> CREATE OR REPLACE TYPE  project_type AS OBJECT(
  2      projectid NUMBER,
  3      projectname VARCHAR2(50),
  4      projectfunds NUMBER,
  5      PUBDATE DATE
  6      );
  7      /

类型已创建。