SQL> l 1 create or replace procedure del_hubei_SSR_forall 2 as 3 type ridArray is table of rowid index by binary_integer; 4 type dtArray is table of varchar2(50) index by binary_integer; 5 6 v_rowid ridArray; 7 v_mid_to_delete dtArray; 8 9 begin 10 select mid,rowid bulk collect into v_mid_to_delete,v_rowid from temp_mid_hubei where rownum<11; 11 12 forall i in 1 .. v_mid_to_delete.COUNT 13 delete from SSR where mid = v_mid_to_delete(i); 14 15 DBMS_OUTPUT.PUT_LINE('Full Finished!!!'); 16 DBMS_OUTPUT.PUT_LINE('Totally '||to_char(v_mid_to_delete.COUNT)||' records deleted from hubei_SSR !!!'); 17 18 forall i in 1 .. v_rowid.COUNT 19 delete from temp_mid_hubei where rowid = v_rowid(i); 20 21 DBMS_OUTPUT.PUT_LINE('Full Finished!!!'); 22 DBMS_OUTPUT.PUT_LINE('Totally '||to_char(v_rowid.COUNT)||' records deleted from temp_mid_hubei !!!'); 23 24* end; SQL> /
Procedure created.
Elapsed: 00:00:00.07 SQL> SQL> exec del_hubei_SSR_forall; Full Finished!!! Totally 10 records deleted from hubei_SSR !!! Full Finished!!! Totally 10 records deleted from temp_mid_hubei !!!
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.16 SQL>
| 
