Проблема удаления дублей

Источник: oracle
Виктор Абрамов, IT-дирекция

Автор: Виктор Абрамов, IT-дирекция

Для начала вспомним старую задачу отыскания и удаления дублей в таблице Oracle. Допустим, что в таблице tab есть повторяющиеся записи, причем мы допускаем, что записи, в которых значения столбцов c1,c2,c3 совпадают, и есть дубли. Удалить такие дубли можно одним SQL-оператором:
 

 

SQL>  DELETE FROM tab
WHERE ROWID IN (SELECT ROWID FROM tab
MINUS
SELECT MIN (ROWID) FROM tab GROUP BY c1, c2, c3);

Мы не будем дальше останавливаться на этой задаче, поскольку совсем не этот аспект является темой настоящей статьи. Более того, темой статьи не является даже и отыскание дублей.

Нашей проблемой является удаление повторяющихся записей, на которые есть ссылки из других таблиц. Сами повторяющиеся строки известны заранее.

Очень часто оказывается, что пользователи не только несколько раз ввели  одну и ту же запись в таблицу, но и успели несколько раз на нее сослаться из других таблиц, благо, приложение это позволяло. Например, не посмотрев, что некая компания уже имеется в списке контрагентов, пользователь внес ее в справочник контрагентов еще раз, причем немного ошибся в названии, так что отследить дубль по названию не представлялось возможным. Затем он завел список контактных лиц, заполнил справочник синонимов названия компании, и зарегистрировал парочку заказов. Впоследствии оказалось, что предприятие давно работает с данной компанией, и уже выполнило для нее несколько десятков заказов. И вот теперь нам нужно удалить из справочника компаний лишнюю запись, переключив предварительно все ссылки на нее на ту запись в таблице, которая остается.

Конечно, учет психологии пользователя при разработке интерфейса приложения может уменьшить количество таких дублей, но я не знаю абсолютно надежного способа предотвратить подобное поведение пользователей. Ручное же лечение проблемы занимает слишком много драгоценного времени администратора приложения.

Если подобная проблема возникает только с одной таблицей, можно написать хранимую процедуру именно под нее, но что делать, если таких таблиц несколько?

Эта проблема может возникнуть и не из-за ошибок пользователя, а, например, при слиянии подразделений, когда необходимо всех сотрудников перевести из одного подразделения в другое.

Итак, попытаемся разработать универсальную процедуру для удаления повторяющихся строк в любых таблицах, на которые есть ссылки. Для упрощения задачи наложим следующие ограничения:

  1. Все таблицы приложения хранятся в одной схеме; в ней же мы и создадим нашу процедуру.
  2. Все таблицы имеют ограничения целостности - первичные и внешние ключи.
  3. Используются только простые первичные ключи (состоящие из одного столбца).

Второе ограничение очень важно. Если Ваша база данных разработана недоучками, не знающими, зачем нужны первичные и внешние ключи, то эта статья Вам не поможет.

Сначала извлечем нужную нам информацию из словаря данных на примере таблицы подразделений  DEPARTMENTS.

  1. Используя представление словаря данных user_constraints, найдем все внешние ключи, ссылающиеся на первичный ключ таблицы DEPARTMENTS:
    SELECT a.constraint_name
    FROM user_constraints a, user_constraints b
    WHERE a.r_constraint_name = b.constraint_name
    AND b.table_name = 'DEPARTMENTS'
    AND b.constraint_type = 'P';

  2. Затем, используя представление словаря user_cons_columns, отыщем соответствующие им имена таблиц и столбцов. Таким образом, мы получим список таблиц и столбцов, значения которых нужно будет изменять:
    SELECT c.table_name, c.column_name
    FROM user_constraints a, user_constraints b, user_cons_columns c
    WHERE a.r_constraint_name = b.constraint_name
    AND a.constraint_name = c.constraint_name
    AND b.table_name = 'DEPARTMENTS'
    AND b.constraint_type = 'P';

  3. Используя те же самые представления словаря данных, найдем имя столбца первичного ключа в таблице
    DEPARTMENTS:
    SELECT c.table_name, c.column_name
    FROM user_cons_columns c, user_constraints m
    WHERE c.constraint_name = m.constraint_name
    AND m.table_name = 'DEPARTMENTS'
    AND constraint_type = 'P';

