oracel数据处理

删除大量数据，避免大事务
DECLARE
i number;
allcount number;
BEGIN
i := 0;
select ceil(count(*) / 100) into allcount from ops_rank_bak;
WHILE i < allcount LOOP
i := i + 1;
delete from ops_rank_bak where rownum <= 100;
commit;
END LOOP;
commit;
END;

游标移动历史数据 每5000条提交一次。
declare
cursor cur is
select t.* from ops_test t where t.id > 6;
i number;
begin
i := 0;
for item in cur loop
merge into ops_test_history target using (select item.id as id,item.create_time as create_time from dual) sources on (sources.id=target.id)
when matched then
update set target.create_time=sources.create_time
when not matched then
insert (target.id,target.create_time)values (sources.id,sources.create_time);
delete from ops_test where id = item.id;
i := i + 1;
if (i >= 5000) then
commit;
i := 0;
end if;
end loop;
commit;
end;

创建带参数的存储过程
create or replace procedure clear_ops_kpi_d(from_file_name IN ops_kpi_d.from_file_name%type) as
begin
declare
cursor cur is
select id from ops_kpi_d where FROM_FILE_NAME = from_file_name;
i number;
begin
i := 0;
for item in cur loop
delete from ops_kpi_d where id = item.id;
i := i + 1;
if (i >= 5000) then
commit;
i := 0;
end if;
end loop;
commit;
end;
end;

调用存储过程：
call CLEAR_OPS_KPI_D('CHAN_KPI_D_[20190917].DAT')
DECLARE
i number;
allcount number;
BEGIN
i:= 0;
select ceil(count(*) / 100) into allcount from ops_rank_bak;
WHILE i < allcount LOOP
i:= i + 1;
delete from ops_rank_bak where rownum <= 100;
commit;
END LOOP;
commit;
END;