oracle java存储过程返回值类型_Java 调用 Oracle 存储过程返回结果集

import java.sql.*;

import java.io.*;

import oracle.jdbc.driver.*;

public class JDBCDemo {

/**

* Compile-time flag for deciding which query to use

*/

private boolean useOracleQuery = true;

/**

* Class name of Oracle JDBC driver

*/

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

/**

* Initial url fragment

*/

private String url = "jdbc:oracle:thin:@";

/**

* Standard Oracle listener port

*/

private String port = "1521";

/**

* Oracle style of calling a stored procedure

*/

private String oracleQuery = "begin ? := sp_get_stocks(?); end;";

/**

* JDBC style of calling a stored procedure

*/

private String genericQuery = "{ call ? := sp_get_stocks(?) }";

/**

* Connection to database

*/

private Connection conn = null;

/**

* Constructor. Loads the JDBC driver and establishes a connection

*

* @param host the host the db is on

* @param db the database name

* @param user user's name

* @param password user's password

*/

public JDBCDemo(String host, String db, String user, String password)

throws ClassNotFoundException, SQLException {

// construct the url

url = url + host + ":" + port + ":" + db;

// load the Oracle driver and establish a connection

try {

Class.forName(driver);

conn = DriverManager.getConnection(url, user, password);

}

catch (ClassNotFoundException ex) {

System.out.println("Failed to find driver class: " + driver);

throw ex;

}

catch (SQLException ex) {

System.out.println("Failed to establish a connection to: " + url);

throw ex;

}

}

/**

* Execute the stored procedure

*

* @param price price parameter for stored procedure

*/

private void execute(float price)

throws SQLException {

String query = useOracleQuery ? oracleQuery : genericQuery;

System.out.println("Query: " + query + "n");

CallableStatement stmt = conn.prepareCall(query);

// register the type of the out param - an Oracle specific type

stmt.registerOutParameter(1, OracleTypes.CURSOR);

// set the in param

stmt.setFloat(2, price);

// execute and retrieve the result set

stmt.execute();

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

// print title

System.out.println("ric   price    updated");

System.out.println("---   -----    -------");

// print the results

while (rs.next()) {

System.out.println(rs.getString(1) + "     " +

rs.getFloat(2) + "     " +

rs.getDate(3).toString());

}

rs.close();

stmt.close();

}

/**

* Cleanup the connection

*/

private void cleanup() throws SQLException {

if (conn != null)

conn.close();

}

/**

* Prints usage statement on stdout

*/

static private void usage() {

System.out.println("java com.enterprisedt.demo.oracle.JDBCDemo " +

" host db user password price");

}

/**

* Runs the class

*/

public static void main(String[] args) throws Exception {

if (args.length != 5) {

JDBCDemo.usage();

System.exit(1);

}

else {

try {

// assign the args to sensible variables for clarity

String host = args[0];

String db = args[1];

String user = args[2];

String password = args[3];

float price = Float.valueOf(args[4]).floatValue();

// and execute the stored proc

JDBCDemo jdbc = new JDBCDemo(host, db, user, password);

jdbc.execute(price);

jdbc.cleanup();

}

catch (ClassNotFoundException ex) {

System.out.println("Demo failed");

}

catch (SQLException ex) {

System.out.println("Demo failed: " + ex.getMessage());

}

}

}

}