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

转自：http://wxy0327.itpub.net/post/16888/149516

         http://www.blogjava.net/TrampEagle/archive/2011/05/03/23605.html

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

1. 建立 ref cursor 类型和过程

drop table  prices  
/

drop table  test  
/

create or replace package types
as
    type ref_cursor is ref cursor;
end;
/

create table prices(
    ric varchar(6) primary key,
    price number(7,2),
    updated date )
/

create or replace function sp_get_prices(v_price in number) 
    return types.ref_cursor
as
    stock_cursor types.ref_cursor;
begin
    open stock_cursor for 
    select ric,price,updated from prices;

    return stock_cursor;
end;

/
 insert into prices 
select  '1',12,sysdate from dual  union all 
select  '2',123,sysdate from dual  union all 
select  '3',1245,sysdate from dual  
/
commit;

--创建表    
 create table test   
(   
carno     varchar2(30),   
carinfoid number   
)   
/   
--带 参数，无返回
create or replace procedure pro_ins(mt1 in varchar, mt2 in number) 
as  
carinfo_id number;   
begin  
  
insert into test(test.carno,test.carinfoid) values(mt1,mt2);   
commit;   
end pro_ins;   
/ 

--带参数，有返回
create or replace procedure pro_return(para1 in varchar2,para2 out varchar2)  as  
  
begin 
  
  select para1||' add ' into  para2 from dual;    
  
end pro_return; 
/

2. Java 调用

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.driver.OracleTypes;

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_prices(?); end;";

    /**
     * JDBC style of calling a stored procedure
     */
    private String genericQuery = "{ call ? := sp_get_prices(?) }";

    /**
     * Connection to database
     */
    private Connection conn = null;

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

    /**
     * 
     * 返回 Cursor结果集
     * 
     * @throws SQLException
     * @author
     * @date
     */
    private void exeRetCursor() throws SQLException
    {

        String query = useOracleQuery ? oracleQuery : genericQuery;
        query = "{ call ? := sp_get_prices(?) }";
        query = "begin ? := sp_get_prices(?); end;";
        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, 1);

        // 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) + " | " + rs.getFloat(2) + " |" + rs.getDate(3).toString());
        }

        rs.close();
        stmt.close();
    }

    /**
     * 
     * 无返回结果
     * 
     * @throws SQLException
     * @author
     * @date
     */
    private void exeNoRet() throws SQLException
    {

        String query = "{ call pro_ins(?,?) }";
        System.out.println("Query: " + query + "n");
        CallableStatement stmt = conn.prepareCall(query);

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

        // set the in param
        stmt.setString(1, "abcd");
        stmt.setInt(2, 111);

        // execute and retrieve the result set
        stmt.execute();
        System.out.println(" call pro_ins success");

        stmt.close();
    }

    /**
     * 
     * 返回 String
     * 
     * @throws SQLException
     * @author
     * @date
     */
    private void exeRetSingle() throws SQLException
    {

        String query = "{ call pro_return(?,?) }";
        System.out.println("Query: " + query + "n");
        CallableStatement stmt = conn.prepareCall(query);

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

        // set the in param
        stmt.setString(1, "abcd");
        stmt.registerOutParameter(2, Types.VARCHAR);

        // execute and retrieve the result set
        stmt.execute();
        String ret = stmt.getString(2);
        // print the results
        System.out.println("get pro_return:" + ret);

        stmt.close();
    }

    /**
     * Cleanup the connection
     */
    private void cleanup() throws SQLException
    {

        if (conn != null)
            conn.close();
    }

    /**
     * Runs the class
     */
    public static void main(String[] args) throws Exception
    {

        try
        {
            // assign the args to sensible variables for clarity
            String host = "127.0.0.1";
            String db = "orcl";
            String user = "zxinweb";
            String password = "zxinweb";

            // and execute the stored proc
            JDBCDemo jdbc = new JDBCDemo(host, db, user, password);
            // 返回 Cursor结果集
            jdbc.exeRetCursor();
            // 无返回
            jdbc.exeNoRet();
            // 返回String
            jdbc.exeRetSingle();
            jdbc.cleanup();
        } catch (ClassNotFoundException ex)
        {
            System.out.println("Demo failed");
        } catch (SQLException ex)
        {
            System.out.println("Demo failed: " + ex.getMessage());
        }
    }
}