Java: ResultSet on store procedure
Oracle
CREATE OR REPLACE PROCEDURE get_result (
start_pattern IN VARCHAR2,
end_pattern IN VARCHAR2,
result_set OUT SYS_REFCURSOR
) IS
BEGIN
OPEN result_set FOR
SELECT
sheet_id
FROM checksheet_with_process
WHERE
sheet_id >= start_pattern
AND sheet_id <= end_pattern
ORDER BY sheet_id DESC;
END;
-- test
SET SERVEROUTPUT ON;
DECLARE
result_set SYS_REFCURSOR;
sheet_id VARCHAR2(50);
BEGIN
DBMS_OUTPUT.ENABLE;
get_sheet_print_list('20100923','20101023',result_set);
LOOP
FETCH result_set INTO
sheet_id
;
EXIT WHEN result_set%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(sheet_id);
END LOOP;
END;
ProcedureResult.java
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ProcedureResult {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn = null;
CallableStatement stmt = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection("connection string");
stmt = conn.prepareCall("CALL get_result(?,?,?)");
stmt.setString(1, "20101001");
stmt.setString(2, "20101031");
stmt.registerOutParameter(3, oracle.jdbc.driver.OracleTypes.CURSOR);
stmt.execute();
rs = (ResultSet) stmt.getObject(3);
while (rs != null && rs.next()) {
System.out.println(rs.getString("sheet_id"));
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (rs != null)
try {
rs.close();
} catch (SQLException e2) {
// TODO Auto-generated catch block
e2.printStackTrace();
}
if (stmt != null)
try {
stmt.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Posts
