如何在选择中使用SYS_REFCURSUR在PL/SQL中进行更新 [英] How use SYS_REFCURSUR in select for update in pl/sql

问题描述

我想选择多行，并更新所有选中的行.所以这个目标我写了这个查询.但是执行时会抛出异常.

I want to select multiple rows and also update all selected rows. so this goal i wrote this query. but when execute it throw exception.

我在像波纹管这样的生产者中写了这个查询.

I wrote this query in a producer like bellow.

PROCEDURE get_rows(
   a_cursor OUT SYS_REFCURSOR,
   a_id IN VARCHAR,
   a_count IN NUMBER); 

例外详情:

java.sql.SQLException:ORA-01002:按顺序提取

java.sql.SQLException: ORA-01002: fetch out of sequence

a_cursor是SYS_REFCURSOR

a_cursor is SYS_REFCURSOR

OPEN a_cursor FOR
  SELECT mytable.VID
  FROM   mytable
  WHERE  ROWNUM <= COUNT FOR UPDATE;

loop 
  FETCH a_cursor INTO a_id;
  if a_cursor %notfound then
    cnumber := 9999;
  else
    UPDATE mytable SET
    ...
    WHERE  VID = a_vid;
    COMMIT;
  end if;
end loop;

推荐答案

在update语句中不能使用sys_refcursor.您可以使用如下所示的显式游标.使用这种方式:

A sys_refcursor cannot be used in update statement. You can use an explicit cursor as shown below. Use this way:

    DECLARE
    cursor a_cursor is
      SELECT mytable.VID
      FROM   mytable
      WHERE  ROWNUM <= COUNT FOR UPDATE;

    a_id number;
    begin
    OPEN a_cursor;
    loop     
      FETCH a_cursor INTO a_id;          
      exit when a_cursor%notfound; 

        UPDATE mytable SET
        ...
        WHERE  VID = a_vid;                   
    end loop;
    COMMIT;
   close a_cursor;
 end;

create or replace PROCEDURE get_rows(
                                   a_cursor OUT SYS_REFCURSOR,
                                   a_id IN VARCHAR,
                                   a_count IN NUMBER)
IS                                   
cursor a_cur is
  SELECT mytable.VID
  FROM   mytable
  WHERE  ROWNUM <= a_COUNT ;

a_id NUMBER;
cnumber number;
BEGIN 
OPEN a_cur;

 LOOP
  FETCH a_cur INTO a_id; 

  IF a_cur%notfound THEN
   cnumber := 9999;
  End if;

  exit when a_cursor%notfound; 

    UPDATE mytable SET
     ...
    WHERE  VID = a_vid;
  END loop;
  COMMIT;    
  CLOSE a_cur;

  Open a_cursor for select * from mytable;

end ;

这篇关于如何在选择中使用SYS_REFCURSUR在PL/SQL中进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！