Nested Table是嵌套表,表中有表。
create type worker_t as object(id varchar2(10), name varchar2(20));create type workers_t as table of worker_t;SQL> create table test (deptno number(4), emp_p workers_t)
2 NESTED TABLE emp_p STORE AS emp_ptab;表已创建。增加数据:
SQL> insert into test values(1, workers_t(worker_t('01','name1'), worker_t('02','name2')));已创建 1 行。SQL> insert into test values(2, workers_t(worker_t('21','name21'), worker_t('22','name22')));已创建 1 行。SQL> insert into test values(3, workers_t(worker_t('31','name31')));已创建 1 行。查询:SQL> select * from test where deptno=1; DEPTNO
----------
EMP_P(ID, NAME)
-------------------------------------------------------------- 1
WORKERS_T(WORKER_T('01', 'name1'), WORKER_T('02', 'name2')) DEPTNO
----------
EMP_P(ID, NAME)
----------------------------------------------------------------ID NAME
---------- --------------------
1
WORKERS_T(WORKER_T('01', 'name1'), WORKER_T('02', 'name2'))
01 name1 1
WORKERS_T(WORKER_T('01', 'name1'), WORKER_T('02', 'name2'))
02 name2SQL> select e.* from test, table(emp_p) e where deptno=1;ID NAME
---------- --------------------
01 name1
02 name2SQL> select value(e) from test, table(emp_p) e;VALUE(E)(ID, NAME)
------------------------------------------------WORKER_T('01', 'name1')
WORKER_T('02', 'name2')
WORKER_T('21', 'name21')
WORKER_T('22', 'name22')
WORKER_T('31', 'name31')SQL> select value(e) from test, table(emp_p) e where deptno=3;VALUE(E)(ID, NAME)
--------------------------------------------------------------WORKER_T('31', 'name31')更新:SQL> update test set emp_p = workers_t(worker_t('33','name33'), worker_t('32','name32')) where deptno=3;已更新 1 行。SQL> select value(e) from test, table(emp_p) e where deptno=3;VALUE(E)(ID, NAME)
--------------------------------------------------------------WORKER_T('33', 'name33')
WORKER_T('32', 'name32')
create type worker_t as object(id varchar2(10), name varchar2(20));create type workers_t as table of worker_t;SQL> create table test (deptno number(4), emp_p workers_t)
2 NESTED TABLE emp_p STORE AS emp_ptab;表已创建。增加数据:
SQL> insert into test values(1, workers_t(worker_t('01','name1'), worker_t('02','name2')));已创建 1 行。SQL> insert into test values(2, workers_t(worker_t('21','name21'), worker_t('22','name22')));已创建 1 行。SQL> insert into test values(3, workers_t(worker_t('31','name31')));已创建 1 行。查询:SQL> select * from test where deptno=1; DEPTNO
----------
EMP_P(ID, NAME)
-------------------------------------------------------------- 1
WORKERS_T(WORKER_T('01', 'name1'), WORKER_T('02', 'name2')) DEPTNO
----------
EMP_P(ID, NAME)
----------------------------------------------------------------ID NAME
---------- --------------------
1
WORKERS_T(WORKER_T('01', 'name1'), WORKER_T('02', 'name2'))
01 name1 1
WORKERS_T(WORKER_T('01', 'name1'), WORKER_T('02', 'name2'))
02 name2SQL> select e.* from test, table(emp_p) e where deptno=1;ID NAME
---------- --------------------
01 name1
02 name2SQL> select value(e) from test, table(emp_p) e;VALUE(E)(ID, NAME)
------------------------------------------------WORKER_T('01', 'name1')
WORKER_T('02', 'name2')
WORKER_T('21', 'name21')
WORKER_T('22', 'name22')
WORKER_T('31', 'name31')SQL> select value(e) from test, table(emp_p) e where deptno=3;VALUE(E)(ID, NAME)
--------------------------------------------------------------WORKER_T('31', 'name31')更新:SQL> update test set emp_p = workers_t(worker_t('33','name33'), worker_t('32','name32')) where deptno=3;已更新 1 行。SQL> select value(e) from test, table(emp_p) e where deptno=3;VALUE(E)(ID, NAME)
--------------------------------------------------------------WORKER_T('33', 'name33')
WORKER_T('32', 'name32')
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货