那就建立个过程返回游标：
//返回游标
CREATE OR REPLACE PACKAGE test
AS
TYPE myrctype IS REF CURSOR;
END test;
/create procedure pro(c out test.myrctype)
as
str varchar2(200);
begin
str:='select * from table_name where id =.....';
open c for str;
end;
/
//返回游标
CREATE OR REPLACE PACKAGE test
AS
TYPE myrctype IS REF CURSOR;
END test;
/create procedure pro(c out test.myrctype)
as
str varchar2(200);
begin
str:='select * from table_name where id =.....';
open c for str;
end;
/
* This sample shows how to call a PL/SQL function that opens
* a cursor and get the cursor back as a Java ResultSet.
*
* sqlType CURSOR corresponds to "ref cursor". open the cursor
* by specifying CURSOR type at register method. retrieve the
* value by getObject method.
*
* note: jdk1.2 is recommanded. jdk1.1 will also work
*/import java.sql.*;
import java.io.*;// Importing the Oracle Jdbc driver package makes the code more readable
import oracle.jdbc.*;class RefCursorExample
{
public static void main (String args [])
throws SQLException
{
// Load the driver
DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); String url = "jdbc:oracle:oci8:@";
try {
String url1 = System.getProperty("JDBC_URL");
if (url1 != null)
url = url1;
} catch (Exception e) {
// If there is any security exception, ignore it
// and use the default
} // Connect to the database
Connection conn =
DriverManager.getConnection (url, "hr", "hr"); // Create the stored procedure
init (conn); // Prepare a PL/SQL call
CallableStatement call =
conn.prepareCall ("{ ? = call java_refcursor.job_listing (?, ?)}"); // Find out all the SALES person
// (eting representative and eting manager)
call.registerOutParameter (1, OracleTypes.CURSOR);
call.setString (2, "SA_REP");
call.setString (3, "SA_MAN");
call.execute ();
ResultSet rset = (ResultSet)call.getObject (1); // Dump the cursor
while (rset.next ())
System.out.println (rset.getString ("FIRST_NAME") + " "
+ rset.getString ("LAST_NAME") + " "
+ rset.getString ("JOB_ID"));
// Close all the resources
rset.close();
call.close();
conn.close(); } // Utility function to create the stored procedure
static void init (Connection conn)
throws SQLException
{
Statement stmt = conn.createStatement (); try
{
stmt.execute ("create or replace package java_refcursor as " +
" type myrctype is ref cursor return EMPLOYEES%ROWTYPE; " +
" function job_listing (j varchar2, k varchar2) return myrctype; " +
"end java_refcursor;"); stmt.execute ("create or replace package body java_refcursor as " +
" function job_listing (j varchar2, k varchar2) return myrctype is " +
" rc myrctype; " +
" begin " +
" open rc for select * from employees where job_id = j or job_id = k;" +
" return rc; " +
" end; " +
"end java_refcursor;");
} catch (Exception e)
{
e.printStackTrace();
} stmt.close();
}
}