存储过程的编写
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;