Проблема удаления дублей в таблицах Oracle

Written on . Posted in Oracle

Удалить такие дубли можно одним 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;
    /
форум oracle