oracle行锁 merge,merge into引发死锁

oracle行锁 merge,merge into引发死锁createorreplaceprocedureup_mid_66880002(v_rqnumber,计算日期rc1outbssys.tp_cursor,rc2outbssys.tp_cursor)asnb_rqnumber;nb_ExecTimedate;check_rqdate;nb_rowsnumber(10);v_status…

create or replace procedure up_mid_66880002(v_rq number, –计算日期

rc1  out bssys.tp_cursor,

rc2  out bssys.tp_cursor)

as

nb_rq       number;

nb_ExecTime date;

check_rq    date;

nb_rows     number(10);

v_status    int;

v_msg       varchar2(200);

begin

nb_ExecTime := sysdate;

–当处理日期为空时,取系统日期作为处理日期

if v_rq is null then

nb_rq := to_number(to_char(sysdate, ‘yyyymmdd’));

else

nb_rq := v_rq;

end if;

–校验日期格式

begin

select to_date(nb_rq, ‘yyyymmdd’) into check_rq from dual;

exception

when others then

v_status := -2;

v_msg    := ‘日期格式错误’;

open rc1 for

select v_status as errorcode, v_msg as errormsg from dual;

return;

end;

–根据员工角色、员工角色分配查询出所有“工作平台坐席人员”、“质检人员”

–初始化坐席统计汇总表数据

/*merge into t_zxtjhz a

using(

select t2.ygbh, decode(t3.jsbh,4,1,30,1,409,2,1) as mtlx

from t_yg t1, t_yg_ygjsfp t2, t_yg_js t3

where t1.ygbh = t2.ygbh and t2.jsbh = t3.jsbh and t1.ygzt = 1

and t2.zt = 1 and (t3.jsbh = 4 or t3.jsbh = 40)

) b

on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)

when matched then update set

zxsc = 0,hrcs = 0,hrsc = 0,pjhrsc = 0,hccs = 0,hcsc = 0,pjhcsc = 0,fwcs = 0,fwsc = 0,

pjfwsc = 0,zjs = 0,zjsc = 0,fds = 0,pxs = 0,pjdf = 0,sscgs = 0,cjbhg = 0,bzjs = 0,bzjpjf = 0

when not matched then insert(a.rq, a.ygbh, a.mtlx) values(nb_rq, b.ygbh, b.mtlx);

nb_rows := sql%rowcount ;

commit;

p_cc_log(nb_rq, ‘up_mid_66880002’, nb_ExecTime, sysdate, ‘0’, nb_rows,’0-初始化计算员工成功!’);*/

–1.计算在线时长(只统计了1:话务,2:全媒体,91:UC签出,21%:话务签出)

nb_ExecTime := sysdate;

merge into t_zxtjhz a

using (select t1.ygbh, sum(t1.hs) zxsc, 1 as mtlx

from t_hwczlsk t1

where t1.rq = nb_rq

and t1.czlx like ‘21%’

and t1.sfcg = 1

group by t1.ygbh

union all

select t1.ygbh, sum(t1.hs) zxsc, 2 as mtlx

from t_hwczlsk t1

where t1.rq = nb_rq

and t1.czlx = 91

and t1.sfcg = 1

group by t1.ygbh) b

on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)

when matched then

update set a.zxsc = b.zxsc

when not matched then

insert

(a.rq, a.ygbh, a.mtlx, a.zxsc)

values

(nb_rq, b.ygbh, b.mtlx, b.zxsc);

nb_rows := sql%rowcount;

commit;

p_cc_log(nb_rq,

‘up_mid_66880002’,

nb_ExecTime,

sysdate,

‘0’,

nb_rows,

‘1-计算在线时长成功!’);

–2.计算呼入次数,时长,平均呼入时长

nb_ExecTime := sysdate;

merge into t_zxtjhz a

using (select t1.ygbh,

count(t1.lsh) hrcs,

sum(t1.thsc) hrsc,

round(avg(t1.thsc), 2) pjhrsc,

1 mtlx

from t_zxthlsk t1

where t1.rq = nb_rq

and mtlx = 0

and t1.hjlx = 0

group by t1.ygbh) b

on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)

when matched then

update set a.hrcs = b.hrcs, a.hrsc = b.hrsc, a.pjhrsc = b.pjhrsc

when not matched then

insert

(a.rq, a.ygbh, a.mtlx, a.hrcs, a.hrsc, a.pjhrsc)

