RDTEX Oracle Blog

Хорошей практикой в разработке баз данных Oracle являются комментарии к объектам физического и логического слоя (таблицам, представлениям, полям) – такая БД является самодокументированной. Вместе со стандартом именования объектов это значительно облегчает жизнь команды разработчиков, облегчает введение в должность новых сотрудников.

Однако приходится иногда делать отчет с группировкой по тем или иным объектам так, чтобы он хорошо выглядел в технической документации по модулю. Приводим пример запроса, который извлекает из БД документацию по интерфейсным представлениям бизнес-модуля “Форма 302”. Использованы аналитические функции LAG (предыдущее значение согласно сортировке) и DENSE_RANK (порядковый номер согласно сортировке).

SELECT CASE
          WHEN NVL(LAG(uv1.view_name) 
                   OVER (ORDER BY uv1.view_name, tc1.column_name), '#') <> uv1.view_name
          THEN DENSE_RANK() 
                   OVER (ORDER BY uv1.view_name)
          ELSE NULL
       END AS r_num,
       CASE
          WHEN NVL(LAG(uv1.view_name) 
                   OVER (ORDER BY uv1.view_name, tc1.column_name), '#') <> uv1.view_name
          THEN uv1.view_name
          ELSE NULL
       END AS first_view_name,
       tc1.column_name,
       cc1.comments
  FROM USER_VIEWS uv1
       LEFT JOIN USER_TAB_COLUMNS tc1 
         ON tc1.table_name = uv1.view_name
       LEFT JOIN USER_COL_COMMENTS cc1 
         ON cc1.table_name = uv1.view_name
        AND cc1.column_name = tc1.column_name
 WHERE LOWER(uv1.view_name) LIKE '%302%gui%v%' /* используйте свои критерии */
 ORDER BY uv1.view_name, tc1.column_name

Часть результатов, включая недокументированные представления, пустая – есть где поработать техническим писателям!

На примере одной из витрин отчетности поверх хранилища данных возникла задача по поддержанию актуальности денормализованной таблицы. В рассматриваемом примере есть таблица клиентов и таблица телефонов клиентов. Мы хотим ускорить работу отчетов, дополнив таблицу клиентов актуальными номерами телефонов.

CREATE TABLE test_clients
(
   id         NUMBER,
   name       VARCHAR2(30),
   phone      VARCHAR2(30),
   phone_type VARCHAR2(20)
);

INSERT INTO test_clients
VALUES (1, 'Вася', NULL, NULL);

INSERT INTO test_clients
VALUES (2, 'Петя', NULL, NULL);

CREATE TABLE test_phones
(
   client_id   NUMBER,
   phone       VARCHAR2 (30),
   phone_type  VARCHAR2 (20)
);

INSERT INTO test_phones
VALUES (1, '8(905)133-30-10', 'Мобильный');

INSERT INTO test_phones
VALUES (2, '8(905)247-89-17', 'Мобильный');

Решение в виде одного оператора Merge предложил Максим Медведев:

MERGE INTO test_clients dest
  USING 
    (WITH f AS 
      (SELECT c.rowid AS rid,
              NVL(p.client_id, c.id) AS id,
              p.phone,
              p.phone_type,
              CASE
                WHEN c.id IS NULL THEN 'I'
                WHEN p.client_id IS NULL THEN 'D'
              ELSE
                CASE ROW_NUMBER() 
                     OVER (PARTITION BY p.client_id 
                     ORDER BY p.phone)
                WHEN 1 THEN 'U' 
                ELSE 'I'
                END
              END AS r,
              ROW_NUMBER() 
              OVER (PARTITION BY NVL(c.id, p.client_id)
                    ORDER BY 1) AS rn
         FROM test_phones p
              FULL OUTER JOIN
              test_clients c
              ON c.id = p.client_id AND NVL(c.phone, p.phone) = p.phone
        WHERE (p.client_id IS NOT NULL AND NVL(c.phone_type, '-') != p.phone_type)
           OR (p.client_id IS NULL))
     SELECT f.*, s.name
       FROM f JOIN (SELECT DISTINCT id, name FROM test_clients) s ON s.ID = f.ID) src
    ON (dest.rowid = src.rid AND src.r IN ('U', 'D'))
WHEN MATCHED THEN
   UPDATE SET phone = src.phone,
              phone_type = src.phone_type
   DELETE WHERE src.r = 'D' AND src.rn > 1
WHEN NOT MATCHED THEN
   INSERT VALUES (src.id,
                  src.name,
                  src.phone,
                  src.phone_type);

Теперь можно изучить результат, попробовать добавлять, менять и удалять данные из таблицы с номерами телефонов

INSERT INTO test_phones 
VALUES (2, '8(496)245-55-86', 'Второй домашний');

DELETE FROM test_phones
WHERE client_id = 2 AND ROWNUM >= 1;

Задача: есть таблица с одним полем из целых положительных чисел. Нужно запросом вывести все числа столько раз, чему равно само число.

SELECT q.n
  FROM (    SELECT DISTINCT t.ROWID rn, 
                            t.n, 
                            LEVEL l
              FROM test_numbers t
           CONNECT BY LEVEL >= t.n) q
 ORDER BY q.rn

Интересный вариант с рекурсивным запросом предложил Дмитрий Грачев. Обратите внимание, что такой пример сработает только начиная с 11-ой версии Oracle и результат, вообще говоря, будет отличаться от того, что выдаст первый пример:

WITH f(a, b) 
  AS (SELECT n a, n b
        FROM test_numbers
       WHERE n > 0
       UNION ALL
      SELECT a - 1, b
        FROM f
       WHERE a > 1)
SELECT b
  FROM f
 ORDER BY b

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

Нужно SELECT-запросом во втором столбце пустоты заполнить нижеследующими непустыми значениями вот так:

SELECT col1,
       NVL(col2, 
           LEAD(col2) OVER (PARTITION BY col1 
                            ORDER BY ROWID)) col2
  FROM t1

Это, пожалуй, один из самых простых примеров, когда использование аналитических функций значительно облегчает разработку запроса. В Учебном Центре РДТЕХ читается двухдневный, очень интенсивный курс по аналитическим функциям в применении к хранилищам данных: Oracle Database 12c: Analytic SQL for Data Warehousing.

Сумму в S рублей нужно разбить монетами по 1, 2 или 5 рублей.

WITH t
     AS (    SELECT LEVEL n
               FROM DUAL
         CONNECT BY LEVEL <= :S)
SELECT t1.n || ' по 1 рублю, ' ||
       t2.n || ' по 2 рубля, ' ||
       t3.n || ' по 5 рублей'
  FROM t t1, t t2, t t3
 WHERE 1 * t1.n + 2 * t2.n + 5 * t3.n = :S
 ORDER BY t1.n DESC, t2.n DESC, t3.n DESC

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

WITH t
     AS (    SELECT LEVEL n, LEVEL * LEVEL n2
               FROM DUAL
         CONNECT BY LEVEL <= :S)
SELECT t1.n
  FROM t t1
 WHERE NOT EXISTS
          (SELECT 1
             FROM t t2
            WHERE t1.n >= t2.n2 
              AND t2.n != 1 
              AND MOD(t1.n, t2.n) = 0)

P.S. Способ полностью рабочий, но не самый быстрый. Скорее, классический в плане знаний диалекта SQL от Oracle и обобщенных выражений.