java读取oracle的存储过程

java读取oracle的存储过程(转 邢红瑞's blog)

java读取oracle的存储过程网上的例子不少，都太简单，没有一个返回参考游标的,返回记录集，自己写一个怕日后忘了。

oracle的PLSQL

CREATE OR REPLACE package chapter_13 as
TYPE rs IS REF CURSOR ;
procedure founder(oFields out rs);

end;

CREATE OR REPLACE package body chapter_13 as

PROCEDURE founder(oFields out rs) IS
BEGIN
open oFields for
select * from person;
END founder;
end;
java代码

package jdbc;

import java.io.*;
import java.sql.*;
import java.text.*;
import oracle.jdbc.OracleTypes;

public class TestStoredProcedures {
Connection conn;

public TestStoredProcedures() {
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "jola", "jola");
} catch (SQLException e) {
System.err.println(e.getMessage());
e.printStackTrace();
}
}

public static void main(String[] args) throws Exception {
new TestStoredProcedures().process();
}

public void process() throws SQLException {

long start = 0;
long end = 0;
CallableStatement cstmt = null;

try {
start = System.currentTimeMillis();

// *** SQL92 escape syntax ***

cstmt = conn.prepareCall(
"{call chapter_13.founder(?)}");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
ResultSet rs = null;

cstmt.execute();
rs = (ResultSet)cstmt.getObject(1);
while (rs.next()) {
System.out.println(rs.getString("NAME"));
}
rs.close();

end = System.currentTimeMillis();
System.out.println("Average elapsed time = " +
(end - start) / 8 + " milliseconds");
} catch (SQLException e) {
System.err.println("SQL Error: " + e.getMessage());
} finally {
if (cstmt != null) {
try {
cstmt.close();
} catch (SQLException ignore) {}
}
}
}

protected void finalize() throws Throwable {
if (conn != null) {
try {
conn.close();
} catch (SQLException ignore) {}
}
super.finalize();
}
}