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