🎵 «В темно-синем лесу
Где трепещут осины
Где с дубов-колдунов
Облетает листва
Hа поляне траву
Зайцы в полночь косили
И при этом напевали
Странные слова» — Леонид Дербенёв.

🎯 Решение задачи про вывод SYS_REFCURSOR

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

Вот один из простых и красивых способов:
через DBMS_XMLGEN, который превращает курсор в XML и сохраняет даже NULL-поля.

DECLARE
  refcur  SYS_REFCURSOR;
  xml  CLOB;
  ctx   DBMS_XMLGEN.ctxhandle;
BEGIN
  TEST_REFCUR.get_refcur (refcur => refcur);

  ctx := DBMS_XMLGEN.newcontext(refcur);

  DBMS_XMLGEN.setnullhandling(ctx, DBMS_XMLGEN.empty_tag);

  xml := DBMS_XMLGEN.getxml(ctx);

  DBMS_XMLGEN.closecontext(ctx);

  DBMS_OUTPUT.put_line(SUBSTR(xml, 1, 10000));
END;

📄 На выходе получаем XML со всеми колонками — даже пустыми.
Удобно, когда нужно просто убедиться, что метод возвращает нужные данные.

А один из подписчиков в LinkedIn предложил альтернативу — через DBMS_SQL:

DECLARE
    refcur               SYS_REFCURSOR;
    cur                  INTEGER;
    columnCount          INTEGER;
    columnDiscriptions   DBMS_SQL.DESC_TAB;
    columnValue          VARCHAR2 (4000);
