ANBOB™

昨看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);

打赏

微信扫一扫，打赏作者吧～