Mysql存储过程（二）——多结果集返回与java获取

存储过程经常需要返回多个结果集。Mysql中直接用select即可返回结果集。而oracle则需要使用游标来返回结果集。这一点Mysql相对比较方便，如下代码即可实现输出两个结果集：

         Sql code 2-1:

[c-sharp] view plain copy print ?

1. CREATE PROCEDURE test_proc_multi_select() 
2. BEGIN 
3.          select * from testproc; 
4.          select * from testproc where id=1; 
5. END; 

CREATE PROCEDURE test_proc_multi_select()BEGIN select * from testproc; select * from testproc where id=1;END;

java中利用循环，即可获取结果集数据：

         java code 2-1:

[java] view plain copy print ?

1. con = MConnection.getConn(); 
2.        String sql = "{call test_proc_multi_select()}"; 
3.       cs = con.prepareCall(sql); 
4.       boolean hadResults = cs.execute(); 
5.       int i=0; 
6.       while (hadResults) { 
7.           System.out.println("result No:----"+(++i)); 
8.           ResultSet rs = cs.getResultSet(); 
9.           while (rs != null && rs.next()) { 
10.              int id1 = rs.getInt(1); 
11.              String name1 = rs.getString(2); 
12.              System.out.println(id1 + ":" + name1); 
13.           } 
14.           hadResults = cs.getMoreResults(); //检查是否存在更多结果集 
15.       } 

con = MConnection.getConn(); String sql = "{call test_proc_multi_select()}"; cs = con.prepareCall(sql); boolean hadResults = cs.execute(); int i=0; while (hadResults) { System.out.println("result No:----"+(++i)); ResultSet rs = cs.getResultSet(); while (rs != null && rs.next()) { int id1 = rs.getInt(1); String name1 = rs.getString(2); System.out.println(id1 + ":" + name1); } hadResults = cs.getMoreResults(); //检查是否存在更多结果集 }