清理oracle 数据文件,Oracle 高效清理数据

declare

-- Local variables here

CURSOR CUR1 IS

SELECT ec.ROWID

from epcisbase.employee_channel ec, epcisbase.sas_employee t

where t.employee_code = ec.employee_code

and t.employee_channel in ('DS')

and ec.business_source_code = '2'

and ec.business_source_detail_code = '1'

and ec.channel_source_code = 'J'

and ec.channel_source_detail_code = 'H';

V_ROWID_TAB DBMS_SQL.UROWID_TABLE;

V_LIMIT PLS_INTEGER := 5000; -->定义了一个变量来作为LIMIT的值

V_COUNTER PLS_INTEGER := 0;

begin

OPEN CUR1;

LOOP

FETCH CUR1 BULK COLLECT

INTO V_ROWID_TAB -->FETCH时使用了BULK COLLECT子句

LIMIT V_LIMIT; -->使用LIMIT子句限制提取数据量

EXIT WHEN V_ROWID_TAB.COUNT = 0; -->注意此时游标退出使用了TMP_TAB.COUNT，而不是CUR1%NOTFOUND

V_COUNTER := V_COUNTER + 1; -->记录使用LIMIT之后FETCH的次数

if V_ROWID_TAB.count > 0 then

forall j in V_ROWID_TAB.FIRST .. V_ROWID_TAB.LAST

delete epcisbase.employee_channel ec

where ROWID = V_ROWID_TAB(j);

end if;

commit;

END LOOP;

CLOSE CUR1;

end;

/