Chinaunix

标题: db2存储过程实例，请教高手帮忙 [打印本页]

作者: echo_huang 时间: 2004-10-10 09:33
标题: db2存储过程实例，请教高手帮忙
下面是一段伪存储过程的代码，我只是表达了我希望它实现的效果。但是因为对sql存储过程不太了解，希望各位高手帮忙看看该如何写。
数据库是db2/400的，支持sql存储过程
目的是查出一个满足条件的结果集，将结果集返回给调用它的java程序，从而生成一个报表。

代码如下：

CREATE PROCEDURE LYPLIBS.getRpt ( IN CustNo VARCHAR(

,
                              IN DistNo VARCHAR(6),
                              IN startdate INTEGER,
                              IN stopdate INTEGER,
                              OUT l3203 VARCHAR(16),
                              OUT l1105 VARCHAR(40),
                              OUT l3205 INTEGER,
                              OUT l3206 INTEGER,
                              OUT av1 FLOAT(5),
                              OUT sum1 INTEGER,
                              OUT av2 FLOAT(5),
                              OUT l1809 INTEGER,
                              OUT l32061 INTEGER )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL 存储过程
-- CustNo
-- DistNo
-- startdate
-- stopdate
-- l3203 品号
-- l1105 品名
-- l3205 期间进货量
-- l3206 期间出货量
-- av1 平均日出量
-- sum1 当前库存量
-- av2 周转天数
-- l1809 最近出货日
-- l32061 未出货天数
------------------------------------------------------------------------
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 av1_TMP FLOAT(5) DEFAULT 0;
DECLARE sum1_TMP INTEGER DEFAULT 0;
DECLARE av2_TMP FLOAT(5) DEFAULT 0;
DECLARE l1809_TMP INTEGER DEFAULT 0;
DECLARE l32061_TMP INTEGER DEFAULT 0;

-- 声明游标
--1、求出唯一的品号，期间进货量，期间出货量
DECLARE cursor1 CURSOR 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

--2、对应该该品号求当前库存量
DECLARE cursor2 CURSOR FOR
select ly0201,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

--3、对应该品号求最近出货日
DECLARE cursor3 CURSOR FOR
select ly1806,max(ly1809)
from lyplibs.lyf18
where ly1840 = CustNo and ly1806 = l3203 and ly1809 >;= startdate and ly1809 <= stopdate
group by ly1806

--4、对应该品号求未出货天数
DECLARE cursor4 CURSOR FOR
select ly3203,count(ly3206)
from lyplibs.lyf32
where ly3230= CustNo
and ly3206 = 0  and ly3203 = l3203  and ly3201 >;= startdate and ly3201<= stopdate
group by ly3203

-- 客户机应用程序的游标未关闭
OPEN cursor1;
-- 客户机应用程序的游标未关闭
OPEN cursor2;
-- 客户机应用程序的游标未关闭
OPEN cursor3;
-- 客户机应用程序的游标未关闭
OPEN cursor4;
SET l3203 = l3203_TMP;
SET l1105 = l1105_TMP;
SET l3205 = l3205_TMP;
SET l3206 = l3206_TMP;
SET av1 = av1_TMP;
SET sum1 = sum1_TMP;
SET av2 = av2_TMP;
SET l1809 = l1809_TMP;
SET l32061 = l32061_TMP;
END P1

作者: javacool 时间: 2004-10-10 15:42
标题: db2存储过程实例，请教高手帮忙
是不是想把 cursor1 到 cursor3 回给用户？
只要将RESULT SET 改为4 同时将cursor属性设为 with return就可以了

作者: echo_huang 时间: 2004-10-10 17:08
标题: db2存储过程实例，请教高手帮忙
楼上的高手，能不能麻烦写个demo教程出来。
没写过存储过程，db2存储过程的书又特别少，不像PL/sql有好多入门的书以及实例。
胡子眉毛一把抓，比较晕。
我是想将查到的N条记录返回出去，这样我的java程序可以获得这些数据，从而可以将这些数据写入excel中。

作者: javacool 时间: 2004-10-10 18:00
标题: db2存储过程实例，请教高手帮忙
create procedure TestProcedureQQ(OUT how_many SMALLINT)
dynamic result sets 1
READS SQL DATA
LANGUAGE SQL
BEGIN
  declare c1 cursor with return for select * from customer;
  select count(*) into how_many from customer;
  open c1;
END

作者: echo_huang 时间: 2004-10-11 08:46
标题: db2存储过程实例，请教高手帮忙
可以结合我的实例写吗？

作者: javacool 时间: 2004-10-11 12:33
标题: db2存储过程实例，请教高手帮忙
差不多一个意思啦你的实例基本已经成形了就把我上面说的几个地方改一下就行了

作者: echo_huang 时间: 2004-10-12 09:18
标题: db2存储过程实例，请教高手帮忙
楼上的，我的实例成形了？
霍霍，看来我还是蛮有天分的嘛。。

可是我要返回一组结果集，并不只是一个值，而是一组值。

作者: javacool 时间: 2004-10-12 12:58
标题: db2存储过程实例，请教高手帮忙
你的一组值是用cursor 返回的只要把cursor设成with return 并在sp中打开他就可以在应用程序中获得这个cursor的访问了具体你看例子和说明就明白了

作者: echo_huang 时间: 2004-10-12 15:49
标题: db2存储过程实例，请教高手帮忙

原帖由 "javacool" 发表：
是不是想把 cursor1 到 cursor3 回给用户？
只要将RESULT SET 改为4 同时将cursor属性设为 with return就可以了

我已经按着改了

可是我查出来的记录外面的java程序怎么取呢？另外，是否在select 的时候应该将字段into到某个变量中去？

作者: javacool 时间: 2004-10-12 16:48
标题: db2存储过程实例，请教高手帮忙
String procName = "ONE_RESULT_SET";
   String sql = "CALL " + procName + "(?, ?)";
   CallableStatement callStmt = con.prepareCall(sql);

   // set input parameter to median value passed back by OUT_PARAM
   callStmt.setDouble(1, outMedian);

   // register the output parameter
   callStmt.registerOutParameter(2, Types.INTEGER);

   // call the stored procedure
   System.out.println();
   System.out.println("Call stored procedure named " + procName);
   callStmt.execute();

   // retrieve output parameter
   int outErrorCode = callStmt.getInt(2);

   if (outErrorCode == 0)
   {
      System.out.println(procName + " completed successfully"

;
      ResultSet rs = callStmt.getResultSet();
      fetchAll(rs);

      // close ResultSet
      rs.close();
   }
IBM自己的例子

作者: echo_huang 时间: 2004-10-15 10:29
标题: db2存储过程实例，请教高手帮忙
javacool,
SET l3203 = l3203_TMP;
SET l1105 = l1105_TMP;
SET l3205 = l3205_TMP;
SET l3206 = l3206_TMP;
SET av1 = av1_TMP;
SET sum1 = sum1_TMP;
SET av2 = av2_TMP;
SET l1809 = l1809_TMP;
SET l32061 = l32061_TMP;
这些我试过删掉，似乎没有问题，不清楚这几个赋值有何用途？

另外，在pl/sql里面
有这种写法，
select a1 into name,a2 into age from tab where....
不知db2是否支持？

作者: echo_huang 时间: 2004-10-15 10:40
标题: db2存储过程实例，请教高手帮忙
还有就是，在构建的时候，上面的例子程序报错
LIB.getrpt ― 构建已启动。

[SQL0199] Keyword DECLARE not expected. Valid tokens: FOR WITH FETCH ORDER UNION OPTIMIZE.

LIB.getrpt ― 构建失败。

LIB.getrpt ― 回滚成功完成。

作者: javacool 时间: 2004-10-15 11:23
标题: db2存储过程实例，请教高手帮忙

原帖由 "echo_huang" 发表：
javacool,
SET l3203 = l3203_TMP;
SET l1105 = l1105_TMP;
SET l3205 = l3205_TMP;
SET l3206 = l3206_TMP;
SET av1 = av1_TMP;
SET sum1 = sum1_TMP;
SET av2 = av2_TMP;
SET l1809 = l1809_TMP;
..........

这个是没问题啊这几个参数是你通过 OUT 参数返回的

后面的错误不太明白最好吧你现在写的SP贴出来

作者: echo_huang 时间: 2004-10-15 12:00
标题: 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句子的条件进行查询。

作者: javacool 时间: 2004-10-15 12:24
标题: db2存储过程实例，请教高手帮忙
难怪内你的所有DECLARE语句必须在SP的 BEGIN 语句后面声明以后才具体操作

作者: echo_huang 时间: 2004-10-15 13:06
标题: db2存储过程实例，请教高手帮忙
这样还是不行
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

--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 cursor1;
open cursor2;
SET l0203 = l0203_TMP;
SET l3203 = l3203_TMP;
SET l1105 = l1105_TMP;
SET l3205 = l3205_TMP;
SET l3206 = l3206_TMP;

END P1

作者: javacool 时间: 2004-10-15 14:00
标题: db2存储过程实例，请教高手帮忙
什么错误？

作者: echo_huang 时间: 2004-10-15 14:16
标题: db2存储过程实例，请教高手帮忙
LIB.getrpt1 ― 构建已启动。

DROP SPECIFIC PROCEDURE LIB.GETRPT1

LYPLIBS.getrpt1 ― 删除存储过程完成。

[SQL0199] Keyword DECLARE not expected. Valid tokens: FOR WITH FETCH ORDER UNION OPTIMIZE.

LIB.getrpt1 ― 构建失败。

LIB.getrpt1 ― 回滚成功完成。

作者: javacool 时间: 2004-10-15 14:24
标题: db2存储过程实例，请教高手帮忙
你的cursor DELCARE 最后没加；号

作者: echo_huang 时间: 2004-10-15 14:33
标题: db2存储过程实例，请教高手帮忙
你说加哪儿？

不明白

作者: javacool 时间: 2004-10-15 14:53
标题: db2存储过程实例，请教高手帮忙
-- 声明游标
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 ;（这边要逗号的）

作者: echo_huang 时间: 2004-10-15 14:54
标题: db2存储过程实例，请教高手帮忙
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;
2句declare cursor后面我都加了;

LYPLIBS.getrpt1 ― 构建已启动。

DROP SPECIFIC PROCEDURE LYPLIBS.GETRPT1

LYPLIBS.getrpt1 ― 删除存储过程完成。

[SQL0104] Token ; was not valid. Valid tokens: ( WITH SELECT <IDENTIFIER>;.

LYPLIBS.getrpt1 ― 构建失败。

LYPLIBS.getrpt1 ― 回滚成功完成。

作者: echo_huang 时间: 2004-10-15 14:58
标题: db2存储过程实例，请教高手帮忙
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;
2句declare cursor后面我都加了;

LYPLIBS.getrpt1 ― 构建已启动。

DROP SPECIFIC PROCEDURE LYPLIBS.GETRPT1

LYPLIBS.getrpt1 ― 删除存储过程完成。

[SQL0104] Token ; was not valid. Valid tokens: ( WITH SELECT <IDENTIFIER>;.

LYPLIBS.getrpt1 ― 构建失败。

LYPLIBS.getrpt1 ― 回滚成功完成。

作者: javacool 时间: 2004-10-15 15:22
标题: db2存储过程实例，请教高手帮忙
没问题啊我用你的例子建了一遍处；除了cursor中的语句不同以外别的都一样生成成功拉下面是例子
CREATE PROCEDURE getrpt1 ( IN custno VARCHAR(16),
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 c1 cursor with return for select * from testcursor;
declare c2 cursor with return for select * from goods;

OPEN c1;
open c2;
SET l0203 = l0203_TMP;
SET l3203 = l3203_TMP;
SET l1105 = l1105_TMP;
SET l3205 = l3205_TMP;
SET l3206 = l3206_TMP;

END P1

你最好看看SQL有没有什么问题

作者: echo_huang 时间: 2004-10-15 16:35
标题: db2存储过程实例，请教高手帮忙
我也重建了一次，可以运行了。
不过问题在于，第二个cursor的结果集没有出现，出现的结果只是第一个查出来的记录，也就是4个字段的结果集，少二个字段。

作者: javacool 时间: 2004-10-15 17:12
标题: db2存储过程实例，请教高手帮忙
那是你的java程序的问题下面是处理多结果集的代码跟上次给你的差不多 try
{
   // prepare the CALL statement for TWO_RESULT_SETS
   String procName = "TWO_RESULT_SETS";
   String sql = "CALL " + procName + "(?, ?)";
   CallableStatement callStmt = con.prepareCall(sql);

   // set input parameter to median value passed back by OUT_PARAM
   callStmt.setDouble(1, outMedian);

   // register the output parameter
   callStmt.registerOutParameter(2, Types.INTEGER);

   // call the stored procedure
   System.out.println();
   System.out.println("Call stored procedure named " + procName);
   callStmt.execute();

   // retrieve output parameter
   int outErrorCode = callStmt.getInt(2);

   if (outErrorCode == 0)
   {
      System.out.println(procName + " completed successfully"

;

      System.out.println(
      "Result set 1: Employees who make more than " + outMedian);
      // get first result set
      ResultSet rs = callStmt.getResultSet();
      fetchAll(rs);

      System.out.println();
      System.out.println("Result set 2: Employees who make less than " +
                        outMedian);
      // get second result set
      callStmt.getMoreResults();
      rs = callStmt.getResultSet();
      fetchAll(rs);

      // close ResultSet
      rs.close();
   }

作者: echo_huang 时间: 2004-10-15 17:24
标题: db2存储过程实例，请教高手帮忙
不是的，我是设置输入参数后直接运行这个存储过程，没有用java程序调用。

作者: javacool 时间: 2004-10-15 17:30
标题: db2存储过程实例，请教高手帮忙
我试了可以返回两个结果集啊  这是我的例子直接调用SP产生的结果

D:\zhang>;db2 call getrpt1('11',2,3,?,?,?,?,?)
  输出参数的值
  --------------------------
  参数名： L3203
  参数值：

  参数名： L1105
  参数值：

  参数名： L3205
  参数值：  0.00

  参数名： L3206
  参数值：  0.00

  参数名： L0203
  参数值：  0.00

  结果集 1
  --------------

  NUM       NAME
  ----------- --------------------
      122 zhang
      990 zhang
      105 zhang
      122 zhang
      109 zhang
      350 zhang
      128 gng

  7 条记录已选择。

  结果集 2
  --------------

  GROUP    NAME
  ----------- --------------------
         1 A1
         1 A2
         1 A3
         1 A4
         1 A5

  5 条记录已选择。

  返回状态 = 0

作者: echo_huang 时间: 2004-10-16 15:01
标题: db2存储过程实例，请教高手帮忙
咦，你的2个sql句子返回的记录数怎么不一样的。
我的2个sql句子返回的记录是一一对应的。
第1个查询查出来的“号码”字段作为第2个查询查到的“数量”字段的条件。
存储过程是这样写的吗？

作者: javacool 时间: 2004-10-16 19:26
标题: db2存储过程实例，请教高手帮忙
我不是说了吗我的cursor后面接的SQL跟你的不一样是我加上去得
如果你还有错的话最好查查你的SQL是不是写的对

作者: echo_huang 时间: 2004-10-17 19:53
标题: db2存储过程实例，请教高手帮忙
嗯！
知了！
回去再查查！！！
先谢啦

作者: echo_huang 时间: 2004-10-19 09:46
标题: db2存储过程实例，请教高手帮忙
真的不行，下面一个cursor 的select记录出不来，单独的我试过下一个sql，是可以执行出结果的。对应一个条件就是一条记录。
2个sql句子单独肯定都没有问题。这点我可以确定。

作者: echo_huang 时间: 2004-10-22 23:25
标题: db2存储过程实例，请教高手帮忙
知道原因了，是第一个select 出来的字段变量没有传到第二个select的where子句中的条件中去。
怎么传呢？
又不能用select... into.. 因为我2个句子全是返回resultset的。

作者: 石三一 时间: 2004-12-01 14:26
标题: db2存储过程实例，请教高手帮忙
echo_huang ：

你的db2是哪个版本的，我的是7。2怎么都不行。

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)