@Rajat,
您可以尝试下面的方法吗:
要检索光标,您应该在包规范中将其声明为 REF CURSOR。
--Creating the REF CURSOR type
type g_cursor is ref cursor;
在规范和正文中,您都需要在过程签名中声明一个 out REF CURSOR 变量,如上面引用的那样。
procedure PRO_RETURN_CARS(
i_id in tbl_car.car_id%type,
o_cursor in out g_cursor);
必须在过程主体中打开游标才能返回,如下所示:
open o_cursor for
select car_id, company, model, color, hp, price
from tbl_car
where car_id = i_id;
完整的套餐:
create or replace package PAC_CURSOR is
--Creating REF CURSOR type
type g_cursor is ref cursor;
--Procedure that return the cursor
procedure PRO_RETURN_CARS(
i_id in tbl_car.car_id%type,
o_cursor in out g_cursor); -- Our cursor
end PAC_CURSOR;
/
create or replace package body PAC_CURSOR is
procedure PRO_RETURN_CARS(
i_id in tbl_car.car_id%type,
o_cursor in out g_cursor) is
begin
--Opening the cursor to return matched rows
open o_cursor for
select car_id, company, model, color, hp, price
from tbl_car
where car_id = i_id;
end PRO_RETURN_CARS;
end PAC_CURSOR;
我们已经准备好了 Oracle 端,现在我们需要创建 Java 调用
过程如何返回游标,我们将使用java.sql.CallableStatement
实例:
CallableStatement cs = conn.prepareCall("{call PAC_CURSOR.PRO_RETURN_CARS(?,?)}");
The registerOutParameter
将获得oracle.jdbc.OracleTypes.CURSOR
输入并返回java.sql.ResultSet
实例。我们可以迭代ResultSet
像一个普通的Iterator
.
SELECT 返回的每一行列将使用相应的 getter 来表示映射的方式。例如,当列的值为 varchar 时,我们将调用 getString() 方法;当列的值为日期时,我们将调用 getDate() 方法等。
完整的代码将是这样的:
//Calling Oracle procedure
CallableStatement cs = conn.prepareCall("{call PAC_CURSOR.PRO_RETURN_CARS(?,?)}");
//Defining type of return
cs.registerOutParameter("o_cursor", OracleTypes.CURSOR);
cs.setLong("i_id", id);
cs.execute();//Running the call
//Retrieving the cursor as ResultSet
ResultSet rs = (ResultSet)cs.getObject("o_cursor");
//Iterating the returned rows
while(rs.next()){
//Getting column values
System.out.println("ID: " + rs.getLong("car_id"));
System.out.println("Manufacturer: " + rs.getString("company"));
System.out.println("Model: " + rs.getString("model"));
System.out.println("Color: " + rs.getString("color"));
System.out.println("HP: " + rs.getString("hp"));
System.out.println("Price: " + rs.getFloat("price"));
}
最后,您将获得 SELECT 子句中返回的任何值。