I have 3 tables:

```
Meeting_t : Meeting_id (PK), create_date (Date)
Prog_call : prog_call_id (PK) and Meeting_id (FK)
Lock_t    : lock_id (PK) and prog_call_id (FK)

```

There could be multiple records corresponding to each Meeting\_id in Prog\_call and similarly multiple records in Lock\_t corresponding to each prog\_call\_id.

Now I want to create a procedure which deletes all the records with create\_date < first day of previous year. This should also delete the corresponding data from other 2 tables (Prog\_call and Lock\_t). 

I created the following prodecure to do the task:

```
PROCEDURE CleanBefore(delete_before DATE)
IS
  meeting_id_v           dev.prog_call.meeting_id%TYPE;
  prog_call_id_v         dev.prog_call.prog_call_id%TYPE;

  CURSOR meeting_c IS
    SELECT st.meeting_id
    FROM dev.meeting_t st
    WHERE TRUNC(st.logoff_datum) <= TRUNC(CleanBefore.delete_before);

  CURSOR prog_call_c IS
    SELECT pa.prog_call_id
    FROM dev.prog_call pa
    WHERE pa.meeting_id = meeting_id_v;

BEGIN
  OPEN meeting_c;
    LOOP
      FETCH meeting_c INTO meeting_id_v;
        OPEN prog_call_c;
          LOOP
            FETCH prog_call_c INTO prog_call_id_v;

            DELETE FROM dev.lock_t lt
            WHERE lt.prog_call_id = prog_call_id_v;

            DELETE FROM dev.prog_call pa
            WHERE pa.prog_call_id = prog_call_id_v;
          END LOOP;
        CLOSE prog_call_c;

        DELETE FROM dev.meeting_t st
        WHERE st.meeting_id = meeting_id_v;
    END LOOP;
  CLOSE meeting_c;
END CleanBefore;

```

So far it is looking good to me.. I have compiled it and testing is in Progress. However I always look for a better solution. And i believe here is also a probability of making it more optimized. Any Suggestion would be of great help.