Oracle 存储过程 小例子

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();