CREATE OR REPLACE PROCEDURE up_mid_91100073(V_p_gybh int, -- 员工编号
V_p_gymm varchar2, -- 员工密码
V_p_czzd varchar2, -- 操作站点
V_p_gnbh int, -- 功能编号
V_p_kzcs varchar2, -- 扩展参数
v_hcsjbh int,
v_hcsjlx int,
v_ygbhs varchar2,
v_tdwbhs varchar2,--分支机构
v_ksrq int,
v_jsrq int,
RC1 OUT jz_oraoledb.m_refcur,
RC2 OUT jz_oraoledb.m_refcur) AS
v_count int;
p_tdwbhs varchar2(8000);
nb_sqlerrm varchar2(2000);
BEGIN
p_tdwbhs := v_tdwbhs;
select count(*)
into v_count
from t_Yg a, t_dw b
where a.dwbh = b.dwbh
and a.ygbh = v_p_gybh
and lxdh3 = 4;
if v_count > 0 then
begin
select dwbh into p_tdwbhs from t_Yg where ygbh = v_p_gybh;
exception
when others then
p_tdwbhs := '';
end;
end if;
execute immediate 'truncate table TMP_YYHF_CG_1';
execute immediate 'truncate table TMP_TJJGHZ_4';
--2融客户回访
insert into TMP_YYHF_CG_1
(YYB, XKHS, ZT, FLAG)
select dwbh, count(distinct khbh), zt, max(clzt)
from (select e.jgid dwbh,
a.khbh,
case
when (a.clzt = 2 or (a.clzt = 0 and a.kzbs = 1 )) and a.zhhcjg in (57, 58) then
1--完成问卷
when (a.clzt = 2 or (a.clzt = 0 and a.kzbs = 1 )) and a.zhhcjg = 72 then
2 --无人接听
when (a.clzt = 2 or (a.clzt = 0 and a.kzbs = 1 )) and a.zhhcjg in (59, 75, 63, 65, 73) then
4 -- 客户拒绝接听我司电话
when (a.clzt = 2 or (a.clzt = 0 and a.kzbs = 1 )) and a.zhhcjg in (61,84) then
3--无效电话
else
0 ---没回访
end zt,
case when (a.clzt = 2 or (a.clzt = 0 and a.kzbs = 1 )) then 2
else 0 end as clzt
from t_hcgl_khmddy a,
t_yg d,
t_kh e
where a.shbz='R' and a.khbh = e.khbh
and d.ygbh = a.zxygbh
-- and (a.clzt = 2 or
-- (a.clzt = 0 and a.kzbs = 1 ))
and (v_hcsjbh=-1 or a.hcsjbh=v_hcsjbh)
and (charindex(',' || to_char(a.zxygbh) || ',',
',' || to_char(v_ygbhs) || ',') > 0 or
v_ygbhs is null)
and (charindex(',' || to_char(e.jgid) || ',',
',' || to_char(p_tdwbhs) || ',') > 0 or
p_tdwbhs is null)) --where --zt=1
group by dwbh, zt;
commit;
insert into TMP_TJJGHZ_4
select dwbh, 0, 0, 0, 0, 0, 0, 0, 0, 0
from t_dw
where lxdh3 = 4
and (charindex(',' || to_char(dwbh) || ',',
',' || to_char(p_tdwbhs) || ',') > 0 or
p_tdwbhs is null);
--融资融券客户开户数
merge into TMP_TJJGHZ_4 b
using (select count(*) xkhs, jgid
from t_kh m
where khrq between v_ksrq and v_jsrq
and khzt = 0
and khlx = 3
group by jgid) a
on (a.jgid = b.yyb)
when matched then
update set RZRQKHS = a.xkhs;
commit;
--融资融券开户回访任务数
merge into TMP_TJJGHZ_4 m
using (select yyb, sum(xkhs) ykhs
from TMP_YYHF_CG_1
group by yyb) a
on (a.yyb = m.yyb)
when matched then
update set m.RZRQHFZS = a.ykhs;
---融资融券开户回访任务未完成回访数
merge into TMP_TJJGHZ_4 m
using (select yyb, sum(xkhs) xkhwzhfs
from TMP_YYHF_CG_1
where flag=0
group by yyb) a
on (a.yyb = m.yyb)
when matched then
update set m.RZRQHFWWCS = a.xkhwzhfs;
--融资融券开户回访任务已完成回访数
--融资融券回访有效数
--融资融券不完整回访数
--融资融券回访无效电话数
merge into TMP_TJJGHZ_4 m
using (select yyb, sum(xkhs) as RZRQHFWCS,sum(case when zt in(1,2) then xkhs else 0 end) RZRQYXS,
sum(case when zt =4 then xkhs else 0 end) RZRQBWZS,
sum(case when zt =3 then xkhs else 0 end) RZRQWXDHS
from TMP_YYHF_CG_1
where flag =2
group by yyb) a
on (a.yyb = m.yyb)
when matched then
update set m.RZRQYXS = a.RZRQYXS,m.RZRQBWZS=a.RZRQBWZS,m.RZRQHFWCS=a.RZRQHFWCS,m.RZRQWXDHS=a.RZRQWXDHS;
commit;
OPEN RC1 FOR
SELECT 0 AS errorcode, '查询成功!' AS errormsg FROM DUAL;
OPEN RC2 FOR
select a.*,
(b.dwmc || '(' || to_char(b.dwbh) || ')') as dwmc,
(case
when a.RZRQKHS = 0 then
'0.00%'
else
to_char(round((a.RZRQHFZS) / (a.RZRQKHS), 2) * 100) || '%'
end) rzrqhfblx,
(case
when a.RZRQHFZS = 0 then
'0.00%'
else
to_char(round((a.RZRQYXS) / a.RZRQHFZS, 2) * 100) || '%'
end) rzrqhfyxblx
from TMP_TJJGHZ_4 a, t_dw b
where a.yyb = b.dwbh
order by b.dwbh;
exception
when others then
nb_sqlerrm := sqlerrm;
OPEN RC1 FOR
SELECT -9110 AS errorcode, '查询yyb失败!' || nb_sqlerrm AS errormsg
FROM DUAL;
END up_mid_91100073;
使用例子2
create or replace procedure up_mid_91100029( --
V_p_gybh int, -- 员工编号
V_p_gymm varchar2, -- 员工密码
V_p_czzd varchar2, -- 操作站点
V_p_gnbh int, -- 功能编号
V_p_kzcs varchar2, -- 扩展参数
v_qsrq int,
jsrq int,
v_tjlx int, -- 1 新客户数 2,新开户任务数,3--已完成,4未完成,5回访有效,5不完整数,6无效电话
RC1 OUT jz_oraoledb.m_refcur,
RC2 OUT jz_oraoledb.m_refcur) as
begin
OPEN RC1 FOR
SELECT 0 as errorcode, '查询明细成功!!' as errormsg FROM DUAL;
if v_tjlx = 1 then
OPEN RC2 FOR
select m.khbh, m.khxm, m.jgid dwbh, m.jgmc dwmc, m.khzzh, m.khjl
from t_kh m
where khrq between v_qsrq and jsrq
and khzt = 0
and khlx = 3
and rownum < 5;
elsif v_tjlx = 2 then
OPEN RC2 FOR
select e.khbh,
e.khxm,
e.jgid dwbh,
e.jgmc dwmc,
e.khzzh,
e.khjl,
r.hcsjbh,
x.hcsjmc,
f_get_ygxm(a.zxygbh) ygxm
from t_hcgl_khmddy a,
t_rzrq_hfryk r,
t_hcgl_hcsjdy x,
t_yg d,
t_kh e
where a.hcsjbh = r.hcsjbh
and khrq between v_qsrq and jsrq
and a.khbh = e.khbh
and d.ygbh = a.zxygbh
and x.hcsjbh = a.hcsjbh
and x.hcsjlx = 63;
elsif v_tjlx = 3 then
OPEN RC2 FOR
select e.khbh,
e.khxm,
e.jgid dwbh,
e.jgmc dwmc,
e.khzzh,
e.khjl,
r.hcsjbh,
x.hcsjmc,
f_get_ygxm(a.zxygbh) ygxm
from t_hcgl_khmddy a,
t_rzrq_hfryk r,
t_hcgl_hcsjdy x,
t_yg d,
t_kh e
where a.hcsjbh = r.hcsjbh
and khrq between v_qsrq and jsrq
and a.khbh = e.khbh
and d.ygbh = a.zxygbh
and x.hcsjbh = a.hcsjbh
and x.hcsjlx = 63
and (a.clzt = 2 or (a.clzt = 0 and a.kzbs = 1));
elsif v_tjlx = 4 then
OPEN RC2 FOR
select e.khbh,
e.khxm,
e.jgid dwbh,
e.jgmc dwmc,
e.khzzh,
e.khjl,
r.hcsjbh,
x.hcsjmc,
f_get_ygxm(a.zxygbh) ygxm
from t_hcgl_khmddy a,
t_rzrq_hfryk r,
t_hcgl_hcsjdy x,
t_yg d,
t_kh e
where a.hcsjbh = r.hcsjbh
and khrq between v_qsrq and jsrq
and a.khbh = e.khbh
and d.ygbh = a.zxygbh
and x.hcsjbh = a.hcsjbh
and x.hcsjlx = 63
and not (a.clzt = 2 or (a.clzt = 0 and a.kzbs = 1));
elsif v_tjlx = 5 then
OPEN RC2 FOR
select e.khbh,
e.khxm,
e.jgid dwbh,
e.jgmc dwmc,
e.khzzh,
e.khjl,
r.hcsjbh,
x.hcsjmc,
f_get_ygxm(a.zxygbh) ygxm
from t_hcgl_khmddy a,
t_rzrq_hfryk r,
t_hcgl_hcsjdy x,
t_yg d,
t_kh e
where a.hcsjbh = r.hcsjbh
and khrq between v_qsrq and jsrq
and a.khbh = e.khbh
and d.ygbh = a.zxygbh
and x.hcsjbh = a.hcsjbh
and x.hcsjlx = 63
and not (a.clzt = 2 or (a.clzt = 0 and a.kzbs = 1));
elsif v_tjlx = 6 then
OPEN RC2 FOR
select e.khbh,
e.khxm,
e.jgid dwbh,
e.jgmc dwmc,
e.khzzh,
e.khjl,
r.hcsjbh,
x.hcsjmc,
f_get_ygxm(a.zxygbh) ygxm
from t_hcgl_khmddy a,
t_rzrq_hfryk r,
t_hcgl_hcsjdy x,
t_yg d,
t_kh e
where a.hcsjbh = r.hcsjbh
and khrq between v_qsrq and jsrq
and a.khbh = e.khbh
and d.ygbh = a.zxygbh
and x.hcsjbh = a.hcsjbh
and x.hcsjlx = 63
and not (a.clzt = 2 or (a.clzt = 0 and a.kzbs = 1));
elsif v_tjlx = 7 then
OPEN RC2 FOR
select e.khbh,
e.khxm,
e.jgid dwbh,
e.jgmc dwmc,
e.khzzh,
e.khjl,
r.hcsjbh,
x.hcsjmc,
f_get_ygxm(a.zxygbh) ygxm
from t_hcgl_khmddy a,
t_rzrq_hfryk r,
t_hcgl_hcsjdy x,
t_yg d,
t_kh e
where a.hcsjbh = r.hcsjbh
and khrq between v_qsrq and jsrq
and a.khbh = e.khbh
and d.ygbh = a.zxygbh
and x.hcsjbh = a.hcsjbh
and x.hcsjlx = 63
and not (a.clzt = 2 or (a.clzt = 0 and a.kzbs = 1));
end if;
EXCEPTION
WHEN OTHERS THEN
OPEN RC1 FOR
SELECT -10006 as errorcode, '查询异常!' as errormsg FROM DUAL;
end up_mid_91100029;