对于数据库表中有海量的数据记录的情况。直接用delete命令删除的话,非常的慢。
以下是一个方法来批量删除数据记录:
以下是一个方法来批量删除数据记录:
CREATE OR REPLACE PROCEDURE delbigtab (
p_tablename IN VARCHAR2,
p_condition IN VARCHAR2,
p_count IN VARCHAR2
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
n_delete NUMBER := 0;
BEGIN
/*
示例 :
begin
delbigtab('sb_user', 'deleted = 1', '500');
end;
*/
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_delete := n_delete + SQL%ROWCOUNT;
END IF;
COMMIT;
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line ('Finished!');
DBMS_OUTPUT.put_line ('Totally ' || TO_CHAR (n_delete)
|| ' records deleted!'
);
END delbigtab;
/