How to delete a record in a datasheet in a conditional step-by-step way

Data | How conditions conditionally delete a record in a datasheet author: Eygle Origin: http://blog.eygle.com Date: February 22, 2005
«Do-it-yourself, ample clothing | Blog Home
Sometimes we need to allocate some records to delete the datasheet and submit it in batches to reduce the use of undo, this article provides a simple stored procedure for implementing this 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*PL US, type "exec deletetab (' Foo ', ' id>=1000000 ', ' 3000 ');" 5 * * Delete the records in the table "Foo" and commit per 3000 records. 6 * * * 7 **/8 (9 p_tablename in Varchar2,--the tablename which you want to deletes from the p_condition in Varchar2,-De Lete condition, such as "id>=100000" one p_count in Varchar2--commits after deletes how many records) a autonomous_transaction; N_delete number:=0; 1=1 Loop-EXECUTE IMMEDIATE ' delete from ' | | p_tablename| | ' WHERE ' | | p_condition| | ' and rownum <=: Rn ' USING p_count; If Sql%notfound then exit; Or else N_delete:=n_delete + sql%rowcount; The IF; a commit; End Loop; commit; Dbms_output. Put_Line (' finished! '); Dbms_output. Put_Line (' Totally ' | | To_char (n_delete) | | | ' records deleted! ');to end; 32/procedure created. sql> INSERT INTO Test select * from dba_objects;6374 rows created. sql>/6374 rows created. sql>/6374 rows created. Sql> commit; Commit complete. sql> exec deletetab (' TEST ', ' object_id >0 ', ' 3000 ') finished! Totally 19107 records Deleted!pl/sql procedure successfully.

It's simple, but there are people who want to use it.