Oracle

Интернет-журнал ФОРС

Полезная ссылка на полезное чтиво об Оракле — Интернет-журнал ФОРС. Тот случай, когда конкуренты делают общее дело!

SQL and PL/SQL

Отчеты по самодокументированной базе данных

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

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

  1. SELECT CASE
  2. WHEN NVL(LAG(uv1.view_name)
  3. OVER (ORDER BY uv1.view_name, tc1.column_name), '#') <> uv1.view_name
  4. THEN DENSE_RANK()
  5. OVER (ORDER BY uv1.view_name)
  6. ELSE NULL
  7. END AS r_num,
  8. CASE
  9. WHEN NVL(LAG(uv1.view_name)
  10. OVER (ORDER BY uv1.view_name, tc1.column_name), '#') <> uv1.view_name
  11. THEN uv1.view_name
  12. ELSE NULL
  13. END AS first_view_name,
  14. tc1.column_name,
  15. cc1.comments
  16. FROM USER_VIEWS uv1
  17. LEFT JOIN USER_TAB_COLUMNS tc1
  18. ON tc1.table_name = uv1.view_name
  19. LEFT JOIN USER_COL_COMMENTS cc1
  20. ON cc1.table_name = uv1.view_name
  21. AND cc1.column_name = tc1.column_name
  22. WHERE LOWER(uv1.view_name) LIKE '%302%gui%v%' /* используйте свои критерии */
  23. ORDER BY uv1.view_name, tc1.column_name

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

1 CBR_302_GUI_DICT_BS_V BUSINESS_SIZE_CD
BUSINESS_SIZE_NAME
SORT_ID
2 CBR_302_GUI_DICT_OKATO_V OKATO_CD
OKATO_CODE
SORT_ID
3 CBR_302_GUI_DICT_OKVED_V OKVED_CD
OKVED_CODE
SORT_ID
4 CBR_302_GUI_OKATO_V AS_OF_DATE Отчетная дата
FDDR_ID ID ФДП-отчета
OKATO Код Окато
SECT_ID Код раздела
Новости

Мужской квест… год спустя

Квест Диверсант
Спустя год наши коллеги-женщины опять подарили нам участие в квесте. За год из команды ушел Дима Грачев, а пришли новые сотрудники Виталий и Евгений. И если мы с Сергеем знали, с чем столкнемся, то для новичков квест был в диковинку. На самом деле час (58 минут 40 секунд) в двух комнатах с участием актера и холостой стрельбы из боевых пистолетов вышли очень напряженными, и если бы не некоторые подсказки, нас бы точно застрелили нам не удалось бы выбраться из запертых комнат и не пришлось бы сломя голову бежать куда глаза глядят. Было офигенно и с атмосферой Великой Отечественной Войны.

SQL and PL/SQL

Super Merge

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

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

  1. MERGE INTO test_clients dest
  2. USING
  3. (WITH f AS
  4. (SELECT c.ROWID AS rid,
  5. NVL(p.client_id, c.id) AS id,
  6. p.phone,
  7. p.phone_type,
  8. CASE
  9. WHEN c.id IS NULL THEN 'I'
  10. WHEN p.client_id IS NULL THEN 'D'
  11. ELSE
  12. CASE ROW_NUMBER()
  13. OVER (PARTITION BY p.client_id
  14. ORDER BY p.phone)
  15. WHEN 1 THEN 'U'
  16. ELSE 'I'
  17. END
  18. END AS r,
  19. ROW_NUMBER()
  20. OVER (PARTITION BY NVL(c.id, p.client_id)
  21. ORDER BY 1) AS rn
  22. FROM test_phones p
  23. FULL OUTER JOIN
  24. test_clients c
  25. ON c.id = p.client_id AND NVL(c.phone, p.phone) = p.phone
  26. WHERE (p.client_id IS NOT NULL AND NVL(c.phone_type, '-') != p.phone_type)
  27. OR (p.client_id IS NULL))
  28. SELECT f.*, s.name
  29. FROM f JOIN (SELECT DISTINCT id, name FROM test_clients) s ON s.ID = f.ID) src
  30. ON (dest.ROWID = src.rid AND src.r IN ('U', 'D'))
  31. WHEN MATCHED THEN
  32. UPDATE SET phone = src.phone,
  33. phone_type = src.phone_type
  34. DELETE WHERE src.r = 'D' AND src.rn > 1
  35. WHEN NOT MATCHED THEN
  36. INSERT VALUES (src.id,
  37. src.name,
  38. src.phone,
  39. src.phone_type);

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

  1. INSERT INTO test_phones
  2. VALUES (2, '8(496)245-55-86', 'Второй домашний');
  3.  
  4. DELETE FROM test_phones
  5. WHERE client_id = 2 AND ROWNUM <= 1;
Новости

Разработчики РДТЕХ успешно решают не только SQL-задачки!

Квест Чокнутый профессор

Команда разработчиков РДТЕХ (Максим Медведев, Тагир Билалов, Дмитрий Грачев и Сергей Шеболденков), получив в подарок от женской половины коллектива ко дню защитника отечества возможность выбраться из запертой комнаты в ходе квеста «Чокнутый профессор», успешно преодолела данный квест и, ничего не сломав и воспользовавшись парой подсказок от ведущей, выбралась из полной загадок комнаты за 58 минут!

Пруфлинк

SQL and PL/SQL

Повторы

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

N
1
3
2

Должно получиться так:

N
1
3
3
3
2
2
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
SQL and PL/SQL

Наращивание пустых строк непустыми записями старше по дате

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

Col1 Col2
1
1 10
1
1 15
2
2 20

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

Col1 Col2
1 10
1 10
1 15
1 15
2 20
2 20
SELECT col1,
       NVL(col2, 
           LEAD(col2) OVER (PARTITION BY col1 
                            ORDER BY ROWID)) col2
  FROM t1

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

SQL and PL/SQL

Разбиение суммы на монетки

Сумму в 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 and PL/SQL

Простые числа

Знаменитая задачка про поиск простых чисел средствами чистого 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 и обобщенных выражений.

Analyses and Dashboards

Форматирование больших чисел в отчетах BI Answers

Как известно, при выводе больших чисел (как пример, цифр отчета о прибылях и убытках) OBIEE по непредсказуемому алгоритму отбрасывает значащие знаки после 15-ой цифры, иногда это может проявиться в десятичных знаках, а при слишком больших числах и в единицах, десятках, тысячах. Чтобы преодолеть это ограничение, в выражении для числовых столбцов реализовано преобразование числа в строку при помощи функции TO_CHAR():

SELECT
    TO_CHAR(12345678901234567890.09,
            '999G999G999G999G999G999G999G990D99',
            'nls_language=english')
  FROM DUAL;

Вывод при этом, конечно, текстовый, но со всеми значащими цифрами:

    12,345,678,901,234,567,890.09

В одном из запросов на изменение понадобилось в этом выводе подставлять пробелы вместо запятых в разделителях тысяч. Это решается таким способом:

SELECT
    TO_CHAR(12345678901234567890.09,
            '999G999G999G999G999G999G999G990D99',
            'nls_numeric_characters = ''. ''')
  FROM DUAL;

Результат радует:

    12 345 678 901 234 567 890.09

Остается добавить в отчете Answers выравнивание по правой границе и формат отображения текстовый с неразрывными пробелами, и отчет прекрасно выглядит! Конечно, при выгрузке в Excel форматирование чисел остается текстовым, но Заказчик согласен на такие условия.

Страница 1 из 212