PL/SQL中FOR UPDATE 问题

[milo1984cn]

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无效
请问如何解决？

[oracledbalgtu]

你的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);

[Quote=引用楼主 milo1984cn 的帖子:]
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
…
[/Quote]

[cosio]

RA-01410 invalid ROWID

Cause: A ROWID was entered incorrectly. ROWIDs must be entered as formatted hexadecimal strings using only numbers and the characters A through F. A typical ROWID format is '000001F8.0001.0006'.

Action: Check the format, then enter the ROWID using the correct format. ROWID format: block ID, row in block, file ID.

[sleepzzzzz]

帮你顶下,学习

[milo1984cn]

暂时没有想要的答案 只有改成子查询了

[robin_ares]

可能是全表扫描了
R1.FIRST有索引吗，重建一下看看