--大表删除数据的方法(参数说明:表名,update的条件,update提交的数量) CREATE OR REPLACE PROCEDURE updateBIGTAB(P_TABLENAME IN VARCHAR2, P_CONDITION IN VARCHAR2, P_COUNT IN VARCHAR2) AS PRAGMA AUTONOMOUS_TRANSACTION; N_update NUMBER := 0; BEGIN WHILE 1 = 1 LOOP EXECUTE IMMEDIATE 'delete from ' || P_TABLENAME || ' where ' || P_CONDITION || ' and rownum <= :rn' USING P_COUNT; IF SQL%NOTFOUND THEN EXIT; ELSE N_update := N_update + SQL%ROWCOUNT; END IF; COMMIT; END LOOP; COMMIT; DBMS_OUTPUT.PUT_LINE('Finished!'); DBMS_OUTPUT.PUT_LINE('Totally ' || TO_CHAR(N_update) || ' records update!'); END;
PL SQL里怎么做,请详细说明一下,谢谢
--注意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!
PL/SQL procedure successfully completed
Executed in 0.015 seconds
个人经验: 很少使用第三方程序来控制事物。 一般在自己要做更新的存储过程中进行 判断 方法很简单 UPDATE .............; A := A + SQL%ROWCOUNT; IF MOD(A,1000)= 0 THEN COMMIT; END IF;
方法不能这么简单吧, 如果UPDATE .............; 更新了2500件 再执行IF MOD(A,1000)= 0 THEN COMMIT; END IF; 还有意义么?
--大表删除数据的方法(参数说明:表名,update的条件,update提交的数量)
CREATE OR REPLACE PROCEDURE updateBIGTAB(P_TABLENAME IN VARCHAR2,
P_CONDITION IN VARCHAR2,
P_COUNT IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
N_update NUMBER := 0;
BEGIN
WHILE 1 = 1 LOOP
EXECUTE IMMEDIATE 'delete from ' || P_TABLENAME || ' where ' ||
P_CONDITION || ' and rownum <= :rn'
USING P_COUNT;
IF SQL%NOTFOUND THEN
EXIT;
ELSE
N_update := N_update + SQL%ROWCOUNT;
END IF;
COMMIT;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally ' || TO_CHAR(N_update) || ' records update!');
END;
PL SQL里怎么做,请详细说明一下,谢谢
--注意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!
PL/SQL procedure successfully completed
Executed in 0.015 seconds
很少使用第三方程序来控制事物。
一般在自己要做更新的存储过程中进行 判断
方法很简单
UPDATE .............;
A := A + SQL%ROWCOUNT;
IF MOD(A,1000)= 0 THEN
COMMIT;
END IF;
方法不能这么简单吧,
如果UPDATE .............; 更新了2500件
再执行IF MOD(A,1000)= 0 THEN
COMMIT;
END IF; 还有意义么?