create table lcn_test0907(
id varchar2(100) primary key not null,
name varchar2(100) not null,
age varchar2(100) not null
);
insert into lcn_test0907 values(‘1’,‘李白’,‘18’);
insert into lcn_test0907 values(‘2’,‘lily’,‘16’);
insert into lcn_test0907 values(‘3’,‘Tom’,‘18’);
–1.删除
–示例:call pro_del_table(‘要操作的表名’,‘条件’,‘每次commit的行数’)
–示例1:call pro_del_table(‘Test’,‘age=18’,‘1000’) ,删除Test表中age=18的记录,每1000条commit一次
–示例2:call pro_del_table(‘Test’,‘1=1’,‘1000’) ,删除Test表中所有的记录,每1000条commit一次
create or replace procedure pro_del_table
(
d_TableName in varchar2,
d_Condition in varchar2,
d_Count in varchar2
)
as
–自治事物
pragma autonomous_transaction;
d_delete number:=0;
begin
while 1=1 loop
EXECUTE IMMEDIATE
‘delete from ’ || d_TableName || ’ where ’ || d_Condition || ’ and rownum <= :rn’