java批量删除mysql数据_批量删除数据库记录

有条件的分步删除数据表中的记录

--创建测试表

create table test as select * from dba_objects;

Table created.

--创建删除表的存储过程

create or replace procedure deleteTab

--插入语句

SQL> insert into test select * from dba_objects;

6374 rows created.

SQL> /

6374 rows created.

SQL> /

6374 rows created.

SQL> commit;

--创建删除的存储过程

create or replace procedure deleteTab

/**

** Usage: run the script to create the proc deleteTab

**        in SQL*PLUS, type "exec deleteTab('Foo','ID>=1000000','3000');"

**        to delete the records in the table "Foo", commit per 3000 records.

**       Condition with default value '1=1' and default Commit batch is 10000.

**/

(

p_TableName    in    varchar2,    -- The TableName which you want to delete from

p_Condition    in    varchar2 default '1=1',    -- Delete condition, such as "id>=100000"

p_Count        in    varchar2 default '10000'    -- Commit after delete How many records

)

as

pragma autonomous_transaction;

n_delete 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_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;

/

--执行语句

SQL> exec deleteTab('TEST','object_id >0','10000')

你看看执行结果我试验过，效果还可以