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.
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;