- 论坛徽章:
- 0
|
db2存储过程实例,请教高手帮忙
为了方便调试,我只用了2个sql句子。
CREATE PROCEDURE LYPLIBS.getrpt1 ( IN custno VARCHAR(,
IN startdate INTEGER,
IN stopdate INTEGER,
OUT l3203 VARCHAR(16),
OUT l1105 VARCHAR(40),
OUT l3205 DECIMAL(10,2),
OUT l3206 DECIMAL(10,2),
OUT l0203 DECIMAL(10,2))
RESULT SETS 2
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL 存储过程
-- custno 客户代号
-- startdate 初始日期
-- stopdate 结束日期
-- l3203 品号
-- l1105 品名
-- l3205 期间进货量
-- l3206 期间出货量
------------------------------------------------------------------------
P1: BEGIN
-- 声明变量
DECLARE l3203_TMP VARCHAR(16) DEFAULT ' ';
DECLARE l1105_TMP VARCHAR(40) DEFAULT ' ';
DECLARE l3205_TMP INTEGER DEFAULT 0;
DECLARE l3206_TMP INTEGER DEFAULT 0;
DECLARE l0203_TMP FLOAT(5) DEFAULT 0;
-- 声明游标
DECLARE cursor1 CURSOR WITH RETURN FOR
select ly3203,ly1105,sum(ly3205/ly1112),sum(ly3206/ly1112)
from lyplibs.lyf32,lyplibs.lyf11
where ly3230=ly1120 and ly3203=ly1101 and ly3201>;=startdate and
ly3201<=stopdate and ly3230=custno
group by ly3203,ly1105
OPEN cursor1;
SET l3203 = l3203_TMP;
SET l1105 = l1105_TMP;
SET l3205 = l3205_TMP;
SET l3206 = l3206_TMP;
--2、对应该该品号求当前库存量
DECLARE cursor2 CURSOR WITH RETURN FOR
select sum(ly0203/ly1112)
from lyplibs.lyf02,lyplibs.lyf03,lyplibs.lyf11
where ly0200=ly0320 and ly0200=ly1120 and ly0201=ly1101 and
ly0202 = ly0301 and ly0200 = CustNo and and ly1101 = l3203 and ly0323 = 'NM'
group by ly0201
open cursor2;
SET l0203 = l0203_TMP;
END P1
不知道这种写法是否正确,2个sql是应该嵌套关系的。第一个查出来的l3203作为第二个sql句子的条件进行查询。 |
|