Вполне вероятно в некоторых случаях скрипт работать не будет.
СНЯТЬ БЕКАП ПЕРЕД ЗАПУСКОМ!
set serveroutput on size 1000000
DROP TABLE ref_table;
CREATE TABLE ref_table(
table_name VARCHAR2(30) NOT NULL,
rid rowid NOT NULL PRIMARY KEY,
lvl NUMBER NOT NULL);
DECLARE
lLVL INTEGER := 0;
lROWS_QNT NUMBER;
i PLS_INTEGER;
function build_column_list(aCONSTRAINT_NAME VARCHAR2, aPREFIX VARCHAR2) RETURN VARCHAR2 IS
RESULT VARCHAR2(4000);
BEGIN
FOR rec IN (
SELECT *
FROM user_cons_columns
WHERE constraint_name = aCONSTRAINT_NAME
ORDER BY position
) LOOP
RESULT := RESULT || ',' || aPREFIX || rec.column_name;
END LOOP;
--dbms_output.put_line(LTRIM(RESULT, ','));
RETURN LTRIM(RESULT, ',');
END build_column_list;
FUNCTION process_reference(aLVL NUMBER, aTBL_NAME VARCHAR2, aFK_CONS VARCHAR2, aPAR_TABLE_NAME VARCHAR2, aPK_CONS VARCHAR2) RETURN NUMBER IS
LQNT NUMBER;
BEGIN
dbms_output.put_line('Обработка ссылки на уровне ' || aLVL || ' ' || aTBL_NAME || '.' || aFK_CONS || ' -> ' || aPAR_TABLE_NAME || '.' || aPK_CONS);
IF aTBL_NAME <> aPAR_TABLE_NAME THEN
EXECUTE IMMEDIATE '
UPDATE /*+ NO_INDEX(x)*/ref_table x
SET lvl = (
select max(lvl) + 1
FROM ' || aPAR_TABLE_NAME || ' pt, ref_table rt
WHERE rt.rid = pt.rowid
AND ( ' || build_column_list(aPK_CONS, 'pt.') || ') IN (SELECT ' || build_column_list(aFK_CONS, 't.') || ' FROM ' || aTBL_NAME || ' t)
)
WHERE (rid || '' '') IN (
SELECT /*+ NO_INDEX(t)*/ t.rowid || '' ''
FROM ' || aTBL_NAME || ' t
WHERE ( ' || build_column_list(aFK_CONS, 't.') || ')
IN (SELECT ' || build_column_list(aPK_CONS, 'pt.') || ' FROM ref_table rt, ' || aPAR_TABLE_NAME || ' pt WHERE rt.rid = pt.rowid))';
END IF;
dbms_output.put_line('Обновлено уровней: ' || SQL%ROWCOUNT);
EXECUTE IMMEDIATE '
INSERT INTO ref_table(table_name, rid, lvl)
SELECT ''' || UPPER(aTBL_NAME) || ''', t.rowid, :LVL + 1
FROM ' || aTBL_NAME || ' t
WHERE ( ' || build_column_list(aFK_CONS, 't.') || ')
IN (SELECT ' || build_column_list(aPK_CONS, 'pt.') || ' FROM ref_table rt, ' || aPAR_TABLE_NAME || ' pt WHERE rt.rid = pt.rowid)
AND NOT EXISTS (
SELECT NULL
FROM ref_table rt
WHERE rid = t.rowid)'
USING aLVL;
lQNT := SQL%ROWCOUNT;
dbms_output.put_line('Добавлено строк ' || lQNT);
RETURN lQNT;
END;
FUNCTION process_level(aLVL NUMBER) RETURN NUMBER IS
lQNT NUMBER := 0;
BEGIN
dbms_output.put_line('+++ level = ' || aLVL);
FOR rec IN (
SELECT DISTINCT f.table_name chi_table_name, f.constraint_name chi_table_cons,
pp.table_name par_table_name, pp.constraint_name par_table_cons
FROM user_constraints f, user_constraints pp
WHERE f.constraint_type = 'R'
AND f.r_constraint_name = pp.constraint_name
AND pp.table_name IN (SELECT upper(table_name) FROM ref_table WHERE lvl = aLVL)
) LOOP
lQNT := lQNT + process_reference(aLVL, rec.chi_table_name, rec.chi_table_cons, rec.par_table_name, rec.par_table_cons);
END LOOP;
RETURN lQNT;
END;
BEGIN
INSERT INTO ref_table(table_name, rid, lvl)
SELECT 'USR', u.rowid, 0
FROM usr u
WHERE u.usr_status = 'Deleted';
lROWS_QNT := process_level(lLVL);
dbms_output.put_line('Будет удалено на уровне ' || (lLVL + 1) || ' ' || lROWS_QNT || ' строк');
WHILE lROWS_QNT > 0 LOOP
lLVL := lLVL + 1;
lROWS_QNT := process_level(lLVL);
dbms_output.put_line('Будет удалено на уровне ' || (lLVL + 1) || ' ' || lROWS_QNT || ' строк');
END LOOP;
dbms_output.put_line('=========');
dbms_output.put_line('Удаление');
dbms_output.put_line('=========');
-- Удаляем
FOR rec IN (
SELECT DISTINCT table_name, lvl
FROM ref_table
ORDER BY lvl DESC
) LOOP
dbms_output.put_line('Удаляем записи из ' || rec.table_name || ' lvl = ' || rec.lvl);
EXECUTE IMMEDIATE '
delete from ' || rec.table_name || ' t
where t.rowid IN (SELECT rid FROM ref_table WHERE table_name = :TN AND lvl = :lvl)'
USING rec.table_name, rec.lvl;
dbms_output.put_line('Из таблицы ' || rec.table_name || ' удалено строк ' || SQL%ROWCOUNT);
END LOOP;
END;
/
