oracle存储返回结果,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(?) }";

/**