游标异常打印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;