日期:2014-05-16  浏览次数:20432 次

oracle的批量删除数据表记录
对于数据库表中有海量的数据记录的情况。直接用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;
/