加上exception when就可以了
begin
sqlstr :='update ' ¦ ¦db ¦ ¦' set ' ¦ ¦zdmc ¦ ¦'=''' ¦ ¦v2 ¦ ¦''' where ' ¦ ¦zdmc ¦ ¦'=''' ¦ ¦v1 ¦ ¦'''';
execute immediate sqlstr;
declare
cursor table1
is
select kmc,kzdmc from zd_wh2 where zdmc = db;
table2 table1%ROWTYPE;
begin
open table1;
loop
fetch table1 into table2;
exit when table1%NOTFOUND;
sqlstr1:='update ' ¦ ¦table2.kmc ¦ ¦' set ' ¦ ¦table2.kzdmc ¦ ¦'=''' ¦ ¦v2 ¦ ¦''' where ' ¦ ¦table2.kzdmc ¦ ¦'=''' ¦ ¦v1 ¦ ¦'''';
execute immediate sqlstr1;
end loop;
close table1;
exception when others then
rollback;
end;
exception when others then
rollback;
end;
begin
sqlstr :='update ' ¦ ¦db ¦ ¦' set ' ¦ ¦zdmc ¦ ¦'=''' ¦ ¦v2 ¦ ¦''' where ' ¦ ¦zdmc ¦ ¦'=''' ¦ ¦v1 ¦ ¦'''';
execute immediate sqlstr;
declare
cursor table1
is
select kmc,kzdmc from zd_wh2 where zdmc = db;
table2 table1%ROWTYPE;
begin
open table1;
loop
fetch table1 into table2;
exit when table1%NOTFOUND;
sqlstr1:='update ' ¦ ¦table2.kmc ¦ ¦' set ' ¦ ¦table2.kzdmc ¦ ¦'=''' ¦ ¦v2 ¦ ¦''' where ' ¦ ¦table2.kzdmc ¦ ¦'=''' ¦ ¦v1 ¦ ¦'''';
execute immediate sqlstr1;
end loop;
close table1;
exception when others then
rollback;
end;
exception when others then
rollback;
end;
rollback;
也可以用savepoint
oracle用begin... end来标示一段代码,在里面单独进行例外处理。
sqlstr :='update '||db||' set '||zdmc||'='''||v2||'''
where '||zdmc||'='''||v1||'''';
这句话还是会这行
怎么办
你的数据库是哪个版本的?
如果是8.0.5以前的,要用dbms_sql包
用show error命令查看一下错误信息。
begin开始一个事务吗?
如果游标有误,会执行第一个rollback;
但是不会执行第二个,
我想也执行第二个怎么办