interbase的存储过程嵌套问题（1）

[futurei]

执行
select *
from p_this('01','2000 01 01','2003 01 01')
时进入死循环，如何处理？

/*判断日期和仓库*/
入库：
STORAGE_ID=仓库编号 INSTORAGE_DATE between 开始日期 and 截至日期
出库：
STORAGE_ID=仓库编号 OUTSTORAGE_DATE between 开始日期 and 截至日期

/*取入库数据*/
select ware_id,sum(num),sum(unit_price),sum(price)
from T_INSTORAGE
group by ware_id

/*取出库数据*/
select ware_id,sum(num),sum(unit_price),sum(price)
from T_OUTSTORAGE
group by ware_id



select a.ware_id,sum(a.num) a_num,sum(a.unit_price)
a_unit_price,sum(a.price) a_price,
b.ware_id,sum(b.num) b_num,sum(b.unit_price)
b_unit_price,sum(b.price) b_price
from T_INSTORAGE a left join T_OUTSTORAGE b on a.ware_id=b.ware_id
group by a.ware_id,b.ware_id

select a.ware_id,a.num a_num,a.unit_price a_unit_price,a.price a_price,
b.ware_id,b.num b_num,b.unit_price b_unit_price,b.price b_price
from T_INSTORAGE a left join T_OUTSTORAGE b on a.ware_id=b.ware_id


/*入库存储过程*/
drop procedure p_in;
SET TERM !! ;
create procedure p_in(p_i_sid char(2),p_i_d1 date,p_i_d2 date)
returns (r_ware_id varchar(10),r_STORAGE_ID char(2),r_num
integer,r_unit_price numeric(15,3),r_price numeric(15,3))
as
declare variable v_i_sid char(2);
declare variable v_i_d1 date;
declare variable v_i_d2 date;

declare variable v_ware_id varchar(10);
declare variable v_STORAGE_ID char(2);
declare variable v_num integer;
declare variable v_unit_price numeric(15,3);
declare variable v_price numeric(15,3);
begin

v_i_sid=p_i_sid;
v_i_d1=p_i_d1;
v_i_d2=p_i_d2;

for
select ware_id,STORAGE_ID,sum(num) num,sum(unit_price)
unit_price,sum(price) price
from T_INSTORAGE
where STORAGE_ID=:v_i_sid and INSTORAGE_DATE between :v_i_d1 and :v_i_d2
group by ware_id,STORAGE_ID
into :v_ware_id,:v_STORAGE_ID,:v_num,:v_unit_price,:v_price
do
begin
r_ware_id=:v_ware_id;
r_STORAGE_ID=:v_STORAGE_ID;

if (v_num IS NULL) then
r_num=0;
else
r_num=:v_num;

if (v_unit_price IS NULL) then
r_unit_price=0;
else
r_unit_price=:v_unit_price;

if (v_price IS NULL) then
r_price=0;
else
r_price=:v_price;

suspend;
end

end

/*出库存储过程*/
drop procedure p_out;
SET TERM !! ;
create procedure p_out(p_i_sid char(2),p_i_d1 date,p_i_d2 date)
returns (r_ware_id varchar(10),r_STORAGE_ID char(2),r_num
integer,r_unit_price numeric(15,3),r_price numeric(15,3))
as
declare variable v_i_sid char(2);
declare variable v_i_d1 date;
declare variable v_i_d2 date;

declare variable v_ware_id varchar(10);
declare variable v_STORAGE_ID char(2);
declare variable v_num integer;
declare variable v_unit_price numeric(15,3);
declare variable v_price numeric(15,3);
begin

v_i_sid=p_i_sid;
v_i_d1=p_i_d1;
v_i_d2=p_i_d2;

