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;

 

Oracle异常处理,动态游标,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。