在Oracle存储过程中从SYS_REFCURSOR读取数据，并在java中重用它 [英] read data from SYS_REFCURSOR in a Oracle stored procedure and reuse it in java

问题描述

我有这个表：

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

然后我在Oracle中有一个简单的存储过程：
1.get a record from一个表
2.在找到的记录上更新一个列
3.返回一个指向找到的记录的OUT参数SYS_REFCURSOR：

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;

调用此过程找到
并更新字段，游标
无效，我不能使用它用于其他操作，
在这个例子中我使用光标记录id字段

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;

我使用实体以java模型预订

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 {
..
..
}

...并且我通过NamedNativeQuery获取它：

...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
}

对我唯一的请求是
-选择预订和在同一个事务中将其更新为存储过程
- 从java中调用存储过程并以@ Entity-Booking形式检索更新的预订

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

推荐答案

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

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.

当我选择一个预订时，我需要更新call_time来标记
为selected 。当一个预订有一个非null call_time是不可选
任何更多我需要返回更新的记录到java应用程序
所以我需要返回它作为过程的第一个参数
OUT sys_refcursor 类型。

请注意，真正的选择可能很难，所以我不想要执行
一次以上

" Note that the real select could be hard so i don't want to execute it more than one time"

好的，这里是一种方法Caveat： （即未经测试的代码），不能保证工作，但似乎是一个可行的解决方案。

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

基本上：

1. 更新您要选择的行


2. 使用RETURNING子句捕获更新行的rowid


3. 然后使用rowids打开refcursor仅选择更新的行。

您发出两个查询，但使用ROWID选择漂亮快速。

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

这篇关于在Oracle存储过程中从SYS_REFCURSOR读取数据，并在java中重用它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！