for
select ware_id,STORAGE_ID,sum(num) num,sum(unit_price)
unit_price,sum(price) price
from T_OUTSTORAGE
where STORAGE_ID=:v_i_sid and OUTSTORAGE_DATE between :v_i_d1 and :v_i_d2
group by ware_id,STORAGE_ID
into :v_ware_id,:v_STORAGE_ID,:v_num,:v_unit_price,:v_price
do
begin
r_ware_id=:v_ware_id;
r_STORAGE_ID=:v_STORAGE_ID;

if (v_num IS NULL) then
r_num=0;
else
r_num=:v_num;

if (v_unit_price IS NULL) then
r_unit_price=0;
else
r_unit_price=:v_unit_price;

if (v_price IS NULL) then
r_price=0;
else
r_price=:v_price;

suspend;
end

end

后续

[futurei]

接上

/*本期存储过程*/
drop procedure p_this;
SET TERM !! ;
create procedure p_this(p_i_sid char(2),p_i_d1 date,p_i_d2 date)
returns (in_ware_id varchar(10),in_STORAGE_ID char(2),in_num integer,in_unit_price numeric(15,3),in_price numeric(15,3),out_ware_id varchar(10),out_STORAGE_ID char(2),out_num integer,out_unit_price numeric(15,3),out_price numeric(15,3),this_num integer,this_unit_price numeric(15,3),this_price numeric(15,3))
as
declare variable v_i_sid char(2);
declare variable v_i_d1 date;
declare variable v_i_d2 date;

declare variable v_in_ware_id varchar(10);
declare variable v_in_STORAGE_ID char(2);
declare variable v_in_num integer;
declare variable v_in_unit_price numeric(15,3);
declare variable v_in_price numeric(15,3);

declare variable v_out_ware_id varchar(10);
declare variable v_out_STORAGE_ID char(2);
declare variable v_out_num integer;
declare variable v_out_unit_price numeric(15,3);
declare variable v_out_price numeric(15,3);

declare variable v_this_num integer;
declare variable v_this_unit_price numeric(15,3);
declare variable v_this_price numeric(15,3);

begin

v_i_sid=p_i_sid;
v_i_d1=p_i_d1;
v_i_d2=p_i_d2;

for
select a.r_ware_id in_ware_id,a.r_storage_id in_storage_id,a.r_num in_num,a.r_unit_price in_unit_price,a.r_price in_price,
b.r_ware_id out_ware_id,b.r_storage_id in_storage_id,b.r_num out_num,b.r_unit_price out_unit_price,b.r_price out_price
from p_in(:v_i_sid,:v_i_d1,:v_i_d2) a left join
p_out(:v_i_sid,:v_i_d1,:v_i_d2) b
on a.r_ware_id=b.r_ware_id
into :v_in_ware_id,:v_in_STORAGE_ID,:v_in_num,:v_in_unit_price,:v_in_price,:v_out_ware_id,:v_out_STORAGE_ID,:v_out_num,:v_out_unit_price,:v_out_price
do
begin
/*取本期入库记录*/
in_ware_id=:v_in_ware_id;
in_STORAGE_ID=:v_in_STORAGE_ID;

if (v_in_num IS NULL) then
in_num=0;
else
in_num=:v_in_num;

if (v_in_unit_price IS NULL) then
in_unit_price=0;
else
in_unit_price=:v_in_unit_price;

if (v_in_price IS NULL) then
in_price=0;
else
in_price=:v_in_price;

/*取本期出库记录*/
out_ware_id=:v_out_ware_id;
out_STORAGE_ID=:v_out_STORAGE_ID;

if (v_out_num IS NULL) then
out_num=0;
else
out_num=:v_out_num;

if (v_out_unit_price IS NULL) then
out_unit_price=0;
else
out_unit_price=:v_out_unit_price;

if (v_out_price IS NULL) then
out_price=0;
else
out_price=:v_out_price;

/*取本期结存*/
this_num=in_num-out_num;
this_unit_price=in_unit_price-out_unit_price;
this_price=in_price-out_price;

suspend;
end

end


