create or replace procedure XXXXXXXX
is cursor c_tbl_1 is
SELECT
R1.FIRST,
TO_CHAR(ADD_MONTHS(TO_DATE(R1.TIME,'YYYYMM'),1),'YYYYMM') AS SECOND,
R2.YEAR AS THIRD
FROM
A R1
INNER JOIN
B R2
ON
R2.FIRST = R1.FIRST
AND R2.TIME = TO_CHAR(ADD_MONTHS(TO_DATE(R1.TIME,'YYYYMM'),1),'YYYYMM')
FOR UPDATE OF R1.FIRST;
begin
for r_Record in c_tbl_1 loop
UPDATE A
SET
TIME = r_Record.SECOND,
YEAR = r_Record.THIRD
WHERE CURRENT OF c_tbl_1;
end loop;
commit;
exception
when others then
rollback;
end XXXXXXXX;ORA-01410: ROWID澷澚丅ORA-01410: ROWID无效
请问如何解决?
is cursor c_tbl_1 is
SELECT
R1.FIRST,
TO_CHAR(ADD_MONTHS(TO_DATE(R1.TIME,'YYYYMM'),1),'YYYYMM') AS SECOND,
R2.YEAR AS THIRD
FROM
A R1
INNER JOIN
B R2
ON
R2.FIRST = R1.FIRST
AND R2.TIME = TO_CHAR(ADD_MONTHS(TO_DATE(R1.TIME,'YYYYMM'),1),'YYYYMM')
FOR UPDATE OF R1.FIRST;
begin
for r_Record in c_tbl_1 loop
UPDATE A
SET
TIME = r_Record.SECOND,
YEAR = r_Record.THIRD
WHERE CURRENT OF c_tbl_1;
end loop;
commit;
exception
when others then
rollback;
end XXXXXXXX;ORA-01410: ROWID澷澚丅ORA-01410: ROWID无效
请问如何解决?
你的prodedure应该等同于下面的sql:
UPDATE A R1
SET TIME = TO_CHAR(ADD_MONTHS(TO_DATE(R1.TIME, 'YYYYMM'), 1), 'YYYYMM'),
YEAR = (SELECT R2.YEAR
FROM B R2
WHERE R2.FIRST = R1.FIRST
AND R2.TIME =
TO_CHAR(ADD_MONTHS(TO_DATE(R1.TIME, 'YYYYMM'), 1),
'YYYYMM')
AND ROWNUM = 1)
WHERE EXISTS
(SELECT NULL
FROM B R2
WHERE R2.FIRST = R1.FIRST
AND R2.TIME =
TO_CHAR(ADD_MONTHS(TO_DATE(R1.TIME, 'YYYYMM'), 1), 'YYYYMM')
AND ROWNUM = 1);
R1.FIRST有索引吗,重建一下看看