create or replace procedure job_proc is
CURSOR pcursor is
select ID,formmain_id,sort from formson_0021 where FIELD0010 is null order by sort for update;
orgidCursor pcursor%rowtype;--定义类型为pcursor行数据的 变量
v_id FORMSON_0021.FORMMAIN_ID%type;
f_id FORMSON_0021.FORMMAIN_ID%type;
f_sort FORMSON_0021.sort%type;
f_id_10 FORMSON_0021.FIELD0010%type;
begin
f_id :=0;
f_sort :=0;
f_id_10 :=0;
open pcursor;--打开游标
loop
fetch pcursor
into orgidCursor;--把游标数据放进pcursor变量
exit when pcursor%notfound;
v_id :=orgidCursor.id;
if (v_id is null) then--v_id是可能为空的 表里面t.orgid为空
null;
else
select field0010 into f_id_10 from formson_0021 where formmain_id=orgidCursor.formmain_id and sort=orgidCursor.sort-1;
update formson_0021 x set x.field0010=f_id_10+field0009-field0013 where formmain_id=orgidCursor.formmain_id and sort=orgidCursor.sort;
commit;
end if;
end loop;
close pcursor;
end;
错误日志:
在行: 2 上开始执行命令时出错 -
call job_proc()
错误报告 -
SQL 错误: ORA-01002: 提取违反顺序
ORA-06512: 在 "SEEYON.JOB_PROC", line 15
01002. 00000 - "fetch out of sequence"
*Cause: This error means that a fetch has been attempted from a cursor
which is no longer valid. Note that a PL/SQL cursor loop
implicitly does fetches, and thus may also cause this error.
There are a number of possible causes for this error, including:
1) Fetching from a cursor after the last row has been retrieved
and the ORA-1403 error returned.
2) If the cursor has been opened with the FOR UPDATE clause,
fetching after a COMMIT has been issued will return the error.
3) Rebinding any placeholders in the SQL statement, then issuing
a fetch before reexecuting the statement.
*Action: 1) Do not issue a fetch statement after the last row has been
retrieved - there are no more rows to fetch.
2) Do not issue a COMMIT inside a fetch loop for a cursor
that has been opened FOR UPDATE.
3) Reexecute the statement after rebinding, then attempt to
fetch again.
CURSOR pcursor is
select ID,formmain_id,sort from formson_0021 where FIELD0010 is null order by sort for update;
orgidCursor pcursor%rowtype;--定义类型为pcursor行数据的 变量
v_id FORMSON_0021.FORMMAIN_ID%type;
f_id FORMSON_0021.FORMMAIN_ID%type;
f_sort FORMSON_0021.sort%type;
f_id_10 FORMSON_0021.FIELD0010%type;
begin
f_id :=0;
f_sort :=0;
f_id_10 :=0;
open pcursor;--打开游标
loop
fetch pcursor
into orgidCursor;--把游标数据放进pcursor变量
exit when pcursor%notfound;
v_id :=orgidCursor.id;
if (v_id is null) then--v_id是可能为空的 表里面t.orgid为空
null;
else
select field0010 into f_id_10 from formson_0021 where formmain_id=orgidCursor.formmain_id and sort=orgidCursor.sort-1;
update formson_0021 x set x.field0010=f_id_10+field0009-field0013 where formmain_id=orgidCursor.formmain_id and sort=orgidCursor.sort;
commit;
end if;
end loop;
close pcursor;
end;
错误日志:
在行: 2 上开始执行命令时出错 -
call job_proc()
错误报告 -
SQL 错误: ORA-01002: 提取违反顺序
ORA-06512: 在 "SEEYON.JOB_PROC", line 15
01002. 00000 - "fetch out of sequence"
*Cause: This error means that a fetch has been attempted from a cursor
which is no longer valid. Note that a PL/SQL cursor loop
implicitly does fetches, and thus may also cause this error.
There are a number of possible causes for this error, including:
1) Fetching from a cursor after the last row has been retrieved
and the ORA-1403 error returned.
2) If the cursor has been opened with the FOR UPDATE clause,
fetching after a COMMIT has been issued will return the error.
3) Rebinding any placeholders in the SQL statement, then issuing
a fetch before reexecuting the statement.
*Action: 1) Do not issue a fetch statement after the last row has been
retrieved - there are no more rows to fetch.
2) Do not issue a COMMIT inside a fetch loop for a cursor
that has been opened FOR UPDATE.
3) Reexecute the statement after rebinding, then attempt to
fetch again.
set t1.field0010 = (select t2.field0010 + t2.field0009 - t2.field0013
from formson_0021 t2
where t1.formmain_id = t2.formmain_id and t1.sort = t2.sort - 1)
where t1.FIELD0010 is null
;