values

(nb_rq, b.ygbh, b.mtlx, b.hrcs, b.hrsc, b.pjhrsc);

nb_rows := sql%rowcount;

commit;

p_cc_log(nb_rq,

‘up_mid_66880002’,

nb_ExecTime,

sysdate,

‘0’,

nb_rows,

‘2.计算呼入次数、时长、平均呼入时长成功!’);

–3.计算呼出次数,时长,平均呼出时长

nb_ExecTime := sysdate;

merge into t_zxtjhz a

using (select t1.ygbh,

count(t1.lsh) hccs,

sum(t1.thsc) hcsc,

avg(t1.thsc) pjhcsc,

1 mtlx

from t_zxthlsk t1

where t1.rq = nb_rq

and mtlx = 0

and t1.hjlx <> 0

group by t1.ygbh) b

on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)

when matched then

update set a.hccs = b.hccs, a.hcsc = b.hcsc, a.pjhcsc = b.pjhcsc

when not matched then

insert

(a.rq, a.ygbh, a.mtlx, a.hccs, a.hcsc, a.pjhcsc)

values

(nb_rq, b.ygbh, b.mtlx, b.hccs, b.hcsc, b.pjhcsc);

nb_rows := sql%rowcount;

commit;

p_cc_log(nb_rq,

‘up_mid_66880002’,

nb_ExecTime,

sysdate,

‘0’,

nb_rows,

‘3.计算呼出次数、时长、平均呼出时长成功!’);

–4.计算服务次数,服务时长,平均服务时长

nb_ExecTime := sysdate;

merge into t_zxtjhz a

using (select t1.ygbh,

count(t1.lsh) fwcs,

sum(t1.thsc) fwsc,

round(avg(t1.thsc), 2) pjfwsc,

2 mtlx

from t_zxthlsk t1

where t1.rq = nb_rq

and mtlx != 0 –全媒体

group by t1.ygbh) b

on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)

when matched then

update set a.fwcs = b.fwcs, a.fwsc = b.fwsc, a.pjfwsc = b.pjfwsc

when not matched then

insert

(a.rq, a.ygbh, a.mtlx, a.fwcs, a.fwsc, a.pjfwsc)

values

(nb_rq, b.ygbh, b.mtlx, b.fwcs, b.fwsc, b.pjfwsc);

nb_rows := sql%rowcount;

commit;

p_cc_log(nb_rq,

‘up_mid_66880002’,

nb_ExecTime,

sysdate,

‘0’,

nb_rows,

‘4.计算服务次数,服务时长,平均服务时长成功!’);

–5.计算质检个数,质检录音时长,辅导座席数,培训数,平均得分,申诉成功数,抽检不合格数

nb_ExecTime := sysdate;

merge into t_zxtjhz a

using (select t1.zjyg as ygbh,

count(t1.lsh) zjs,

sum(tt.zjsc) zjsc,

sum(fd.fds) fds,

count(px.zjlsh) pxs,

avg(t1.zjzpf) pjdf,

count(zjss.zjlsh) sscgs,

(select count(rwk.lsh) cjbhg

from t_zj_rwk yy

inner join (select a.*

from t_zj_rwk a, t_zj_djk c

where a.bzbh = c.bzbh

and c.djmc = ‘不合格’

and a.zjzpf <= c.djzgf) rwk

on yy.swlsh = rwk.swlsh

where yy.zjrq = nb_rq

and yy.sfpf != 0

group by yy.zjyg) cjbhg

,

4 as mtlx

from t_zj_rwk t1

inner join (select t1.swlsh as swlsh, sum(t2.thsc) zjsc

from t_zj_rwk t1, t_zxthlsk t2

where t1.swlsh = t2.order_id

and t1.dxbh in (1, 5)

and t1.zjrq = nb_rq

and t1.sfpf != 0

group by t1.swlsh

union all –呼出

select t1.swlsh as swlsh, nvl(sum(t4.thsc), 0) zjsc

from t_zj_rwk      t1,

t_hcgl_khmddy t2,

t_hcgl_hcxw   t3,

t_zxthlsk     t4

where t1.swlsh = t2.id

and t1.swlsh = t3.rwid(+)

and t3.thlsh = t4.lsh(+)

and t1.zjrq = nb_rq

and t1.sfpf != 0

and t1.dxbh = 2

group by t1.swlsh) tt

on t1.swlsh = tt.swlsh

left join (select k.fqrbh, k.swlsh, count(k.jsrbh) fds

from t_xxtsk k

where xxdx = 210

and scrq = nb_rq

group by k.fqrbh, k.swlsh) fd

on t1.zjyg = fd.fqrbh

and t1.lsh = fd.swlsh

left join t_gzl_px px

on t1.lsh = px.zjlsh

left join (select a.zjlsh

from t_gzl_zjss a

where exists

(select 1

from (select t1.processkey,

t1.processinstanceid,

t1.doresult

from t_nodetask_handle t1

where rowid =

(select max(rowid)

from t_nodetask_handle t2

where t1.processkey =

t2.processkey

and t1.processinstanceid =

t2.processinstanceid)) b

where a.process_key = b.processkey

and a.processinstanceid = b.processinstanceid

and a.process_key = ‘qualityinspectionflow’

and b.doresult = ‘0’ –质检申诉通过

)) zjss

on t1.lsh = zjss.zjlsh

where t1.zjrq = nb_rq

and t1.sfpf != 0

group by t1.zjyg) b

