SQL & PL/SQL

Hello Champs,

I am having requirement to create procedure to achive the deletes from 5 big tables. 1 master table and 4 child tables. Each table has 28 millions records. Now I have to schedule a procedure to run it on daily basis to delete approximately 500k records from each tables. But the condition is: Seperate procedures for each tables and delete only 1000 rows at a time. The plan is below. Since I am new for writing complicate procedure, I don't have much idea how to code the procedures, can some one help me to design the procedure queries. Many Thanks

1. Fetch 1000 rows from master table with where clause condition.
2. Match them with each child tables and delete the records.
3. Atlast delete those 1000 rows from master table.
4. over all commit for all 5 X 1000 records.
5. Repeat the steps 1 to 4 till no rows to fetch from master table.


Below are detailed procedure plan:----


----- Main procedure to fetch 1000 rows at a time and providing them as input to 5 different procedures to delete records, repeating same steps till no rows to fetch i.e. (i=0)-----
Create procedure fetch_record_from_master_table:
loop
i = Select column1 from mastertable where <somecondition> and rowcount <= 1000;
call procedure 1 and i rows as input;
call procedure 2 and i rows as input;
call procedure 3 and i rows as input;
call procedure 4 and i rows as input;
call procedure 5 and i rows as input;
commit;
exit when i=0
end loop;


----- Sepereate procedure's to delete records from each child tables------
Create procedure 1 with input:
delete from child_table1 where column1=input rows of master table;

Create procedure 2 with input:
delete from child_table2 where column1=input rows of master table;

Create procedure 3 with input:
delete from child_table3 where column1=input rows of master table;

Create procedure 4 with input:
delete from child_table4 where column1=input rows of master table;


--- procedure to delete records from master table atlast----
Create procedure 5 with input:
delete from Master_table where column1=input rows of master table;