今天才学嵌套表方面,遇见一下问题,请大神指教。
第一宗:采用嵌套表SQL> 
SQL> create type bookobj as object(
  2  title varchar2(40),
  3  author varchar2(40),
  4  catalog_number number(4)
  5  );
  6  /
 
Type created
 
SQL> 
SQL> create type
  2  booklist as table of bookobj;
  3  /
 
Type created
 
SQL> 
SQL> create table
  2  course_material(
  3  department
  4  char(3),
  5  course
  6  number(3),
  7  required_reading booklist
  8  ) nested table required_reading store as
  9  required_tab;
 
Table created
 
SQL> 
SQL> declare
  2  v_books
  3  booklist:=booklist(bookobj('ssss','www',444));
  4  begin
  5  insert
  6  into course_material
  7  values('cs',101,booklist(bookobj('www','bbb',1),bookobj('aa','dd',33)));
  8  insert into course_material
  9  values('his',301,v_books);
 10  end;
 11  /
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> insert into course_material
  2  values('ss',102,booklist(bookobj('w','b',1),bookobj('a','d',3)));
 
1 row inserted
 
SQL> select * from course_material d,table(d.required_reading) emp;
 
DEPARTMENT COURSE REQUIRED_READING TITLE                                    AUTHOR                                   CATALOG_NUMBER
---------- ------ ---------------- ---------------------------------------- ---------------------------------------- --------------
cs            101 <Object>         www                                      bbb                                                   1
cs            101 <Object>         aa                                       dd                                                   33
his           301 <Object>         ssss                                     www                                                 444
ss            102 <Object>         w                                        b                                                     1
ss            102 <Object>         a                                        d                                                     3
 
SQL> 
SQL> update the(select required_reading from course_material
  2  where department='his' )
  3  set
  4  catalog_number = catalog_number + 10
  5  where catalog_number = 444;
 
1 row updated
 
SQL> select * from course_material d,table(d.required_reading) emp;
 
DEPARTMENT COURSE REQUIRED_READING TITLE                                    AUTHOR                                   CATALOG_NUMBER
---------- ------ ---------------- ---------------------------------------- ---------------------------------------- --------------
cs            101 <Object>         www                                      bbb                                                   1
cs            101 <Object>         aa                                       dd                                                   33
his           301 <Object>         ssss                                     www                                                 454
ss            102 <Object>         w                                        b                                                     1
ss            102 <Object>         a                                        d                                                     3
 
SQL> 
这样用嵌套表进行dml操作时没有问题的
第二种,采用可变数组---------------------------------------------------------------------
SQL> CREATE TYPE comm_info AS OBJECT(
  2  no number(3),
  3  comm_type varchar2(20),
  4  comm_no varchar2(30)
  5  );
  6  /
 
Type created
 
SQL> CREATE TYPE comm_info_list AS VARRAY(50) OF comm_info;
  2  /
 
Type created
 
SQL> 
SQL> create table user_info
  2    (user_id number(6),
  3    user_name varchar2(20),
  4    user_comm comm_info_list);
 
Table created
 
SQL> 
SQL> insert into user_info values(1,'mary',comm_info_list(comm_info(1,'手机','13651401919'),comm_info(2,'呼机','1281234567')));
 
1 row inserted
SQL> insert into user_info values(2,'carl',comm_info_list(comm_info(1,'手机','13901018888'), comm_info(2,'呼机','1281234567')));
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from user_info d,table(d.user_comm) emp;
 
USER_ID USER_NAME            USER_COMM   NO COMM_TYPE            COMM_NO
------- -------------------- --------- ---- -------------------- ------------------------------
      1 mary                 <Object>     1 手机                 13651401919
      1 mary                 <Object>     2 呼机                 1281234567
      2 carl                 <Object>     1 手机                 13901018888
      2 carl                 <Object>     2 呼机                 1281234567
 
SQL> 
SQL> update the(select user_comm from user_info  where user_id=2 )  set
  2   comm_no = '18682846677' where comm_no = '13901018888';
 
update the(select user_comm from user_info  where user_id=2 )  set
 comm_no = '18682846677' where comm_no = '13901018888'
 
ORA-25015: 不能在嵌套表视图列中执行 DML
 
SQL> update the(select user_comm from user_info  where user_id=2 )  set comm_no = '18682846677' where comm_no = '13901018888';
 
update the(select user_comm from user_info  where user_id=2 )  set comm_no = '18682846677' where comm_no = '13901018888'
 
ORA-25015: 不能在嵌套表视图列中执行 DML
 
SQL> 
但类似的例子用在可变数组时,执行update操作时,就报ORA-25015: 不能在嵌套表视图列中执行 DML的错误哦。请问第一种嵌套表的形式和第二种可变数组的形式上有什么不一样吗?为什么对第二种可变数据进行update的时候会报错!