Java 调用 Oracle 存储过程返回结果集

Oracle 存储过程返回结果集用 ref cursor 实现。试验步骤如下：


1. 建立 ref cursor 类型和过程

CREATE OR REPLACE PACKAGE types
AS
    TYPE ref_cursor IS REF CURSOR;
END;
/

CREATE TABLE STOCK_PRICES(
    RIC VARCHAR(6) PRIMARY KEY,
    PRICE NUMBER(7,2),
    UPDATED DATE );
/

CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER) 
    RETURN types.ref_cursor
AS
    stock_cursor types.ref_cursor;
BEGIN
    OPEN stock_cursor FOR 
    SELECT ric,price,updated FROM stock_prices WHERE price < v_price;

    RETURN stock_cursor;
END;

2. 用 sqlplus 测试过程

SQL> var results refcursor
SQL> exec :results := sp_get_stocks(20.0)
SQL> print results

3. 从 Java 调用

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 the results
        while (rs.next()) {
            System.out.println(rs.getString(1) + "t" +
            rs.getFloat(2) + "t" +
            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());
            }
        }
    }
}