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.