存储过程的编写

create or replace procedure CALL_RealTimeCallReason2(arg_voiceDate in varchar2,
                                                     arg_time      in varchar2,
                                                     out_result    out varchar2) is


  /*
  运营分析-实时数据分析
  功能：查询
  创建时间：2012-3-*
  创建人：binliu
  更新历史：
  */


  sql_str           varchar2(2000);
  temp_max          varchar2(200);
  temp_min          varchar2(200);
  temp_partitionArr varchar2(2000);
  temp_partition    varchar2(200);
  temp_index        number(20);
  temp_tableCount   number(20);
  temp_sum          number default(0);
  temp_date         varchar2(2000);
  temp_result       varchar2(2000);
  temp_count        number;


begin
  --创建临时表
  select count(*)
    into temp_tableCount
    from user_tables
   where table_name = 'CALL_TEMP_REALTIMEDATA';
  if (temp_tableCount = 0) then
    begin
      sql_str := '
           create global temporary table call_temp_realtimedata
           (
                  callid number(19),
                  starttime date
           )
            on commit preserve rows;
    ';
      execute immediate sql_str;
    end;
  end if;


  temp_date := arg_voiceDate || ' ' || arg_time;


  --计算表分区区间
  if arg_voiceDate is not null then
    select min(t.startid), max(t.endid)
      into temp_min, temp_max
      from call_dayinfo t
     where t.voicedate between
           to_date(arg_voiceDate || ' 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
           to_date(arg_voiceDate || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss');
  end if;
  select transformpartition(temp_min, temp_max)
    into temp_partitionArr
    from dual;
  dbms_output.put_line('startid:' || temp_min);
  dbms_output.put_line('endid:' || temp_max);
  dbms_output.put_line(temp_partitionArr);


  --把数据放入临时表中
  temp_index := instr(temp_partitionArr, '/');
  while temp_index <> 0 loop
    temp_partition := substr(temp_partitionArr, 1, temp_index - 1);
    --判断分区时间
    sql_str := 'select count(t1.id) 
      from call_callrecord partition(' || temp_partition ||
               ') t1
     where t1.starttime between to_date(''' || arg_voiceDate || ' ' ||
               arg_time || ''',''yyyy-mm-dd hh24:mi:ss'')-1/24/4
                and to_date(''' || arg_voiceDate || ' ' ||
               arg_time || ''',''yyyy-mm-dd hh24:mi:ss'')+1/8
                ';
    execute immediate sql_str
      into temp_count;
  
    if temp_count <> 0 then
      sql_str := 'select t1.callid , t2.starttime  from call_modelcall  partition( ' ||
                 temp_partition ||
                 ' ) t1 inner join call_callrecord partition( ' ||
                 temp_partition ||
                 ' ) t2 on t2.id = t1.callid where  modelid in 
               (select id from voiceinsightbase.base_specialmodel t3 where t3.modeltype = 4)
                and t2.starttime between to_date(''' ||
                 arg_voiceDate || ' ' || arg_time ||
                 ''',''yyyy-mm-dd hh24:mi:ss'')-1/24/4
                and to_date(''' || arg_voiceDate || ' ' ||
                 arg_time || ''',''yyyy-mm-dd hh24:mi:ss'')+1/8
                ';
    
      sql_str := 'insert into call_temp_realtimedata (' || sql_str || ')';
      dbms_output.put_line('sql_str is:' || sql_str);
      execute immediate sql_str;
      commit;
    end if;
    temp_partitionArr := substr(temp_partitionArr, temp_index + 1);
    temp_index        := instr(temp_partitionArr, '/');
  end loop;


  --从临时表中查数据


  sql_str := 'select count(t.callid) from call_temp_realtimedata t where t.starttime between to_date(''' ||
             arg_voiceDate || ' ' || arg_time ||
             ''',''yyyy-mm-dd hh24:mi:ss'')-1/24/4
                  and to_date(''' || arg_voiceDate || ' ' ||
             arg_time || ''',''yyyy-mm-dd hh24:mi:ss'')
                  ';
  dbms_output.put_line('sql_str = ' || sql_str);
  execute immediate sql_str
    into temp_sum;
  temp_result := temp_sum;
  temp_index  := 1;
  while temp_index <= 12 loop
    sql_str := 'select count(t.callid) from call_temp_realtimedata t where t.starttime between to_date(''' ||
               arg_voiceDate || ' ' || arg_time ||
               ''',''yyyy-mm-dd hh24:mi:ss'')+(1/24/4)*' ||
               (temp_index - 1) || '
                  and to_date(''' || arg_voiceDate || ' ' ||
               arg_time || ''',''yyyy-mm-dd hh24:mi:ss'')+(1/24/4)*' ||
               temp_index;
    dbms_output.put_line('sql_str2 --' || sql_str);
    execute immediate sql_str
      into temp_sum;
    temp_result := temp_result || '-' || temp_sum;
  
    temp_index := temp_index + 1;
  end loop;
  dbms_output.put_line(temp_result);
  out_result := temp_result;


end CALL_RealTimeCallReason2;