昨看tom的书偶然发现了cast multiset这奇怪语法，回来试一下，主要是为了告诉查询返回是一个set集合，向嵌套表或varray column插入数据使用
现在做4个测试
SQL> conn anbob/anbob
Connected.
SQL> create table class (id int primary key,name varchar2(20));
Table created.
SQL> create table student(id int primary key,name varchar2(20),cid int,constraint fk_stu_cid foreign key (cid) references class(id));
Table created.
SQL> insert into class values(1,'english');
1 row created.
SQL> insert into class values(2,'Chinese');
1 row created.
SQL> insert into student values(1,'zhangsan',1);
1 row created.
SQL> insert into student values(2,'lisi',1);
1 row created.
SQL> insert into student values(3,'wangwu',2);
1 row created.
SQL> commit;
Commit complete.
SQL> select c.*,(select * from student s where s.cid=c.id) from class c;
select c.*,(select * from student s where s.cid=c.id) from class c
*
ERROR at line 1:
ORA-00913: too many values
SQL> create or replace type class_tab_sname
2 as table of varchar2(20);
3 /
Type created.
SQL> select c.*,cast(multiset(select s.name from student s where s.cid=c.id) as class_tab_sname) sname from class c;
ID NAME SNAME
---------- ------------------------------ ----------------------------------------
1 english CLASS_TAB_SNAME('zhangsan', 'lisi')
2 Chinese CLASS_TAB_SNAME('wangwu')
SQL> create or replace type type_stu as object
2 (id int,name varchar2(20));
3 /
Type created.
SQL> create or replace type type_stu_tab as table of type_stu;
2 /
Type created.
SQL> create table class_stu(
2 id int,
3 stu type_stu_tab)
4 nested table stu store as stu_tab;
Table created.
SQL> insert into class_stu
2 select c.id,cast(multiset(select s.id,s.name from student s where s.cid=c.id) as type_stu_tab) stab from class c;
2 rows created.
SQL> col stu for a100
SQL> select * from class_stu;
ID STU(ID, NAME)
---------- ----------------------------------------------------------------------------------------------------
1 TYPE_STU_TAB(TYPE_STU(1, 'zhangsan'), TYPE_STU(2, 'lisi'))
2 TYPE_STU_TAB(TYPE_STU(3, 'wangwu'))
SQL> create table class_stu2(
2 id int,
3 stu type_stu)
4 ;
Table created.
SQL> insert into class_stu2
2 select c.id,cast(multiset(select s.id,s.name from student s where s.cid=c.id) as type_stu) from class c;
select c.id,cast(multiset(select s.id,s.name from student s where s.cid=c.id) as type_stu) from class c
*
ERROR at line 2:
ORA-22907: invalid CAST to a type that is not a nested table or VARRAY
SQL> insert into class_stu2
2 values (1,type_stu(1,'anbob'));
1 row created.
SQL> select * from class_stu2;
ID STU(ID, NAME)
---------- ----------------------------------------------------------------------------------------------------
1 TYPE_STU(1, 'anbob')
SQL> create or replace type type_stu_list as varray(10) of type_stu;
2 /
Type created.
SQL> create table class_stu3(
2 id int,
3 stu type_stu_list);
Table created.
SQL> insert into class_stu3
2 select c.id,cast(multiset(select s.id,s.name from student s where s.cid=c.id) as type_stu_list) from class c;
2 rows created.
SQL> select * from class_stu3;
ID STU(ID, NAME)
---------- ----------------------------------------------------------------------------------------------------
1 TYPE_STU_LIST(TYPE_STU(1, 'zhangsan'), TYPE_STU(2, 'lisi'))
2 TYPE_STU_LIST(TYPE_STU(3, 'wangwu'))
note: cast a type only nested table or VARRAY,if column datatype is only an simple type , you can insert use typename(xx,xx);