最近项目中需要用到oracle的项目表
学习了一下
具体代码如下
SQL> create type depscore_t as object(
2 depid number,
3 score number);
4 /
类型已创建。
SQL> create type depscores_t as table of depscore_t;
2 /
类型已创建。
SQL> create table befscore(proid number,
2 depscore depscores_t)
3 nested table depscore store as depscoretab;
表已创建。
1* insert into befscore values(1,depscores_t(depscore_t(1,1),depscore_t(2,2)))
SQL> /
已创建 1 行。
SQL> insert into befscore values(2,depscores_t(depscore_t(2,2),depscore_t(3,4)));
已创建 1 行。
SQL> insert into befscore values(3,depscores_t(depscore_t(3,5),depscore_t(5,5)));
已创建 1 行。
SQL> select * from befscore where proid=1;
PROID
----------
DEPSCORE(DEPID, SCORE)
--------------------------------------------------------------------------------
1
DEPSCORES_T(DEPSCORE_T(1, 1), DEPSCORE_T(2, 2))
SQL> select e.* from befscore,table(depscore) e where proid=1;
DEPID SCORE
---------- ----------
1 1
2 2
SQL> select e.depid from befscore,table(depscore) e where proid=1;
DEPID
----------
1
2
SQL> select value(e) from befscore,table(depscore) e;
VALUE(E)(DEPID, SCORE)
--------------------------------------------------------------------------------
DEPSCORE_T(1, 1)
DEPSCORE_T(2, 2)
DEPSCORE_T(2, 2)
DEPSCORE_T(3, 4)
DEPSCORE_T(3, 5)
DEPSCORE_T(5, 5)
已选择6行。
SQL> update befscore set depscore=depscores_t(depscore_t(8,8),depscore_t(7,7))
2 where proid=1;
已更新 1 行。
1* select e.depid,score from befscore,table(depscore) e
SQL> /
DEPID SCORE
---------- ----------
2 2
3 4
3 5
5 5
8 8
7 7
已选择6行。
SQL> select e.depid id,score depscore from befscore,table(depscore) e;
ID DEPSCORE
---------- ----------
2 2
3 4
3 5
5 5
8 8
7 7
已选择6行。
