Java calls the Oracle stored procedure and returns multiple result sets

Java calls the Oracle stored procedure and returns multiple result sets

Oracle version: 11 GB

The Oracle stored procedure uses a cursor to return data sets of multiple rows and columns:

Create or replace procedure SP_DATA_TEST (/* P_ID in int, */-- input parameter, without comments
O_CUR OUT SYS_REFCURSOR -- output data. This article focuses on
) IS
BEGIN
OPEN O_CUR
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 code encoding. The program directly calls the stored procedure of Oracle: SP_DATA_TEST. The Calling method is call SP_DATA_TEST (?), This article only needs to output data and does not need to input parameters, so it only needs a "?" If you want to transmit parameters, enter multiple parameters as needed. This article uses the main method for testing. You can also use it after creating a Java Oracle connection pool.

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.exe cute ();

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 ();
}
}

}

Program return value:

A: 11:12:52
B: 11:12:52
C: 11:12:52