[PLSQL] 使用cursor 與迴圈 刪除表格資料

前言:

    一般說來, 刪資料有delete table, truncate兩種, 如果要刪部分資料,只能用delete table的方式了。

    為何要寫另一個暫存索引表格test.usersid_pk 來刪資料?

因為線上的real.users 表格直接刪5筆檔案仍會hang住...已經很確定瞬間沒有任何table lock...

#針對test.users 的pk (ID) 欄位 , 建一個暫存的index 表格

create table test.usersid_pk tablespace users_bck storage (initial 4M next 4M)  as (select id from test.users where DATE < '20130301')

alter table test.usersid_pk add (seq number);

#建立一個sequence for test.usersid_pk(seq) 欄位

CREATE SEQUENCE test.id_seq MINVALUE 1 START WITH 1 INCREMENT BY 1;

#針對括欄後的test.usersid_pk 表格, 每一筆資料都按照序號發號碼牌

declare

 v_counter number:=0;

cursor cur is

select seq from test.usersid_pk  for update;

BEGIN

for c_id in cur loop

UPDATE test.usersid_pk

 SET seq = (test.id_seq.nextval );

 IF MOD( v_counter, 1000) = 0 THEN    -- Commits once per 1000 records

 commit;

 END IF;

  v_counter:=v_counter + 1;

END LOOP;

commit;

END;

/

#使用cursor 於loop內真正的刪除資料於 real.users 表格

#select count(*)  test.usersid_pk = 345643筆

sqlplus "/ as sysdba" <<EOF

spool del_usr_tab.log

set timing on

set time on

set echo on

set serverout on

DECLARE

   v_myid number:=0;

   BEGIN

   for v_number in 1..345643 loop

      select myid into v_myid from test.usersid_pk where seq = v_number;

       delete from real.users where id= v_myid;

       IF MOD( v_number, 25 ) = 0 THEN    -- Commits once per 25 records

          commit;

       END IF;

     end loop;

   END;

/

commit;

EOF

後來看到網路上也有使用rowid來刪資料的例子~
感覺這應該比較快~
http://blog.xuite.net/charley_ocp/mydba01/40532189