oracle is ref cursor java,在Oracle存储过程中从SYS_REFCURSOR读取数据，并在java中重用它...

I have this table:

CREATE TABLE "QMS_MODEL"."BOOKING" (

"ID" NUMBER ( 19, 0 ) CONSTRAINT "QMS_BOOKING_NN_1" NOT NULL ENABLE

,"CALL_TIME" TIMESTAMP ( 6 )

);

Then i have a simple stored procedure in Oracle that:

1.get a record from a table

2.update a column on the found record

3.returns by an OUT parameter a SYS_REFCURSOR that points to the found record :

CREATE OR REPLACE

PROCEDURE GET_BOOKING

(

refCursorValue OUT SYS_REFCURSOR,

bookingId IN QMS_MODEL.booking.id%type

)

AS

bookingResult QMS_MODEL.booking%ROWTYPE;

todayAtNow QMS_MODEL.booking.booking_time%type;

BEGIN

--********************************

--get booking cursor....

--********************************

OPEN refCursorValue FOR

SELECT

bb.*

FROM qms_model.booking bb

WHERE bb.id = bookingId

FOR UPDATE;

--****************************************

--from boking cursor get booking record...

--****************************************

FETCH refCursorValue INTO bookingResult;

--********************************

--update a column on found booking....

--********************************

SELECT SYSDATE into todayAtNow FROM DUAL;

UPDATE qms_model.booking SET

call_time = todayAtNow

WHERE id = bookingResult.id;

/*

after the fetch refCursorValue is not

valid and the client can't use it!

*/

END;

Calling this procedure the booking is found

and the field is updated,but at the end the cursor

is not valid and i cant use it for other operation,

in this example i use the cursor to log the id field

set serveroutput on format wrapped;

DECLARE

REFCURSORVALUE SYS_REFCURSOR;

BOOKINGID NUMBER;

bookingResult QMS_MODEL.booking%ROWTYPE;

BEGIN

BOOKINGID := 184000000084539;

GET_BOOKING(

REFCURSORVALUE,

BOOKINGID

);

FETCH REFCURSORVALUE INTO bookingResult;

DBMS_OUTPUT.PUT_LINE('>>>OUT , cursor fetc,id='|| bookingResult.id );

END;

I model the booking in java using an entity

@Entity

@Table(name = "BOOKING", schema = "QMS_MODEL")

@NamedNativeQueries({

@NamedNativeQuery(name = "booking.callNext.Oracle",

query = "call GET_BOOKING(?,:bookingId)",

callable = true,

resultClass = Booking.class)

})

public class Booking implements Serializable {

..

..

}

...and i get it by a NamedNativeQuery:

long bookingID=...some value

Query q = entityMng.createNamedQuery("booking.callNext.Oracle");

q.setParameter("bookingId", bookingID);

List results = q.getResultList();

if (results!=null && !results.isEmpty()) {

Booking eBooking = (Booking) results.get(0);

..

..

..

..i want use some booking data here....

..but i can't because the cursor is closed

}

the only request for me are

-select the booking and update it in the same transaction into a stored procedure

-call the stored procedure from java and retrieve the updated booking in the form of a @Entity-Booking

thank you in advance.

解决方案

A ref cursor is not like the scrollable cursor we find in front end languages. It is a pointer to a resultset. This means, we can read it once and then it is exhausted. It is not reusable.

"when a booking is selected i need to update the call_time to mark it

as "selected".When a booking has a non-null call_time isn't selectable

any more. I need to return the updated record to the java application

so i need to return it as the first parameter of the procedure with

OUT sys_refcursor type.

" Note that the real select could be hard so i don't want to execute

it more than one time"

Okay, here is one approach. Caveat: this is proof of concept (i.e. untested code) and not guaranteed to work but it seems like a feasible solution.

CREATE OR REPLACE PROCEDURE GET_BOOKING

( refCursorValue OUT SYS_REFCURSOR,

bookingId IN QMS_MODEL.booking.id%type )

AS

rowids dbms_debug_vc2coll;

begin

update qms_model.booking bb

set bb.call_time = sysdate

where bb.id = bookingId

returning rowidtochar(rowid) bulk collect into rowids;

open refCursorValue for

select *

from qms_model.booking bbto

where rowid in ( select chartorowid(column_value) from table(rowids));

end;

/

Basically:

update the row(s) you want to select

use the RETURNING clause to capture the rowids of the updated rows

then open the refcursor using the rowids to select only the updated rows.

You do issue two queries but selecting using ROWID is pretty fast.