摘-- 關於在函式或過程中 ref cursor 的用法

http://blog.itpub.net/post/16888/149516

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());
}
}
}
}

[@more@]