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

futurei 2002-09-10 11:57:50
执行
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

后续
...全文
72 1 打赏 收藏 转发到动态 举报
写回复
用AI写文章
1 条回复
切换为时间正序
请发表友善的回复…
发表回复
futurei 2002-09-10
  • 打赏
  • 举报
 回复
接上

/*本期存储过程*/
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
Oracle的存储过程
- **特点**：存储过程可以包含复杂的逻辑结构，支持分支和循环，甚至可以嵌套其他存储过程或函数。 - **优势**： - 提高代码执行效率。 - 减少网络流量：只需要传递存储过程名和参数，而无需传递大量SQL语句。 - ...
SQL.rar_delphi sql_delphi sql_delphi 高级_sql
2. 存储过程：在数据库中预编译的SQL代码块，可提高性能并减少网络传输。在Delphi中调用存储过程通常比直接执行SQL语句更安全、高效。 3. 触发器：数据库级别的事件响应机制，可以在数据插入、更新或删除时自动执行...
SQL21日自学通
目录1 译者的话 14 第一周概貌 16 从这里开始 16 第一天SQL 简介 17 SQL 简史 17 数据库简史 17 设计数据库的结构21 SQL 总览23 流行的SQL 开发工具 24 SQL 在编程中的应用 27 第二天查询— — SELECT 语句的使用 30...
delphi导入电子表格
- 如果数据存储在数据库中，如 MySQL、SQL Server 或 InterBase，你可以使用 Delphi 自带的 DBExpress 或 ADO 组件直接将数据库数据导出为电子表格格式，或者将电子表格数据导入数据库。这样，数据交换变得更加便捷...
数据库sql入门基础
SQL（Structured Query Language）是一种用于存储、检索、操作数据的计算机语言，主要用在关系数据库管理系统中。SQL入门基础通常包括学习如何执行各种基本操作，例如数据的查询、更新、插入和删除。 2. SELECT语句...
其他数据库

2,209

社区成员

9,519

社区内容

发帖
与我相关
我的任务
社区描述
其他数据库开发 其他数据库
社区管理员
  • 其他数据库社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章