create or replace procedure c1
is
cursor c1 is select cid,.... from tb where .....;
begin
open c1
loop c1 into v_id,......
update tb1 set column1=v_c1,...... where id=v_id ......
commit;
end loop;
exception
when others then
--异常如何处理,v_id为主键id
insert into tb_expo(eid,......)values(v_id,.....)
rollback;
end--这里如果update失败了，如何处理，我的目的是在异常处理的时候记录下v_id，
--然后回滚这个update操作，然后继续遍历剩下的游标记录，这个思路如何实现？
is
cursor c1 is select cid,.... from tb where .....;
begin
open c1
loop c1 into v_id,......
update tb1 set column1=v_c1,...... where id=v_id ......
commit;
end loop;
exception
when others then
--异常如何处理,v_id为主键id
insert into tb_expo(eid,......)values(v_id,.....)
rollback;
end--这里如果update失败了，如何处理，我的目的是在异常处理的时候记录下v_id，
--然后回滚这个update操作，然后继续遍历剩下的游标记录，这个思路如何实现？
那就表示，游标c1只执行了异常前的一部分，剩下的就没有执行了？
我想要的效果是，异常的记录到异常表里面去，然后继续遍历游标，继续update操作啊，这个怎么实现？
is
cursor c1 is select cid,.... from tb where .....;
begin
open c1
loop c1 into v_id,......
<<inner1>>
commit;
update tb1 set column1=v_c1,...... where id=v_id ......
exception
when others then
rollback; --异常如何处理,v_id为主键id
insert into tb_expo(eid,......)values(v_id,.....);
end inner1;
end loop;
end
将异常放在内部块里处理
loop c1 into v_id,......这些格式有问题
不过在这里不是你的重点
格式自己改好
改下
create or replace procedure c1
is
cursor c1 is select cid,.... from tb where .....;
begin
open c1
loop c1 into v_id,......
<<inner1>>
savepoint a;
update tb1 set column1=v_c1,...... where id=v_id ......
exception
when others then
rollback to a; --异常如何处理,v_id为主键id
insert into tb_expo(eid,......)values(v_id,.....);
end inner1;
end loop;
commit;
end
你把rollback放insert 后面，等于插入白做了
到最后再提交
为什么我编译的时候会报如下错误呢？Compilation errors for PROCEDURE ADD114.YH_TMQY_MERGER_UPDATEError: PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
Line: 149
Text: exceptionError: PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
;
Line: 161
Text: end loop;Error: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
Line: 169
Text: end YH_TMQY_MERGER_Update;
out_flag out number,
out_message out varchar2
)
/*
号百优化，增量同名企业归并、设置不供查可查询显示存储过程
skyman 2009-09-14
*/
is
v_customer_id t_customer_tel.customer_id%type;
v_out_code number;
v_out_message varchar2(200);
v_num number:=0;
v_search_telid varchar2(40);
--执行过程标记
v_flag number;
--定义增量游标
cursor cids is
select t.customer_id
from t_customer_tel t,
YH_TRS_QYCY$_TEMP y,
t_customer c
where t.telephone is not null
and t.check_status='1'
and y.custemer_tel_id=t.customer_tel_id
and t.customer_id=c.customer_id and c.type IN (1,2); --企业类型
--存放所有v_customer_id下面需要归并的customer_tel_id记录。
type tel_record is record
(
telid t_customer_tel.customer_tel_id%type,
flag t_customer_tel.merger_flag%type
);
type tel_records is table of tel_record index by binary_integer;
telids tel_records;
begin
v_flag:=0;
--从增量表里面取得telid以及对应的customer_id。
insert into YH_TRS_QYCY$_TEMP(SEQ_ID, CUSTEMER_TEL_ID, SQL_TYPE, TRS_FLAG)
select yh_seq_id.nextval, t.xh, t.sql_type, t.trs_flag
from TRS_QYCY$_TEMP t;
--从异常信息表里面取得前一天执行失败的tel_id记录。
insert into YH_TRS_QYCY$_TEMP(SEQ_ID, CUSTEMER_TEL_ID, SQL_TYPE, TRS_FLAG)
select yh_seq_id.nextval, y.exec_pk_auto_id,null,null
from YH_EXCEPTION y
where trunc(sysdate)-trunc(y.post_time)=1
and y.exec_column_name='customer_tel_id'
and y.exec_table_name='t_customer_tel'
and y.exec_object_name='YH_TMQY_MERGER_Update';
open cids;
loop
fetch cids into v_customer_id;
exit when cids%notfound;
<<inner1>>
--1，设置增量企业归并标记，存放在cids里面。
select
t.customer_tel_id,
case when count(t.customer_id)over(partition by t.customer_id)=1 then 1
else
(row_number() over (partition by t.customer_id order by t.ownership asc,t.source desc,t.is_extention asc,t.customer_tel_id desc ))+1
end as flag
bulk collect into telids
from t_customer_tel t
where t.customer_id=v_customer_id
and ( (t.is_open=0 and t.is_search=0) or t.isOpen_flag='1')
and t.telephone is not null
and t.check_status='1'
and not exists( select 1 from t_dc_discount d where d.customer_id=t.customer_id)--没有折扣信息
and t.prompt is not null
and not exists( select 1 from t_customer_info i where i.customer_tel_id=t.customer_tel_id)--没有深度信息
and not exists--没有区域信息
(
select 1
from t_product p,t_query_recommend q
where p.customer_tel_id=q.customer_tel_id
and q.product_type in ('1','2')
and p.product_status='2'
and q.busi_area is not null
and q.customer_tel_id=t.customer_tel_id
)
and not exists--没有关键词业务
(
select 1
from t_product p,t_query_recommend r
where p.product_status='2'
and p.product_spec_id in ('A101','A102')
and p.customer_tel_id=t.customer_tel_id
and r.product_id=p.product_id
);
--设置同名归并增量标记
for i in telids.first .. telids.last loop
update t_customer_tel t
set t.merger_flag=telids(i).flag
where t.customer_tel_id=telids(i).telid;
end loop;
--2，设置增量不可供查可查询显示标记。
select count(*) into v_num
from t_customer_tel t
where t.customer_id=v_customer_id
and t.is_search=0 and t.is_open=0;
v_search_telid:='';
if(v_num>0) then
update t_customer_tel t
set t.isopen_flag=0
where t.customer_id=v_customer_id;
else
--将旧的不不可供查可查询显示标记删除掉。
update t_customer_tel t
set t.isopen_flag=0
where t.customer_id=v_customer_id
and t.isopen_flag=1;
--重新设置不可供查可查询显示标记
select b.customer_tel_id into v_search_telid
from
(
select a.customer_id,a.customer_tel_id,
row_number() over (partition by a.customer_id order by a.qyzj_flag asc,a.customer_tel_id desc) as rowflag
from
(
select t2.customer_id,t2.customer_tel_id, t2.is_open,t2.is_search,
case when t2.is_extention=1 then 1 else 2 end as qyzj_flag
from t_customer_tel t2
where t2.customer_id=v_customer_id
and (t2.is_open!=0 or t2.is_search!=0)
and not exists(select 1 from t_customer_tel t1 where t1.customer_id=t2.customer_id and t1.is_open=0 AND t1.is_search=0)
and t2.telephone is not null
)a
)b where b.rowflag=1;
update t_customer_tel t
set t.isopen_flag=1
where t.customer_tel_id=v_search_telid;
end if;
commit;
--异常处理，编译的时候在这里exception一句里面报上面的错误。
exception when others then
out_flag:=0;
v_out_message:=sqlerrm;
rollback; --异常如何处理,v_id为主键id
insert into yh_exception
(exec_pk_auto_id,exec_column_name,exec_table_name,exec_object_name,exception_info)
values(v_customer_id,'customer_id','t_customer_tel','YH_TMQY_MERGER_Update','v_flag:'||v_flag||', '||v_out_message);
commit;
end inner1;
end loop;
close cids;
out_flag:=1;
--异常处理
-- return ;
end YH_TMQY_MERGER_Update;
在plsql里面edit存储过程，点击 F8键 编译失败。
下面有end inner1
上面没有begin
所以出错
那个<<inner1>>起啥作用呢？
游标里面处理
新开一个BEGIN EXCEPTION end就可以了