游标异常打印oracle,Oracle异常处理,动态游标

procedure visitcount(in_date number,

out_code outnumber,

out_desc outvarchar2)ist_datenumber(8);

t_dateprenumber(8);

t_sqlvarchar2(2000);

t_tempcountnumber(8);

c_data C_CURSOR;

v_citynamevarchar(20);

v_visittimenumber(8);

v_visitcountnumber(8);

v_counttypenumber(8);begin

if(in_date<=0) thent_date:=to_number(trunc(sysdate)-1,‘yyyymmdd‘);

t_datepre:=to_number(trunc(sysdate)-2,‘yyyymmdd‘);elset_date:=in_date;

t_datepre:=to_number(to_char(to_date(in_date,‘yyyy-mm-dd‘)-1,‘yyyymmdd‘));end if;--删除之前的数据

--select count(1) into t_tempcount from cn_visitcount

--where visittime=t_date;

--if t_tempcount>0 then

delete fromcn_visitcountwhere visittime=t_date;--end if;

t_sql:=‘select * from (

select cityname,‘||t_date||‘visittime,count(1) visitcount,1 counttype from

(

select

(

case

when cityid=68 then‘‘深圳‘‘when cityid=56 then‘‘广州‘‘end

)cityname,mobile,count(1) from cn_visitanalysis

where to_number(to_char(visittime,‘‘yyyymmdd‘‘))=‘||t_date||

‘and (cityid=68 or cityid=56)

group by cityid,mobile

)

group by cityname‘;

t_sql:=t_sql||‘union all

select cityname,‘||t_date||‘visittime,count(1) visitcount,2 counttype from

(

select

(

case

when cityid=68 then‘‘深圳‘‘when cityid=56 then‘‘广州‘‘end

)cityname,mobile,count(1) from cn_visitanalysis

where to_number(to_char(visittime,‘‘yyyymmdd‘‘))=‘||t_date||

‘and (cityid=68 or cityid=56)

and mobile not in (

select mobile from cn_visitanalysis

where to_number(to_char(visittime,‘‘yyyymmdd‘‘))<=‘||t_datepre||‘and (cityid=68 or cityid=56)

)

group by cityid,mobile

)

group by cityname)

order by cityname,visitcount desc‘;--插入查询的数据

open c_data fort_sql;

loopfetch c_data intov_cityname,v_visittime,v_visitcount,v_counttype ;exit when c_data%notfound;insert intocn_visitcount

(visitcountid, cityname, visitcount, visittime, counttype)values(seq_cn_visitcountid.nextval,v_cityname, v_visitcount, v_visittime, v_counttype);endloop;--备份每日的手机号

delete from cn_visitmobile where visittime=t_date;insert intocn_visitmobileselect seq_cn_visitmobileid.nextval,mobile,cityid,visittime from(select mobile,cityid,to_number(to_char(visittime,‘yyyymmdd‘)) visittimefromcn_visitanalysiswhere to_number(to_char(visittime,‘yyyymmdd‘))=t_dateand (cityid=68 or cityid=56)group by cityid,mobile,to_number(to_char(visittime,‘yyyymmdd‘))

)commit;

exceptionwhen others thenout_desc:=‘sqlcode:‘||sqlcode ||‘err_message:‘ ||sqlerrm;beginout_code:= -1;--out_description := ‘系统繁忙,请稍后再试!‘;

rollback;--raise;

--错误日志

insert intocn_joblog(joblogid,procname,starttime,endtime,logtype,remark)values(seq_cn_joblogid.Nextval,‘fx114v01_cn_job.visitcount‘,sysdate,sysdate,‘error‘,out_desc);commit;end;end visitcount;

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值