ORACLE存储过程编写

一、定义包头：

create or replace package IBOSV20_PV AS
 -- 成功与错误代码
  SUCC_CODE CONSTANT NUMBER(5) := 0;
  ERROR_DATABASE CONSTANT NUMBER(5) := 10000;
  type C_CURSOR is ref cursor;

 --根据PV记录，计算同一个用户页面之间的访问时间
 procedure updatePageDiffTime
 (
 in_statDate  in number,
 out_code     out number
 );

end;

 二、包体

 --根据PV记录，计算同一个用户页面之间的访问时间
 procedure updatePageDiffTime
 (
 in_statDate  in number,
 out_code     out number
 )
 is
   t_statDate NUMBER(8);
   t_startDate DATE;
   t_endDate DATE;

    v_curpagetime    number;
    v_nextpagetime   number;
    v_pagedifftime   number(8,2);
    v_count          number;
    
    v_first          number;
    v_sorucechannel  varchar2(30);  --来源频道
    v_comefromid     number;   --从哪个搜索过来的 0自身的，1百度，2谷歌
 begin
     IF in_statDate = 0 THEN
      t_startDate := to_date(to_char(SYSDATE - 1,'yyyymmdd'), 'yyyymmdd');
      t_statDate := to_number(to_char(SYSDATE - 1,'yyyymmdd'));
    ELSE
      t_startDate := to_date(to_char(in_statDate), 'yyyymmdd');
      t_statDate := in_statDate;
    END IF;
    t_endDate := t_startDate + 1;

     --计算页面之间的访问时间差  应该每天晚上零点以后跑，否则统计不准确
    --查询当日产生的所有当日KEY
    declare cursor c_todaykey is
            select todaykey from pv_visit where todaykey is not null
                                          and nextpagedifftime<0
                                          and visittimedate=t_statDate
                                          group by todaykey;
    begin
      for t in c_todaykey loop
          --查询该KEY下所有的记录，然后计算页面之间的访问时间差
         declare cursor c_difftime is
         select visitid,visittime,cururlchannel,refurl from pv_visit where todaykey=t.todaykey order by visittime;
         begin
           v_first:=1;
           v_comefromid:=0;  --默认来自自身的URL直接输入
           for d in c_difftime loop
             --当前访问的时间
             select count(1) into v_count from
                                       (select visittime from pv_visit
                                        where todaykey=t.todaykey
                                        and visittime>d.visittime);
             if v_count>0 then
               v_curpagetime:=d.visittime;
               --获取访问下一个页面的时间
               select visittime into v_nextpagetime from
                (select visittime from pv_visit
                where todaykey=t.todaykey
                and visittime>d.visittime
                 order by visittime) where rownum<=1;

               --计算当前页面和下一个页面的访问时间差
               select round((v_nextpagetime-v_curpagetime)/60,2) into v_pagedifftime from dual;
               --更新时间差
               update pv_visit set nextpagedifftime=v_pagedifftime where visitid=d.visitid;
               
               if v_pagedifftime>20 then
                  v_first:=1;
               end if;
               if v_first=1 then
                  --判断来源类型
                  if  instr(d.refurl,'baidu.com')<>0 then
                      v_comefromid:=1;
                  elsif instr(d.refurl,'google.com')<>0 then
                      v_comefromid:=2;
                  else
                      v_comefromid:=0;
                  end if;
                  
                  v_sorucechannel:=d.cururlchannel;
                  v_first:=2;  --之后的频道都以第一个频道为源
               end if;
                --更新时间差和频道来源
               update pv_visit set nextpagedifftime=v_pagedifftime,SOURCECHANNEL=v_sorucechannel,comefromid=v_comefromid where visitid=d.visitid;    
             elsif v_first=2 then
                --说明是最后一个页面
               update pv_visit set SOURCECHANNEL=v_sorucechannel,comefromid=v_comefromid where visitid=d.visitid;  
             else   --只有一个页面的情况
               --判断来源类型
                  if  instr(d.refurl,'baidu.com')<>0 then
                      v_comefromid:=1;
                  elsif instr(d.refurl,'google.com')<>0 then
                      v_comefromid:=2;
                  else
                      v_comefromid:=0;
                  end if;
               update pv_visit set SOURCECHANNEL=d.cururlchannel,comefromid=v_comefromid where visitid=d.visitid;         
             end if;
           end loop ;
         end;
      end loop ;
    end;
  commit;
  exception
  when others then
    rollback;
    raise;
 end updatePageDiffTime;

 

三、需要返回游标的

包头

 --根据开始时间和结束时间获取对应用户类型的PV统计
 procedure getPvVisitStat
 (
 in_startDate in number,
 in_endDate   in number,
 in_userType  in number,
 out_pvtable  out C_CURSOR
 );

 包体：

 --根据开始时间和结束时间获取对应用户类型的PV统计
 procedure getPvVisitStat
 (
 in_startDate in number,
 in_endDate   in number,
 in_userType  in number,
 out_pvtable  out C_CURSOR
 )
 is
 diffday number:=1;
 begin

open out_pvtable for
 select 1 a from dual;

when others then
    rollback;
    raise;
 end;