今天才学嵌套表方面,遇见一下问题,请大神指教。
第一宗:采用嵌套表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的时候会报错!
第一宗:采用嵌套表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的时候会报错!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货