1. 建立 ref cursor 类型和过程
Sql代码
CREATE OR REPLACE PACKAGE typesASTYPE ref_cursor IS REF CURSOR;END;/CREATE TABLE STOCK_PRICES( RIC VARCHAR(6) PRIMARY KEY, PRICE NUMBER(7,2), UPDATED DATE ); SQL> insert into stock_prices values('2',11,sysdate);已创建 1 行。SQL> insert into stock_prices values('3',13,sysdate);已创建 1 行。SQL> insert into stock_prices values('4',15,sysdate);已创建 1 行。SQL> insert into stock_prices values('5',20,sysdate);已创建 1 行。SQL> insert into stock_prices values('6',21,sysdate);已创建 1 行。SQL> insert into stock_prices values('7',25,sysdate);已创建 1 行。SQL> commit;CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER)RETURN types.ref_cursorASstock_cursor types.ref_cursor;BEGINOPEN stock_cursor FORSELECT ric,price,updated FROM stock_prices WHERE price < v_price;RETURN stock_cursor;END;/SQL> var results refcursorSQL> exec :results := sp_get_stocks(20.0)SQL> print results
3. 从 Java 调用
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 titleSystem.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()); } } } }
