17,082
社区成员
发帖
与我相关
我的任务
分享
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操作,然后继续遍历剩下的游标记录,这个思路如何实现?
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
create or replace procedure c1
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
create or replace procedure 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;