BEGIN
    TEST_REFCUR.get_refcur (refcur => refcur);

    cur := DBMS_SQL.to_cursor_number (refcur);

    DBMS_SQL.describe_columns (cur, columnCount, columnDiscriptions);

    FOR index# IN 1 .. columnCount
    LOOP
        DBMS_SQL.define_column (cur,
                                index#,
                                columnValue,
                                4000);
    END LOOP;

    WHILE DBMS_SQL.fetch_rows (cur) > 0
    LOOP
        FOR index# IN 1 .. columnCount
        LOOP
            DBMS_SQL.COLUMN_VALUE (cur, index#, columnValue);
            DBMS_OUTPUT.put (
                   columnDiscriptions (index#).col_name
                || ' = "'
                || columnValue
                || '"  ');
        END LOOP;

        DBMS_OUTPUT.new_line;
    END LOOP;

    DBMS_SQL.close_cursor (cur);
END;

📊 Оба варианта рабочие:
DBMS_XMLGEN — проще и даёт структурированный XML,
DBMS_SQL — чуть нижеуровневый, но гибче.

⚠️UPDATE 29.10.2025 (Спасибо за инфу читателю "Asmodeus")
Дело в том, что SQL Developer, TOAD, SQL*Plus умеют "перехватывать" результат курсора и показывать его во вкладке Data Grid или подобной.
Я использую PL/SQL developer и он, к сожалению, это делать не умеет. Ну как не умеет - умеет, но в режиме Test. Пользоваться таким образом не удобно.
Итак, для "Жабы" все гораздо проще.

DECLARE
  refcur SYS_REFCURSOR;
BEGIN
  TEST_REFCUR.get_refcur(refcur => refcur);
  DBMS_SQL.RETURN_RESULT(refcur);
END;
/

Немного за PL/SQL developer стало стыдно!

UPDATE 30.10.2025
Я вспомнил еще один способ решения этой задачи.

DECLARE
  refcur SYS_REFCURSOR;
  l_xml XMLTYPE;
BEGIN
  TEST_REFCUR.get_refcur (refcur  => refcur);

  l_xml := XMLTYPE.createxml (refcur);

  CLOSE refcur;

  DBMS_OUTPUT.put_line (l_xml.GetCLOBVal());
END;

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

👍 Палец вверх — так держать
👎 Палец вниз — "машина - задний ход"

👇 Хотите молчать - молчите! Хотите сказать - скажите!
#️⃣ #Cases #SQL #Oracle #PLSQL

🎵 «Но песню иную
О дальней земле
Возил мой приятель
С собою в седле.
Он пел, озирая
Родные края:
"Гренада, Гренада,
Гренада моя!"» — Михаил Светлов

📊 Не менее полезная задача из практики.
В прошлом посте мы разбирали, как неявно вывести поля из SYS_REFCURSOR — много полезного подчерпнул.
Теперь предвкушаю такое же удовольствие по похожей задаче.

Довольно часто нужно проверять коллекцию из набора полей.
Указывать их явно в цикле утомительно, особенно если их десятки.
Мне интересно: как вы решаете такую проблему?

Например, есть функция и процедура, которые возвращает коллекцию:

CREATE OR REPLACE TYPE COLLECTION_REC FORCE IS OBJECT
(
    col1 INTEGER,
    col2 NUMBER,
    col3 VARCHAR2 (10),
    col4 NUMBER,
    col5 VARCHAR2 (10)
)
/
CREATE OR REPLACE TYPE COLLECTION_TBL FORCE IS TABLE OF COLLECTION_REC
/
CREATE OR REPLACE PACKAGE TEST_COLLECTION
AS
    PROCEDURE get_collection (collection OUT COLLECTION_TBL);
    
    FUNCTION get_collection RETURN COLLECTION_TBL;
END TEST_COLLECTION;
/ 
CREATE OR REPLACE PACKAGE BODY TEST_COLLECTION
AS
    PROCEDURE get_collection (collection OUT COLLECTION_TBL)
    IS
    BEGIN
        collection :=
            COLLECTION_TBL (COLLECTION_REC (col1   => 1,
                                            col2   => 2.3,
                                            col3   => '4',
                                            col4   => NULL,
                                            col5   => NULL));
    END get_collection;

    FUNCTION get_collection RETURN COLLECTION_TBL
    IS
    BEGIN
        RETURN
            COLLECTION_TBL (COLLECTION_REC (col1   => 1,
                                            col2   => 2.3,
                                            col3   => '4',
                                            col4   => NULL,
                                            col5   => NULL));
    END get_collection;
END TEST_COLLECTION;
/

🧩 Вопрос подписчикам:
Как вывести содержимое коллекций, не указывая имена колонок явно?

Я знаю хороший способ для функции, но для процедуры — пока нет.

⚠️ Хотите проверить скрипты, но нет базы под рукой — онлайн-песочница
💎 Поддержка канала⁉️

👍 Палец вверх — вызов принят
👎 Палец вниз — скукотище

👇 Делитесь своими подходами в комментариях!
#️⃣ #Cases #SQL #Oracle #PLSQL

🎵«Он песенку эту
Твердил наизусть...
Откуда у хлопца
Испанская грусть?
Ответь, Александровск,
И, Харьков, ответь:
Давно ль по-испански
Вы начали петь?» - Михаил Светлов

💡 Разбор задачи — как вывести содержимое коллекции, не указывая поля явно.

🧩 Задача:
Есть типы COLLECTION_REC и COLLECTION_TBL, а также функция и процедура, возвращающие коллекцию.
Нужно красиво вывести содержимое, не перечисляя вручную col1, col2, col3, …

Решение для функции
Мой способ — использовать DBMS_XMLGEN.
Он позволяет превратить результат SQL-запроса (в том числе из коллекции) в XML
и посмотреть структуру без указания имён колонок.
Единственно, этот метод явно пустые значения не выдает.

DECLARE
    l_xml   CLOB;
BEGIN
    l_xml :=
        DBMS_XMLGEN.getXML (
            sqlQuery   =>
                'SELECT * FROM TABLE(CAST(TEST_COLLECTION.get_collection () AS COLLECTION_TBL))');
    DBMS_OUTPUT.put_line (l_xml);
END;

Результат:

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <COL1>1</COL1>
  <COL2>2.3</COL2>
  <COL3>4</COL3>
 </ROW>
</ROWSET>

Все имена и значения подхватываются автоматически, даже если структура объекта изменится.

⚙️ Провернуть такой фокус с процедурой не получится, так как DBMS_XMLGEN не поддерживает передачу параметров в запрос.
Приходится "костылить" - , проще всего, сделать функцию-обёртку,
которая просто вызывает процедуру и возвращает ту же коллекцию, и
тогда можно пользоваться универсальным способом через DBMS_XMLGEN, без циклов и лишнего кода.

Итого:
Для функции → DBMS_XMLGEN.getxml('SELECT * FROM TABLE(CAST(tmp_function () AS COLLECTION_TBL))')
Для процедуры → завернуть в функцию.
Никаких явных имён полей, всё динамически.

UPDATE 2025.10.31
Подписчик Антон предложил вариант через ANYDATA (Спасибо огромное).
Вариант проще моего, к тому же повторяет названия коллекции COLLECTION_TBL и название объекта COLLECTION_REC .

DECLARE
    l_xml        XMLTYPE;
    collection   COLLECTION_TBL;
BEGIN
    TEST_COLLECTION.get_collection (collection => collection);
    l_xml := XMLTYPE (ANYDATA.convertCollection (COALESCE (collection, COLLECTION_TBL ())));

    DBMS_OUTPUT.put_line (l_xml.getclobval(1,4));
END;

Вот так: век живи - век учись!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница
💎 Поддержка канала⁉️

👍 Палец вверх — отличный контент.
👎 Палец вниз — не то, что надо.

👇 А Вы как выводите содержимое коллекций в Oracle?
#️⃣ #Cases #SQL #Oracle #PLSQL

🎵 «Не кочегары мы, не плотники,
Но сожалений горьких нет, как нет!
А мы монтажники-высотники, да,
И с высоты вам шлём привет!» — Владимир Котов

🚀 Серия: Оптимизация SQL-запросов. HASH-соединения таблиц

В прошлый раз мы разобрали, как работает NESTED LOOP JOIN — пошаговое соединение,
когда для каждой строки одной таблицы ищутся совпадения в другой.
Но что, если у нас миллиарды строк с обеих сторон?

Возьмём две таблицы: A и B и в каждой — по миллиарду записей.
И самый простой запрос:

SELECT a.*, b.*
FROM a
JOIN b ON (a.id = b.id);

В таком случае оптимизатор выбирает другие механизмы — HASH JOIN или MERGE JOIN.

📘 HASH JOIN — тяжёлая артиллерия оптимизатора

Перед тем как соединять таблицы, Oracle считывает данные и строит hash-таблицы,
которые состоят из результата hash-функции и данных таблицы.

Как именно оптимизатор строит hash-таблицу?
Он делает это по условию соединения — в данном случае по a.id и b.id.

Точную функцию, которую использует Oracle, мы не знаем,
но, вероятно, она похожа на:

STANDARD_HASH(a.id)

Если соединение идёт по нескольким полям:

a.id = b.id AND a.something = b.something

то hash-функция будет примерно такой:

STANDARD_HASH(a.id || '/' || a.something)

где '/' — это разделитель, помогающий избежать коллизий и проблем с NULL.

Главная идея метода — минимум обращений к диску:
данные считываются один раз (или частями, если они слишком велики).

Пример плана выполнения:

|   0 | SELECT STATEMENT     |      
| * 1 |   HASH JOIN          |      
|   2 |    TABLE ACCESS FULL | A    
|   3 |    TABLE ACCESS FULL | B    

Особенности HASH JOIN
1️⃣ Соединение только по равенству.
Если условие будет вроде

a.id > b.id - 1 AND a.id < b.id + 1

то построить hash-таблицы невозможно — хэш не работает с неравенствами.
2️⃣ Порядок таблиц имеет значение.
Первая таблица (build input) должна быть меньше по размеру.
Если A — 1 млрд записей, а B — 100 млн, правильный порядок: B => A, а не наоборот.
3️⃣ Использование TEMP.
На малых объёмах hash-таблица хранится в памяти,
но при больших — оптимизатор выгружает промежуточные данные в табличное пространство TEMP.
Если там не хватает места — возможны сильные тормоза.
4️⃣ Коллизии.
Иногда hash-функция вычисляет одинаковые значения для разных строк.
Oracle умеет такие конфликты корректно обрабатывать.
5️⃣ Для глубокого понимания.
Подробный разбор алгоритма есть в книге Джонатана Льюиса
«Основы стоимостной оптимизации» — чтение полезное, но не из лёгких. Я новичкам читать не рекомендую - отобьёт всякое желание.

В итоге оптимизатор сравнивает:
что выгоднее — прочитать всё и построить hash-таблицу,
или пройтись циклом (Nested Loop).

Из практики — в сложных запросах оптимизатор может ошибаться и выбрать не самый удачный вариант.
Поэтому можно явно подсказать порядок и тип соединения:

SELECT /*+ LEADING(b a) USE_HASH(b a) */
  a.*, b.*
FROM a
JOIN b ON (a.id = b.id);

или так:

SELECT /*+ ORDERED USE_NL(a b) */
  a.*, b.*
FROM a
JOIN b ON (a.id = b.id);

💡 Можно строить гипотезы, какой метод лучше,
но любые гипотезы требуют проверки и замеров.

Чтобы не "перекормить" новичков информацией,
метод MERGE JOIN разберём в следующем посте.

⚠️ Хотите проверить скрипты, но нет базы под рукой — используйте онлайн-песочницу.
💎 Поддержка канала⁉️

👍 Палец вверх — машина: "Полный ход!"
👎 Палец вниз — Стоп машина!

👇 Если заметили неточность — напишите!
А если есть, что добавить — тем более.
#️⃣ #SQLOptimization #SQL #Oracle

🎵 «Там, где клен шумит над речной волной,
Говорили мы о любви с тобой.
Пожелтел тот клен, в поле бродит мгла,
А любовь как сон стороной прошла.» - Леонтий Шишко

📌 Рубрика: решение задач процедурными методами
Тема: Проверка, является ли бинарное дерево деревом поиска (BST — Binary Search Tree)

Что делает скрипт?
Он создаёт бинарное дерево, обходит его разными способами (в глубину и в ширину)
и проверяет, выполняется ли у него свойство BST —
у каждого узла значение слева меньше, а справа больше.

Этапы работы скрипта:

1️⃣ Инициализация дерева.
Процедура init_btree создаёт случайное бинарное дерево из N чисел,
вставляя каждое значение по правилам дерева поиска:
меньшее идёт влево,
большее — вправо.
Хранение ведётся в объектной таблице nodes,
а каждый узел — это объект типа node с полями "value", left, right.

2️⃣ Вывод дерева.
print_btree рекурсивно обходит дерево и показывает:
уровень узла,
его значение,
есть ли левые/правые потомки.

3️⃣ Обход в ширину (BFS).
get_BFS формирует таблицу уровней, а print_BFS выводит дерево по слоям,
как будто вы смотрите на него горизонтально.

4️⃣ Проверка на BST.
Функция is_bst проверяет каждый узел рекурсивно:
если левый ребёнок ≥ родителя — дерево нарушает правила;
если правый ≤ родителя — тоже нарушение.
Если всё соблюдено — возвращается TRUE,
и вы увидите сообщение:
BFS IS BST!
иначе — BFS IS NOT BST!

Код во вложении.

💡 Как работает:
Каждый узел проверяет своих потомков и сообщает:
«Я — родитель, и порядок у меня соблюдён!»
Если хотя бы один узел нарушает порядок — дерево теряет статус BST.

📊 На выходе вы увидите дерево, его уровни и результат проверки.

⚠️ Хотите попробовать, но нет базы под рукой — воспользуйтесь онлайн-песочницей.
💎 Поддержка канала⁉️

👍 Палец вверх — если считаете, что PL/SQL способен на алгоритмы
👎 Палец вниз — если деревья не дают вам спать

💬 Молчание - золото? Возможно, но только не на этом канала! 👇.
#️⃣ #SQL #Oracle #PLSQL

🎵 «Ах, как хочется вернуться,
Ах, как хочется ворваться в городок
На нашу улицу в три дома,
Где все просто и знакомо, на денек.» — Кирилл Крастошевский

📌 Затравка в следующему посту.
Как Вы считаете, есть ли здесь неточность в запросе?
Выдаст ли ORACLE exception NO_DATA_FOUND?

DECLARE
  something   NUMBER := 122;
  id          NUMBER;
BEGIN
  SELECT a.id
    INTO id
    FROM a
   WHERE a.something = something;

  DBMS_OUTPUT.put_line ('id = ' || TO_CHAR (id));   
END;

если

CREATE TABLE a
(
  id           NUMBER PRIMARY KEY,
  something    NUMBER
);

CREATE UNIQUE INDEX a_01
  ON a (something);

INSERT INTO a (id, something)
VALUES (1,123);

COMMIT;

⚠️ Хотите попробовать, но нет базы под рукой — воспользуйтесь онлайн-песочницей.
💎 Поддержка канала⁉️

👌 - Если не вижу проблем.
🙏 - Если считаю, что ошибка есть.

💬 Буквы => Слова => Предложения => Комментарии 👇.
#️⃣ #Cases #SQL #Oracle #PLSQL

🎵«Где без спроса входят в гости,
Где нет зависти и злости — милый дом,
Где рождение справляют
И навеки провожают всем двором.» — Кирилл Крастошевский

📌 Сегодня — о возвращении к основам.
Вот пример, где всё кажется очевидным:

DECLARE
  something   NUMBER := 122;
  id          NUMBER;
BEGIN
  SELECT a.id
    INTO id
    FROM a
   WHERE a.something = something;

  DBMS_OUTPUT.put_line('id = ' || TO_CHAR(id));
END;

🧩 Выглядит невинно, но проблема — в именах.
PL/SQL видит в запросе два something:
одно — из таблицы, другое — из блока.

Без уточнения, что something — это переменная, легко попасть в ловушку:
код выглядит корректно, но сравнение может вернуть не тот результат, или не вернуть ничего вовсе.
Дело в том, что условие a.something = something выглядит в глазах ORACLE, как 1 = 1, если something IS NOT NULL.

⚙️ Как избежать таких ситуаций?
Есть два подхода:
Использовать “венгерский код”, когда имя отражает тип или назначение переменной:
v_ — обычная переменная,
p_ — параметр,
l_ — локальная,
c_ — константа.

Большинство организаций применяют этот подход — он рабочий.
Но не всем он нравится.

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

Я подхожу к неймингу по другому - имена даю из расчета сути переменной и не более.
Если меня зовут Кирилл, зачем мне писать l_kirill или p_kirill?
Если вы работаете с заказом (order), пользователем (user) или другой сущностью (entity) — зачем лишние префиксы и суффиксы?

Хотя, как говорится, это вопрос “религиозный”.
Должны же мы разработчики хоть во что-то верить.

💡 Совет:
Какое бы правило именования вы ни выбрали —
всегда явно указывайте полное имя переменной в DML-запросах.

Полное имя состоит из двух частей:
имя блока или метода и имя переменной через точку.

Даже неименованному блоку можно задать имя!

DECLARE
BEGIN
 <<get_a_id>>
  DECLARE
    something   NUMBER := 123;
    id          NUMBER;
  BEGIN
    SELECT a.id
      INTO id
      FROM a
     WHERE a.something = get_a_id.something;

    DBMS_OUTPUT.put_line ('id = ' || TO_CHAR (id));
  END;
END;
/

или при создании метода:

CREATE OR REPLACE PROCEDURE get_a_id (something IN NUMBER)
IS
  id  NUMBER;
BEGIN
  SELECT a.id
    INTO id
    FROM a
   WHERE a.something = get_a_id.something;

  DBMS_OUTPUT.put_line('id = ' || TO_CHAR(id));
END get_a_id;
/

🎯 Результат:
📉 меньше шансов на логические ошибки,
📈 выше читаемость,
и Oracle не преподносит сюрпризов.

⚠️ Хотите попробовать, но нет базы под рукой — воспользуйтесь онлайн-песочницей.
💎 Поддержка канала⁉️

👍 Палец вверх — нравится
👎 Палец вниз — не нравится

💬 Каждый комментарий на вес золото. Будьте щедры 👇.
#️⃣ #Cases #SQL #Oracle #PLSQL

🎵 «Капелькой дождя упал
На твои ладони этот день.
Ты меня повсюду ждал
И бродил за мною словно тень.
Прячешь ты глаза словно
Робостью навек, скован.
Сделай первый шаг,
Будь же посмелей со мной» — Леонид Агутин

🚀 Серия: Оптимизация SQL-запросов. MERGE-соединения таблиц

В прошлый раз мы говорили о HASH JOIN — тяжёлой артиллерии оптимизатора, которая идеально подходит для гигантских таблиц и равных соединений.

Сегодня разберём MERGE JOIN — метод, который сочетает в себе простоту и эффективность, но требует одного важного условия: оба набора данных должны быть отсортированы по ключу соединения.
Вернее, если набор данных отсортирован, такой вид соединения весьма эффективен. Если же нет — Oracle потратит значительные ресурсы на сортировку. ⚡

Итак, по порядку:

Если у нас есть таблицы A и B, и обе отсортированы по id,
Oracle считывает данные из двух наборов и идёт по ним “в ногу”, сравнивая строки и сдвигаясь вперёд, пока не найдёт совпадение.
Сначала он идёт по первому набору, пока не найдёт расхождение со вторым. Потом перескакивает и идёт по второму набору до расхождения, и так до конца одного из списков.

Понятней будет на примере:

SELECT a.*, b.*
FROM a
JOIN b ON (a.id = b.id);

Есть набор данных, по которым идёт соединение:

A = 1, 2, 3, 5, 6, 8, 9
B = 3, 4, 5, 6

Два списка можно представить так:

A: 1   2   3       5   6       8   9
B:         3   4   5   6
       ↑       ↑
     ptrA    ptrB

Комментарий:
ptrA и ptrB — указатели на элементы набора A и B.
Oracle идёт по общему отсортированному потоку, сравнивая стрелки:
если A < B → двигаем A ⬇️
если A > B → двигаем B ⬇️
если A = B → найдено совпадение ✅ → двигаем обе стрелки ⬇️⬇️

Результат: пары (3,3), (5,5), (6,6)

Вот как MERGE JOIN выглядит в плане выполнения:

|   0 | SELECT STATEMENT     |
| * 1 |   MERGE JOIN         |
|   2 |    SORT JOIN         | A
|   3 |    SORT JOIN         | B

📊 Получается потоковое соединение без случайных обращений к данным.

🔥 Когда MERGE JOIN — лучший выбор
1️⃣ Оба набора данных уже отсортированы (например, при индексе по ключу соединения).
2️⃣ Условие соединения — равенство или диапазон (в отличие от HASH JOIN).
3️⃣ Таблицы слишком большие для полной hash-таблицы в памяти.
4️⃣ Нужно соединять результат, который уже упорядочен — например, после ORDER BY или GROUP BY.

⚠️ Особенности и подводные камни
1️⃣ Сортировка — не бесплатная.
Если данные не отсортированы, Oracle создаст временные сортировки (SORT JOIN).
Это может привести к активному использованию TEMP и росту времени выполнения.
2️⃣ MERGE JOIN может быть быстрее HASH JOIN,
если сортировки уже есть (например, индексы по id) или если соединяются диапазоны.
3️⃣ Порядок таблиц важен.
Как и в других соединениях, можно управлять порядком через хинт LEADING.
Я не нашел, прямого упоминания, что порядок имеет значение. Но и обратного утверждения в доках нет. Возможно, что разница есть, но она не значительная.

В сложных запросах, особенно с временными данными, оптимизатору сложно разобраться.
Он может выбрать далеко не самый оптимальный вариант.
Его можно понять — "Все мы люди и все ошибаемся"
Поэтому можно явно подсказать порядок и тип соединения:

SELECT /*+ LEADING(b a) USE_MERGE(b a) */
  a.*, b.*
FROM a
JOIN b ON (a.id = b.id);

или так:

SELECT /*+ ORDERED USE_HASH(a b) */
  a.*, b.*
FROM a
JOIN b ON (a.id = b.id);

💡 Если вы считаете, что оптимизатор ошибается — смело давайте ему советы. Только ваши гипотезы требуют проверки и доказательства. Нельзя вмешиваться без чёткой уверенности в своих действиях.

⚠️ Хотите проверить скрипты, но нет базы под рукой — используйте онлайн-песочницу.
💎 Поддержка канала⁉️

👍 Палец вверх — есть контакт
👎 Палец вниз — нет контакта

👇 Каждый комментарий от профи — драгоценность для начинающих.
Каждый вопрос от новичка — проверка знаний для профи.
И то, и другое приветствуется.
#️⃣ #SQLOptimization #SQL #Oracle

🔆 «Мало кто знает, как размножаются колобки.
Они забираются на сусеки и там метутся и скребутся!» — анекдот

📌 Нейминг для Терминатора T-800 или умение писать читаемый код

Многие, как и я в прошлом, недооценивают значение нейминга в коде.
Ведь, как нас учат в школе и в ВУЗах — алгоритмы, оптимизация, следите за памятью… и, по сути, всё.
Ребята, этого драматически мало.

🧩 Нейминг и архитектура кода — это важные, критически важные вещи.

Если бы код читал Терминатор T-800 🤖, то для него что переменная
user_id NUMBER;, что a765434 NUMBER; — разницы нет.
Для человека же контекст и имена, связанные с ним, имеют огромное значение.

📜 Возьмём отрывок из простой русской сказки — «Колобок».
Вот как она звучит в оригинале:

«Жил-был старик со старухою.
Просит старик: „Испеки, старуха, колобок“.
— „Из чего печь-то? Муки нету“.
— „Э-эх, старуха! По коробу поскреби, по сусекам помети; авось муки и наберётся“.»

Теперь заменим:
👴 Дед → u1 👵 Старуха → u2 🍞 Колобок → u3
⚙️ Действия: Жил → a1, Был → a2, Просит → a3, Испеки → a4, Поскреби → a5, Помети → a6, Наберётся → a7

a1-a2 u1 со u2.
a3 u1: «a4, u2, u3».
— «Из чего a4-то? Муки нету».
— «Э-эх, u2! По коробу a5, по сусекам a6; авось муки и a7».

Ну как, понятно? 🙂
Это я ещё дополнения не заменил.
А теперь представьте, что таких участков — сотни и тысячи строк. Попробуйте разобраться!
То, что Терминатору не проблема, для человека — суровое испытание.
Не все ведь заканчивали школу Абвера по дешифровке.

Теперь попробуем изменить нейминг сказки (кода):

👴 Дед → old_man_user 👵 Старуха → old_woman_user 🍞 Колобок → ban_user
⚙️ Жил → live, Был → be, Просит → ask, Испеки → bake, Поскреби → scratch, Помети → sweep, Наберётся → get

live-be old_man_user со old_woman_user.
ask old_man_user: «bake, old_woman_user, ban_user». — «Из чего bake-то? Муки нету».
— «Э-эх, old_woman_user! По коробу scratch, по сусекам sweep; авось муки и get».

📘 По-моему, такой текст уже гораздо проще читать.
Так можно и суть понять, найти ошибку или даже дописать новый эпизод —
«Колобок и Бармалей», «Колобок и Чапаев» и т.д.

🎯 Мораль:
Язык программирования — это, прежде всего, язык.
Если вы что-то пишете, это должно выглядеть как повествование:
где есть главные и второстепенные герои, завязка, развязка и окончание.

👨‍💻 Программисты — это авторы.
Ваш код должен быть понятен всем, не только Вам.

Если Вы тратите на разработку 2–3 часа,
то не менее часа стоит потратить на нейминг —
продумать имена переменных и методов так, чтобы они отражали суть и были ясны без комментариев.

Да, поначалу поиск нужных слов вызывает сложности.
🎓 Умение писать читаемый код — это навык, который нарабатывается годами.

💎 Поддержка канала⁉️

👍 Палец вверх — годится
👎 Палец вниз — не годится

👇 Ваш комментарий нужен не только Вам.
#️⃣ #Cases #SQL #Oracle #PostgreSQL #PLSQL

🎵 «Как следует смажь оба "кольта",
"Винчестер" как следует смажь
И трогай в дорогу, поскольку
Взбрела тебе в голову блажь.» — Юлий Ким

📌 Разбор задачи: Разбиение строки на слова
Цель — реализовать задачу разделения строки на последовательность слов из словаря,
используя только процедурные методы PL/SQL, без регулярных выражений и внешних функций.

💡 Идея алгоритма:
Это приём динамического программирования (DP):
мы проверяем, можно ли разбить строку так, чтобы каждая часть входила в словарь.

Алгоритм проходит по строке, проверяет все возможные подстроки и отмечает,
где удаётся найти известное слово.

Исходные данные:
Строка: catsandddog
Словарь: cat, cats, and, sand, dog

Задача — понять, можно ли эту строку собрать из слов словаря.

🔧 Ключевые шаги:

1️⃣ Инициализируем массив matrix, где matrix(i) = 1,
если подстрока до позиции i может быть составлена из слов словаря.

2️⃣ Проходим по всем позициям строки,
для каждой проверяем возможные окончания слов —
если найдено совпадение с элементом из словаря, помечаем позицию как достижимую.

3️⃣ В массив prevs записываем позиции,
с которых начинается каждое найденное слово —
это даёт возможность восстановить путь разбиения.

DECLARE
 str            VARCHAR2 (100) := 'catsandddog';

 TYPE word_tab IS TABLE OF VARCHAR2 (100);

 words          word_tab
        := word_tab ('cat',
         'cats',
         'and',
         'sand',
         'dog');

 TYPE matrix_tab IS TABLE OF INTEGER;

 matrix         matrix_tab := matrix_tab ();
 prevs          matrix_tab := matrix_tab ();

 PROCEDURE get_matrix (words IN OUT NOCOPY word_tab, matrix IN OUT NOCOPY matrix_tab, prevs OUT matrix_tab) IS
  resultPrevs   matrix_tab := matrix_tab ();
 BEGIN
  matrix.EXTEND (LENGTH (str) + 1);

  matrix (1)   := 1;

  FOR i IN 2 .. matrix.COUNT LOOP
   matrix (i)   := 0;

   FOR j IN 2 .. i LOOP
    DECLARE
     isWordFound   VARCHAR2 (1) := 'N';
    BEGIN
     FOR q IN 1 .. words.COUNT LOOP

      IF matrix (j - 1) = 1 AND SUBSTR (str, j - 1, i - j + 1) = words (q) THEN
       isWordFound   := 'Y';
       EXIT;
      END IF;
     END LOOP;

     IF isWordFound = 'Y' THEN
      matrix (i)                        := 1;

      resultPrevs.EXTEND;
      resultPrevs (resultPrevs.COUNT)   := j;
     END IF;
    END;
   END LOOP;
  END LOOP;

  prevs        := resultPrevs;
 END get_matrix;

 PROCEDURE print_matrix (matrix IN OUT NOCOPY matrix_tab) IS
  strTemp   VARCHAR2 (100);
 BEGIN
  FOR i IN 1 .. matrix.COUNT LOOP
   strTemp   :=
    CASE
     WHEN strTemp IS NULL THEN '"' || COALESCE (TO_CHAR (matrix (i)), ' ')
     ELSE strTemp || '  "' || COALESCE (TO_CHAR (matrix (i)), ' ') || '"'
    END;
  END LOOP;

  DBMS_OUTPUT.put_line ('matrix');
  DBMS_OUTPUT.put_line (strTemp);
 END print_matrix;
BEGIN
    get_matrix (words => words, matrix => matrix, prevs => prevs);

    print_matrix (matrix => matrix);
    print_matrix (matrix => prevs);

    IF matrix (matrix.COUNT) = 1 THEN
        DBMS_OUTPUT.PUT_LINE('YES');
    ELSE
        DBMS_OUTPUT.PUT_LINE('NO');
    END IF;
END;

🪄 Что делает код:
Проверяет, можно ли из заданных слов собрать исходную строку.
Заполняет таблицу достижимых позиций.
Выводит результат и промежуточные таблицы (matrix, prevs) для визуального анализа.

Пример работы:
Исходная строка: catsandddog
Результат: NO

Потому что в словаре нет слова “ddog” — значит строка неразбиваемая. Если добавить в словарь "ddog", алгоритм сразу найдёт решение и выведет YES.

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

👍 Палец вверх — Ни дня без алгоритмов.
👎 Палец вниз — Хорошо же сидели и вот опять!

💬 Хороший коммент - половина дела 👇.
#️⃣ #Oracle #PLSQL

🔆 Cogito, ergo sum
или по-русски «Мыслю, следовательно, существую» - Рене Декарт

🚀 Серия: Оптимизация SQL-запросов
Тема: MERGE CARTESIAN — соединения таблиц

💡 Во всех источниках по оптимизации SQL можно встретить предупреждение:
если видите в плане строку MERGE JOIN CARTESIAN, — стоит насторожиться.

Почему?
Потому что это значит, что оптимизатор решил перемножить два набора данных,
то есть выполнить декартово произведение множеств.

Такой вид соединения вы гарантированно получите, если напишете, например:

SELECT * FROM a, b;
-- или
SELECT * FROM a CROSS JOIN b;
-- или
SELECT * FROM a INNER JOIN b ON (1 = 1);

📘 Результат этих запросов — это декартово произведение множеств A и B.
Если вдруг забыли, как это выглядит — вот пример:

A = {1, 2, 3}
B = {'A', 'B', 'C'}

A × B = {
  {1,'A'},{1,'B'},{1,'C'},
  {2,'A'},{2,'B'},{2,'C'},
  {3,'A'},{3,'B'},{3,'C'}
}

То есть количество строк = 3 * 3 = 9.

🧠 А теперь представьте, что таблица A содержит 1 млрд строк,
а таблица B — 100 млн строк.
И вот уже результат — 10^17 строк.
Не уверен, что во Вселенной столько атомов,
но пространства TEMP точно не хватит нигде.
Да и выполняться это будет сказочно долго.

📛 Поэтому в книгах по оптимизации справедливо пишут:
если в плане появился MERGE JOIN CARTESIAN,
скорее всего, вы забыли указать условие соединения.
У новичков такое бывает — пока рефлекс не выработался.

Но!
Я хочу поделиться своей идеей, которую придумал лет 5–6 назад.
В ней я осознанно и эффективно использую картезианское соединение.
Скорее всего, не я один, но приятно, что дошёл до этого сам.
Работа разработчика ведь часто состоит в том,
чтобы переосмысливать то, что увиденно, услышано, прочитано.

📊 Представьте, что вы читаете данные из таблицы A дважды или трижды,
но с разными условиями clause1, clause2,
а затем объединяете результаты.
Причём вам нужно рассчитать разные вычисляемые поля field1, field2
в зависимости от условий.
В выражения могут использоваться, и аналитические, и групповые функции.

Обычно это делают через UNION ALL:

SELECT expression1 AS field1, expression2 AS field2
FROM a
WHERE clause1 = TRUE
UNION ALL
SELECT expression3 AS field1, expression4 AS field2
FROM a
WHERE clause2 = TRUE;

🎯 Проблема в том, что таблица A должна считаться дважды (FULL ACCESS TABLE).
И по-другому вроде бы нельзя, ведь строки должны повторяться.

Попытка сделать всё в одном запросе через CASE не работает:

SELECT
  CASE WHEN clause1 THEN expression1 ELSE expression3 END AS field1,
  CASE WHEN clause2 THEN expression2 ELSE expression4 END AS field2
FROM a
WHERE clause1 OR clause2;

⚠️ Такой подход ошибочен,
потому что строки, подходящие под оба условия, обязаны дублироваться.

🧩 Чтобы не читать таблицу дважды, я придумал такой вариант:

SELECT
  CASE 
    WHEN c."case" = 1 AND clause1 THEN expression1 
    WHEN c."case" = 2 AND clause2 THEN expression2 
  END AS field1,
  CASE 
    WHEN c."case" = 1 AND clause1 THEN expression3 
    WHEN c."case" = 2 AND clause2 THEN expression4 
  END AS field2
FROM a
CROSS JOIN (
  SELECT 1 AS "case" FROM dual
  UNION ALL
  SELECT 2 AS "case" FROM dual
) c
WHERE clause1 OR clause2;

В чём выигрыш?
Мы читаем таблицу всего один раз,
а затем просто умножаем её на 2 с помощью CROSS JOIN.
Для многих отчётных запросов это даёт отличную оптимизацию.

💬 Кто не знал про такой подход — пользуйтесь!
Пусть MERGE CARTESIAN будет вам не врагом, а инструментом ⚙️

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

👍 Хороший прием буду знать!
👎 Какая ерунда, честно слово!

💬 Как приятно читать Ваши комменты! Вы бы знали! 👇
#️⃣ #SQLOptimization #Cases #SQL #Oracle #PostgreSQL #PLSQL

🎵 «Пусть нету ни кола и не двора,
Зато не платят королю налоги
Работники ножа и топора -
Романтики с большой дороги.» — Юрий Энтин

📌 Затравка для следующего поста про коллекции:
Анализируя "чужой" код, вижу неоднократно повторяющийся скрипт примерно с таким содержание.

DECLARE
  TYPE words_tab IS TABLE OF VARCHAR2 (100)
    INDEX BY PLS_INTEGER;

  words   words_tab;
BEGIN
  words (1) := 'Первый';
  words (2) := 'Второй';
  words (3) := 'Третий';

  FOR index# IN 1 .. words.COUNT
  LOOP
    DBMS_OUTPUT.put_line ('index#=' || TO_CHAR (index#) || ' -> ' || words (index#));
  END LOOP;
END;
/

Скрипт рабочий, но Вас ничего не смущает?

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Лучшая награда посту - это комменты! 👇
#Cases #SQL #Oracle #PLSQL

🚀 Хотите прокачать SQL, но базы под рукой нет?

У Славы Рожнева — автора курса SQL в Яндексе — есть два классных ресурса:
🔹 sqltest.online — задачи и тесты для практики
📢 Канал: t.me/sqltestonline
🔹 sqlize.online — онлайн-песочница для SQL-запросов прямо в браузере
📢 Канал: t.me/sqlize

✨ Отличный вариант, чтобы учиться и держать форму в любом месте и в любое время.
#FriendlyResources

🎵 «Нам лижут пятки языки костра.
За что же так не любят недотроги
Работников ножа и топора —
Романтиков с большой дороги?» — Юрий Энтин

📌 Разбор ситуации с ассоциативным массивом
Многие читатели обратили внимание, что демо код — не самый безопасный:

DECLARE
  TYPE words_tab IS TABLE OF VARCHAR2(100)
    INDEX BY PLS_INTEGER;

  words   words_tab;
BEGIN
  words(1) := 'Первый';
  words(2) := 'Второй';
  words(3) := 'Третий';

  FOR index# IN 1 .. words.COUNT LOOP
    DBMS_OUTPUT.put_line('index#=' || index# || ' -> ' || words(index#));
  END LOOP;
END;
/

Дело в том, что ассоциативные массивы в Oracle не гарантируют плотность индексов.
Они могут иметь пропуски, ключи вовсе не обязаны начинаться с единицы, и элементы могут добавляться в любом порядке.

👉 COUNT — это количество элементов,
а не максимальный индекс.

💥 Поэтому диапазон 1 .. words.COUNT корректен только если индексы плотные (1,2,3…) и начинаются с 1.
Но в реальном проекте никто не мешает изменить коллекцию, и тогда неизбежно вылетает ошибка:

ORA-01403: no data found

Да, можно сказать: “А я слежу за тем, чтобы коллекция всегда начиналась с 1 и была плотной.”
Но при совместной разработке невозможно гарантировать, что все коллеги будут поддерживать вот это всё.
Именно поэтому код становится небезопасным.

👍 Вариант 1: использовать обычную TABLE

DECLARE
  TYPE words_tab IS TABLE OF VARCHAR2(100);
  words   words_tab := words_tab();
BEGIN
  words.EXTEND;  words(words.COUNT) := 'Первый';
  words.EXTEND;  words(words.COUNT) := 'Второй';
  words.EXTEND;  words(words.COUNT) := 'Третий';

  FOR index# IN 1 .. words.COUNT LOOP
    DBMS_OUTPUT.put_line('index#=' || index# || ' -> ' || words(index#));
  END LOOP;
END;
/

Nested table плотная по определению, и здесь цикл безопасен.

👍 Вариант 2: безопасная навигация по индексам FIRST/NEXT

DECLARE
  TYPE words_tab IS TABLE OF VARCHAR2(100)
    INDEX BY PLS_INTEGER;

  words   words_tab;
  index#  PLS_INTEGER;
BEGIN
  words(1) := 'Первый';
  words(3) := 'Третий';

  index# := words.FIRST;
  WHILE index# IS NOT NULL LOOP
    DBMS_OUTPUT.put_line(words(index#));
    index# := words.NEXT(index#);
  END LOOP;
END;
/

Это гарантированный способ обхода любой неплотной коллекции.

🤔 Так в чём же прикол использовать TABLE OF вместо TABLE в таких плотных коллекциях?

Если коротко — ни в чём, если вам нужна плотная, предсказуемая коллекция.
INDEX BY — это гибкость, работа с “дырками” и ручное управление ключами.
TABLE — это надёжный массив с естественным, непрерывным диапазоном индексов.

Для большинства рабочих задач, где коллекция просто хранит список значений, удобнее и безопаснее использовать именно TABLE.

В качестве дополнения рекомендую статью на Habr: 📚 «Всё о коллекциях в Oracle».
Когда опыта было недостаточно, всегда использовал её как справочник.

👍 Хорошая тема для поста!
👎 Не годится никак!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Комменты иногда читать интереснее чем пост! 👇
#Cases #SQL #Oracle #PLSQL

🎵 «Закройте вашу книжку,
Допейте вашу чашку,
Дожуйте ваш дежурный бутерброд…
Снимите и продайте последнюю рубашку
И купите билет на пароход.

Ну что там ваши сводки, анкеты и доклады,
Когда повсюду рядом — там и тут —
Счастливые находки, таинственные клады…
Неужели так и пропадут?!»
— Юлий Ким

📌 Практическая "банковская" задача

Интересную задачу прислал мой друг (Спасибо тебе, Ирек! 🙌).
Решить её можно по-разному, и наши решения с ним заметно расходятся.
Любопытно было бы узнать ваш подход — такие задачки отлично тренируют мышление.

Допустим, есть таблица:

CREATE TABLE things
(
  id NUMBER,
  saved_date DATE,
  thing NUMBER
);

CREATE UNIQUE INDEX THINGS_U01
  ON things (id, saved_date);

В таблице огромный объём данных — от 1 млн до 1 млрд строк.

А также есть входящая таблица:

CREATE TABLE tmp_orders (
  id NUMBER,
  processed_date DATE
);

Здесь данных может быть сколько угодно — от пусто до миллионов.

🎯 Требуется:
Для каждой записи tmp_orders найти thing при условиях:

1️⃣ things.id = tmp_orders.id
2️⃣ Если по tmp_orders.id данных нет — вернуть NULL
3️⃣ Для каждой tmp_orders.processed_date:
• выбрать первую запись справа, то есть processed_date <= saved_date
• если записей справа нет — взять первую запись слева, то есть saved_date < processed_date

💡 Решать можно любым способом, но результат должен быть именно в виде SQL-запроса:
(id, processed_date, thing)

👍 Хорошая задача!
👎 Так себе задача!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Как бы эту задачу решали Вы? 👇
#RealInterviewTasks #Cases #SQL #Oracle #PostgreSQL #PLSQL #PLpgSQL

🎵 «Ах, знаю-знаю-знаю, порядок есть порядок
Все клады и находки учтены
Записаны в анкетах, отмечены в докладах
И законами все защищены
Они помогут делу, они послужат людям
Я знаю это, знаю наперёд
И значит мы не станем, и значит мы не будем
Покупать билет на пароход» — Юлий Ким

🔍 Доступ к временным данным или большая победа канала

Друзья, прежде всего хочу выразить благодарность Иреку Вафину за задачу и свой вариант решения,
а также читателю Алексею Онину за активное участие — и всем, кто заинтересовался задачей 🙌
Что сказать? Это тот редкий и любимый мной момент, когда комменты интереснее поста 😄

⭐ Были представлены несколько интересных вариантов решения
🟦 Вариант 1 — от Алексея Онина

select
    o.id,
    o.processed_date,
    COALESCE(
        (select
            tr.thing
            from things tr
            where tr.id = o.id and o.processed_date <= tr.saved_date
            order by tr.saved_date asc
            fetch first 1 rows only
        ), (select
            tl.thing
            from things tl
            where tl.id = o.id and tl.saved_date < o.processed_date
            order by tl.saved_date desc
            fetch first 1 rows only
        )
    ) AS thing
from tmp_orders o;

Всё логично 👍 И coalesce здесь не случайно:
в отличие от NVL, он не трогает следующие значения, если текущее IS NOT NULL.

🟦 Вариант 2 — также от Алексея Онина

with tr as (
    select
      t.id,
      coalesce(
          lag(t.saved_date) over(partition by t.id order by t.saved_date ASC) + interval '1' second,
          date '1900-01-01'
      ) as start_saved_date,
      nvl2(
          lead(t.saved_date) over(partition by t.id order by t.saved_date ASC),
          t.saved_date,
          DATE '9999-12-31'
      ) as end_saved_date,
      t.thing
    from things t
    where t.id in (select o0.id from tmp_orders o0)
      and t.saved_date is not null
)
select
  o.id,
  o.processed_date,
  tr.thing
from tmp_orders o
left join tr
  on tr.id = o.id
 and o.processed_date between tr.start_saved_date and tr.end_saved_date;

Тоже логично, но логика уже сложнее:
строим периоды [start_saved_date, end_saved_date] и ищем пересечение.

Проблема: нужно полностью прочитать things, что плохо для больших таблиц.

🟦 Вариант 3 — от Ирека Вафина

SELECT --+ use_hash(o t)
       o.id,
       o.processed_date,
       MAX(t.thing)
         KEEP (DENSE_RANK FIRST
               ORDER BY
                 CASE
                   WHEN o.processed_date <= t.saved_date THEN t.saved_date
                   ELSE NULL
                 END NULLS LAST,
                 t.saved_date DESC) AS thing
FROM tmp_orders o
LEFT OUTER JOIN things t ON t.id = o.id
GROUP BY o.Id, o.processed_date;

Очень интересный вариант через аналитику и сортировку.
Но проблема всё та же — полное чтение things + сортировка.

🟦 Вариант 4 — мой

Идея: использовать FFS или листовой доступ (FAST FULL INDEX SCAN).
Для этого создаём два метода:

🔹 get_previous_thing

CREATE OR REPLACE FUNCTION get_previous_thing (...)
...

🔹 get_next_thing

CREATE OR REPLACE FUNCTION get_next_thing (...)
...

И затем:

SELECT
  tmp_o.id,
  tmp_o.processed_date,
  COALESCE(
      get_next_thing (...),
      get_previous_thing (...)
  ) AS thing
FROM tmp_orders tmp_o;

Суть метода:
идём по листам индекса, достаём первый попавшийся элемент слева или справа.

🧪 Эксперимент
Заполняем:
things — 10 млн строк
tmp_orders — 10 тыс. строк
200k уникальных id × 50 записей в things

Все скрипты приложены к посту.

UPDATE 2025.11.23
В скрипте приложенном к посту есть ошибка (Спасибо Алексею Онину - заметил).
Исправленный скрипт в комментах.

Продолжение 👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

Продолжение👆

🔍 Доступ к временным данным или большая победа канала

📊 Результаты
вариант 1
time: 0.002201 s, consistent gets: 250556, physical reads: 2, CPU: 149
вариант 2
time: 0.001823 s, consistent gets: 30506, physical reads: 30224, CPU: 171
вариант 3
time: 0.000877 s, consistent gets: 30491, physical reads: 30224, CPU: 82
вариант 4
time: 0.000311 s, consistent gets: 40247, physical reads: 2, CPU: 29

Все скрипты выполнялись на «разогретой» базе — блоки уже были в кэше.
Из-за этого результаты противоречивы:

Вариант 4 — самый быстрый,
но делает больше логических чтений (consistent gets).

Вариант 3 — читает меньше блоков,
но читает с диска, а не из кэша.

Выводы делайте сами!

👍 Отличный пост!
👎 Могло быть и лучше!

⚠️ Хотите проверить скрипты, но нет базы под рукой - онлайн-песочница.
💎 Поддержка канала⁉️.

💬 Как бы эту задачу решали Вы? 👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL