使用sql%rowcount来提供判断效率

没有使用之前

 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;

 

转载于:https://www.cnblogs.com/creasypita/p/8288862.html