CREATE OR REPLACE PROCEDURE P_TEST AS ID ROWID; LCOUNT INTEGER :=0; CURSOR CSR1 IS SELECT ROWID ROW_ID FROM TEST.PEOTAB; BEGIN FOR CSRREC IN CSR1 LOOP ID := CSRREC.ROW_ID; UPDATE TEST.PEOTAB SET PEO9='女' WHERE ROWID=ID; LCOUNT :=LCOUNT+1; IF LCOUNT > 3000 THEN COMMIT; END IF; END LOOP; COMMIT; END ;
oracle什么版本,我在8.0.5下没问题! 另:报什么错误贴出来
CREATE OR REPLACE PROCEDURE P_TEST AS ID ROWID; LCOUNT INTEGER :=0; CURSOR CSR1 IS SELECT ROWID ROW_ID FROM TEST.PEOTAB;
BEGIN FOR CSRREC IN CSR1 LOOP ID := CSRREC.ROW_ID; UPDATE TEST.PEOTAB SET PEO9='女' WHERE ROWID=ID; LCOUNT :=LCOUNT+1; IF LCOUNT > 3000 THEN COMMIT; LCOUNT :=0; END IF; END LOOP; COMMIT; END ;楼主在此用了游标不是多此一举吗,直接update语句就可以
ID ROWID;
LCOUNT INTEGER :=0;
CURSOR CSR1 IS
SELECT ROWID ROW_ID FROM TEST.PEOTAB; BEGIN
FOR CSRREC IN CSR1 LOOP
ID := CSRREC.ROW_ID;
UPDATE TEST.PEOTAB SET PEO9='女' WHERE ROWID=ID;
LCOUNT :=LCOUNT+1;
IF LCOUNT > 3000 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END ;
另:报什么错误贴出来
ID ROWID;
LCOUNT INTEGER :=0;
CURSOR CSR1 IS
SELECT ROWID ROW_ID FROM TEST.PEOTAB;
BEGIN
FOR CSRREC IN CSR1 LOOP
ID := CSRREC.ROW_ID;
UPDATE TEST.PEOTAB SET PEO9='女' WHERE ROWID=ID;
LCOUNT :=LCOUNT+1;
IF LCOUNT > 3000 THEN
COMMIT;
LCOUNT :=0;
END IF;
END LOOP;
COMMIT;
END ;楼主在此用了游标不是多此一举吗,直接update语句就可以
我不想用UPDATE,所以才出问题;警告: 创建的过程带有编译错误。SP2-0158: 未知的SHOW选项"EROR"
PROCEDURE P_TEST 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
4/9 PLS-00341: 游标 'CSR1' 的说明不完整或格式错误
5/2 PL/SQL: SQL Statement ignored
5/27 PLS-00201: 必须说明标识符 'TEST.PEOTAB'
6/9 PL/SQL: Item ignored
9/2 PL/SQL: Statement ignored
9/8 PLS-00364: 循环索引变量 'CSRREC' 的使用无效
10/2 PLS-00201: 必须说明标识符 'PEOTAB'
10/2 PL/SQL: SQL Statement ignored
后来我把 TEST.PEOTAB 设在当前用户下面,则问题解决!