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;
上一篇: 页面长按截图+保存页面截图到本地功能
下一篇: node 开发笔记