create or replace procedure cleandb
as
cursor tb is select t.*,t.rowid from t_temp_affair_loudong t order by t.scn;
cursor tb1 is select t.*,t.rowid from t_temp_affair_loudong t order by t.scn;
countout number:=0;
countin number;
mycount number:=0;
type myarr is varray(500) of varchar2(20);
arr myarr:= myarr();
begin
arr.extend(2);
for loudong in tb loop
countout := countout+1;
countin := 0;
for loudong1 in tb1 loop
countin := countin+1;
if loudong1.scn = loudong.scn and not loudong.rowid=loudong1.rowid and countin>countout then
mycount:=mycount+1;
if arr.count()<mycount then
arr.extend(2);
end if;
DBMS_OUTPUT.put_line(loudong.rowid);
arr(mycount):=loudong.rowid;
end if;
end loop;
update t_temp_affair_loudong t set t.applytime_d = to_date(loudong.applytime_v,'yyyy-mm-dd') where t.rowid = loudong.rowid;
update t_temp_affair_loudong t set t.audittime_d = to_date(loudong.audittime_v,'yyyy-mm-dd') where t.rowid = loudong.rowid;
end loop;
for i in 1..arr.count() loop
DBMS_OUTPUT.put_line(arr(i)||'*');
if arr(i) is not null then
delete from t_temp_affair_loudong t where t.rowid = arr(i);
end if;
end loop;
delete from t_temp_affair_loudong t where not length(t.scn)=10 or t.scn is null;
commit;
end;
-- call cleandb();
as
cursor tb is select t.*,t.rowid from t_temp_affair_loudong t order by t.scn;
cursor tb1 is select t.*,t.rowid from t_temp_affair_loudong t order by t.scn;
countout number:=0;
countin number;
mycount number:=0;
type myarr is varray(500) of varchar2(20);
arr myarr:= myarr();
begin
arr.extend(2);
for loudong in tb loop
countout := countout+1;
countin := 0;
for loudong1 in tb1 loop
countin := countin+1;
if loudong1.scn = loudong.scn and not loudong.rowid=loudong1.rowid and countin>countout then
mycount:=mycount+1;
if arr.count()<mycount then
arr.extend(2);
end if;
DBMS_OUTPUT.put_line(loudong.rowid);
arr(mycount):=loudong.rowid;
end if;
end loop;
update t_temp_affair_loudong t set t.applytime_d = to_date(loudong.applytime_v,'yyyy-mm-dd') where t.rowid = loudong.rowid;
update t_temp_affair_loudong t set t.audittime_d = to_date(loudong.audittime_v,'yyyy-mm-dd') where t.rowid = loudong.rowid;
end loop;
for i in 1..arr.count() loop
DBMS_OUTPUT.put_line(arr(i)||'*');
if arr(i) is not null then
delete from t_temp_affair_loudong t where t.rowid = arr(i);
end if;
end loop;
delete from t_temp_affair_loudong t where not length(t.scn)=10 or t.scn is null;
commit;
end;
-- call cleandb();