oracle 嵌套表例子,Oracle之嵌套表(了解)-白红宇

oracle 嵌套表例子,Oracle之嵌套表(了解)

阅读量：793 次

发布时间：2023-02-24

本文共 1258 字，大约阅读时间需要 4 分钟。


   
 
   
 
   
??Oracle?????????????
??SQL???????????????
CREATE TABLE department(  deptno NUMBER(2) PRIMARY KEY,  dname VARCHAR(20) NOT NULL);
CREATE TABLE project(  proid NUMBER(4) PRIMARY KEY,  proname VARCHAR2(50) NOT NULL,  prodate DATE NOT NULL,  deptno NUMBER(2),  CONSTRAINT department_project_deptno FOREIGN KEY(deptno) ON DELETE CASCADE);
?????????
CREATE TYPE project_ty AS OBJECT(  proid NUMBER(4),  proname VARCHAR2(50),  prodate DATE);
????????
CREATE TYPE project_nt AS TABLE OF project_ty;
??????????????
CREATE TABLE department(  deptno NUMBER(2) PRIMARY KEY,  dname VARCHAR(20) NOT NULL,  projects project_nt) NESTED TABLE projects STORE AS project_nt_tab_temp;
???????
INSERT INTO department(deptno, dname, projects) VALUES(  1,  'ERP??',  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);
???????
UPDATE TABLE (SELECT projects FROM department WHERE deptno = 1) pro SET VALUE(pro) = project_ty('1001', '?ERP??', TO_DATE('1998-02-12', 'yyyy-mm-dd')) WHERE pro.proid = 1001;
???????????
SELECT * FROM TABLE (SELECT projects FROM department WHERE deptno = 1);