感谢同事的无私奉献！

-------------------------------------------------------------------------------------------------------------

因数据量巨大，每天增长量2000万，因此删除特定条件的数据，是从存储过程来实现批量删除掉的功能！

-------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE proc_deletes(in_directory IN VARCHAR2, --directories (e.g:data_pump_dir)

in_tablename IN VARCHAR2,

out_msg OUT NUMBER) AS

i NUMBER := 0;

v_count NUMBER := 2000;

v_total NUMBER := 0;

v_lines NUMBER := 0;

v_source SYS_REFCURSOR;

v_file UTL_FILE.FILE_TYPE;

v_lname VARCHAR2(128);

v_sql VARCHAR2(4000);

TYPE autoids IS TABLE OF number;

v_autoid autoids;

BEGIN

v_sql := 'select t.autoid from ' || in_tablename ||

' t where t.yn = 0 and rownum <=100000';

v_lname := 'proc_delete:' || in_tablename ||

to_char(SYSDATE, 'YYYYMMDD_HHMISS') || '.log';

v_file := UTL_FILE.FOPEN(upper(in_directory), v_lname, 'w', 32767);

UTL_FILE.PUT_LINE(v_file, 'Start delete tables:');

UTL_FILE.FFLUSH(v_file);

DBMS_OUTPUT.PUT_LINE('Start delete tables:');

--for debug v_sql

--UTL_FILE.PUT_LINE(v_file, v_sql);

LOOP

OPEN v_source FOR v_sql;

FETCH v_source BULK COLLECT

INTO v_autoid;

--unload row record

FOR indx IN v_autoid.FIRST .. v_autoid.LAST LOOP

i := i + 1;

EXECUTE IMMEDIATE 'delete from ' || in_tablename ||

' t where t.autoid = :1'

using v_autoid(indx);

IF MOD(i, v_count) = 0 THEN

v_total := v_total + 1;

UTL_FILE.PUT_LINE(v_file,

'. Run: ' || LPAD(v_total, 30, '.') ||

' RecordNum: ' || LPAD(i, 30, '.') ||

' ' ||

TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS'));

UTL_FILE.FFLUSH(v_file);

COMMIT;

END IF;

END LOOP;

COMMIT;

v_lines := v_source%ROWCOUNT;

IF v_lines = 0 THEN

COMMIT;

EXIT;

END IF;

CLOSE v_source;

END LOOP;

UTL_FILE.PUT_LINE(v_file, 'Success unload tables!');

DBMS_OUTPUT.PUT_LINE('Success unload tables!');

UTL_FILE.FCLOSE(v_file);

--succeed

OUT_MSG := 1;

RETURN;

END;

ITPub博客

批量删除数据_procedure

最新文章