create or replace procedure P_ST_ORG_CUR_MONTH(v_orgcode varchar2, --机构代码
v_startdate varchar2, --开始年月
v_issucc out number, --1执行成功，0执行失败
v_result out varchar2, --提示语句，如果执行失败，则为失败原因描述
v_data out sys_refcursor) is
/*
作者：吕梦龙
功能：
时间：2016
*/
p_length number; --group by 的长度
p_strjoin varchar2(10); --gropu by 需要拼接的长度
begin
v_issucc := 1;
v_result := '成功！';
p_strjoin := '';
p_length := 6;
if instr(v_orgcode, '0000') > 0 then
--当前账号为广东局账号
p_length := 4;
p_strjoin := '00';
end if;
open v_data for
--获取用户所在机构
with t_org as
(select y.sta_area_code, y.area_name, y.oper_area_code
from st_sy_area_coon y
where instr(y.area_sta_bunc, v_orgcode || '00') > 0),
--今年数据
t_biz as
(select t1.org_code,
nvl(t1.reg_ent, 0) entnum, --企业数
nvl(t1.reg_goods, 0) goodnum, --商品数
nvl(t1.inbound_bc_lot, 0) inbound_bc_lot, --直邮进境
nvl(t1.inbound_bc_gval, 0) inbound_bc_gval,
nvl(t1.inbound_bbc_lot, 0) inbound_bbc_lot, --保税进境
nvl(t1.inbound_bbc_gval, 0) inbound_bbc_gval,
nvl(t1.improt_bbc_lot, 0) improt_bbc_lot, --出区入境
nvl(t1.improt_bbc_gval, 0) improt_bbc_gval,
nvl(t1.outbound_bbc_lot, 0) outbound_bbc_lot, --出区出境
nvl(t1.outbound_bbc_gval, 0) outbound_bbc_gval,
(nvl(t1.in_bbc_nopass, 0) + nvl(t1.im_bbc_nopass, 0) +
nvl(t1.out_bbc_nopass, 0) + nvl(t1.in_bc_nopass, 0) +
nvl(t1.out_bc_nopass, 0)) nopass,
(nvl(t1.in_bbc_noval, 0) + nvl(t1.im_bbc_noval, 0) +
nvl(t1.out_bbc_noval, 0) + nvl(t1.in_bc_noval, 0) +
nvl(t1.out_bc_noval, 0)) noval
from st_elist_month t1
where t1.STA_MONTH = v_startdate),
--同比数据
t_biz2 as
(select t1.org_code,
nvl(t1.reg_ent, 0) entnum, --企业数
nvl(t1.reg_goods, 0) goodnum, --商品数
nvl(t1.inbound_bc_lot, 0) inbound_bc_lot, --直邮进境
nvl(t1.inbound_bc_gval, 0) inbound_bc_gval,
nvl(t1.inbound_bbc_lot, 0) inbound_bbc_lot, --保税进境
nvl(t1.inbound_bbc_gval, 0) inbound_bbc_gval,
nvl(t1.improt_bbc_lot, 0) improt_bbc_lot, --出区入境
nvl(t1.improt_bbc_gval, 0) improt_bbc_gval,
nvl(t1.outbound_bbc_lot, 0) outbound_bbc_lot, --出区出境
nvl(t1.outbound_bbc_gval, 0) outbound_bbc_gval,
(nvl(t1.in_bbc_nopass, 0) + nvl(t1.im_bbc_nopass, 0) +
nvl(t1.out_bbc_nopass, 0) + nvl(t1.in_bc_nopass, 0) +
nvl(t1.out_bc_nopass, 0)) nopass,
(nvl(t1.in_bbc_noval, 0) + nvl(t1.im_bbc_noval, 0) +
nvl(t1.out_bbc_noval, 0) + nvl(t1.in_bc_noval, 0) +
nvl(t1.out_bc_noval, 0)) noval
from st_elist_month t1
where t1.STA_MONTH = To_number(substr(v_startdate, 0, 4)) - 1 ||
substr(v_startdate, 5, 7)),
--业务数据
temp1 as
(select mt1.oper_area_code,
mt1.area_name,
mt1.oper_area_code org_code,
substr(mt1.oper_area_code, 0, 2) pcode, --总局
substr(mt1.oper_area_code, 0, p_length) stacode, --分支局
--企业、商品数量
nvl(mt2.entnum, 0) regnum,
nvl(mt2.goodnum, 0) goodnum,
--直邮进境 包裹数 万单
round(nvl(mt2.inbound_bc_lot, 0) / 10000, 4) inbound_bc_lot,
round(nvl(mt3.inbound_bc_lot, 0) / 10000, 4) inbound_bc_lot2,
--货值（万美元）
round(nvl(mt2.inbound_bc_gval, 0) / 10000, 4) inbound_bc_gval,
round(nvl(mt3.inbound_bc_gval, 0) / 10000, 4) inbound_bc_gval2,
--保税进境 批次
nvl(mt2.inbound_bbc_lot, 0) inbound_bbc_lot,
nvl(mt3.inbound_bbc_lot, 0) inbound_bbc_lot2,
--货值（万美元）
round(nvl(mt2.inbound_bbc_gval, 0) / 10000, 4) inbound_bbc_gval,
round(nvl(mt3.inbound_bbc_gval, 0) / 10000, 4) inbound_bbc_gval2,
--出区入境 包裹数(万单）
round(nvl(mt2.improt_bbc_lot, 0) / 10000, 4) improt_bbc_lot,
round(nvl(mt3.improt_bbc_lot, 0) / 10000, 4) improt_bbc_lot2,
--货值（万元）
round(nvl(mt2.improt_bbc_gval, 0) / 10000, 4) improt_bbc_gval,
round(nvl(mt3.improt_bbc_gval, 0) / 10000, 4) improt_bbc_gval2,
--出境 包裹数（单）
nvl(mt2.outbound_bbc_lot, 0) outbound_bbc_lot,
nvl(mt3.outbound_bbc_lot, 0) outbound_bbc_lot2,
--货值（万元）
round(nvl(mt2.outbound_bbc_gval, 0) / 10000, 4) outbound_bbc_gval,
round(nvl(mt3.outbound_bbc_gval, 0) / 10000, 4) outbound_bbc_gval2,
--不合格情况 批次
nvl(mt2.nopass, 0) nopass,
nvl(mt3.nopass, 0) nopass2,
--货值 元
nvl(mt2.noval, 0) noval,
nvl(mt3.noval, 0) noval2
from t_org mt1
left join t_biz mt2
on mt1.oper_area_code = mt2.org_code
left join t_biz2 mt3
on mt1.oper_area_code = mt3.org_code),
--分组数据
temp2 as
(select stacode || p_strjoin org_code,
sum(regnum) regnum,
sum(goodnum) goodnum,
--直邮进境 包裹数 万单
sum(inbound_bc_lot) inbound_bc_lot,
sum(inbound_bc_lot2) inbound_bc_lot2,
--货值（万美元）
sum(inbound_bc_gval) inbound_bc_gval,
sum(inbound_bc_gval2) inbound_bc_gval2,
--保税进境 批次
sum(inbound_bbc_lot) inbound_bbc_lot,
sum(inbound_bbc_lot2) inbound_bbc_lot2,
--货值（万美元）
sum(inbound_bbc_gval) inbound_bbc_gval,
sum(inbound_bbc_gval2) inbound_bbc_gval2,
--出区入境 包裹数(万单）
sum(improt_bbc_lot) improt_bbc_lot,
sum(improt_bbc_lot2) improt_bbc_lot2,
--货值（万元）
sum(improt_bbc_gval) improt_bbc_gval,
sum(improt_bbc_gval2) improt_bbc_gval2,
--出境 包裹数（单）
sum(outbound_bbc_lot) outbound_bbc_lot,
sum(outbound_bbc_lot2) outbound_bbc_lot2,
--货值（万元）
sum(outbound_bbc_gval) outbound_bbc_gval,
sum(outbound_bbc_gval2) outbound_bbc_gval2,
--不合格情况 批次
sum(nopass) nopass,
sum(nopass2) nopass2,
--货值 元
sum(noval) noval,
sum(noval2) noval2
from temp1
group by stacode),
--合计分组数据
temp3 as
(select pcode || '0000' org_code,
sum(regnum) regnum,
sum(goodnum) goodnum,
--直邮进境 包裹数 万单
sum(inbound_bc_lot) inbound_bc_lot,
sum(inbound_bc_lot2) inbound_bc_lot2,
--货值（万美元）
sum(inbound_bc_gval) inbound_bc_gval,
sum(inbound_bc_gval2) inbound_bc_gval2,
--保税进境 批次
sum(inbound_bbc_lot) inbound_bbc_lot,
sum(inbound_bbc_lot2) inbound_bbc_lot2,
--货值（万美元）
sum(inbound_bbc_gval) inbound_bbc_gval,
sum(inbound_bbc_gval2) inbound_bbc_gval2,
--出区入境 包裹数(万单）
sum(improt_bbc_lot) improt_bbc_lot,
sum(improt_bbc_lot2) improt_bbc_lot2,
--货值（万元）
sum(improt_bbc_gval) improt_bbc_gval,
sum(improt_bbc_gval2) improt_bbc_gval2,
--出境 包裹数（单）
sum(outbound_bbc_lot) outbound_bbc_lot,
sum(outbound_bbc_lot2) outbound_bbc_lot2,
--货值（万元）
sum(outbound_bbc_gval) outbound_bbc_gval,
sum(outbound_bbc_gval2) outbound_bbc_gval2,
--不合格情况 批次
sum(nopass) nopass,
sum(nopass2) nopass2,
--货值 元
sum(noval) noval,
sum(noval2) noval2
from temp1
group by pcode),
--广州局合计
temp4 as
(select pcode || '0000' org_code,
sum(regnum) regnum,
sum(goodnum) goodnum,
--直邮进境 包裹数 万单
sum(inbound_bc_lot) inbound_bc_lot,
sum(inbound_bc_lot2) inbound_bc_lot2,
--货值（万美元）
sum(inbound_bc_gval) inbound_bc_gval,
sum(inbound_bc_gval2) inbound_bc_gval2,
--保税进境 批次
sum(inbound_bbc_lot) inbound_bbc_lot,
sum(inbound_bbc_lot2) inbound_bbc_lot2,
--货值（万美元）
sum(inbound_bbc_gval) inbound_bbc_gval,
sum(inbound_bbc_gval2) inbound_bbc_gval2,
--出区入境 包裹数(万单）
sum(improt_bbc_lot) improt_bbc_lot,
sum(improt_bbc_lot2) improt_bbc_lot2,
--货值（万元）
sum(improt_bbc_gval) improt_bbc_gval,
sum(improt_bbc_gval2) improt_bbc_gval2,
--出境 包裹数（单）
sum(outbound_bbc_lot) outbound_bbc_lot,
sum(outbound_bbc_lot2) outbound_bbc_lot2,
--货值（万元）
sum(outbound_bbc_gval) outbound_bbc_gval,
sum(outbound_bbc_gval2) outbound_bbc_gval2,
--不合格情况 批次
sum(nopass) nopass,
sum(nopass2) nopass2,
--货值 元
sum(noval) noval,
sum(noval2) noval2
from temp1
----442200 天河 442100 黄埔 442300 机场 440100 广州
where stacode in ('4422', '4421', '4423', '4401')
group by pcode)
select 'x2' seq,
tt.area_name,
ta1.org_code,
ta1.regnum,
ta1.goodnum,
ta1.inbound_bc_lot,
ta1.inbound_bc_lot2,
round((case
when ta1.inbound_bc_lot2 = 0 then
0
else
((ta1.inbound_bc_lot - ta1.inbound_bc_lot2) /
ta1.inbound_bc_lot2) * 100
end),
4) inbound_bc_lot_tb, -- 同比
ta1.inbound_bc_gval,
ta1.inbound_bc_gval2,
round((case
when ta1.inbound_bc_gval2 = 0 then
0
else
((ta1.inbound_bc_gval - ta1.inbound_bc_gval2) /
ta1.inbound_bc_gval2) * 100
end),
4) inbound_bc_gval_tb, -- 同比
ta1.inbound_bbc_lot,
ta1.inbound_bbc_lot2,
round((case
when ta1.inbound_bbc_lot2 = 0 then
0
else
((ta1.inbound_bbc_lot - ta1.inbound_bbc_lot2) /
ta1.inbound_bbc_lot2) * 100
end),
4) inbound_bbc_lot_tb, -- 同比
ta1.inbound_bbc_gval,
ta1.inbound_bbc_gval2,
round((case
when ta1.inbound_bbc_gval2 = 0 then
0
else
((ta1.inbound_bbc_gval - ta1.inbound_bbc_gval2) /
ta1.inbound_bbc_gval2) * 100
end),
4) inbound_bbc_gval_tb, -- 同比
ta1.improt_bbc_lot,
ta1.improt_bbc_lot2,
round((case
when ta1.improt_bbc_lot2 = 0 then
0
else
((ta1.improt_bbc_lot - ta1.improt_bbc_lot2) /
ta1.improt_bbc_lot2) * 100
end),
4) improt_bbc_lot_tb, -- 同比
ta1.improt_bbc_gval,
ta1.improt_bbc_gval2,
round((case
when ta1.improt_bbc_gval2 = 0 then
0
else
((ta1.improt_bbc_gval - ta1.improt_bbc_gval2) /
ta1.improt_bbc_gval2) * 100
end),
4) improt_bbc_gval_tb, -- 同比
ta1.outbound_bbc_lot,
ta1.outbound_bbc_lot2,
round((case
when ta1.outbound_bbc_lot2 = 0 then
0
else
((ta1.outbound_bbc_lot - ta1.outbound_bbc_lot2) /
ta1.outbound_bbc_lot2) * 100
end),
4) outbound_bbc_lot_tb, -- 同比
ta1.outbound_bbc_gval,
ta1.outbound_bbc_gval2,
round((case
when ta1.outbound_bbc_gval2 = 0 then
0
else
((ta1.outbound_bbc_gval - ta1.outbound_bbc_gval2) /
ta1.outbound_bbc_gval2) * 100
end),
4) outbound_bbc_gval_tb, -- 同比
ta1.nopass,
ta1.nopass2,
round((case
when ta1.nopass2 = 0 then
0
else
((ta1.nopass - ta1.nopass2) / ta1.nopass2) * 100
end),
4) nopass_tb, -- 同比
ta1.noval,
ta1.noval2,
round((case
when ta1.noval2 = 0 then
0
else
((ta1.noval - ta1.noval2) / ta1.noval2) * 100
end),
4) noval_tb -- 同比
from temp2 ta1
left join st_sy_area_coon tt
on ta1.org_code = tt.oper_area_code
union
select 'x1' seq,
'合计',
ta1.org_code,
ta1.regnum,
ta1.goodnum,
ta1.inbound_bc_lot,
ta1.inbound_bc_lot2,
round((case
when ta1.inbound_bc_lot2 = 0 then
0
else
((ta1.inbound_bc_lot - ta1.inbound_bc_lot2) /
ta1.inbound_bc_lot2) * 100
end),
4) inbound_bc_lot_tb, -- 同比
ta1.inbound_bc_gval,
ta1.inbound_bc_gval2,
round((case
when ta1.inbound_bc_gval2 = 0 then
0
else
((ta1.inbound_bc_gval - ta1.inbound_bc_gval2) /
ta1.inbound_bc_gval2) * 100
end),
4) inbound_bc_gval_tb, -- 同比
ta1.inbound_bbc_lot,
ta1.inbound_bbc_lot2,
round((case
when ta1.inbound_bbc_lot2 = 0 then
0
else
((ta1.inbound_bbc_lot - ta1.inbound_bbc_lot2) /
ta1.inbound_bbc_lot2) * 100
end),
4) inbound_bbc_lot_tb, -- 同比
ta1.inbound_bbc_gval,
ta1.inbound_bbc_gval2,
round((case
when ta1.inbound_bbc_gval2 = 0 then
0
else
((ta1.inbound_bbc_gval - ta1.inbound_bbc_gval2) /
ta1.inbound_bbc_gval2) * 100
end),
4) inbound_bbc_gval_tb, -- 同比
ta1.improt_bbc_lot,
ta1.improt_bbc_lot2,
round((case
when ta1.improt_bbc_lot2 = 0 then
0
else
((ta1.improt_bbc_lot - ta1.improt_bbc_lot2) /
ta1.improt_bbc_lot2) * 100
end),
4) improt_bbc_lot_tb, -- 同比
ta1.improt_bbc_gval,
ta1.improt_bbc_gval2,
round((case
when ta1.improt_bbc_gval2 = 0 then
0
else
((ta1.improt_bbc_gval - ta1.improt_bbc_gval2) /
ta1.improt_bbc_gval2) * 100
end),
4) improt_bbc_gval_tb, -- 同比
ta1.outbound_bbc_lot,
ta1.outbound_bbc_lot2,
round((case
when ta1.outbound_bbc_lot2 = 0 then
0
else
((ta1.outbound_bbc_lot - ta1.outbound_bbc_lot2) /
ta1.outbound_bbc_lot2) * 100
end),
4) outbound_bbc_lot_tb, -- 同比
ta1.outbound_bbc_gval,
ta1.outbound_bbc_gval2,
round((case
when ta1.outbound_bbc_gval2 = 0 then
0
else
((ta1.outbound_bbc_gval - ta1.outbound_bbc_gval2) /
ta1.outbound_bbc_gval2) * 100
end),
4) outbound_bbc_gval_tb, -- 同比
ta1.nopass,
ta1.nopass2,
round((case
when ta1.nopass2 = 0 then
0
else
((ta1.nopass - ta1.nopass2) / ta1.nopass2) * 100
end),
4) nopass_tb, -- 同比
ta1.noval,
ta1.noval2,
round((case
when ta1.noval2 = 0 then
0
else
((ta1.noval - ta1.noval2) / ta1.noval2) * 100
end),
4) noval_tb -- 同比
from temp3 ta1
union
--广州局合计
select 'x3' seq,
'广州局合计',
ta1.org_code,
ta1.regnum,
ta1.goodnum,
ta1.inbound_bc_lot,
ta1.inbound_bc_lot2,
round((case
when ta1.inbound_bc_lot2 = 0 then
0
else
((ta1.inbound_bc_lot - ta1.inbound_bc_lot2) /
ta1.inbound_bc_lot2) * 100
end),
4) inbound_bc_lot_tb, -- 同比
ta1.inbound_bc_gval,
ta1.inbound_bc_gval2,
round((case
when ta1.inbound_bc_gval2 = 0 then
0
else
((ta1.inbound_bc_gval - ta1.inbound_bc_gval2) /
ta1.inbound_bc_gval2) * 100
end),
4) inbound_bc_gval_tb, -- 同比
ta1.inbound_bbc_lot,
ta1.inbound_bbc_lot2,
round((case
when ta1.inbound_bbc_lot2 = 0 then
0
else
((ta1.inbound_bbc_lot - ta1.inbound_bbc_lot2) /
ta1.inbound_bbc_lot2) * 100
end),
4) inbound_bbc_lot_tb, -- 同比
ta1.inbound_bbc_gval,
ta1.inbound_bbc_gval2,
round((case
when ta1.inbound_bbc_gval2 = 0 then
0
else
((ta1.inbound_bbc_gval - ta1.inbound_bbc_gval2) /
ta1.inbound_bbc_gval2) * 100
end),
4) inbound_bbc_gval_tb, -- 同比
ta1.improt_bbc_lot,
ta1.improt_bbc_lot2,
round((case
when ta1.improt_bbc_lot2 = 0 then
0
else
((ta1.improt_bbc_lot - ta1.improt_bbc_lot2) /
ta1.improt_bbc_lot2) * 100
end),
4) improt_bbc_lot_tb, -- 同比
ta1.improt_bbc_gval,
ta1.improt_bbc_gval2,
round((case
when ta1.improt_bbc_gval2 = 0 then
0
else
((ta1.improt_bbc_gval - ta1.improt_bbc_gval2) /
ta1.improt_bbc_gval2) * 100
end),
4) improt_bbc_gval_tb, -- 同比
ta1.outbound_bbc_lot,
ta1.outbound_bbc_lot2,
round((case
when ta1.outbound_bbc_lot2 = 0 then
0
else
((ta1.outbound_bbc_lot - ta1.outbound_bbc_lot2) /
ta1.outbound_bbc_lot2) * 100
end),
4) outbound_bbc_lot_tb, -- 同比
ta1.outbound_bbc_gval,
ta1.outbound_bbc_gval2,
round((case
when ta1.outbound_bbc_gval2 = 0 then
0
else
((ta1.outbound_bbc_gval - ta1.outbound_bbc_gval2) /
ta1.outbound_bbc_gval2) * 100
end),
4) outbound_bbc_gval_tb, -- 同比
ta1.nopass,
ta1.nopass2,
round((case
when ta1.nopass2 = 0 then
0
else
((ta1.nopass - ta1.nopass2) / ta1.nopass2) * 100
end),
4) nopass_tb, -- 同比
ta1.noval,
ta1.noval2,
round((case
when ta1.noval2 = 0 then
0
else
((ta1.noval - ta1.noval2) / ta1.noval2) * 100
end),
4) noval_tb -- 同比
from temp4 ta1
order by seq, org_code;
/*select mt1.oper_area_code,
mt1.area_name,
nvl(mt4.entnum, 0) regnum,
nvl(mt4.goodnum, 0) goodnum,
nvl(mt4.inbound_bc_lot, 0) inbound_bc_lot, --直邮进境
nvl(mt5.inbound_bc_lot, 0) inbound_bc_lot2,
nvl(case
when mt5.inbound_bc_lot = 0 then
0
else
round(((mt4.inbound_bc_lot - mt5.inbound_bc_lot) /
mt5.inbound_bc_lot) * 100,
2)
end,
0) inbound_bc_lot_tb,
nvl(mt4.inbound_bc_gval, 0）inbound_bc_gval, nvl(mt5.inbound_bc_gval, 0) inbound_bc_gval2, nvl(case
when mt5.inbound_bc_gval = 0 then
0
else
round(((mt4.inbound_bc_gval -
mt5.inbound_bc_gval) /
mt5.inbound_bc_gval) * 100,
2)
end, 0) inbound_bc_gval_tb,
nvl(mt4.inbound_bbc_lot, 0) inbound_bbc_lot, --保税进境
nvl(mt5.inbound_bbc_lot, 0) inbound_bbc_lot2, nvl(case
when mt5.inbound_bbc_lot = 0 then
0
else
round(((mt4.inbound_bbc_lot -
mt5.inbound_bbc_lot) /
mt5.inbound_bbc_lot) * 100,
2)
end, 0) inbound_bbc_lot_tb,
nvl(mt4.inbound_bbc_gval, 0) inbound_bbc_gval, nvl(mt5.inbound_bbc_gval, 0) inbound_bbc_gval2, nvl(case
when mt5.inbound_bbc_gval = 0 then
0
else
round(((mt4.inbound_bbc_gval -
mt5.inbound_bbc_gval) /
mt5.inbound_bbc_gval) * 100,
2)
end, 0) inbound_bbc_gval_tb,
nvl(mt4.improt_bbc_lot, 0) improt_bbc_lot, --出区入境
nvl(mt5.improt_bbc_lot, 0) improt_bbc_lot2, nvl(case
when mt5.improt_bbc_lot = 0 then
0
else
round(((mt4.improt_bbc_lot -
mt5.improt_bbc_lot) /
mt5.improt_bbc_lot) * 100,
2)
end, 0) improt_bbc_lot_tb,
nvl(mt4.improt_bbc_gval, 0) improt_bbc_gval, nvl(mt5.improt_bbc_gval, 0) improt_bbc_gval2, nvl(case
when mt5.improt_bbc_gval = 0 then
0
else
round(((mt4.improt_bbc_gval -
mt5.improt_bbc_gval) /
mt5.improt_bbc_gval) * 100,
2)
end, 0) improt_bbc_gval_tb,
nvl(mt4.outbound_bbc_lot, 0) outbound_bbc_lot, --出区出境
nvl(mt5.outbound_bbc_lot, 0) outbound_bbc_lot2, nvl(case
when mt5.outbound_bbc_lot = 0 then
0
else
round(((mt4.outbound_bbc_lot -
mt5.outbound_bbc_lot) /
mt5.outbound_bbc_lot) * 100,
2)
end, 0) outbound_bbc_lot_tb,
nvl(mt4.outbound_bbc_gval, 0) outbound_bbc_gval, nvl(mt5.outbound_bbc_gval, 0) outbound_bbc_gval2, nvl(case
when mt5.outbound_bbc_gval = 0 then
0
else
round(((mt4.outbound_bbc_gval -
mt5.outbound_bbc_gval) /
mt5.outbound_bbc_gval) * 100,
2)
end, 0) outbound_bbc_gval_tb, nvl(mt4.nopass, 0) nopass, nvl(mt5.nopass, 0) nopass2,
nvl(case
when mt5.nopass = 0 then
0
else
round(((mt4.nopass - mt5.nopass) / mt5.nopass) * 100, 2)
end, 0) nopass_tb, nvl(mt4.noval, 0) noval, nvl(mt5.noval, 0) noval2,
nvl(case
when mt5.noval = 0 then
0
else
round(((mt4.noval - mt5.noval) / mt5.noval) * 100, 2)
end, 0) noval_tb, '' matsergood
from t_org mt1
--left join t_ent mt2 on mt1.oper_area_code = mt2.all_org_code
--left join t_good mt3 on mt1.oper_area_code = mt3.all_org_code
left join t_biz mt4 on mt1.oper_area_code = mt4.org_code left join t_biz2 mt5 on mt1.oper_area_code = mt5.org_code
order by mt1.oper_area_code asc;*/
EXCEPTION
WHEN OTHERS THEN
rollback;
v_issucc := 0;
v_result := '失败！,' || sqlerrm || ',' ||
dbms_utility.format_error_backtrace;
dbms_output.put_line(v_result);
end;
扫一扫
298
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
