How to have conditional step-by-step deletion of records in the data table
Author: eygle
Source: http://blog.eygle.com
Date: February 22, 2005
«Do yourself, full of clothing | blog Home
Sometimes we need to assign some records of deleting data tables, submitted batch batch to reduce the use of UNDO, providing a simple stored procedure for implementing the logic.
You can make appropriate adjustments according to your needs, this example is for reference only:
SQL> CREATE TABLE TEST AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> Create or Replace Procedure deleteTab
2 /**
3 ** usage: Run the script to create the proc deleteTab
4 ** in SQL * Plus, Type "Exec DeleteTab ('foo', 'ID> = 1000000', '3000');"
5 ** to delete the records in the table "foo", commit per 3000 records.
6 **
7 ** /
8 (
9 p_tablename in varchar2, - The Tablename Which you want to delete from
10 p_condition in varcha2, - delete conputing, such as "ID> = 100000"
11 p_count in varchar2 - Commit After delete how Many Records
12)
13 AS
14 prgma autonomous_transaction;
15 n_delete number: = 0;
16 Begin
17 While 1 = 1 loop
18 Execute Immediate
19 'delete from' || p_tablename || 'Where' || p_condition || 'and rownum <=: rn'
20 using p_count;
21 if SQL% Notfound Then
22 exit;
23 Else
24 n_delete: = n_delete SQL% ROWCOUNT;
25 end if;
26 commit;
27 end loop;
28 commit;
29 dbms_output.put_line ('finished!');
30 dbms_output.put_line ('totally' || to_char (n_delete) || 'Records deleted!');
31 End;
32 /
Procedure create.
SQL> INSERT INTO TEST SELECT * FROM DBA_OBJECTS;
Rows created.
SQL> /
Rows created.
SQL> /
Rows created.
SQL> commit;
COMMIT COMPLETE.
SQL> EXEC DeleteTab ('Test', 'Object_ID> 0', '3000') Finished!
Totally 19107 Records Deleded!
PL / SQL Procedure SuccessFully Completed.
Very simple, but I want to come. Some people can use it.
