proc.sql

一个财务程序和相应的数据库脚本

result sets 1
language sql
begin
   declare sqlcode integer default 0;
   declare global temporary table tmp_mxz_c
  (
    num      int default 0 not null,
    pxbm     varchar(8) default ' ',
    lbbm     varchar(4) default ' ',
    pzrq     date,
    gjdbm    varchar(2) default ' ',
    pzbm     varchar(17) default ' ' not null,
    pzzy     varchar(255) default ' ' not null,
    pzjf     decimal(18,2) default 0 not null,
    pzdf     decimal(18,2) default 0 not null,
    jd       varchar(4) default ' ',
    ye       decimal(18,2) default 0 not null,
    kmbm     varchar(50) default ' ',
    kmmc     varchar(255) default ' ',
    zph      varchar(15) default ' ',
    dfkm     varchar(255) default ' ',
    pzywyd   varchar(6) default ' ',
    pzid     varchar(36) default ' ',
    pzlb     smallint default 0 not null,
    pzlbview varchar(10) default ' ' not null,
    hslx     smallint default 1 not null,
    hsxm     varchar(100) default ' ' not null,
    pzlbpx   smallint default 0 not null,
    ywlsh    varchar(20) default ' ' not null
  )
  on commit preserve rows with replace;   
begin
  declare v_drq1         varchar(6);
  declare v_drq          varchar(6);
  declare v_qyny         varchar(6);
  declare v_printort     smallint;
  declare v_printzyhs_zx smallint;
  declare v_printzyhs_hx smallint;
  declare v_nyefx        smallint;
  declare v_nrets        smallint;
  declare v_nqce         decimal(16,2);
  declare v_nqcjf        decimal(16,2);
  declare v_nqcdf        decimal(16,2);
  declare v_cmxkm        varchar(50);
  declare v_ckmmc        varchar(255);
  declare v_cmc          varchar(255);
  declare v_cm0          varchar(4);
  declare v_cl0          varchar(4);
  declare v_cjd          varchar(4);
  declare v_cpzid        varchar(36);
  declare v_i            integer;
  declare v_j            smallint;
  declare v_k            integer;
  declare v_npzjf        decimal(16,2);
  declare v_npzdf        decimal(16,2);
  declare v_nkmfx        smallint;
  declare v_cpxbm        varchar(8) ;
  declare v_clbbm        varchar(4) ;
  declare v_else         decimal(16,2);
  declare v_cpzbm        varchar(10);
  declare v_cdfkm        varchar(255);
  declare v_kmjb         smallint default 0;
  declare v_pzlbbm       smallint default 0;
  declare v_clsh         varchar(20);
  declare v_gjd          varchar(2);
  declare v_np1 smallint default 1;
  declare v_np2 smallint default 1;
  declare v_date date;
  declare  v_dpzrq date;
  declare  v_cjd1  varchar(4);
  declare  v_nqce1 decimal(16,2);
  declare v_pzzy         varchar(255);
  declare v_pxbm        varchar(8) ;
  declare v_pxbm1       varchar(8) ;
  declare v_pzjf        decimal(16,2);
  declare v_pzdf        decimal(16,2);
  declare v_cnt integer default 0;
  declare v_lsb varchar(30) default 'session.tmp_mxz_c';
  --错误处理
  declare v_errorcode integer;
  declare v_errormsg  varchar(255);

  declare pz_cur cursor for select gjdbm,pzbm,pzid,pzjf,pzdf,ywlsh from session.tmp_mxz_c where pzywyd=v_drq order by pzrq,pzbm;
  declare bm_cur  cursor for select pxbm,trim(lbbm),jd,ye,pzid,pzrq from session.tmp_mxz_c order by pxbm,lbbm;

  declare mx_cur  cursor for select a.kmbm,a.kmmc from jtpjek a,jtpkmk b
        where a.kmid=b.kmid and a.kmbm like trim(v_ckmbm)||'%'
        and b.kmjb=v_kmjb+(case when v_cnt>0 then 1 else 0 end) order by a.kmbm;
  declare pz_cur1  cursor for select gjdbm,pzbm,pzid,pzjf,pzdf from session.tmp_mxz_c where pzywyd=v_drq and trim(kmbm)=trim(v_cmxkm) order by pzrq,pzbm;
  declare bm_cur1  cursor for select pxbm,trim(lbbm),jd,ye,pzid,pzrq from session.tmp_mxz_c where trim(kmbm)=trim(v_cmxkm) order by pxbm,lbbm;
  declare bm_cur2  cursor for select pxbm,pzzy from session.tmp_mxz_c order by pxbm,lbbm;

  declare exit handler for sqlexception   
  begin
    get diagnostics exception 1 v_errormsg=message_text;
    set v_errorcode=sqlcode;
    rollback;
    insert into log_err(err_date,name_proc,err_code,err_msg)
      values(current timestamp,'p_lsz_calc',v_errorcode,v_errormsg);
    set v_nrets = -5;
    commit;
    --return;
  end;

  set v_drq1=v_drq11;
  if v_lmxz = 0 then
    set v_nrets=0;
    set v_nqce=0;
    set v_else=0;

    select qyny,printort,printzyhs_zx,printzyhs_hx into v_qyny,v_printort,v_printzyhs_zx,v_printzyhs_hx from jtpndk;
    select b.yefx,a.kmmc into v_nyefx, v_ckmmc from jtpjek a,jtpkmk b where a.kmid=b.kmid and trim(a.kmbm)=v_ckmbm;
    set v_cm0=(case when v_nyefx=1 then '借' else '贷' end);
    set v_cl0=(case when v_nyefx=1 then '贷' else '借' end);

    if v_drq1<v_qyny then
        set v_drq1=v_qyny;
    end if;

    set v_date = date(substr(v_drq1,1,4)||'-'||substr(v_drq1,5,2)||'-01')-1 months;
    set v_drq=substr(trim(char(v_date)),1,4)||substr(trim(char(v_date)),6,2);

    set v_np2=2;
    call p_calc(1,2, v_qyny,v_drq1,v_ckmbm,v_lpz,v_ljz,v_nqce  ,v_nrets );	--期初余额
    set v_np2=9;
    call p_calc(1,9, v_drq ,v_drq ,v_ckmbm,v_lpz,v_ljz,v_nqcjf ,v_nrets );	--期初累计借方发生额
    set v_np2=10;
    call p_calc(1,10,v_drq ,v_drq ,v_ckmbm,v_lpz,v_ljz,v_nqcdf ,v_nrets );	--期初累计贷方发生额

    if v_ljz=0 then
        insert into session.tmp_mxz_c (pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,kmmc,zph,pzid,pzywyd,pzlb)
          select pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,v_ckmmc,zph,pzid,pzywyd,pzlb from jtppzk
                where pzywyd between v_drq1 and v_drq2 and kmbm like trim(v_ckmbm)||'%' and ifnull(trim(pzjzid),'')<>''
                      and pzywlb not between '01' and '39'
                order by pzrq,pzbm;

        insert into session.tmp_mxz_c (pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,kmmc,zph,pzid,pzywyd,pzlb,ywlsh)
          select cwpzrq,gjdbm,cwpzh,pzzy,pzjf,pzdf,kmbm,v_ckmmc,' ',char(nextval for pzid_new),cwyd,pzlb,pzbh from jtppzfj
                where cwyd||cwpzh||trim(char(pzlb)) in (select pzywyd||pzbm||trim(char(pzlb)) from jtppzk
                where pzywyd between v_drq1 and v_drq2 and kmbm like trim(v_ckmbm)||'%' and ifnull(trim(pzjzid),'')<>''
                      and pzywlb between '01' and '39') and kmbm like trim(v_ckmbm)||'%'
                order by pzrq,cwpzh;

    elseif v_lpz=0   then
        insert into session.tmp_mxz_c (pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,kmmc,zph,pzid,pzywyd,pzlb)
          select pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,v_ckmmc,zph,pzid,pzywyd,pzlb from jtppzk
                where pzywyd between v_drq1 and v_drq2 and kmbm like trim(v_ckmbm)||'%' and ifnull(trim(pzfhid),'')<>''
                      and pzywlb not between '01' and '39'
                order by pzrq,pzbm;

        insert into session.tmp_mxz_c (pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,kmmc,zph,pzid,pzywyd,pzlb,ywlsh)
          select cwpzrq,gjdbm,cwpzh,pzzy,pzjf,pzdf,kmbm,v_ckmmc,' ',char(nextval for pzid_new),cwyd,pzlb,pzbh from jtppzfj
                where cwyd||cwpzh||trim(char(pzlb)) in (select pzywyd||pzbm||trim(char(pzlb)) from jtppzk
                where pzywyd between v_drq1 and v_drq2 and kmbm like trim(v_ckmbm)||'%' and ifnull(trim(pzfhid),'')<>''
                      and pzywlb between '01' and '39') and kmbm like trim(v_ckmbm)||'%'
                order by pzrq,cwpzh;

    else
        insert into session.tmp_mxz_c (pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,kmmc,zph,pzid,pzywyd,pzlb)
          select pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,v_ckmmc,zph,pzid,pzywyd,pzlb from jtppzk
                where pzywyd between v_drq1 and v_drq2 and kmbm like trim(v_ckmbm)||'%'
                      and pzywlb not between '01' and '39'
                order by pzrq,pzbm ;

        insert into session.tmp_mxz_c (pzrq,gjdbm,pzbm,pzzy,pzjf,pzdf,kmbm,kmmc,zph,pzid,pzywyd,pzlb,ywlsh)
          select cwpzrq,gjdbm,cwpzh,pzzy,pzjf,pzdf,kmbm,v_ckmmc,' ',char(nextval for pzid_new),cwyd,pzlb,pzbh from jtppzfj
                where cwyd||cwpzh||trim(char(pzlb)) in (select pzywyd||pzbm||trim(char(pzlb)) from jtppzk
                where pzywyd between v_drq1 and v_drq2 and kmbm like trim(v_ckmbm)||'%'
                      and pzywlb between '01' and '39') and kmbm like trim(v_ckmbm)||'%'
                order by pzrq,cwpzh;
    end if;

    set v_drq=v_drq1;
    set v_i=1;
    while v_drq<=v_drq2 do
        if v_i=1 then
            set v_cpxbm=right('00000000'||trim(char(v_i)),8);
            set v_cjd=(case when v_nqce=0 then '平' else v_cm0 end);
            insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,jd,ye,kmbm,kmmc)
            values(v_i,v_cpxbm,'00',date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01'),
                    '    '||case when substr(v_drq,5,2)='01' then '上年结转' else '上期结转' end,
                    v_cjd,v_nqce,v_ckmbm,v_ckmmc) ;
        end if;
        set v_i=v_i+1;
        open pz_cur;
        loop_pz_cur:
        loop
            fetch pz_cur into v_gjd,v_cpzbm,v_cpzid,v_npzjf,v_npzdf,v_clsh;
            if sqlcode=100 then
              leave loop_pz_cur;
            end if;
            set v_cpzbm=v_gjd||v_cpzbm;
            set v_nqce=v_nqce + (case when v_nyefx=1 then v_npzjf-v_npzdf else v_npzdf-v_npzjf end);
            set v_cjd=(case when v_nqce=0 then '平' else v_cm0 end);
            set v_cpxbm=right('00000000'||trim(char(v_i)),8);
            update session.tmp_mxz_c set num = v_i, jd = v_cjd ,pxbm = v_cpxbm,lbbm='01',ye = v_nqce where pzid = v_cpzid and ywlsh=v_clsh;
            if v_ldfkm=1 then
                set v_nkmfx= case when v_npzjf>0 then 1 else 2 end;
                set v_cdfkm=repeat(' ',255);
                call p_get_dfkm(v_drq, v_cpzbm, v_nkmfx, v_cdfkm);
                update session.tmp_mxz_c set dfkm = v_cdfkm where pzid = v_cpzid;
            end if;
            set v_i=v_i+1;
        end  loop;
        close pz_cur;
        if v_lrjz = 1 then
            insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,pzjf,pzdf,jd,ye,kmbm,kmmc,pzid)
                select 0,max(pxbm),'02',pzrq,'    当日小计',sum(pzjf),sum(pzdf),
                ' ',v_else,
                v_ckmbm,v_ckmmc,max(pxbm) from session.tmp_mxz_c
                where pzywyd=v_drq group by pzrq;
            set v_i=v_i+1;
        end if;
        if v_nqce=0 then
            set v_cjd='平';
        end if;
        set v_cpxbm=right('00000000'||trim(char(v_i)),8);
        insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,pzjf,pzdf,jd,ye,kmbm,kmmc)
        select v_i,v_cpxbm,'03',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    本月合计',
                value(sum(pzjf),0),value(sum(pzdf),0),v_cjd,v_nqce,v_ckmbm,v_ckmmc from session.tmp_mxz_c where pzywyd=v_drq;
        set v_i=v_i+1;
        set v_cpxbm=right('00000000'||trim(char(v_i)),8);
        if v_ljz=0 then
            insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,pzjf,pzdf,jd,ye,kmbm,kmmc)
            select v_i,v_cpxbm,'04',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    本年累计',
                    v_nqcjf+value(sum(pzjf),0),v_nqcdf+value(sum(pzdf),0),v_cjd,v_nqce,v_ckmbm,v_ckmmc from jtppzk
            where pzywyd>=v_drq1 and pzywyd<=v_drq and kmbm like trim(v_ckmbm)||'%' and ifnull(trim(pzjzid),'')<>'';
        elseif v_lpz = 0 then
            insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,pzjf,pzdf,jd,ye,kmbm,kmmc)
            select v_i,v_cpxbm,'04',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    本年累计',
                    v_nqcjf+value(sum(pzjf),0),v_nqcdf+value(sum(pzdf),0),v_cjd,v_nqce,v_ckmbm,v_ckmmc from jtppzk
            where pzywyd>=v_drq1 and pzywyd<=v_drq and kmbm like trim(v_ckmbm)||'%' and ifnull(trim(pzfhid),'')<>'';
        else
            insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,pzjf,pzdf,jd,ye,kmbm,kmmc)
            select v_i,v_cpxbm,'04',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    本年累计',
                    v_nqcjf+value(sum(pzjf),0),v_nqcdf+value(sum(pzdf),0),v_cjd,v_nqce,v_ckmbm,v_ckmmc from jtppzk
            where pzywyd>=v_drq1 and pzywyd<=v_drq and kmbm like trim(v_ckmbm)||'%';
        end if;
        if substr(v_drq,5,2)='12' and v_nqce <> 0 then
            insert into session.tmp_mxz_c (num,pxbm,lbbm,pzrq,pzzy,pzjf,pzdf,jd,ye,kmbm,kmmc)
            values( v_i,v_cpxbm,'05',last_day(date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')), '    结转下年',
                    0,0,v_cjd,v_nqce,v_ckmbm,v_ckmmc );
        end if;

        set v_date = date(substr(v_drq,1,4)||'-'||substr(v_drq,5,2)||'-01')+1 months;
        set v_drq=substr(trim(char(v_date)),1,4)||substr(trim(char(v_date)),6,2);