经验总结：Oracle之Package

1.使用Package实现数据集合输出

<span style="font-size:10px;"><span style="font-family:Microsoft YaHei;">create or replace package pack_name is

  -- Author  : YANGEZHI
  -- Created : 2016/4/14 17:59:54
  -- Purpose :

  -- Public type declarations
  type rowcurcor is ref cursor;<span style="color:#ff0000;">--声明输出游标</span>

  --声明存储过程
  procedure proc_query(v_querydate in varchar2,v_querytype in varchar2,v_counttype in varchar2, v_ip in varchar2,<span style="color:#ff0000;">countList out rowcurcor</span>);
  --声明多个存储过程
  procedure proc_queryAll(v_itemId in varchar2,countList out rowcurcor);

end pack_name;</span>
</span>

create or replace package body pack_name as

 <span style="color:#ff0000;"> --在包体中实现存储过程</span>
  procedure proc_query(v_querydate in varchar2,
                                v_querytype in varchar2,
                                v_counttype in varchar2,
                                v_ip        in varchar2,
                                countList out rowcurcor) as
   <span style="color:#ff0000;"> --v_querydate 查询日期：now-按当前时间查询；日期值-需要查询的截止日期
    --v_querytype 查询类型：1-物理内存 2-交换分区 3-CPU使用率 4-磁盘
    --v_counttype 统计类型：0-按小时 1-按日 2-按周 3-按月
    --v_ip        主机IP地址
    --rate        (数据库配置)频率：以min(分钟)为计量单位</span>

    nowdate        varchar2(20); --当前时间
    maxdata        varchar2(100); --最大值
    mindata        varchar2(100); --最小值
    avgdata        varchar2(100); --平均值
    v_startdate    varchar2(20); --开始时间
    v_enddate      varchar2(20); --结束时间
    i              int := 1; --查询次数循环变量
    countnum       int; --查询次数
    v_execsql  varchar2(300); --查询SQL
    v_tempexcsql   varchar2(500);
    v_tempwheresql varchar2(200);
    sequencenum    varchar2(20);
    v_rate varchar2(5);

  begin

    if v_querydate='now' then
      nowdate := to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
    else nowdate := v_querydate||' 00:00:00';
    end if;

    <span style="color:#ff0000;">--删除历史数据并提交</span>
    delete from aim_temp_hostcount where serialno like to_char(sysdate,'yyyy-mm-dd')||'%';
    commit;

    <span style="color:#ff0000;">--判断查询类型,赋值SQL语句</span>
    case
      when v_querytype = '1' then
        v_execsql := 'select max(replace(pmem, ''%'','''')),min(replace(pmem, ''%'','''')),round(avg(replace(pmem, ''%'','''')),2)
         from aim_host_memruninfo_rep';
      when v_querytype = '2' then
        v_execsql := 'select max(used/total*100),min(used/total*100),round(avg(used/total*100),2)
         from aim_host_memswap_rep';
      when v_querytype = '3' then
        v_execsql := 'select max(us+sys),min(us+sys),round(avg(us+sys),2)
         from aim_host_cpuruninfo_rep';
      when v_querytype = '4' then
        v_execsql := 'select max(kb_read) || ''|'' || max(kb_write),min(kb_read) || ''|'' || min(kb_write),round(avg(kb_read),2) || ''|'' || round(avg(kb_write),2)
         from aim_host_diskio_rep';
      else
        v_execsql := '';
    end case;

    <span style="color:#ff0000;">--查询统计频率</span>
    select rate into v_rate from AIM_CONF_HOSTCOUNT where counttype=v_counttype;

    <span style="color:#ff0000;">--计算查询循环次数</span>
    case
      when v_counttype = '0' then
        countnum := (60 / v_rate); --0-按小时(60min/rate)
      when v_counttype = '1' then
        countnum := (24 * 60 / v_rate); --1-按日（24*60/rate）
      when v_counttype = '2' then
        countnum := (7 * 24 * 60 / v_rate); --2-按周（7*24*60/rate）
      when v_counttype = '3' then
        countnum := (30 * 24 * 60 / v_rate); --3-按月（30*24*60/rate）
      else
        countnum := 0;
    end case;

    <span style="color:#ff0000;">--获取流水</span>
    select to_char(sysdate,'yyyy-mm-dd')||SEQUENCE_HOSTCOUNT.Nextval into sequencenum from sys.dual;

    while i <= countnum loop
      maxdata := 0;
      mindata := 0;
      avgdata := 0;
      <span style="color:#ff0000;">--计算开始、结束时间</span>
      v_startdate := to_char(to_date(nowdate, 'yyyy-mm-dd hh24:mi:ss') -
                             (v_rate * i / 1440),
                             'yyyy-mm-dd hh24:mi:ss');
      v_enddate   := to_char(to_date(nowdate, 'yyyy-mm-dd hh24:mi:ss') -
                             (v_rate * (i - 1) / 1440),
                             'yyyy-mm-dd hh24:mi:ss');

      v_tempwheresql := ' where ip='''|| v_ip || ''' and server_date || '' '' || server_time >= '''|| v_startdate || '''
         and server_date || '' '' || server_time < ''' || v_enddate ||  '''';

      <span style="color:#ff0000;">--执行SQL</span>
      v_tempexcsql := v_execsql || v_tempwheresql;

      EXECUTE IMMEDIATE v_tempexcsql
        into maxdata,mindata,avgdata;

      --插入临时表
      insert into aim_temp_hostcount values(sequencenum,nvl(maxdata,'-'),nvl(avgdata,'-'),nvl(mindata,'-'),v_enddate);

      i := i + 1;
    end loop;
    --提交临时表数据
    commit;

    <span style="color:#ff0000;">--返回结果集</span>
    open countList for select * from aim_temp_hostcount where serialno=sequencenum order by servertime asc;

  end proc_query;
  
end pack_name;