不是返回几个字符的问题,而是返回一个类似于JAVA Object形式的,比如 userId userName 01 AA 02 BB 03 CC .......... 100 ZZ现在就是要把这些数据放在一个OUT中返回给JAVA。
create or replace package test is type cur is ref cursor; procedure getResutlData( Rst1 out cur,Rst2 out cur ); end; create or replace package body test is procedure getResutlData( Rst1 out cur,Rst2 out cur ) is begin open Rst1 for select * from 用户情报; open Rst2 for select * from 商品情报; end; end; 在java里应该怎样调用 我就不知道了
/* * 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(); } }
取的时候再拆分就可以了
userId userName
01 AA
02 BB
03 CC
..........
100 ZZ现在就是要把这些数据放在一个OUT中返回给JAVA。
is
type cur is ref cursor;
procedure getResutlData( Rst1 out cur,Rst2 out cur );
end;
create or replace package body test
is
procedure getResutlData( Rst1 out cur,Rst2 out cur )
is
begin
open Rst1 for select * from 用户情报;
open Rst2 for select * from 商品情报;
end;
end;
在java里应该怎样调用 我就不知道了
* 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();
}
}