title: Deleting data with multiple referential integrities
body: <p>I have 3 tables:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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.</p>
  
  <p>Now I want to create a procedure which deletes all the records with create_date &lt; first day of previous year. This should also delete the corresponding data from other 2 tables (Prog_call and Lock_t). </p>
  
  <p>I created the following prodecure to do the task:</p>
  
  <pre><code>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) &lt;= 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;
  </code></pre>
  
  <p>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.</p>