没有使用之前
1 declare rowcount1 int :=0; 2 begin 3 loop 4 delete from bdcqz a where exists (select 1 from bdcqz b where a.zsbsm=b.zsbsm and a.bsm<b.bsm ) 5 and rownum <2 ; 6 commit; 7 select count(1) into rowcount1 from bdcqz a where exists (select 1 from bdcqz b where a.zsbsm=b.zsbsm and a.bsm<b.bsm ) 8 and rownum <2; 9 if rowcount1=0 then 10 dbms_output.put_line('delete complete '); 11 exit; 12 else 13 dbms_output.put_line('continue to delete'); 14 end if; 15 end loop; 16 end;
使用SQL%ROWCOUNT 参数， 循环中通过 SQL%ROWCOUNT 判断影响行数 减少 判断语句 select count(1) into rowcount1 from bdcqz a where exists (select 1 from bdcqz b where a.zsbsm=b.zsbsm and a.bsm<b.bsm ) and rownum <2 的使用次数，从而提高效率
1 declare rowcount1 int :=0; 2 3 begin 4 loop 5 delete from bdcqz a where exists (select 1 from bdcqz b where a.zsbsm=b.zsbsm and a.bsm<b.bsm ) 6 and rownum <2 ; 7 rowcount1 := SQL%ROWCOUNT; 8 commit; 9 if rowcount1=0 then 10 dbms_output.put_line('delete complete '); 11 exit; 12 else 13 dbms_output.put_line('continue to delete'); 14 end if; 15 end loop; 16 end;
