新的情况:
世界真是太奇妙了,就以jiezhi()的例子来说吧(环境为PL/SQL):
insert into t values('insert into t0 values(1,''James'')');
这句最后有分号的,可以运行。
insert into t0 values(1,'James');
这句也有分号,却不能运行,把分号去掉就可以运行了。如果仍要带分号运行
必须写成
begin
insert into t0 values(1,'James');
end;
绝吧。为什么要说这么多,因为我的问题最终就跟这个分号有关系。
我把jiezhi()的例子中的
insert into t values('insert into t0 values(1,''James'')');
insert into t values('insert into t0 values(2,''Mike'')');
改成了
insert into t values('begin insert into t0 values(1,''James'');end;');
insert into t values('begin insert into t0 values(2,''Mike''); end;');
请注意其中的变化!!!
而存储过程是这样的:
create or replace procedure test is
cursor c is select text from t;
s t.text%type;
begin
open c;
fetch c into s;
while c%found loop
execute immediate s;
commit;
fetch c into s;
end loop;
close c;
exception
when others then
dbms_output.put_line('error!!!');
rollback;
end test;
然后
declare
begin
test;
end;
测试通过,而且记录也增加进去了.
世界真是太奇妙了,这是为什么呢?
declare
*
ERROR 位于第 1 行:
ORA-03113: end-of-file on communication channel
可是我其它的存储过程对表进行增加删除都可以啊?为什么会这样?!!!
还有一个问题就是:(与当前问题无关哦)
如果程序中这样写:
open c;
fetch c into s;
while c%found loop
execute immediate s;
fetch c into s;
--------请注意这里
if s='select' then
return;
---------------------
end loop;
close c;
exception
when others then
dbms_output.put_line('error!!!');
rollback;
。。。。。
也就是在中间出现return退出存储过程后,游标会不会自动关闭,
如果不会,应该怎么办?会有什么问题呢?
谢谢赐教。
declare
str varchar(2000);
type t_sor is ref cursor;
v_sor t_sor;
begin
str:='insert into test values(1,2)';
execute immediate str;
commit;
open v_sor for select * from test;
loop
fetch v_sor into ...;
exit when v_sor%notfound;
dbms_output.put_line(...);
end loop;
close v_sor;
end;
/
SQL> insert into t values('insert into t0 values(1,''James'')');
1 row inserted
SQL> insert into t values('insert into t0 values(2,''Mike'')');
1 row inserted
SQL> select * from t;
TEXT
--------------------------------------------------------------------------------
insert into t0 values(2,'Mike')
insert into t0 values(1,'James')
create or replace procedure test is
cursor c is select text from t;
s t.text%type;
begin
open c;
fetch c into s;
while c%found loop
execute immediate s;
fetch c into s;
end loop;
close c;
exception
when others then
dbms_output.put_line('error!!!');
rollback;
end test;
SQL> set serveroutput on;
SQL> declare
2 begin
3 test;
4 end;
5 /
PL/SQL procedure successfully completed
SQL> select * from t0;
ID NAME
--------------------------------------- ------------------------------
2 Mike
1 James