长宁信息港
体育
当前位置:首页 > 体育

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

发布时间:2019-10-17 02:51:23 编辑:笔名

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

有一张表 Report_call_hist(电话进线表)、

数据格式如下:

datetime( varchar类型) type(进线类型 ) calltype(呼叫类型) callresult(呼叫结果) groupid(组) agentID(坐席编号)

2009-09-08 09:08:45 acdgroup out actv 7500 2130

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;

兰州治疗白带异常医院
呼和浩特好的白癜风医院
泰安治疗阴道炎方法
青海治疗白斑病费用
庆阳治疗子宫内膜炎费用
友情链接