Java调用Oracle存储过程返回多条结果集
Oracle版本:11g
Oracle存储过程,使用游标的方式返回多行、多列数据集合:
CREATE OR REPLACE PROCEDURE SP_DATA_TEST( /*P_ID IN INT,*/ --传入参数,不需要可注释
O_CUR OUT SYS_REFCURSOR --输出数据,本文重点描述
) IS
BEGIN
OPEN O_CUR FOR
SELECT *
FROM (SELECT 'A', SYSDATE - 1
FROM DUAL
UNION ALL
SELECT 'B', SYSDATE
FROM DUAL
UNION ALL
SELECT 'C', SYSDATE + 1 FROM DUAL) O
WHERE 1 = 1;
END;
Java代码编码,程序直接调用Oracle的存储过程:SP_DATA_TEST,调用的方法:call SP_DATA_TEST(?),本文只需要输出数据,不需要输入参数,故只需要一个“?”即可,若是需要传输参数,则根据需要填写多个参数即可。本文直接使用了main方法测试,也可先自建Java Oracle连接池后使用。
package com.***.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ProceTest {
public static void main(String[] args) {
try {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@10.0.0.1:1521:dbcsk";
String username = "****";
String password = "***";
Connection conn = DriverManager.getConnection(url, username,
password);
String sql = "{call SP_DATA_TEST(?)}";
CallableStatement statement = conn.prepareCall(sql);
// statement.setInt(1, 1);
statement.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
statement.execute();
ResultSet rs = (ResultSet)statement.getObject(1);
int i=1;
while (rs.next()) {
System.out.println(rs.getString(1)+":"+rs.getString(2));
i++;
}
rs.close();
statement.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
程序返回值:
A:2016-04-29 11:12:52
B:2016-03-23 11:12:52
C:2016-03-24 11:12:52