常用的批量插入和删除语句

创建测试表:

SQL> create table test(id number(10),name varchar2(10),time date); 

Table created.

1).批量插入语句(总共插入10000条记录,每500条COMMIT一次)

SQL>declare
  l_num number;
begin
for i in 1..10000
loop
insert into test values(i,'test',sysdate);
 l_num := l_num + 1;
 if l_num = 500 then --或者l_num%500 = 0
    commit;
    l_num :=0;
 end if;  
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

  COUNT(*)
----------
     10000

2).批量删除语句(有条件的分步删除数据表中的记录,每500条COMMIT一次):
方法1:
SQL>declare
  l_Lnum number default 1;   
  l_Sdrow number default 0;  
  l_Edrow number default 0;                               
  begin    
    l_Sdrow :=&Sdrow;
    l_Edrow :=&Edrow;                                
    while l_Lnum <= trunc(l_Sdrow/l_Edrow) loop                          
    delete from test where rownum <=l_Edrow;                                 
    commit;   
          l_Lnum :=l_Lnum + 1;                         
   end loop;
    if mod(l_Sdrow,l_Edrow) <> 0 then
    delete from test where rownum <=mod(l_Sdrow,l_Edrow);                                 
    commit;   
    end if;
   dbms_output.put_line('finished');
  end;
/  

Enter value for sdrow: 9000
old   6:     l_Sdrow :=&Sdrow;
new   6:     l_Sdrow :=9000;
Enter value for edrow: 500
old   7:     l_Edrow :=&Edrow;
new   7:     l_Edrow :=500;

PL/SQL procedure successfully completed. 

SQL> select count(*) from test;

  COUNT(*)
----------
      1000

方法2:
Oracle专家eygle给出的解决方案:
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;
/

Procedure created.

--执行语句:
SQL> set serveroutput on
SQL> select count(*) from test where id > 1000;

  COUNT(*)
----------
      9000
     
SQL> exec deleteTab('TEST','id >1000','500');
Finished!
Totally 9000 records deleted!

PL/SQL procedure successfully completed.