create or replace procedure proc_jxTJ ( v_startday in date, v_endday in date, v_phone in varchar2(15) ) is cursor c1;begin if v_phone is not null then open c1 for SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal, IVRTotal / hjTotal * 100% IVRPercent, jtTotal / hjTotal * 100% 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(r.datetime,'HH24')) and r.datetime between v_startday and v_endday where r.calltype ='in' and r.dnis like '%'||v_phone||'%' group by rn) t ORDER BY t.calltime; end if; end proc_jxTJ;
有点问题,修改下,一下代码未包含type = 'p'的处理create or replace procedure proc_jxTJ ( v_startday in date, v_endday in date, v_phone in varchar2(15), c1 out sys_refcursor ) isbegin if v_phone is not null then open c1 for SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal, IVRTotal / hjTotal * 100% IVRPercent, jtTotal / hjTotal * 100% 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(r.datetime,'HH24')) and r.datetime between v_startday and v_endday where r.calltype ='in' and r.dnis like '%'||v_phone||'%' group by rn) t ORDER BY t.calltime; end if; end proc_jxTJ;
create or replace procedure proc_jxTJ ( v_startday in date, v_endday in date, v_phone in varchar2(15), c1 out sys_refcursor ) isbegin if v_phone is not null then open c1 for SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal, IVRTotal / hjTotal * 100% IVRPercent, jtTotal / hjTotal * 100% 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(r.datetime,'HH24')) and r.datetime between v_startday and v_endday where r.calltype ='in' and r.dnis like '%'||v_phone||'%' group by rn) t ORDER BY t.calltime; end if; end proc_jxTJ;
完整代码如下:create or replace package Pack_jxTj is type sys_refcursor is ref cursor; --定义游标变量用于返回记录集 procedure Proc_jxTj ( v_startday in datetime, v_endday in datetime, v_phone in varchar2(15), c1 out sys_refcursor); end Pack_jxTj; --定义包主体 create or replace package body Pack_jxTj is procedure Proc_jxTj (v_startday in datetime, v_endday in datetime, v_phone in varchar2(15), c1 out sys_refcursor ) 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 v_startday and v_endday 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 v_startday and v_endday where r.calltype ='IN' group by rn) t ORDER BY t.calltime; end if; end proc_jxTJ; end Pack_jxTj; 编译都没有通过,也没弹出错误提示 ,奇怪 中间的那段SQL语句加上参数后单独执行没问题
报错:pls-00103 character 符号":="被替换成"(" 把 v_phone in varchar2(15), 改成 v_phone in varchar2 就不报错了 奇怪 ....那位能解释一下
存储过程代码: create or replace package Pack_jxTj is type refCursorType is ref cursor; --定义游标变量用于返回记录集 procedure Proc_jxTj ( v_startday in date, v_endday in date, v_phone in varchar2, c1 out refCursorType); end Pack_jxTj; --定义包主体 create or replace package body Pack_jxTj is procedure Proc_jxTj (
v_startday in date, v_endday in date, 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 v_startday and v_endday 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 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 v_startday and v_endday where r.calltype ='IN' group by rn) t ORDER BY t.calltime; end if; end proc_jxTJ; end Pack_jxTj;编译可以通过,调用的时候报错, ORA-04068:已丢弃程序包的当前状态 ORA-06508:PL/SQL:无法再调用之前找到程序单元 。哪里出问题了
1.权限问题。当前用户有没有权限调用次package。 2.调用语句问题。
改成: 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;OK,奇怪,这个和之前的SQL的存储过程相比运行起来速度要慢很多 ,SQLSERVER 和Oracle库中的两张表结构都一样,最后执行起来的效率感觉差距很大,why? 索引还是 谁推荐一下关于SQL优化方面的知识?
这里 if v_phone is null then SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal,掉了一句 open c1 for 汗。
( v_startday in date,
v_endday in date,
v_phone in varchar2(15)
)
is
cursor c1;begin
if v_phone is not null then
open c1 for
SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal,
IVRTotal / hjTotal * 100% IVRPercent,
jtTotal / hjTotal * 100% 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(r.datetime,'HH24'))
and r.datetime between v_startday and v_endday
where r.calltype ='in'
and r.dnis like '%'||v_phone||'%'
group by rn) t
ORDER BY t.calltime;
end if;
end proc_jxTJ;
( v_startday in date,
v_endday in date,
v_phone in varchar2(15),
c1 out sys_refcursor
)
isbegin
if v_phone is not null then
open c1 for
SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal,
IVRTotal / hjTotal * 100% IVRPercent,
jtTotal / hjTotal * 100% 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(r.datetime,'HH24'))
and r.datetime between v_startday and v_endday
where r.calltype ='in'
and r.dnis like '%'||v_phone||'%'
group by rn) t
ORDER BY t.calltime;
end if;
end proc_jxTJ;
( v_startday in date,
v_endday in date,
v_phone in varchar2(15),
c1 out sys_refcursor
)
isbegin
if v_phone is not null then
open c1 for
SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal,
IVRTotal / hjTotal * 100% IVRPercent,
jtTotal / hjTotal * 100% 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(r.datetime,'HH24'))
and r.datetime between v_startday and v_endday
where r.calltype ='in'
and r.dnis like '%'||v_phone||'%'
group by rn) t
ORDER BY t.calltime;
end if;
end proc_jxTJ;
sql开发中经常需要使用数字或者时间的常量表。比如,输出一年的月份表,一天中每个时间段。 输出1000以内的自然数等等。数量连续且不超过2048。那么使用master..spt_values表就会再也方便不过了。
我这里需要输出一天24个小时里,每个时段的进线总数据 用这个比较方便
IVRTotal / hjTotal * 100% IVRPercent, IVR进线比例
jtTotal / hjTotal * 100% jtPercent 接线比例
这两列要求用百分数表示 只保留整数部分,小数部分四舍五入 结果如23% 45%,如果直接按照你上面那样写报错:无效的字符,这里可能又要强转一下
type sys_refcursor is ref cursor; --定义游标变量用于返回记录集
procedure Proc_jxTj
( v_startday in datetime,
v_endday in datetime,
v_phone in varchar2(15),
c1 out sys_refcursor);
end Pack_jxTj;
--定义包主体
create or replace package body Pack_jxTj is
procedure Proc_jxTj
(v_startday in datetime,
v_endday in datetime,
v_phone in varchar2(15),
c1 out sys_refcursor
)
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 v_startday and v_endday
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 v_startday and v_endday
where r.calltype ='IN' group by rn) t
ORDER BY t.calltime;
end if;
end proc_jxTJ;
end Pack_jxTj;
编译都没有通过,也没弹出错误提示 ,奇怪 中间的那段SQL语句加上参数后单独执行没问题
把
v_phone in varchar2(15),
改成
v_phone in varchar2 就不报错了 奇怪 ....那位能解释一下
create or replace package Pack_jxTj is
type refCursorType is ref cursor; --定义游标变量用于返回记录集
procedure Proc_jxTj
(
v_startday in date,
v_endday in date,
v_phone in varchar2,
c1 out refCursorType);
end Pack_jxTj;
--定义包主体
create or replace package body Pack_jxTj is
procedure Proc_jxTj
(
v_startday in date,
v_endday in date,
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 v_startday and v_endday
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
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 v_startday and v_endday
where r.calltype ='IN' group by rn) t
ORDER BY t.calltime;
end if;
end proc_jxTJ;
end Pack_jxTj;编译可以通过,调用的时候报错,
ORA-04068:已丢弃程序包的当前状态
ORA-06508:PL/SQL:无法再调用之前找到程序单元
。哪里出问题了
2.调用语句问题。
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;OK,奇怪,这个和之前的SQL的存储过程相比运行起来速度要慢很多 ,SQLSERVER 和Oracle库中的两张表结构都一样,最后执行起来的效率感觉差距很大,why? 索引还是 谁推荐一下关于SQL优化方面的知识?
这里 if v_phone is null then
SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal,掉了一句
open c1 for 汗。
http://hi.baidu.com/totaobao/blog/item/94d1a0d3d6201934960a166a.html
(@startday datetime,@endday datetime, @phone varchar(15)) with encryption as
楼主的存储过程还做了加密处理,呵呵
oracle可以用数组来作为参数传递到存储过程,由存储过程内部来批量insert。如果是jdbc的话,可以用PreparedStatement的executeBatch()功能来批量提交。至于其他的比如ADO之类的应该也有这种方法。
jdbc例子:
我这里是读txt文件批量插入的。如果数量太大比如有100万条,最好外面再做个循环,1万条提交一次。public static void readFileWriteBack(String fileName, Connection con)
throws Exception {
String s = "";
File f = new File(fileName);
if (!f.exists()) {
throw new Exception("file doesn't exist....");
}
BufferedReader br = null;
PreparedStatement pstmt = null;
try {
br = new BufferedReader(new InputStreamReader(
new FileInputStream(f)));
String sql = "insert into tbl_report(id,name,operator,sdate)"
+ "values(?,?,?,'2009-12-07')";
pstmt = con.prepareStatement(sql);
while ((s = br.readLine()) != null) {
String[] c = s.split("\t");
printArray(c);
if (c.length == 3) {
pstmt.setString(1, c[0]);
pstmt.setString(2, c[1]);
pstmt.setString(3, c[2]);
pstmt.addBatch();
}
}
pstmt.executeBatch();
System.out.println("file write back finished");
} catch (Exception e) {
throw e;
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
br.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}