救教!~ 存储过程

teng2048 2008-05-06 08:53:22

create procedure p_dpttjqkcx
(
v_hslb varchar(2), --资金类别（01-售房款，02-维修基金）
v_gjdbm varchar(2), --归集点
v_lsgxbm varchar(4), --承办网点
v_dwbm varchar(20),--单位账号
v_rq1 date, --开始日期
v_rq2 date, --结束日期
v_cxlx smallint --0不包含未到账 1包含未到账
)
--加密
result sets 1
language sql
set option commit=*cs

begin
declare v_a075 varchar(3);
declare v_a073 varchar(5);
declare v_a003 varchar(20);
declare v_else decimal(14,2) default 0;
declare v_b016 int;
declare v_cnt smallint default 0;
declare sqlcode integer default 0;

declare global temporary table tmp_dpttjqkcx
(
a003 varchar(20) default ' ',
a004 varchar(80) default ' ',
b001 decimal(14,2) default 0 not null,
b002 int default 0 not null,
b003 decimal(12,2) default 0 not null,
b004 decimal(12,2) default 0 not null,
b005 int default 0 not null,
b006 decimal(12,2) default 0 not null,
b007 int default 0 not null,
b008 decimal(12,2) default 0 not null,
b009 int default 0 not null,
b010 decimal(12,2) default 0 not null,
b011 int default 0 not null,
b012 decimal(12,2) default 0 not null,
b013 decimal(12,2) default 0 not null,
b014 decimal(10,2) default 0 not null,
b015 decimal(14,2) default 0 not null,
b016 int default 0 not null,
b017 int default 0 not null
)
on commit preserve rows with replace;

declare global temporary table tmp_dpttjqkcx1
(
a003 varchar(20) default ' ',
a004 varchar(80) default ' ',
b016 int default 0 not null
)
on commit delete rows with replace;

declare global temporary table tmp_dpttjqkcx2
(
a003 varchar(20) default ' ',
a004 varchar(80) default ' ',
b017 int default 0 not null
)
on commit delete rows with replace;

if v_gjdbm='00' then
set v_a075='%';
else
set v_a075=v_gjdbm;
end if;
if ifnull(trim(v_lsgxbm),'')='' then
set v_a073='%';
else
set v_a073=trim(v_lsgxbm);
end if;
if ifnull(trim(v_dwbm),'')='' then
set v_a003='%';
else
set v_a003=trim(v_dwbm);
end if;

if v_cxlx = 0 then
insert into session.tmp_dpttjqkcx (a003,a004,b001,b002,b003,b004,b005,b006,b007,b008,b009,b010,b011,b012,b013,b014,b015)
select a003,max(a004) a004,
sum(case when p012 between '01' and '13' and p002 <v_rq1 then p008-p009 else v_else end) as b001,
sum(case when p012 in('07','08') and p002 between v_rq1 and v_rq2 then p011 else v_else end) as b002,
sum(case when p012 in('07','08') and p002 between v_rq1 and v_rq2 then p008 else v_else end) as b003,
sum(case when p012 in('01','02') and p002 between v_rq1 and v_rq2 then p008 else v_else end) as b004,
sum(case when p012 in('05','11') and p002 between v_rq1 and v_rq2 then p011 else v_else end) as b005,
sum(case when p012 in('05','11') and p002 between v_rq1 and v_rq2 then p008 else v_else end) as b006,
sum(case when p012 in('06','12') and p002 between v_rq1 and v_rq2 then p011 else v_else end) as b007,
sum(case when p012 in('06','12') and p002 between v_rq1 and v_rq2 then p009 else v_else end) as b008,
sum(case when p012 in('03','13') and p002 between v_rq1 and v_rq2 then p011 else v_else end) as b009,
sum(case when p012 in('03','13') and p002 between v_rq1 and v_rq2 then p009 else v_else end) as b010,
sum(case when p012='04' and p002 between v_rq1 and v_rq2 then p011 else v_else end) as b011,
sum(case when p012='04' and p002 between v_rq1 and v_rq2 then p009 else v_else end) as b012,
sum(case when p012 in('04','12') and p002 between v_rq1 and v_rq2 then p014 else v_else end) as b013,
sum(case when p012='09' and p002 between v_rq1 and v_rq2 then p008 else v_else end) as b014,
sum(case when p012 between '01' and '13' and p002<=v_rq2 then p008-p009 else v_else end) as b015
from gzpzk
where a075 like v_a075 and a003 like v_a003 and p012 between '01' and '13' and p002<=v_rq2 and p000 = v_hslb
and exists(select bm from bm_a003 where bm=gzpzk.a003 and gjdbm like v_a075 and lsgxbm like v_a073 and bm like v_a003)
group by a003;
else
insert into session.tmp_dpttjqkcx (a003,a004,b001,b002,b003,b004,b005,b006,b007,b008,b009,b010,b011,b012,b013,b014,b015)
select a003,max(a004) a004,
sum(case when p012 between '01' and '13' and p006 <v_rq1 then p008-p009 else v_else end) as b001,
sum(case when p012 in('02','07','08') and p006 between v_rq1 and v_rq2 then p011 else v_else end) as b002,
sum(case when p012 in('02','07','08') and p006 between v_rq1 and v_rq2 then p008 else v_else end) as b003,
sum(case when p012 in('01','02') and p006 between v_rq1 and v_rq2 then p008 else v_else end) as b004,
sum(case when p012 in('05','11') and p006 between v_rq1 and v_rq2 then p011 else v_else end) as b005,
sum(case when p012 in('05','11') and p006 between v_rq1 and v_rq2 then p008 else v_else end) as b006,
sum(case when p012 in('06','12') and p006 between v_rq1 and v_rq2 then p011 else v_else end) as b007,
sum(case when p012 in('06','12') and p006 between v_rq1 and v_rq2 then p009 else v_else end) as b008,
sum(case when p012 in('03','13') and p002 between v_rq1 and v_rq2 then p011 else v_else end) as b009,
sum(case when p012 in('03','13') and p002 between v_rq1 and v_rq2 then p009 else v_else end) as b010,
sum(case when p012='04' and p006 between v_rq1 and v_rq2 then p011 else v_else end) as b011,
sum(case when p012='04' and p006 between v_rq1 and v_rq2 then p009 else v_else end) as b012,
sum(case when p012 in('04','12') and p006 between v_rq1 and v_rq2 then p014 else v_else end) as b013,
sum(case when p012='09' and p006 between v_rq1 and v_rq2 then p008 else v_else end) as b014,
sum(case when p012 between '01' and '13' and p006<=v_rq2 then p008-p009 else v_else end) as b015
from gzpzk
where a075 like v_a075 and a003 like v_a003 and p012 between '01' and '13' and p006<=v_rq2 and p000 = v_hslb
and exists(select bm from bm_a003 where bm=gzpzk.a003 and gjdbm like v_a075 and lsgxbm like v_a073 and bm like v_a003)
group by a003;
end if;
commit;
begin
declare sel_cur cursor with return to caller for select * from session.tmp_dpttjqkcx ;
open sel_cur;
end;
return;
end;

这是我公司给的东西我想看懂它需要看哪些资料最好有教程的地址

...全文