oracle with as临时表用法 分组group by



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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值