在Oracle存储过程中从SYS_REFCURSOR读取数据并在java中重用

Question

我有一张桌子：

  CREATE TABLE "QMS_MODEL"."BOOKING" (
       "ID" NUMBER ( 19, 0 ) CONSTRAINT "QMS_BOOKING_NN_1" NOT NULL ENABLE
      ,"CALL_TIME" TIMESTAMP ( 6 )
  );

然后，我在Oracle中有一个简单的存储过程:1.从表2中获取一条记录。更新查找记录上的一列。通过一个OUT参数返回指向已找到的记录的SYS_REFCURSOR：

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;

调用此过程将找到预订并更新字段，但最后游标无效，无法用于其他操作，在本例中，我使用游标记录id字段。

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;

我使用一个实体在java中对预订进行建模

@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，我是通过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
}

我唯一的请求是-select预订，并在同一事务中将其更新为存储过程-call存储过程，从java获取更新的预订，并以@Entity-预订的形式检索更新的预订。

提前谢谢你。

Answer 1

引用游标不像我们在前端语言中找到的可滚动游标。它是指向结果集的指针。这意味着，我们可以读一遍，然后它就筋疲力尽了。它是不可重复使用的。

“当预订被选中时，我需要更新call_time以将其标记为" selected ".When预订有一个非空的call_time不再是可选择的。我需要将更新的记录返回给java应用程序，所以我需要返回它作为过程的第一个参数，不需要sys_refcursor类型。

“请注意，真正的选择可能很难，所以我不想多执行一次。”

好的，这里有一个方法。警告:这是概念的证明(即未经测试的代码)，不能保证工作，但它似乎是一个可行的解决方案。

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;
/

基本上：

1. 更新要选择的行，使用返回子句捕获更新后的行的行
2. ，然后使用rowids打开重新游标，只选择更新的行。

您确实发出了两个查询，但使用ROWID进行选择相当快。

Answer 2

问题在(3) -‘由一个OUT参数返回一个指向已找到的记录的SYS_REFCURSOR’。它并没有指向那个记录，因为你已经通过了fetch。我假设您只需要使用该ID的单个记录；如果您有多个ID，则返回的游标将指向带有该ID的下一个记录，但是您的update将使用该ID更新所有匹配的记录，而不仅仅是您获取的记录。

如果您只有一条记录，为什么要使用游标？我看到的唯一原因是允许您使用for update，但在更新中没有使用相应的where current of。