PLSQL存储过程及游标的使用

先贴代码出来，解释之后再写：

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;