select a.r_ware_id ware_id,a.r_num in_num,a.r_unit_price in_unit_price,a.r_price in_price,
b.r_num out_num,b.r_unit_price out_unit_price,b.r_price out_price,
(a.r_num-b.r_num) this_num,(a.r_unit_price-b.r_unit_price) this_unit_price,(a.r_price-b.r_price) this_price
from p_in('01','2002 01 01','2003 01 01') a left join
p_out('01','2002 01 01','2003 01 01') b
on a.r_ware_id=b.r_ware_id

select *
from p_this('01','2000 01 01','2003 01 01')

/*上期入库存储过程*/
drop procedure p_pre_in;
SET TERM !! ;
create procedure p_pre_in(p_i_sid char(2),p_i_d1 date)
returns (r_ware_id varchar(10),r_STORAGE_ID char(2),r_num
integer,r_unit_price numeric(15,3),r_price numeric(15,3))
as
declare variable v_i_sid char(2);
declare variable v_i_d1 date;

declare variable v_ware_id varchar(10);
declare variable v_STORAGE_ID char(2);
declare variable v_num integer;
declare variable v_unit_price numeric(15,3);
declare variable v_price numeric(15,3);
begin

v_i_sid=p_i_sid;
v_i_d1=p_i_d1;

for
select ware_id,STORAGE_ID,sum(num) num,sum(unit_price)
unit_price,sum(price) price
from T_INSTORAGE
where STORAGE_ID=:v_i_sid and INSTORAGE_DATE <=:v_i_d1
group by ware_id,STORAGE_ID
into :v_ware_id,:v_STORAGE_ID,:v_num,:v_unit_price,:v_price
do
begin
r_ware_id=:v_ware_id;
r_STORAGE_ID=:v_STORAGE_ID;

if (v_num IS NULL) then
r_num=0;
else
r_num=:v_num;

if (v_unit_price IS NULL) then
r_unit_price=0;
else
r_unit_price=:v_unit_price;

if (v_price IS NULL) then
r_price=0;
else
r_price=:v_price;

suspend;
end

end

/*上期出库存储过程*/
drop procedure p_pre_out;
SET TERM !! ;
create procedure p_pre_out(p_i_sid char(2),p_i_d1 date)
returns (r_ware_id varchar(10),r_STORAGE_ID char(2),r_num
integer,r_unit_price numeric(15,3),r_price numeric(15,3))
as
declare variable v_i_sid char(2);
declare variable v_i_d1 date;

declare variable v_ware_id varchar(10);
declare variable v_STORAGE_ID char(2);
declare variable v_num integer;
declare variable v_unit_price numeric(15,3);
declare variable v_price numeric(15,3);
begin

v_i_sid=p_i_sid;
v_i_d1=p_i_d1;

for
select ware_id,STORAGE_ID,sum(num) num,sum(unit_price)
unit_price,sum(price) price
from T_OUTSTORAGE
where STORAGE_ID=:v_i_sid and OUTSTORAGE_DATE <=:v_i_d1
group by ware_id,STORAGE_ID
into :v_ware_id,:v_STORAGE_ID,:v_num,:v_unit_price,:v_price
do
begin
r_ware_id=:v_ware_id;
r_STORAGE_ID=:v_STORAGE_ID;

if (v_num IS NULL) then
r_num=0;
else
r_num=:v_num;

if (v_unit_price IS NULL) then
r_unit_price=0;
else
r_unit_price=:v_unit_price;

if (v_price IS NULL) then
r_price=0;
else
r_price=:v_price;

suspend;
end

end

select a.r_ware_id ware_id,a.r_num in_num,a.r_unit_price in_unit_price,a.r_price in_price,
b.r_num out_num,b.r_unit_price out_unit_price,b.r_price out_price,
(a.r_num-b.r_num) this_num,(a.r_unit_price-b.r_unit_price) this_unit_price,(a.r_price-b.r_price) this_price
from p_pre_in('01','2000 01 01') a left join
p_pre_out('01','2000 01 01') b
on a.r_ware_id=b.r_ware_id