on (a.rq = nb_rq and a.ygbh = b.ygbh and a.mtlx = b.mtlx)

when matched then

update

set a.zjs   = b.zjs,

a.zjsc  = b.zjsc,

a.fds   = b.fds,

a.pxs   = b.pxs,

a.pjdf  = b.pjdf,

a.sscgs = b.sscgs,

a.cjbhg = b.cjbhg

when not matched then

insert

(a.rq,

a.ygbh,

a.mtlx,

a.zjsc,

a.fds,

a.pxs,

a.pjdf,

a.sscgs,

a.cjbhg)

values

(nb_rq,

b.ygbh,

b.mtlx,

b.zjsc,

b.fds,

b.pxs,

b.pjdf,

b.sscgs,

b.cjbhg);

nb_rows := sql%rowcount;

commit;

p_cc_log(nb_rq,

‘up_mid_66880002’,

nb_ExecTime,

sysdate,

‘0’,

nb_rows,

‘5.计算质检个数、质检录音时长、辅导座席数!’);

–6.计算坐席被质检通话数、质检平均分

nb_ExecTime := sysdate;

merge into t_zxtjhz a

using (

–呼入被质检

select t4.bzjyg ygbh, count(t4.swlsh) bzjs, avg(t4.zjzpf) bzjpjf

from t_cc_order t2, t_zxthlsk t3, t_zj_rwk t4

where t2.order_id = t3.order_id

and t2.order_id = t4.swlsh

and t3.rq = nb_rq

and t4.sfpf != 0

group by t4.bzjyg

union all

–呼出被质检

select a3.bzjyg ygbh, count(a3.swlsh) bzjs, avg(a3.zjzpf) bzjpjf

from t_hcgl_khmddy a1, t_hcgl_hcxw a2, t_zj_rwk a3, t_zxthlsk a4

where a1.id = a2.rwid

and a1.id = a3.swlsh

and a2.thlsh = a4.lsh

and a4.rq = nb_rq

and a3.sfpf != 0

group by a3.bzjyg) b

on (a.rq = nb_rq and a.ygbh = b.ygbh)

when matched then

update set a.bzjs = b.bzjs, a.bzjpjf = b.bzjpjf;

nb_rows := sql%rowcount;

commit;

p_cc_log(nb_rq,

‘up_mid_66880002’,

nb_ExecTime,

sysdate,

‘0’,

nb_rows,

‘5.计算质检个数、质检录音时长、辅导座席数!’);

v_status := 0;

v_msg    := ‘success’;

open rc1 for

select v_status as errorcode, v_msg as errormsg from dual;

open rc2 for

select nvl(v_rq, to_number(to_char(sysdate, ‘yyyymmdd’))) as dealdate

from dual;

exception

when others then

v_status := Sqlcode;

v_msg    := Sqlerrm;

p_cc_log(nb_rq,

‘up_mid_66880002’,

nb_ExecTime,

sysdate,

‘1’,

nb_rows,

‘坐席指标计算失败:’ || to_char(Sqlcode) || ‘;’ || Sqlerrm);

open rc1 for

select v_status as errorcode, v_msg as errormsg from dual;

open rc2 for

select nvl(v_rq, to_number(to_char(sysdate, ‘yyyymmdd’))) as dealdate

from dual;

end up_mid_66880002;

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/37093.html

(0)
编程小号编程小号

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注