已有一个SQL存储过程，求一个与之功能一样的ORACLE存储过程?

callresult列 中actv表示有进线 电话打进来了，abdn表示放弃,现在要统计 某个时间范围内：如 从2009-09-01  到2009-10-01之间每天的同一时间段(每天的0-1点，每天的1-2点，每天的2-3点。。。。)的电话进线总数:格式如下：

SQL code
最后得到的结果如下:
calltime(时间)  hjTotal(呼叫总量)  IVRPercent（IVR进线比例）jtPercent(接听比例)   。。。。。。。。。。
00:00           20                  20%                      30%
01:00           34
......       .....
23:00           344
24:00           200

calltime(时间)  hjTotal(呼叫总量)  IVRPercent（IVR进线比例）jtPercent(接听比例)  IVRTotal(IVR总进线) fqTotal（放弃电话总数）
原SQL存储过程如下：

SQL code
create  proc proc_jxTJ
(@startday datetime,@endday datetime, @phone varchar(15)) with encryption as
declare
 @startTime datetime,
 @endTime datetime
set @startTime=convert(varchar(10),@startday,101)
set @endTime=convert(varchar(10),@endday,101) 
if @phone<>''
begin
SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal,
cast(cast((IVRTotal*1.0/hjTotal)*100 as decimal(6,0)) as varchar(10)) +'%' as IVRPercent,
 
cast(cast((jtTotal*1.0/hjTotal)*100 as decimal(6,0)) as varchar(10)) + '%' as jtPercent   FROM (

select [calltime]=right(number+100,2)+':00',

hjTotal=sum(case when r.calltype='in' then 1 else 0 end ),

IVRTotal=sum(case  when  r.calltype='in' and r.type='ivrgroup' then 1 else 0 end),

fqTotal=sum(case when  r.calltype='in' and r.callresult='ABDN' then 1 else 0  end),

jtTotal=sum(case when r.calltype='in'  and r.type='acdgroup' and r.callresult='ACTV' then 1 else 0 end)
from master..spt_values m  left join report_call_hist r

on m.number=datepart(hour,r.[datetime]) and (r.[datetime] between @startTime and @endTime)

where  m.type='p' and number between 0 and 24  and r.calltype='in' and r.dnis like '%'+@phone+'%'  group by number
)t order by t.calltime asc
end

核心代码就是

SQL code
SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal,
cast(cast((IVRTotal*1.0/hjTotal)*100 as decimal(6,0)) as varchar(10)) +'%' as IVRPercent,
 
cast(cast((jtTotal*1.0/hjTotal)*100 as decimal(6,0)) as varchar(10)) + '%' as jtPercent   FROM (

select [calltime]=right(number+100,2)+':00',

hjTotal=sum(case when r.calltype='in' then 1 else 0 end ),

IVRTotal=sum(case  when  r.calltype='in' and r.type='ivrgroup' then 1 else 0 end),

fqTotal=sum(case when  r.calltype='in' and r.callresult='ABDN' then 1 else 0  end),

jtTotal=sum(case when r.calltype='in'  and r.type='acdgroup' and r.callresult='ACTV' then 1 else 0 end)
from master..spt_values m  left join report_call_hist r

on m.number=datepart(hour,r.[datetime]) and (r.[datetime] between @startTime and @endTime)

where  m.type='p' and number between 0 and 24  and r.calltype='in' and r.dnis like '%'+@phone+'%'  group by number
)t order by t.calltime asc

SQL code

create or replace package ZK_jxTj is
type refCursorType is ref cursor;             --定义游标变量用于返回记录集
  procedure Proc_jxTj   
                 (           
                  v_startday  in varchar2,
                  v_endday    in varchar2,
                  v_phone     in  varchar2,
                  c1          out  refCursorType);
end ZK_jxTj;
--定义包主体
create or replace package body ZK_jxTj is
procedure Proc_jxTj
  (  
 
   v_startday  in varchar2,
   v_endday    in varchar2, 
   v_phone     in varchar2,
   c1          out refCursorType
   )
as
begin
 if v_phone is not null then
      open c1 for
         SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal,
              TO_CHAR(ROUND(IVRTotal/hjTotal*100,2),'990')||'%' IVRPercent,
              TO_CHAR(ROUND(jtTotal/hjTotal*100,2),'990')|| '%' jtPercent
         FROM ( select  substr(to_char(rn + 100),2,2)||':00' calltime,
                        sum(case when r.calltype='IN' then 1 else 0 end) hjTotal,
                        sum(case when r.calltype='IN' and r.type='ivrgroup' then 1 else 0 end)  IVRTotal,
                        sum(case when r.calltype='IN' and r.callresult='ABDN' then 1 else 0 end) fqTotal,
                        sum(case when r.calltype='IN' and r.type='acdgroup' and r.callresult='ACTV' then 1 else 0 end) jtTotal        
                from (select rownum - 1  rn from dual connect by rownum <= 24) m
                left join report_call_hist r
                on  m.rn = to_number(to_char(to_date(r.datetime,'yyyy-mm-dd HH24:mi:ss'),'HH24'))
         and to_date(r.datetime,'yyyy-mm-dd hh24:mi:ss')  between  to_date(v_startday,'yyyy-mm-dd hh24:mi:ss') and  to_date(v_endday,'yyyy-mm-dd hh24:mi:ss')
                where r.calltype  ='IN' and r.dnis  like '%' || v_phone ||  '%'  group by rn) t
         ORDER BY t.calltime;
   end if;
   if v_phone is null then
    open c1 for
    SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal,
              TO_CHAR(ROUND(IVRTotal/hjTotal*100,2),'990')||'%' IVRPercent,
              TO_CHAR(ROUND(jtTotal/hjTotal*100,2),'990')|| '%' jtPercent
         FROM ( select  substr(to_char(rn + 100),2,2)||':00' calltime,
                        sum(case when r.calltype='IN' then 1 else 0 end) hjTotal,
                        sum(case when r.calltype='IN' and r.type='ivrgroup' then 1 else 0 end)  IVRTotal,
                        sum(case when r.calltype='IN' and r.callresult='ABDN' then 1 else 0 end) fqTotal,
                        sum(case when r.calltype='IN' and r.type='acdgroup' and r.callresult='ACTV' then 1 else 0 end) jtTotal        
                from (select rownum - 1  rn from dual connect by rownum <= 24) m
                left join report_call_hist r
                on  m.rn = to_number(to_char(to_date(r.datetime,'yyyy-mm-dd HH24:mi:ss'),'HH24'))
                and to_date(r.datetime,'yyyy-mm-dd hh24:mi:ss')  between  to_date(v_startday,'yyyy-mm-dd hh24:mi:ss') and  to_date(v_endday,'yyyy-mm-dd hh24:mi:ss')
                where r.calltype  ='IN' group by rn) t
         ORDER BY t.calltime;
   end if;
end proc_jxTJ;
end ZK_jxTj;