Зная идентификатор подразделения, которое нужно оставить (correct_id) и идентификатор удяляемого подразделение (drop_id), мы можем, используя имена таблиц и столбцов внешнего ключа, полученные из второго запроса, сформировать запрос на изменение значений внешнего ключа. А из третьего запроса - получить SQL -  команду на удаление ставшей ненужной строки из таблицы DEPARTMENTS. Но мы пойдем немного дальше, и создадим хранимую процедуру, которой достаточно будет передать три параметра - имя таблицы, правильное и удаляемое значения первичного ключа, и она все сделает за нас. Чтобы не задумываться над типом столбца первичного ключа, создадим две идентичных процедуры с одинаковыми именами, используя принцип перегрузки, "засунем" их в один хранимый пакет, и назовем его DOUBLES (см. Приложение 1 ).

Теперь, чтобы выполнить распоряжение директора о слиянии бухгалтерии (DEPT_ID = 28) и финансового отдела (DEPT_ID = 101), достаточно выполнить команду:

SQL> SET SERVEROUTPUT ON
SQL> EXECUTE DOUBLES.DEL('DEPARTMENTS',28,101)
DEFAULT_COORDGROUPS - изменено строк: 4
DEFAULT_GROUPS - изменено строк: 2
DEFAULT_ROLES - изменено строк: 4
EMPLOYEES - изменено строк: 17
DEPARTMENTS - удалено строк: 1
PL/SQL procedure successfully completed.
Приложение 1

CREATE OR REPLACE
PACKAGE doubles
IS
   PROCEDURE del (tname IN VARCHAR2, correct_id IN NUMBER, drop_id IN NUMBER);

   PROCEDURE del (tname IN VARCHAR2, correct_id IN VARCHAR2, drop_id IN VARCHAR2);
END doubles;
/

CREATE OR REPLACE
PACKAGE BODY doubles
IS
   CURSOR c_switch_double (p_tname VARCHAR2)
IS
      SELECT c.table_name, c.column_name
FROM user_constraints a, user_constraints b, user_cons_columns c
WHERE a.r_constraint_name = b.constraint_name
AND a.constraint_name = c.constraint_name
AND b.table_name = p_tname
AND b.constraint_type = 'P';
CURSOR c_delete_double (p_tname VARCHAR2)
IS
      SELECT c.table_name, c.column_name
FROM user_cons_columns c, user_constraints m
WHERE c.constraint_name = m.constraint_name
AND m.table_name = p_tname
AND constraint_type = 'P';
PROCEDURE del (tname IN VARCHAR2, correct_id IN NUMBER, drop_id IN NUMBER)
IS
      sqltxt   VARCHAR2 (2000);
BEGIN
      FOR r IN c_switch_double (tname)
LOOP
         BEGIN
            sqltxt :=
'UPDATE '
// r.table_name
// ' SET '
// r.column_name
// '=:1 WHERE '
// r.column_name
// '=:2';
EXECUTE IMMEDIATE sqltxt
USING correct_id, drop_id;
EXCEPTION
            WHEN DUP_VAL_ON_INDEX
THEN
               NULL;
END;
DBMS_OUTPUT.put_line (r.table_name // ' - изменено строк: ' // SQL%ROWCOUNT);
END LOOP;
FOR r IN c_delete_double (tname)
LOOP
         sqltxt := 'DELETE FROM ' // r.table_name // ' WHERE ' // r.column_name // '=:1';
EXECUTE IMMEDIATE sqltxt
USING drop_id;
DBMS_OUTPUT.put_line (r.table_name // ' - удалено строк: ' // SQL%ROWCOUNT);
END LOOP;
COMMIT;
END del;

   PROCEDURE del (tname IN VARCHAR2, correct_id IN VARCHAR2, drop_id IN VARCHAR2)
IS
      sqltxt   VARCHAR2 (2000);
BEGIN
      FOR r IN c_switch_double (tname)
LOOP
         BEGIN
            sqltxt :=
'UPDATE '
// r.table_name
// ' SET '
// r.column_name
// '=:1 WHERE '
// r.column_name
// '=:2';
EXECUTE IMMEDIATE sqltxt
USING correct_id, drop_id;
EXCEPTION
            WHEN DUP_VAL_ON_INDEX
THEN
               NULL;
END;
DBMS_OUTPUT.put_line (r.table_name // ' - изменено строк: ' // SQL%ROWCOUNT);
END LOOP;

      FOR r IN c_delete_double (tname)
LOOP
         sqltxt := 'DELETE FROM ' // r.table_name // ' WHERE ' // r.column_name // '=:1';
EXECUTE IMMEDIATE sqltxt
USING drop_id;

         DBMS_OUTPUT.put_line (r.table_name // ' - удалено строк: ' // SQL%ROWCOUNT);
END LOOP;
COMMIT;
END del;
END doubles;
/


Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=19966