ORACLE创建包和包体及包中函数和存储过程

创建包声明的过程脚本：

CREATE OR REPLACE PACKAGE "COM_BOS" is

  -----------------------------------------------------------------------
  -- 描述：
  --   构造b类型缓存的sql语句
  -- 参数：
  --   @i_bostype：业务类型
  --   @i_bostime：时间类型
  --   @i_timecond：时间条件
  -- 返回值：
  --   返回进行缓存操作的SQL语句
  -----------------------------------------------------------------------
  function fn_compose_b_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2;

  -----------------------------------------------------------------------
  -- 描述：
  --   构造e类型缓存的sql语句
  -- 参数：
  --   @i_bostype：业务类型
  --   @i_bostime：时间类型
  --   @i_timecond：时间条件
  -- 返回值：
  --   返回进行缓存操作的SQL语句
  -----------------------------------------------------------------------
  function fn_compose_e_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2;

  -----------------------------------------------------------------------
  -- 描述：
  --   构造d类型缓存的sql语句
  -- 参数：
  --   @i_bostype：业务类型
  --   @i_bostime：时间类型
  --   @i_timecond：时间条件
  -- 返回值：
  --   返回进行缓存操作的SQL语句
  -----------------------------------------------------------------------
  function fn_compose_d_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2;

  -----------------------------------------------------------------------
  -- 描述：
  --   构造f类型缓存的sql语句
  -- 参数：
  --   @i_bostype：业务类型
  --   @i_bostime：时间类型
  --   @i_timecond：时间条件
  -- 返回值：
  --   返回进行缓存操作的SQL语句
  -----------------------------------------------------------------------
  function fn_compose_f_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2;
 -----------------------------------------------------------------------
  -- 描述：
  --   缓存调用的入口
  -- 参数：
  --   @i_type：缓存类型
  --   @i_subtype：缓存子类型
  --   @i_starttime：数据开始时间
  -- 返回值：
  --   void
  -----------------------------------------------------------------------
  function fnsummarysql1(in_kpitype number, in_timetype number, in_time number, in_cunt0 number,
    in_traders number,
    in_paths number,
    in_limit1 number,
    in_limit2 number ) return varchar2;
  function fnsummarysql2(in_kpitype number, in_time number ) return varchar2;
  -----------------------------------------------------------------------
  -- 描述：
  --   获取时延统计概要数据
  -- 参数：
  --   @out_result：输出数据
  -- 返回值：
  --   void
  -----------------------------------------------------------------------
  procedure getlatencysummary(
    out_result out sys_refcursor
  );
  -----------------------------------------------------------------------
  -- 描述：
  --   获取时延统计概要数据
  -- 参数：
  --   @out_result：输出数据
  -- 返回值：
  --   void
  -----------------------------------------------------------------------
  procedure dosummarycachenow(
    in_s number
  );
  -----------------------------------------------------------------------
  -- 描述：
  --   获取时延随时间分布数据
  -- 参数：
  --   @out_result：输出数据
  --   @in_bostype：业务类型
  --   @in_bostime：时间类型
  --   @in_latencytype：延迟类型
  --   @in_delta：统计粒度
  --   @in_from：统计开始时间
  --   @in_to：统计结束时间
  -- 返回值：
  --   void
  -----------------------------------------------------------------------
  procedure getlatency(
    out_result out sys_refcursor,
    in_bostype number,
    in_bostime number,
    in_latencytype number,
    in_delta number,
    in_from number,
    in_to number
  );
  end com_bos;

创建包体的过程：

