如何在选择中使用SYS_REFCURSUR在PL/SQL中进行更新 [英] How use SYS_REFCURSUR in select for update in pl/sql
本文介绍了如何在选择中使用SYS_REFCURSUR在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屋!
查看全文