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

Question

顶一下   求一个与之功能一模一样的ORACLE存储过程   关键是原SQL存储过程中用到了系统表master..spt_values 

Answer 1

若你能解释下m.type ='P'的意思我想效果会更好这个查下语法也不难写的

Answer 2

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;

Answer 3

有点问题，修改下，一下代码未包含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;

Answer 4

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;

Answer 5

sql开发中经常需要使用数字或者时间的常量表。比如，输出一年的月份表，一天中每个时间段。 输出1000以内的自然数等等。数量连续且不超过2048。那么使用master..spt_values表就会再也方便不过了。
我这里需要输出一天24个小时里，每个时段的进线总数据 用这个比较方便

Answer 6

select rownum - 1 rn from dual connect by rownum <= 24返回0，1，2， 23你的条件里面，between 0 and 24，24其实没必要的，小时数不可能出现24

Answer 7

忘了说一下 Report_call_hist表的 字段datetime为varchar类型  ，我在执行的时候报错:无效的数字 ，这里 to_number(to_char(r.datetime,'HH24')) 可能有问题 
  

Answer 8

这里改成 to_number(to_char(to_date(r.datetime,'yyyy-mm-dd HH24:mi:ss'),'HH24'))就可以了，但是我要计算
IVRTotal / hjTotal * 100% IVRPercent, IVR进线比例
jtTotal / hjTotal * 100% jtPercent    接线比例  
这两列要求用百分数表示 只保留整数部分，小数部分四舍五入  结果如23%  45%，如果直接按照你上面那样写报错：无效的字符，这里可能又要强转一下  

Answer 9

完整代码如下: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语句加上参数后单独执行没问题  

Answer 10

报错:pls-00103  character 符号":="被替换成"("  
把
 v_phone     in varchar2(15),
 改成 
 v_phone     in varchar2  就不报错了 奇怪 ....那位能解释一下  

Answer 11

存储过程代码：
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:无法再调用之前找到程序单元 
 。哪里出问题了  

Answer 12

1.权限问题。当前用户有没有权限调用次package。
2.调用语句问题。

Answer 13

改成:
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优化方面的知识？

Answer 14

这里 if v_phone is  null then
    SELECT calltime,hjTotal,IVRTotal,fqTotal,jtTotal,掉了一句 
open c1 for  汗。   

Answer 15

各位推荐一下关于Oracle优化方面的书籍或者资料(电子书或者Word文档都可以)  

Answer 16

http://hi.baidu.com/totaobao/blog/item/94d1a0d3d6201934960a166a.html

Answer 17

谢谢楼上的 ,关于从Excel表格里批量导入数据到ORACLE或者SQLSERVER数据库里 大家有没有更高效的办法 ，我是直接读取存到DataSet里 ，然后循环往数据库里插入，很耗资源 ，相当的慢。。

Answer 18

create  proc proc_jxTJ
(@startday datetime,@endday datetime, @phone varchar(15)) with encryption as
楼主的存储过程还做了加密处理，呵呵

Answer 19

有点问题，修改下，一下代码未包含type = 'p'的处理

Answer 20

oracle性能诊断艺术 瑞士 christian antognini人民邮电2009年10月第一版

Answer 21

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();
}
}
}

Answer 22

我的那个是ASP.NET程序，不过采用FileStream的方式边读取边插入速度肯定比一次读取到DataSet中然后再插入 要快的多