Oracle异常处理，动态游标

小例子，方便以后查阅.

包头需要声明：   type C_CURSOR is ref cursor;

procedure visitcount(in_date  number,                       
                       out_code out number,
                       out_desc out varchar2                       
                       ) is
  t_date number(8);    
  t_datepre number(8);
  t_sql varchar2(2000);
  t_tempcount  number(8);
  c_data  C_CURSOR;
  
  v_cityname  varchar(20);
  v_visittime number(8);
  v_visitcount number(8);
  v_counttype  number(8);
  begin
    if(in_date<=0) then
       t_date:=to_number(trunc(sysdate)-1,'yyyymmdd');
       t_datepre:=to_number(trunc(sysdate)-2,'yyyymmdd');
    else 
       t_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 from cn_visitcount 
         where  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 for t_sql;    
    loop 
    fetch  c_data into v_cityname,v_visittime,v_visitcount,v_counttype ;
    exit when c_data%notfound;
      insert into cn_visitcount
        (visitcountid, cityname, visitcount, visittime, counttype)
      values
        (seq_cn_visitcountid.nextval,v_cityname, v_visitcount, v_visittime, v_counttype);      
    end loop;     
    --备份每日的手机号
    delete from cn_visitmobile where visittime=t_date;
    insert into cn_visitmobile
    select seq_cn_visitmobileid.nextval,mobile,cityid,visittime from
    (
    select mobile,cityid,to_number(to_char(visittime,'yyyymmdd')) visittime
    from cn_visitanalysis
    where to_number(to_char(visittime,'yyyymmdd'))=t_date
    and (cityid=68 or cityid=56)
    group by cityid,mobile,to_number(to_char(visittime,'yyyymmdd'))
    ) 
    commit;
    
    exception
    when others then
      out_desc:='sqlcode:'||sqlcode ||' err_message:' || sqlerrm;
      begin
        out_code:= -1;
        --out_description := '系统繁忙，请稍后再试!';
        rollback;
        --raise;
        --错误日志
        insert into cn_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;