CREATE OR REPLACE PACKAGE BODY "COM_BOS" is
  -----------------------------------------------------------------------
  -- 描述：
  --   构造b类型缓存的sql语句
  -- 参数：
  --   @i_bostype：业务类型
  --   @i_bostime：时间类型
  --   @i_timecond：时间条件
  -- 返回值：
  --   返回进行缓存操作的SQL语句
  -----------------------------------------------------------------------
  function fn_compose_b_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2
  as
    v_table varchar2(32);
  begin
    v_table := 'cache_b'||i_bostype||'t'||i_bostime;
    return 'select * from (select * from '||v_table||' union all
        select max(time),sum(cunt),sum(cunt0),sum(cunt1),sum(cunt2),sum(cunt3),sum(totallatency),max(maxlatency),min(minlatency) from '||v_table||'m
        )' || i_timecond;
  end;

  -----------------------------------------------------------------------
  -- 描述：
  --   构造e类型缓存的sql语句
  -- 参数：
  --   @i_bostype：业务类型
  --   @i_bostime：时间类型
  --   @i_timecond：时间条件
  -- 返回值：
  --   返回进行缓存操作的SQL语句
  -----------------------------------------------------------------------
  function fn_compose_e_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2
  as
    v_table varchar2(32);
  begin
    v_table := 'cache_e'||i_bostype||'t'||i_bostime;

    return ' select * from (select * from '||v_table||' union all
          select max(time),point,sum(pcunt),sum(ps0cunt),sum(ps1cunt),sum(ps2cunt),sum(perrcunt) from '||v_table||'m group by point
          )' || i_timecond;
  end;

  -----------------------------------------------------------------------
  -- 描述：
  --   构造d类型缓存的sql语句
  -- 参数：
  --   @i_bostype：业务类型
  --   @i_bostime：时间类型
  --   @i_timecond：时间条件
  -- 返回值：
  --   返回进行缓存操作的SQL语句
  -----------------------------------------------------------------------
  function fn_compose_d_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2
  as
    v_table varchar2(32);
  begin
    v_table := 'cache_d'||i_bostype||'t'||i_bostime;

    return 'select * from (select * from '||v_table||' union all
           select max(time), point1, point2,sum(cunt),sum(cunt1),sum(cunt2),sum(cunt3),sum(totallatency),max(maxlatency),min(minlatency) from '||v_table||'m group by point1, point2
           )' || i_timecond;
  end;

  -----------------------------------------------------------------------
  -- 描述：
  --   构造f类型缓存的sql语句
  -- 参数：
  --   @i_bostype：业务类型
  --   @i_bostime：时间类型
  --   @i_timecond：时间条件
  -- 返回值：
  --   返回进行缓存操作的SQL语句
  -----------------------------------------------------------------------
  function fn_compose_f_cache_sql(i_bostype number, i_bostime number, i_timecond varchar2) return varchar2
  as
    v_table varchar2(32);
  begin
    v_table := 'cache_f'||i_bostype||'t'||i_bostime;

    return 'select * from (select * from '||v_table||' union all
           select max(time),sum(totallatency),max(maxlatency),min(minlatency),sum(pcunt) from '||v_table||'
           ) '|| i_timecond;
  end;

 -----------------------------------------------------------------------
  -- 描述：
  --   缓存调用的入口
  -- 参数：
  --   @i_type：缓存类型
  --   @i_subtype：缓存子类型
  --   @i_starttime：数据开始时间
  -- 返回值：
  --   void
  -----------------------------------------------------------------------
  function fnsummarysql1(
    in_kpitype number,
    in_timetype number,
    in_time number,
    in_cunt0 number,
    in_traders number,
    in_paths number,
    in_limit1 number,
    in_limit2 number
  ) return varchar2
  as
    v_timetype number;
    v_from number;
  begin
    v_timetype := 0 ;
    if in_timetype = 0 then --当前一分钟
       v_from := in_time-59;
    elsif in_timetype = 1 then --当前5分钟
       v_from := in_time-299;
    elsif in_timetype = 2 then --当前1小时
       v_timetype := 1 ;
       v_from := in_time-3599;
    end if;

    return 'insert into TMP_CACHE_SUMMARY1 select '||in_kpitype||' as s, '||in_timetype||' as t, nvl('||in_cunt0||',0) as cunt0, nvl(a.cunt1,0) as cunt1,
            nvl(a.cunt2,0) as cunt2,nvl(a.cunt3,0) as cunt3,nvl(a.cunt,0) as cunt,'||in_traders||' as traders, '||in_paths||' as paths,
            nvl(a.minlatency, 0) as minlatency, nvl(a.avglatency,0) as avglatency, nvl(a.maxlatency,0) as maxlatency,
            '||in_limit1||' as limit1, '||in_limit2||' as limit2 from ( select sum(cunt1) as cunt1,sum(cunt2) as cunt2, sum(cunt3) as cunt3,sum(cunt) as cunt,
            min(minlatency)/1000 as minlatency,  sum(totallatency)/decode(sum(cunt),0,1,sum(cunt))/1000 as avglatency, max(maxlatency)/1000 as maxlatency
            from cache_a'||in_kpitype||'t'||v_timetype||'  where time between '||v_from||' and '||in_time||') a';
  end;

 function fnsummarysql2(
    in_kpitype number,
    in_time number
  ) return varchar2
  as
    v_wday number;
  begin
    v_wday := com.fngetweekday(in_time);
    return ' select '||in_kpitype||' as s, 3 as t, nvl(b.cunt0,0) as cunt0, nvl(a.cunt1,0) as cunt1,
              nvl(a.cunt2,0) as cunt2,nvl(a.cunt3,0) as cunt3,nvl(a.cunt,0) as cunt,c.traders as traders, d.paths as paths,
              nvl(a.minlatency, 0) as minlatency, nvl(a.avglatency,0) as avglatency, nvl(a.maxlatency,0) as maxlatency,
              e.limit1 as limit1, e.limit2 as limit2 from ( select sum(cunt1) as cunt1,sum(cunt2) as cunt2, sum(cunt3) as cunt3,sum(cunt) as cunt,
              min(minlatency)/1000 as minlatency, sum(totallatency)/greatest(sum(cunt),1)/1000 as avglatency, max(maxlatency)/1000 as maxlatency
              from cache_a'||in_kpitype||'t1) a, ( select sum(errordercount1sec) as cunt0 from fact_t0d'||v_wday||') b,
              ( select count(traderno) as traders from ( select distinct traderno from cache_a'||in_kpitype||'t1)) c,
              ( select count(point1) as paths from (select distinct point1 from cache_c'||in_kpitype||'t1)) d,
              ( select limit1/1000 as limit1,limit2/1000 as limit2 from cfg_latencylimit where point1=0 and point2=0 and kpitype ='||in_kpitype||') e';

  end;

  -----------------------------------------------------------------------
  -- 描述：
  --   进行某种业务的延迟概要缓存
  -- 参数：
  --   @in_s：业务类型
  -- 返回值：
  --   void
  -----------------------------------------------------------------------
  procedure dosummarycachenow(
    in_s number
  )
  as
    v_time number;
    v_limit1 number;
    v_limit2 number;
    v_wday number;
  begin
    select wday into v_wday from cache_time;--获取周期时间
    ---获取当前时间
    execute immediate 'select nvl(max(time-1), 0) from fact_i0d'||v_wday||' ' into v_time;
    execute immediate 'select limit1/1000 as limit1,limit2/1000 as limit2 from cfg_latencylimit where point1 = 0 and point2 = 0 and kpitype ='|| in_s into v_limit1,v_limit2;
  --select outpoint into v_pointout from cfg_iopoint where id = 1 ;
    execute immediate 'insert into CACHE_SUMMARYH select '||in_s||' as s, 0 as t, nvl(b.cunt0,0) as cunt0, nvl(a.cunt1,0) as cunt1,
              nvl(a.cunt2,0) as cunt2,nvl(a.cunt3,0) as cunt3,nvl(a.cunt,0) as cunt,c.traders as traders, d.paths as paths,
              nvl(a.minlatency, 0) as minlatency, nvl(a.totallatency,0) as avglatency, nvl(a.maxlatency,0) as maxlatency,'||v_limit1||' as limit1, '||v_limit2||' as limit2
              from (select sum(cunt1) as cunt1,sum(cunt2) as cunt2, sum(cunt3) as cunt3,sum(cunt) as cunt,min(minlatency)/1000 as minlatency, sum(totallatency)/greatest(sum(cunt),1)/1000 as totallatency, max(maxlatency)/1000 as maxlatency
              from cache_a'||in_s||'t0  where time between '||(v_time-59)||' and '||v_time||') a,
              (select sum(errordercount1sec) as cunt0 from fact_t0d'||v_wday||'  where time between '||(v_time-59)||' and '||v_time||' and point=140 ) b,
              (select count(distinct traderno) as traders from cache_a'||in_s||'t0  where time between '||(v_time-59)||' and '||v_time||' and traderno <>0 ) c,
              (select count(distinct point1) as paths from cache_c'||in_s||'t0  where time between '||(v_time-59)||' and '||v_time||' and point1>0 ) d';

    execute immediate 'insert into CACHE_SUMMARYH select '||in_s||' as s, 1 as t, nvl(b.cunt0,0) as cunt0, nvl(a.cunt1,0) as cunt1,
              nvl(a.cunt2,0) as cunt2,nvl(a.cunt3,0) as cunt3,nvl(a.cunt,0) as cunt,c.traders as traders, d.paths as paths,
              nvl(a.minlatency, 0) as minlatency, nvl(a.totallatency,0) as avglatency, nvl(a.maxlatency,0) as maxlatency,
              '||v_limit1||' as limit1, '||v_limit2||' as limit2 from (select sum(cunt1) as cunt1,sum(cunt2) as cunt2, sum(cunt3) as cunt3,sum(cunt) as cunt,
              min(minlatency)/1000 as minlatency, sum(totallatency)/greatest(sum(cunt),1)/1000 as totallatency, max(maxlatency)/1000 as maxlatency
              from cache_a'||in_s||'t0  where time between '||(v_time-299)||' and '||v_time||' ) a,
              (select sum(errordercount1sec) as cunt0 from fact_t0d'||v_wday||'  where time between '||(v_time-299)||' and '||v_time||'  and point=140) b,
              (select count(distinct traderno) as traders from cache_a'||in_s||'t0 where time between '||(v_time-299)||' and '||v_time||' and traderno<>0) c,
              (select count(distinct point1) as paths from cache_c'||in_s||'t0 where time between '||(v_time-299)||' and '||v_time||' and point1>0) d';

    execute immediate 'insert into CACHE_SUMMARYH select '||in_s||' as s, 2 as t, nvl(b.cunt0,0) as cunt0, nvl(a.cunt1,0) as cunt1,
              nvl(a.cunt2,0) as cunt2,nvl(a.cunt3,0) as cunt3,nvl(a.cunt,0) as cunt,c.traders as traders, d.paths as paths,
              nvl(a.minlatency, 0) as minlatency, nvl(a.totallatency,0) as avglatency, nvl(a.maxlatency,0) as maxlatency,
              '||v_limit1||' as limit1, '||v_limit2||' as limit2 from ( select sum(cunt1) as cunt1,sum(cunt2) as cunt2, sum(cunt3) as cunt3,sum(cunt) as cunt,
              min(minlatency)/1000 as minlatency, sum(totallatency)/greatest(sum(cunt),1)/1000 as totallatency, max(maxlatency)/1000 as maxlatency
              from cache_a'||in_s||'t0   where time between '||(v_time-3599)||' and '||v_time||' ) a,
              (select sum(errordercount1sec) as cunt0 from fact_t0d'||v_wday ||' where point=140 ) b,
              (select count(distinct traderno) as traders from cache_a'||in_s||'t0 where time between '||(v_time-3599)||' and '||v_time||' and traderno<>0) c,
              (select count(distinct point1) as paths from cache_c'||in_s||'t0  where time between '||(v_time-3599)||' and '||v_time||' and point1>0) d';
    commit;
   -- dbms_output.put_line(v_sql);
  end;
  -----------------------------------------------------------------------
  -- 描述：
  --   获取时延统计概要数据
  -- 参数：
  --   @out_result：输出数据
  -- 返回值：
  --   void
  -----------------------------------------------------------------------
  procedure getlatencysummary(
    out_result out sys_refcursor
  )
  as
  begin
    --清除缓存表

    execute immediate 'truncate table CACHE_SUMMARYH';
    execute immediate 'call com_bos.dosummarycachenow(0)'; --汇总一分钟的时延分布数据
    execute immediate 'call com_bos.dosummarycachenow(1)'; --汇总五分钟的时延分布统计
    execute immediate 'call com_bos.dosummarycachenow(2)'; --汇总一小时的时延分布统计

    open out_result for 'select s as "s",t as "t",cunt0 as "cunt0",cunt1 as "cunt1",cunt2 as "cunt2",cunt3 as "cunt3",cunt as "cunt",
         traders as "traders",paths as "paths",minlatency as "minlatency",avglatency as "avglatency",maxlatency as "maxlatency",
         limit1 as "limit1",limit2 as "limit2" from (select * from CACHE_SUMMARYH union all select * from CACHE_SUMMARY)';
    /*
    execute immediate 'truncate table TMP_CACHE_SUMMARY1';
    execute immediate 'call com_bos.dosummarycachenow(0)';
    execute immediate 'call com_bos.dosummarycachenow(1)';
    execute immediate 'call com_bos.dosummarycachenow(2)';

    open out_result for 'select * from TMP_CACHE_SUMMARY1 union all select * from TMP_CACHE_SUMMARY ';
    */
    EXCEPTION
    when OTHERS then
      dbms_output.put_line(sqlerrm);
  end;

  -----------------------------------------------------------------------
  -- 描述：
  --   获取时延随时间分布数据
  -- 参数：
  --   @out_result：输出数据
  --   @in_bostype：业务类型
  --   @in_bostime：时间类型
  --   @in_latencytype：延迟类型
  --   @in_delta：统计粒度
  --   @in_from：统计开始时间
  --   @in_to：统计结束时间
  -- 返回值：
  --   void
  -----------------------------------------------------------------------
  procedure getlatency(
    out_result out sys_refcursor,
    in_bostype number,
    in_bostime number,
    in_latencytype number,
    in_delta number,
    in_from number,
    in_to number
  )
  as
    v_sql varchar2(2000); -- 拼凑执行的sql
    v_timeregular varchar2(150);
    v_timecond varchar2(128); --时间条件
    v_select varchar(256); --查询的字段
    v_timeregular1 varchar2(150);--转换的时间格式
    v_currenttime number;--当前时间
    v_fromtime number;--开始时间
    v_wday number;--星期数
    v_facttable varchar2(128);--事实表
    v_to number;--结束时间
    --v_bostime number;
  begin

  if in_bostime > 2 then --当天昨天历史
     if in_delta = 1 then -- 1秒粒度 时间范围1分钟
         v_timeregular := com.fngetsecondtostrsql('time', 'hh24:mi:ss');
         v_timeregular1 := 'time';
         v_to := in_to ;
     elsif  in_delta = 60 then --1分钟粒度 时间范围30分钟
         v_timeregular := com.fngetsecondtostrsql('time', 'hh24:mi');
        v_timeregular1 := com.fngetsecondtostrsql('time', 'hh24:mi');
         v_to := in_to ;
     elsif  in_delta = 300 then --5分钟粒度 时间范围一天
         v_timeregular := com.fngetsecondtostrsql('min(time)', 'hh24:mi');
         v_timeregular1 := 'trunc(time/300)';
         v_to := in_to  ;
     elsif  in_delta = 3600 then
         v_timeregular := com.fngetsecondtostrsql('time', 'hh24');
         v_timeregular1 := com.fngetsecondtostrsql('time', 'hh24');
         v_to := in_to + 1;
     elsif  in_delta = 86400 then
         v_timeregular := com.fngetsecondtostrsql('time', 'mm-dd');
         v_timeregular1 :=com.fngetsecondtostrsql('time', 'mm-dd');
         v_to := in_to + 1;
     end if;
        -- 星期数
        v_wday := com.fngetweekday(in_from);
        --要查询的表
        v_facttable := 'fact_s'||in_bostype||'d'||v_wday;
        if in_latencytype = 0 then --最小时延
           v_select := 'nvl(min(globallatency),0)/1000 as "latency", nvl(count(id), 0) as "cunt"';
        elsif in_latencytype = 1 then --平均时延
           v_select := 'nvl(sum(globallatency)/greatest(count(id),1),0)/1000 as "latency", nvl(count(id), 0) as "cunt"';
        else --最大时延
           v_select := 'nvl(max(globallatency),0)/1000 as "latency", nvl(count(id), 0) as "cunt"';
        end if;

     v_timecond := ' where time between '||in_from||' and '||v_to||'';

  else -- 最近1小时 5分钟 1分钟
    select wday into v_wday from cache_time;
    execute immediate 'select nvl(max(time-1), 0) from fact_i0d'||v_wday||' ' into v_currenttime;
    if in_bostime = 0 then --1分钟
          v_timeregular1 := 'time';
          v_fromtime := v_currenttime - 59;
          v_timeregular := com.fngetsecondtostrsql('time', 'hh24:mi:ss');
    elsif in_bostime = 1 then --5分钟
          v_timeregular1 := 'trunc(time/5)';
          v_fromtime := v_currenttime - 299;
          v_timeregular := com.fngetsecondtostrsql('min(time)', 'hh24:mi:ss');
    elsif in_bostime = 2 then --1小时
          v_timeregular1 := com.fngetsecondtostrsql('time', 'hh24:mi');
          v_fromtime := v_currenttime - 3599;
          v_timeregular := com.fngetsecondtostrsql('time', 'hh24:mi');
    end if;

    v_facttable := 'cache_a'||in_bostype||'t0';
    if in_latencytype = 0 then --最小时延
         v_select := 'nvl(min(minlatency),0)/1000 as "latency", nvl(sum(cunt),0) as "cunt"';
    elsif in_latencytype = 1 then --平均时延
         v_select := 'nvl(sum(totallatency)/greatest(sum(cunt),1),0)/1000 as "latency", nvl(sum(cunt), 0) as "cunt"';
    else --最大时延
         v_select := 'nvl(max(maxlatency),0)/1000 as "latency", nvl(sum(cunt), 0) as "cunt"';
    end if;
    v_timecond := ' where time between ' || v_fromtime || ' and ' || v_currenttime;

  end if;

   v_sql := 'select '||v_timeregular||' as "time", '||v_select||' from '||v_facttable||' '||v_timecond||' group by '||v_timeregular1||' order by 1';

  -- insert into debug_log values(v_sql);commit;
   open out_result for v_sql;

   EXCEPTION
    when OTHERS then
      dbms_output.put_line(sqlerrm);
  end;
end com_bos;