我想选择多行并更新所有选定的行。所以这个目标我写了这个查询。但是在执行时抛出异常。
我在像波纹管这样的生产者中写了这个查询。
PROCEDURE get_rows(
a_cursor OUT SYS_REFCURSOR,
a_id IN VARCHAR,
a_count IN NUMBER);
异常详情:
java.sql.SQLException: ORA-01002: 获取顺序错误
a_cursor
是 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;
sys_refcursor
不能在update
语句中使用A。您可以使用显式游标,如下所示。使用这种方式:
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 ;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句