Проблема видалення дублів, Інші СУБД, Бази даних, статті

Автор: Віктор Абрамов, 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;
/

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*