我在pl/sql查询含有varry类型的表时候显示<Collection>,如何让他一次显示出来呢。
例如记录有
name1,<Collection>
name2,<Collection>查询显示成下面的样式.
name1,111
name1,333
name1,555
name2,66
name2,77
........
例如记录有
name1,<Collection>
name2,<Collection>查询显示成下面的样式.
name1,111
name1,333
name1,555
name2,66
name2,77
........
http://topic.csdn.net/u/20090729/15/ba62e205-84f0-4577-83ad-b68c23f9093b.html?seed=1445841257&r=58700310#r_58700310
2 ( ename varchar2(30),
3 empno number
4 )
5 /
Type created.sql> create type empTblType as table of empType
2 /
Type created.sql> create type projType as object
2 ( name varchar2(30),
3 start_date date,
4 end_date date
5 )
6 /
Type created.sql> create type projTblType as table of projType
2 /
Type created.
Now I need some data to query:sql> create table emp as select * from scott.emp;
Table created.sql> create table dept as select * from scott.dept;
Table created.sql> create table proj as
2 select username name,
3 created start_date,
4 created+7 end_date,
5 (mod(rownum,4)+1)*10 deptno
6 from all_users
7 where rownum <= 20
8 /
Table created.And now we can query the data:sql> select dname,
2 cast( multiset( select ename, empno
3 from emp
4 where emp.deptno = dept.deptno ) as empTblType ) emps,
5 cast( multiset( select name, start_date, end_date
6 from proj
7 where proj.deptno = dept.deptno ) as projTblType ) projs
8 from dept
9 /
DNAME EMPS(ENAME, EMPNO) PROJS(NAME, START_DATE, END_DA
-------------- ------------------------------ ------------------------------
ACCOUNTING EMPTBLTYPE(EMPTYPE('CLARK', 77 PROJTBLTYPE(PROJTYPE('DBSNMP',
82), EMPTYPE('KING', 7839), EM '28-AUG-01', '04-SEP-01'), PR
PTYPE('MILLER', 7934)) OJTYPE('OPS$TKYTE', '24-SEP-01
', '01-OCT-01'))RESEARCH EMPTBLTYPE(EMPTYPE('A', 7369), PROJTBLTYPE(PROJTYPE('SYS', '2
EMPTYPE('JONES', 7566), EMPTY 8-AUG-01', '04-SEP-01'), PROJT
PE('SCOTT', 7788), EMPTYPE('AD YPE('OPS$ORA817', '28-AUG-01',
AMS', 7876), EMPTYPE('FORD', 7 '04-SEP-01'), PROJTYPE('OPS$C
902)) LBECK', '09-SEP-01', '16-SEP-0
1'))SALES EMPTBLTYPE(EMPTYPE('ALLEN', 74 PROJTBLTYPE(PROJTYPE('SYSTEM',
99), EMPTYPE('WARD', 7521), EM '28-AUG-01', '04-SEP-01'), PR
PTYPE('MARTIN', 7654), EMPTYPE OJTYPE('SCOTT', '28-AUG-01', '
('BLAKE', 7698), EMPTYPE('TURN 04-SEP-01'), PROJTYPE('A', '20
ER', 7844), EMPTYPE('JAMES', 7 -SEP-01', '27-SEP-01'))
900))OPERATIONS EMPTBLTYPE() PROJTBLTYPE(PROJTYPE('OUTLN',
'28-AUG-01', '04-SEP-01'), PRO
JTYPE('CTXSYS', '28-AUG-01', '
04-SEP-01'))