Java 调用存储过程返回多个结果集

shadowtx 贡献于2013-02-18

作者   创建于2008-11-10 14:46:00   修改者Shadow  修改于2013-02-19 01:47:00字数3070

文档摘要:Java调用存储过程返回多个结果集
关键词:

 Java调用存储过程返回多个结果集 CREATE TABLE GOODS ( ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ) , "NAME" VARCHAR (26) NOT NULL , PRICE DOUBLE NOT NULL , CONSTRAINT PK_ID PRIMARY KEY ( ID) , CONSTRAINT CHK_PRICE CHECK (price>0) ENFORCED ENABLE QUERY OPTIMIZATION ) IN USERSPACE1 ; COMMENT ON TABLE GOODS IS '商品信息表'; COMMENT ON GOODS ( PRICE IS '单价', "NAME" IS '名称', ID IS '编号,自动增长' ) ; COMMENT ON CONSTRAINT GOODS.CHK_PRICE IS '价格不能为负数'; INSERT INTO GOODS (NAME,PRICE) VALUES('长虹牌3919K彩电',2100),('西门子A6冰箱',3000); INSERT INTO GOODS (NAME,PRICE) VALUES('彩虹牌E7灭蚊器',62),('西门子W91洗衣机',1600),('努力牌普通作业本',2); INSERT INTO GOODS (NAME,PRICE) VALUES('ThinkPadRT9',16000); select * from goods order by price; ID NAME PRICE -------------------- -------------------------- ------------------------ 5 努力牌普通作业本 +2.00000000000000E+000 3 彩虹牌E7灭蚊器 +6.20000000000000E+001 4 西门子W91洗衣机 +1.60000000000000E+003 1 长虹牌3919K彩电 +2.10000000000000E+003 2 西门子A6冰箱 +3.00000000000000E+003 6 ThinkPadRT9 +1.60000000000000E+004 6 条记录已选择。 --要把“命令编辑器”的语句结束标记设为“@” CREATE PROCEDURE disGoods(IN vprice double) LANGUAGE SQL RESULT SETS 2 BEGIN --定义两个游标 DECLARE AbvE CURSOR WITH RETURN FOR SELECT ID,NAME,PRICE FROM GOODS WHERE PRICE>= VPRICE; DECLARE Sml CURSOR WITH RETURN FOR SELECT ID,NAME,PRICE FROM GOODS WHERE PRICE< VPRICE; -- 打开而不关闭游标,以便返回结果集给调用此存储过程的高级语言程序 OPEN AbvE; OPEN Sml; END@ db2 => call disGoods(2000) 结果集 1 -------------- ID NAME PRICE -------------------- -------------------------- ------------------------ 1 长虹牌3919K彩电 +2.10000000000000E+003 2 西门子A6冰箱 +3.00000000000000E+003 6 ThinkPadRT9 +1.60000000000000E+004 3 条记录已选择。 结果集 2 -------------- ID NAME PRICE -------------------- -------------------------- ------------------------ 3 彩虹牌E7灭蚊器 +6.20000000000000E+001 4 西门子W91洗衣机 +1.60000000000000E+003 5 努力牌普通作业本 +2.00000000000000E+000 3 条记录已选择。 import java.sql.*; public class MainClass { public static void main(String[] args) { Connection conn; CallableStatement cstmt; ResultSet rs; String sqlStr; try{ Class.forName("COM.ibm.db2.jdbc.app.DB2Driver"); System.out.println("注册DB2数据库驱动程序成功!"); conn = DriverManager.getConnection("jdbc:db2:testmydb"); System.out.println("连接数据库testmydb成功!\n"); sqlStr = "call disGoods(2000)";//硬编码 cstmt = conn.prepareCall(sqlStr); cstmt.execute(); rs = cstmt.getResultSet();//获得当前结果集 System.out.println("价格大于等于2000元的商品:"); System.out.println("编号\t名称\t\t\t\t\t价格"); while(rs.next()) System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+ "\t\t\t"+rs.getDouble(3)); if(cstmt.getMoreResults()==false) { rs.close(); cstmt.close(); conn.close(); System.out.println("\n没有更多的结果集了。操作结束。\n.Bye."); return; } rs = cstmt.getResultSet(); System.out.println("\n价格小于2000元的商品:"); System.out.println("编号\t名称\t\t\t\t\t价格"); while(rs.next()) System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+ "\t\t\t"+rs.getDouble(3)); if(cstmt.getMoreResults()==false) { rs.close(); cstmt.close(); conn.close(); System.out.println("\n没有更多的结果集了。操作结束。\n.Bye."); return; } rs.close(); cstmt.close(); conn.close(); } catch(ClassNotFoundException cnfe) { System.out.println("ClassNotFoundException异常:"+cnfe.getMessage()); } catch(SQLException sqle) { System.out.println("SQLException异常:"+sqle.getMessage()); } catch(Exception e) { System.out.println("Exception异常:"+e.getMessage()); } finally{ System.out.println("\n程序运行结束。\n.Good Bye."); } }//end main }//end class MainClass

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 5 金币 [ 分享文档获得金币 ] 1 人已下载

下载文档