Interview Questions

Oracle PL/SQL to delete child records

In this article lets discuss about how to delete a record(or row) from a table, if it has a tree of child tables (like child table has one or more child tables).

Consider a scenario where you have one parent table and n child tables. If the parent table was created with "ON DELETE CASCADE" in the CREATE TABLE statement then whenever a row in the parent table is deleted, the corresponding rows in the child tables will be deleted. But we won't have such favorable scenarios every time, as most application projects "ON DELETE CASCADE" will not be used while creating tables, just to retain records in child table.

In such cases it is necessary to delete the child records before deleting the parent record in parent table. In the worst case, it would be cumbersome to delete the records if the child tables in turn have a more child tables. So all the child table records in the parent-child hierarchical structure has to be deleted.

The following is PL/SQL script to delete a record from a parent table. It will delete all the child records from child tables first and then delete the parent record from parent table.

The input for the PL/SQL procedure are PARENT_TABLE_NAME, PARENT_COLUMN_NAME, PARENT_COLUMN_VALUE. The PARENT_COLUMN_NAME should be the name of the primary key column name.

CREATE OR REPLACE PROCEDURE del_record (
   p_table_name     IN   VARCHAR2,
   p_column_name    IN   VARCHAR2,
   p_column_value   IN   VARCHAR2
)
IS
   v_vendor_name        VARCHAR2 (255);
   v_parenttable_name   VARCHAR2 (255);
   v_childtable_name    VARCHAR2 (255);
   v_childcolumn_name   VARCHAR2 (255);
   v_table_query        VARCHAR2 (2550);
   v_table_query1       VARCHAR2 (2550);
   v_table_name         VARCHAR2 (255);
   v_id                 VARCHAR2 (255);
   v_count1             NUMBER          := 0;
   v_temp_issue_id      VARCHAR2 (255);
   v_count_query        VARCHAR2 (255);
   v_count              NUMBER          := 0;
   v_child_pk           VARCHAR2 (255);

   TYPE record_cur IS REF CURSOR;

   idcur                record_cur;
   idcur1               record_cur;

BEGIN
   v_table_name := p_table_name;
   v_table_query :=
         'Select a.table_name,b.table_name AS CHILD_TABLE, d.column_name ,(SELECT N.COLUMN_NAME FROM USER_CONSTRAINTS M, user_cons_columns N WHERE M.constraint_type = ''P'' AND M.CONSTRAINT_NAME = N.CONSTRAINT_NAME AND M.TABLE_NAME = B.TABLE_NAME AND ROWNUM=1 ) AS CHILD_PK from user_constraints a, user_constraints b, user_ind_columns c, user_cons_columns d where a.constraint_type = ''P'' and a.CONSTRAINT_NAME = b.R_CONSTRAINT_NAME and b.CONSTRAINT_TYPE = ''R'' and a.table_name = c.table_name  and a.constraint_name = c.index_name and b.CONSTRAINT_NAME = d.constraint_name and a.table_name = '''
      || v_table_name
      || '''';

   SELECT COUNT (*)
     INTO v_count
     FROM user_constraints a,
          user_constraints b,
          user_ind_columns c,
          user_cons_columns d
    WHERE a.constraint_type = 'P'
      AND a.constraint_name = b.r_constraint_name
      AND b.constraint_type = 'R'
      AND a.table_name = c.table_name
      AND a.constraint_name = c.index_name
      AND b.constraint_name = d.constraint_name
      AND a.table_name = '' || v_table_name || '';

   IF (v_count = 0)
   THEN
      DBMS_OUTPUT.put_line (   'DELETE FROM '
                            || p_table_name
                            || ' WHERE '
                            || p_column_name
                            || ' IN ( '''
                            || p_column_value
                            || ''')'
                           );

      EXECUTE IMMEDIATE (   'DELETE FROM '
                         || p_table_name
                         || ' WHERE '
                         || p_column_name
                         || ' IN ( '''
                         || p_column_value
                         || ''')'
                        );

      COMMIT;
      RETURN;
   END IF;

   OPEN idcur FOR v_table_query;

   LOOP
      FETCH idcur
       INTO v_parenttable_name, v_childtable_name, v_childcolumn_name,
            v_child_pk;

      IF (idcur%NOTFOUND)
      THEN
         RETURN;
      END IF;

      DBMS_OUTPUT.put_line (   'v_childtable_name ->'
                            || v_childtable_name
                            || ' v_childcolumn_name ->'
                            || v_childcolumn_name
                           );
      v_table_name := v_childtable_name;
      DBMS_OUTPUT.put_line (   'SELECT QRY --- '
                            || 'SELECT DISTINCT '
                            || v_child_pk
                            || ' FROM '
                            || v_childtable_name
                            || ' WHERE '
                            || v_childcolumn_name
                            || ' IN ( '''
                            || p_column_value
                            || ''')'
                           );
      v_table_query1 :=
            'SELECT DISTINCT '
         || v_child_pk
         || ' FROM '
         || v_childtable_name
         || ' WHERE '
         || v_childcolumn_name
         || ' IN ( '''
         || p_column_value
         || ''')';

      OPEN idcur1 FOR v_table_query1;

      LOOP
         FETCH idcur1
          INTO v_id;

         DBMS_OUTPUT.put_line ('v_id ---' || v_id);
         EXIT WHEN idcur1%NOTFOUND;

         IF v_count1 = 0
         THEN
            v_count1 := v_count1 + 1;
            v_temp_issue_id := v_id;
         ELSE
            v_temp_issue_id := '' || v_temp_issue_id || ''',''' || v_id;
         END IF;
      END LOOP;

      CLOSE idcur1;

      v_count1 := 0;
      DBMS_OUTPUT.put_line ('columns values ---' || v_temp_issue_id);
      del_device (v_childtable_name, v_child_pk, v_temp_issue_id);
      DBMS_OUTPUT.put_line (   'TO DELETE DEPT --- '
                            || 'DELETE FROM '
                            || p_table_name
                            || ' WHERE '
                            || p_column_name
                            || ' IN ( '''
                            || p_column_value
                            || ''')'
                           );

      EXECUTE IMMEDIATE (   'DELETE FROM '
                         || p_table_name
                         || ' WHERE '
                         || p_column_name
                         || ' IN ( '''
                         || p_column_value
                         || ''')'
                        );

      COMMIT;
   END LOOP;

   CLOSE idcur;
END del_record;