欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

PLSQL存储过程及游标的使用

程序员文章站 2022-06-14 08:45:22
...

先贴代码出来,解释之后再写:

create or replace procedure AGENTOPER(
------------------------------------------------------
---个人工作量统计报表
------------------------------------------------------
  v_date                     in varchar2             --输入日期:yyyymmdd
)
as
  V_OPER               VARCHAR2(40)  :='';   --工号
  V_INTIMES            NUMBER(8)     :=0;    --接通次数
  V_OUTTIMES           NUMBER(8)     :=0;    --呼出次数
  V_INTIME             NUMBER(12)    :=0;    --平均通话时长
  V_OUTTIME            NUMBER(12)    :=0;    --平均时长
  V_TOTALTIME          NUMBER(16)    :=0;    --工作总时长
  V_FEELCOUNT          NUMBER(8)     :=0;    --满意度参与量
  V_FEELGOODCOUNT      VARCHAR2(12)  :='';   --满意度

  v_sql                varchar2(1000):='';
  v_curmonth           number(2);            --当前月份

  TYPE REF_CURSOR IS REF CURSOR;
    c_workcount REF_CURSOR;

begin

          delete from DB_AGENTOPER_STAT where substr(opertime,0,8) = v_date;

          commit;
          ------------------------------------------------------------------------
          --总数
          ------------------------------------------------------------------------
          open c_workcount for 'select  distinct oper from db_agent_operator g where 1=1 and substr(sid,1,8)='||v_date;
          loop
               <<c_workcount_mark>>

                              V_OPER          :='';  --工号
                              V_INTIMES       :=0;
                              V_INTIME        :=0;
                              V_OUTTIMES      :=0;
                              V_OUTTIME       :=0;
                              V_TOTALTIME     :=0;
                              V_FEELCOUNT     :=0;
                              V_FEELGOODCOUNT :='';

           fetch c_workcount into V_OPER;

                           -------------------------------------------
                           -----接通次数
                           -------------------------------------------
                           v_sql := 'select count(*) from (select * from db_agent_operator where opertype = 18) e,(select * from db_agent_operator where opertype = 19) n where e.oper='''||V_OPER||''' and e.phone_no=n.phone_no';
                           execute immediate v_sql into V_INTIMES;

                           -------------------------------------------
                           -----平均通话时长
                           -------------------------------------------
                           v_sql := 'select round((select sum(n.reserve4-e.reserve4) from (select * from db_agent_operator where opertype = 18) e,(select * from db_agent_operator where opertype = 19) n where e.oper='''||V_OPER||''' and e.phone_no=n.phone_no) / (select case when c=0 then 1 else c end from (select count(*) c from (select * from db_agent_operator where opertype = 18) e,(select * from db_agent_operator where opertype = 19) n where e.oper='''||V_OPER||''' and e.phone_no=n.phone_no)),2) from dual';
                           execute immediate v_sql into V_INTIME;

                           -------------------------------------------
                           -----呼出次数
                           -------------------------------------------
                           v_sql := 'select count(*) from (select * from db_agent_operator where opertype = 8) e,(select * from db_agent_operator where opertype = 19) n where e.oper='''||V_OPER||''' and e.phone_no=n.phone_no';
                           execute immediate v_sql into V_OUTTIMES;

                           -------------------------------------------
                           -----平均呼出时长
                           -------------------------------------------
                           v_sql := 'select round((select sum(n.reserve4-e.reserve4) from (select * from db_agent_operator where opertype = 8) e,(select * from db_agent_operator where opertype = 19) n where e.oper='''||V_OPER||''' and e.phone_no=n.phone_no) / (select case when c=0 then 1 else c end from (select count(*) c from (select * from db_agent_operator where opertype = 8) e,(select * from db_agent_operator where opertype = 19) n where e.oper='''||V_OPER||''' and e.phone_no=n.phone_no)),2) from dual';
                           execute immediate v_sql into V_OUTTIME;

                           -------------------------------------------
                           -----工作总时长
                           -------------------------------------------
                           v_sql := 'select sum(n.reserve4-e.reserve4) from (select * from db_agent_operator where opertype = 2) e,(select * from db_agent_operator where opertype = 3) n where e.oper='''||V_OPER||''' and e.phone_no=n.phone_no';
                           execute immediate v_sql into V_TOTALTIME;

                           -------------------------------------------
                           -----满意度参与量
                           -------------------------------------------
                           v_sql := 'select count(*) from db_agent_operator where oper='''||V_OPER||''' and opertype = 14';
                           execute immediate v_sql into V_FEELCOUNT;

                           -------------------------------------------
                           ----- 满意度
                           -------------------------------------------
                           v_sql := 'select to_char(round((select count(*) from db_qos q,db_agent_operator a where oper='''||V_OPER||''' and q.sid=a.sid and q.score=''1'' and a.opertype = 14) / (select case when c=0 then 1 else c end from (select count(*) from db_agent_operator where oper='''||V_OPER||''' and opertype = 14)),2)*100)||''%'' from dual';
                           execute immediate v_sql into V_FEELGOODCOUNT;

          

           begin
            ------------------------------------------------------------------------
             --插表
            ------------------------------------------------------------------------
             insert into DB_AGENTOPER_STAT(OPERTIME,OPER,INTIMES,INTIME,OUTTIMES,OUTTIME,TOTALTIME,FEELCOUNT,FEELGOODCOUNT)
                                  values(v_date,V_OPER,V_INTIMES,V_INTIME,V_OUTTIMES,V_OUTTIME,V_TOTALTIME,V_FEELCOUNT,V_FEELGOODCOUNT);


          end;

          exit when c_workcount%notfound;

       end loop;


       close c_workcount;

      commit;
   exception
 when others then
     dbms_output.put_line(sqlcode||':'||sqlerrm);
     rollback;
     return;

end;

 

相关标签: PLSQL