--注意p_count 参数是每次要update的数量。 CREATE OR REPLACE PROCEDURE UPDATEBIGTAB(P_TABLENAME IN VARCHAR2, P_CONDITION IN VARCHAR2, P_COUNT IN VARCHAR2, P_UPDATE IN VARCHAR2) AS PRAGMA AUTONOMOUS_TRANSACTION; N_UPDATE NUMBER := 0; N_SUM NUMBER := 0; L_ROWID VARCHAR2(30); BEGIN EXECUTE IMMEDIATE 'select count(*) from ' || P_TABLENAME || ' where ' || P_CONDITION INTO N_SUM; WHILE (N_UPDATE < N_SUM) LOOP EXECUTE IMMEDIATE 'update ' || P_TABLENAME || ' set ' || P_UPDATE || ' where rowid in (select ss from (select rowid ss,rownum rn from ' || P_TABLENAME || ' t where '||P_CONDITION||' AND rownum<=:x1) where rn>:x2)' USING P_COUNT + N_UPDATE, N_UPDATE; N_UPDATE := SQL%ROWCOUNT + N_UPDATE; --DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); END LOOP; COMMIT; DBMS_OUTPUT.PUT_LINE('Finished!'); DBMS_OUTPUT.PUT_LINE('Totally ' || TO_CHAR(N_UPDATE) || ' records update!'); END; / 测试: DROP TABLE test; CREATE TABLE test AS SELECT object_id,object_name,0 id2 FROM all_objects; UPDATE test SET id2 = NULL; SELECT * FROM test; COMMIT; SQL> exec UPDATEBIGTAB('TEST',' object_id<40',5,' id2=object_id'); Finished! Totally 38 records update!
在存储过程里写
每更新1000条的时候
commit一次
我的意思是你写个存储过程
然后用游标检索出数据
循环游标 然后update
循环到1000的倍数的时候
就commit一次
根据rownum
然后控制rownum的范围比如说一次
更新1000那么rownum取1-1000的更新提交
然后在取rownum为1001至2000的更新提交
依次类推
CREATE OR REPLACE PROCEDURE UPDATEBIGTAB(P_TABLENAME IN VARCHAR2,
P_CONDITION IN VARCHAR2,
P_COUNT IN VARCHAR2,
P_UPDATE IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
N_UPDATE NUMBER := 0;
N_SUM NUMBER := 0;
L_ROWID VARCHAR2(30);
BEGIN
EXECUTE IMMEDIATE 'select count(*) from ' || P_TABLENAME || ' where ' ||
P_CONDITION
INTO N_SUM;
WHILE (N_UPDATE < N_SUM) LOOP
EXECUTE IMMEDIATE 'update ' || P_TABLENAME || ' set ' || P_UPDATE ||
' where rowid in (select ss from (select rowid ss,rownum rn from ' ||
P_TABLENAME || ' t where '||P_CONDITION||' AND rownum<=:x1) where rn>:x2)'
USING P_COUNT + N_UPDATE, N_UPDATE;
N_UPDATE := SQL%ROWCOUNT + N_UPDATE;
--DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally ' || TO_CHAR(N_UPDATE) ||
' records update!');
END;
/
测试:
DROP TABLE test;
CREATE TABLE test AS SELECT object_id,object_name,0 id2 FROM all_objects;
UPDATE test SET id2 = NULL;
SELECT * FROM test;
COMMIT;
SQL> exec UPDATEBIGTAB('TEST',' object_id<40',5,' id2=object_id');
Finished!
Totally 38 records update!
PL/SQL procedure successfully completed
Executed in 0.015 seconds