JDBC调用oracle存储过程(out parameter)

首先我们以一个简单的例子开始，在这个例子中，只存在一个返回值。存储过程如下

SQL> create or replace procedure lizhe (aa out varchar2)is
begin
select password into aa from passport where id='123111';
end lizhe;
/

补充说明：要注意的是这个存储过程存在一个（aa out varchar2）这样的标识，这是一个参数，out表示这是一个输出参数，在select语句中将password的值“into”到这个参数里

然后再java端：

public static void runProcedure(){
try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.159:1521:TOPS","YAC3","YAC3");
java.sql.CallableStatement cs = con.prepareCall("{call lizhe(?)}");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.execute();
System.out.print("value2:" + cs.getString(1)); //取出字符串——关键

} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

补充说明：cs.registerOutParameter(1, java.sql.Types.VARCHAR);
重点只有这一句

2.多个返回值的

SQL> CREATE OR REPLACE PACKAGE TESTPACKAGE AS

TYPE Test_CURSOR IS REF CURSOR;

end TESTPACKAGE;
/

Package created

SQL> CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS

BEGIN

OPEN p_CURSOR FOR 'SELECT * FROM TB';

END TESTC;
/

Procedure created

import java.sql.*;

import java.io.OutputStream;

import java.io.Writer;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import oracle.jdbc.driver.*;

public class TestProcedure {

public TestProcedure() {

}

public static void main(String[] args ){

String driver = "oracle.jdbc.driver.OracleDriver";

String strUrl = "jdbc:oracle:thin:@192.168.0.159:1521:tops";

Statement stmt = null;

ResultSet rs = null;

Connection conn = null;

try {

Class.forName(driver);

conn = DriverManager.getConnection(strUrl, "yac3", "yac3");

CallableStatement proc = null;

proc = conn.prepareCall("{call testc(?)}");

proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

proc.executeQuery();

rs = (ResultSet)proc.getObject(1);

while(rs.next())

{

System.out.println(rs.getString("VALUE"));

}

}

catch (SQLException ex2) {

ex2.printStackTrace();

}

catch (Exception ex2) {

ex2.printStackTrace();

}

finally{

try {

if(rs != null){

rs.close();

if(stmt!=null){

stmt.close();

}

if(conn!=null){

conn.close();

}

}

}

catch (SQLException ex1) {

}

}

}

}