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

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

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

🎵 «Тот мужик поклоны бьет, отвечает вежливо:
"Я не вор, я не шпион, я вообще-то - дух,-
За свободу за мою - захотите ежели вы -
Изобью за вас любого, можно даже двух!"
Тут я понял: это - джинн,- он ведь может многое -
Он же может мне сказать: "Враз озолочу!"...
"Ваше предложение,- говорю,- убогое.
Морды будем после бить - я вина хочу!» — Владимир Высоцкий

🔍 Проверка существования уникальной записи, или «Развенчание мифов»
В предыдущем опросе приняло участие 47 подписчиков — всем большое спасибо за неравнодушие 🙌

Честно сказать, я никогда глубоко не исследовал эту тему.
По наивности думал, что разницы нет, ведь самое тяжёлое в БД — это чтение с диска.
Всё остальное так или иначе оптимизировано и работает приемлемо.

Но коллеги настаивали на своём подходе — и я решил проверить и убедиться сам.

🧪 Начинаем эксперимент

Создадим тестовую таблицу и заполним её данными.
id заполнил значениями от 1 до 1 млн, но только нечётными.
Итого получилось 500 тыс. записей.

CREATE TABLE test_2025_11_23
(
  id       NUMBER PRIMARY KEY,
  email    VARCHAR2(200)
);

INSERT /*+ APPEND */
  INTO test_2025_11_23 (id, email)
  SELECT a.id, a.email
    FROM (
          SELECT LEVEL AS id,
                 'user_' || TRUNC(DBMS_RANDOM.VALUE(1, POWER(10, 9))) || '@example.com' AS email
            FROM DUAL
          CONNECT BY LEVEL <= POWER(10, 6)
         ) a
   WHERE MOD(a.id, 2) = 1;

COMMIT;

Далее исследуем «мини»-трассировкой три варианта проверки существования строки по уникальному индексу.
В цикле — 10 млн итераций, id вычисляется случайно от 1 до 1 млн.

⚙️ Вариант 1 — через EXCEPTION NO_DATA_FOUND

DECLARE
  test_id     test_2025_11_23.id%TYPE;
  random_id   test_2025_11_23.id%TYPE := TRUNC(DBMS_RANDOM.VALUE(1, POWER(10, 6)));
BEGIN
  SELECT t.id
    INTO test_id
    FROM test_2025_11_23 t
   WHERE t.id = get_count_ids.random_id;

  ids_count := ids_count + 1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    non_ids_count := non_ids_count + 1;
END;

⚙️ Вариант 2 — через EXISTS

От себя могу добавить: этот вариант я всегда считал самым логичным и читаемым.
Вообще люблю, когда код отражает логику:
проверяем существование → используем EXISTS. Логично! ✔️

DECLARE
  is_exists_id   VARCHAR2(1);
  random_id      test_2025_11_23.id%TYPE := TRUNC(DBMS_RANDOM.VALUE(1, POWER(10, 6)));
BEGIN
  SELECT CASE
           WHEN EXISTS (SELECT 1
                          FROM test_2025_11_23 t
                         WHERE t.id = get_count_ids.random_id)
           THEN 'Y'
           ELSE 'N'
         END
    INTO is_exists_id
    FROM DUAL;

  IF is_exists_id = 'Y' THEN
    ids_count := ids_count + 1;
  ELSE
    non_ids_count := non_ids_count + 1;
  END IF;
END;

⚙️ Вариант 3 — через MAX

DECLARE
  id_count    NUMBER;
  random_id   test_2025_11_23.id%TYPE := TRUNC(DBMS_RANDOM.VALUE(1, POWER(10, 6)));
BEGIN
  SELECT MAX(t.id)
    INTO id_count
    FROM test_2025_11_23 t
   WHERE t.id = get_count_ids.random_id;

  IF id_count IS NOT NULL THEN
    ids_count := ids_count + 1;
  ELSE
    non_ids_count := non_ids_count + 1;
  END IF;
END;

📊 Результаты

Эксперимент проводился на «разогретой» базе — все блоки таблицы были в кэше

variant1_with_exception_no_data_found
ids_count = 4998770, non_ids_count = 5001230
time: 00:02:23.434
consistent gets: 30000000
CPU: 14262

variant1_with_exists
ids_count = 4997912, non_ids_count = 5002088
time: 00:02:00.054
consistent gets: 30000000
CPU: 11942

variant1_with_max
ids_count = 4999842, non_ids_count = 5000158
time: 00:02:03.955
consistent gets: 30000000
CPU: 12377

🏁 Итог
Самый оптимальный вариант — через EXISTS.
Он быстрее и по времени, и по использованному CPU.
Исходники теста в приложении!

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

👍 Интересно или неожиданно!
👎 Скучно или ожидаемо!

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

💬 Хотите сказать - скажите!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Глеб Жеглов и Володя Шарапов за столом засиделись не зря
Глеб Жеглов и Володя Шарапов ловят банду и главаря!
Расцвела буйным цветом малина, разухабилась разная тварь
Хлеба нет, а полно гуталина, да глумится горбатый главарь»
— Александр Шаганов

🕵️‍♂️ Детектив или просто интересная ошибка?

На днях получил задачу найти баг в коде. Задача оказалась нетривиальной: код незнакомый, пакет — больше 10 тысяч строк, ошибка — где-то внутри метода примерно на 1500 строк.

В общем, «развлечение» на четыре часа.
Но ошибка — интересная!

📌 Суть
Допустим, есть строка, в которой лежит форматированный SQL-запрос.
Запрос парсится на наличие подзапросов, которые сохраняются в коллекцию, а в исходной строке — временно заменяются.
Потом должны вернуться обратно.

Но что-то пошло не так.
Задача: выяснить, где ошибка, и предложить простой вариант исправления.

🧩 Код (публикую «как есть»)

DECLARE
  str CLOB := q'{(/**/select1 (/**/select2 (/**/select4) ) (/**/select3) ) }';
  i   INTEGER := 0;

   type lt_selects is table of varchar2(32000) index by pls_integer;
   v_child_selects                  lt_selects;
BEGIN
  loop
    for rec in (
       select
          childs_select,
          instr(lower(childs_select),'(/**/select') is_child
       from (
          select distinct substr(to_char(regexp_substr(str,'(+)\(([^()]*)\)',1,level)),1) childs_select
          from dual
          connect by level <= REGEXP_COUNT(str,'(+)\(([^()]*)\)')
       )
    )loop
      i := i + 1;
      str := replace(str,rec.childs_select,
        '/*' || TO_CHAR(i) || '_child_start*/' ||
        '@'|| TO_CHAR (i) ||'@' ||
        '/*' || TO_CHAR (i) || '_child_end*/');
              
      v_child_selects(v_child_selects.count+1) := rec.childs_select;
    end loop;
   if nvl(REGEXP_COUNT(str,'(+)\(([^()]*)\)'),0) = 0
   then
     exit;
   end if;
  end loop;

  -- return subselects
  if v_child_selects.count > 0
  then
  for i in 1..v_child_selects.count
    loop
    if instr(str,'@' || i || '@') > 0
    then
     str := replace(str,
                      '@'||i||'@',
                      v_child_selects(i)
                      );
    end if;
    end loop;
  end if;
  
  dbms_output.put_line ('str=' || str);
END;

📤 Результат

str=/*4_child_start*/(/**/select1 /*3_child_start*/@3@/*3_child_end*/ /*2_child_start*/@2@/*2_child_end*/ )/*4_child_end*/ 

❓ Интересен ли вам такой формат или эта ошибка понравилась только мне?

👍 Формат интересен!
👎 Своих проблем хватает!

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

💬 Есть что написать — пишите! 👇
#Cases #SQL #Oracle #PLSQL

🎵 «До утра не погаснет окошко —
Глеб Жеглов и Володя не спят.
Пресловутая «Чёрная кошка» забоится наших ребят!
Глеб Жеглов и Володя Шарапов заслужили в боях ордена —
После мирного дня трудового будь спокойна, родная страна!»
— Александр Шаганов

🕵️‍♂️ Детектив или просто интересная ошибка. Разбор

Вот такой legacy-код необходимо поддерживать. Руки чешутся всё переделать, но нельзя: при любом рефакторинге велик риск изменить логику — и тогда все труды обесценятся, а то и вовсе навредят.
А главное правило при разработке, как у врача, — «Не навреди!» ⚕️
Такой код у новичков вызывает шок, но, на самом деле, это весьма лайтовый вариант - бывает куда хуже!

Читатель Алексей Онин обладает поразительной наблюдательностью — и уже не раз это доказал.
Он правильно отметил, что ошибка в том, что в цикле коллекция наполняется подзапросами в прямом порядке:
от самого верхнего уровня до самого глубокого.
Чтобы подмену провести корректно, замену нужно делать в обратном порядке 🔄.

🎯 Задача: исправленный вариант.

🧩 Код (публикую «как есть»)

DECLARE
  str CLOB := q'{(/**/select1 (/**/select2 (/**/select4) ) (/**/select3) ) }';
  i   INTEGER := 0;

   type lt_selects is table of varchar2(32000) index by pls_integer;
   v_child_selects                  lt_selects;
BEGIN
  loop
    for rec in (
       select
          childs_select,
          instr(lower(childs_select),'(/**/select') is_child
       from (
          select distinct substr(to_char(regexp_substr(str,'(+)\(([^()]*)\)',1,level)),1) childs_select
          from dual
          connect by level <= REGEXP_COUNT(str,'(+)\(([^()]*)\)')
       )
    )loop
      i := i + 1;
      dbms_output.put_line ('rec.childs_select=' || rec.childs_select || ',' || TO_CHAR (rec.is_child));
      str := replace(str,rec.childs_select,
        '/*' || TO_CHAR(i) || '_child_start*/' ||
        '@'|| TO_CHAR (i) ||'@' ||
        '/*' || TO_CHAR (i) || '_child_end*/');
              
      v_child_selects(v_child_selects.count+1) := rec.childs_select;
    end loop;
   if nvl(REGEXP_COUNT(str,'(+)\(([^()]*)\)'),0) = 0
   then
     exit;
   end if;
  end loop;

  IF v_child_selects.COUNT > 0
  THEN
   <<replace_subselects_back>>
    DECLARE
      index#   PLS_INTEGER;
    BEGIN
      index# := v_child_selects.LAST;                           -- REVERSE is needed

      WHILE index# IS NOT NULL
      LOOP
        IF INSTR (str, '@' || TO_CHAR (index#) || '@') > 0
        THEN
          str :=
            REPLACE (str, '@' || TO_CHAR (index#) || '@', v_child_selects (index#));
        END IF;

        index# := v_child_selects.PRIOR (index#);
      END LOOP;
    END;
  END IF;

  DBMS_OUTPUT.put_line ('str=' || str);
END;

📤 Результат — ровно то, что и ожидалось:

str=/*4_child_start*/(/**/select1 /*3_child_start*/(/**/select2 /*1_child_start*/(/**/select4)/*1_child_end*/ )/*3_child_end*/ /*2_child_start*/(/**/select3)/*2_child_end*/ )/*4_child_end*/

Дополнительно я изменил проход по ассоциативному массиву. Мы уже подробно разбирали эту тему.
Объяснять, почему так, — не буду 😉.

👍 Хочу ещё разбор интересных ошибок!
👎 Давайте что-то другое!

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

💬 Пишите комменты: не для меня или себя, а просто так! 👇
#Cases #SQL #Oracle #PLSQL

🎵 «Сколько лет прошло, всё о том же гудят провода
Всё того же ждут самолёты
Девочка с глазами из самого синего льда
Тает под огнём пулемёта
Должен же растаять хоть кто-то» — Александр Васильев

🔒 Deadlock в одной сессии? Да, это возможно!

Что «нефиг делать» для одних — то «не знаю» для других.
Такой вопрос мне частенько задавали устно на собеседованиях, и, наверное, поэтому я так хорошо его запомнил.

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

Вот минимальный пример:

DECLARE
  v_id   t.id%TYPE := 1;

  PROCEDURE update_t (id IN t.id%TYPE)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    UPDATE t
       SET t.something = 111
     WHERE t.id = update_t.id;

    COMMIT;
  END update_t;
BEGIN
  UPDATE t
     SET t.something = 222
   WHERE t.id = v_id;

  update_t (id => v_id);

  COMMIT;
END;

🧩 Что здесь происходит

Главный блок начинает транзакцию и ставит блокировку на строку t.id = 1.
Затем вызывается процедура update_t, помеченная как PRAGMA AUTONOMOUS_TRANSACTION — это новая независимая транзакция, работающая в рамках той же сессии.
Внутри автономной транзакции мы снова пытаемся обновить ту же строку t.id = 1.
Но строка уже заблокирована внешним UPDATE.
Oracle пытается получить блокировку… у самой себя.
Ждать сам себя он, конечно, не может — и возникает классический deadlock:
ORA-00060: deadlock detected while waiting for resource

🔍 Почему Oracle не ждёт сам себя?

Потому что автономная транзакция — это полноценная независимая транзакция со своим собственным контекстом блокировок (lock manager context).
Oracle видит две конкурирующие транзакции внутри одной сессии и прекращает ожидание, считая его заведомо бесперспективным.

🧠 Важный вывод

AUTONOMOUS_TRANSACTION — мощный инструмент,
но на практике оправдан, по моему опыту, только в двух случаях:
Логирование (особенно ошибок).
Выполнение DDL-операций как в «оболочке», чтобы скрыть неявный COMMIT для основной транзакции.

👍 Тема автономок интересна - хочу ещё!
👎 Тема автономок не интересна!

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

💬 Если есть время - уделите 30 секунд комменту!👇
#Cases #SQL #Oracle #PLSQL

🎵 «У леса на опушке
Жила Зима в избушке.
Она снежки солила
В берёзовой кадушке.
Она сучила пряжу,
Она ткала холсты,
Ковала ледяные
Над реками мосты» — Сергей Островой

🧵 Автономка и DDL операции

По реакции видно, что тема автономных транзакций зашла 🔥
Не отпускаем её и посмотрим на другой важный аспект — неявный коммит в DDL.

Для этого возьмём такую задачу 👇

--DROP TABLE test_2025_12_01 PURGE;

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

DECLARE
  PROCEDURE update_execute_rollback (execute_ddl IN VARCHAR2)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    UPDATE test_2025_12_01 t
       SET t.something = NVL (t.something, 0) + 1
     WHERE t.id = 1;

    EXECUTE IMMEDIATE execute_ddl;

    ROLLBACK;
  END update_execute_rollback;
BEGIN
  INSERT INTO test_2025_12_01 (id, something)
       VALUES (1, 1);

  COMMIT;

  update_execute_rollback (
    execute_ddl   => q'{COMMENT ON TABLE test_2025_12_01 IS 'test_comment'}');

  update_execute_rollback (
    execute_ddl   =>
      q'{COMMENT ON COLUMN test_2025_12_01.id IS 'test_column_comment'}');

  update_execute_rollback (
    execute_ddl   =>
      q'{ALTER TABLE test_2025_12_01 ADD CONSTRAINT test_2025_12_01_chk01 CHECK (something > 0)}');

  update_execute_rollback (
    execute_ddl   =>
      q'{CREATE INDEX test_2025_12_01_i01 ON test_2025_12_01 (something)}');

 <<print_something>>
  DECLARE
    something   test_2025_12_01.something%TYPE;
  BEGIN
    SELECT t.something
      INTO something
      FROM test_2025_12_01 t
     WHERE t.id = 1;

    DBMS_OUTPUT.put_line ('something =' || TO_CHAR (something));
  END print_something;
END;

❓ Вопрос к вам

Как по-вашему, какое значение будет выведено в консоль?
И главное — почему именно такое?

👍 Славно, славно!
👎 Кошмар и ужас!

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

💬 Каждый имеет право высказаться в рамках приличия!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Вот и ко мне пришла беда —
стартер заел, —
Теперь уж это не езда,
а ерзанье.
И надо б выйти, подтолкнуть —
но прыти нет, —
Авось подъедет кто-нибудь
и вытянет». — Владимир Высоцкий

🎯 Практическая задача получения списка уникальных символов.

Видимо, под впечатлением от вариантов решения его прошлой задачи, читатель Ирек Вафин, по совместительству мой друг, прислал новую задачу.

Формулируется она очень просто: требуется из строкового поля, по всем строкам большой таблицы, проанализировать и получить список всех уникальных символов, которые могут встречаться в этом поле.

Умение решать такие задачи — ценный навык: детали могут быть разные, но по сути техник решения подобных задача несколько, и трудно придумать новый велосипед 🚲🙂.

Итак, формальная постановка:
есть большая таблица (от 1 млн до 1 млрд записей).

CREATE TABLE test_texts (
  id   NUMBER PRIMARY KEY,
  text VARCHAR2(4000)
);

INSERT INTO test_texts (id, text)
VALUES (1, '11qqq%%11qq^^');

INSERT INTO test_texts (id, text)
VALUES (2, '7766&&^^qqEE11');

COMMIT;

Необходимо получить список уникальных символов (порядок не важен):

'1', 'q', '%', '^', '7', '6', '&', 'E'

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

👍 Задача моей мечты!
👎 Хорошо, что мне такая задача не попалась!

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

💬 С нетерпением жду ваши варианты решения!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Сам виноват — и слёзы лью, и охаю:
Попал в чужую колею глубокую.
Я цели намечал свои на выбор сам —
А вот теперь из колеи не выбраться.
Крутые скользкие края имеет эта колея.
Я кляну проложивших её — скоро лопнет терпенье моё —
И склоняю, как школьник плохой:
Колею, в колее, с колеей…» — Владимир Высоцкий

🔬 Эксперимент: как быстрее всего получить набор уникальных символов из больших текстов?

Как я измучился с этой задачей! Кто бы знал! 😅
— «Зачем тебе это надо?» — спрашивает меня моя любимая женушка.
— «Мне это надо! Мне это нравится!» — отвечаю я ей.

Спасибо всем, кто активно и пассивно участвовал 🙏
Всем, кто присылал свои варианты, — искреннее уважение и благодарность.
И тебе, Ирек, — за три дня беспокойной жизни — тоже скромное, но честное спасибо 😄

Итак… всем розовых слонов раздали 🐘 — начинаем!

🛠 Подготовим тестовую среду

CREATE TABLE test_texts
(
  id      NUMBER PRIMARY KEY,
  text    VARCHAR2 (4000 CHAR)
);

CREATE TABLE test_chars
(
  "CHAR" VARCHAR2 (1 CHAR)
);

DECLARE
  current_max_id INTEGER;
BEGIN
  FOR index# IN 1..100
    LOOP
      IF index# = 1 THEN
        current_max_id := 0;
      ELSE
        current_max_id := current_max_id + POWER (10, 3);
      END IF;

      INSERT INTO test_texts (id, text)
        WITH
          generated_rows
          AS
            (    SELECT current_max_id + LEVEL AS id, TRUNC (DBMS_RANDOM.VALUE (1, 1000)) AS text_length
                   FROM DUAL
             CONNECT BY LEVEL <= POWER (10, 3)),
          generated_random_text
          AS
           (
            SELECT a.id, CHR(
                            CASE
                              WHEN a.random_char_number < 55296 THEN a.random_char_number
                              ELSE a.random_char_number + 2048   -- без суррогатов D800–DFFF
                            END
                            USING NCHAR_CS
                          ) AS random_symbol
            FROM(  
            SELECT gr.id, TRUNC(DBMS_RANDOM.VALUE(1, 60000)) AS random_char_number
                   FROM generated_rows gr
             CONNECT BY     PRIOR gr.id = gr.id
                        AND PRIOR DBMS_RANDOM.VALUE () IS NOT NULL
                        AND LEVEL <= gr.text_length
              ) a
            )
          SELECT grt.id,
                 LISTAGG (grt.random_symbol, '') WITHIN GROUP (ORDER BY NULL)  AS text
            FROM generated_random_text grt
        GROUP BY grt.id;
    END LOOP;
END;

Мы наполняем test_texts случайными данными:

1️⃣ Длина строки — от 1 до 1000.
(Почему не 4000? LISTAGG снова шалит. В каждом релизе Oracle её чинят, но баги — как тараканы — всегда возвращаются.)

2️⃣ Символы — случайные CHAR 1–60000 с пропуском суррогатов.

Получаются строки вроде такой:

賔ᩆऩ뷖⧕쟀ᕺྗ畄됖ֱ䌆䳻漽㕲萓麱䷃䅠쿬...

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

🧪 Тестируемые варианты

1️⃣ CONNECT BY LEVEL + SYS_GUID()
2️⃣ CONNECT BY LEVEL + DBMS_RANDOM.VALUE
3️⃣ CROSS APPLY + CONNECT BY
4️⃣ PL/SQL + ассоциативный массив
5️⃣ То же, но с оптимизацией через REPLACE
6️⃣ Рекурсивный WITH (от «великого и ужасного» Славы Рожнего —
как говорится, «старый богатырь ещё может завинтить свой измаильский штык!» ⚔️)

📦 Вариант 1 — SYS_GUID()

SELECT DISTINCT SUBSTR (tt.text, LEVEL, 1)   AS "CHAR"
  FROM test_texts tt
CONNECT BY     LEVEL <= LENGTH (tt.text)
           AND PRIOR tt.id = tt.id
           AND PRIOR SYS_GUID () IS NOT NULL;

SYS_GUID() здесь выглядит тяжеловесно.
Честно говоря, напоминает «стрелять по воробьям из гаубицы».
И шанс переполнить TEMP очень велик.

📦 Вариант 2 — DBMS_RANDOM

SELECT DISTINCT SUBSTR (tt.text, LEVEL, 1)   AS "CHAR"
  FROM test_texts tt
CONNECT BY     LEVEL <= LENGTH (tt.text)
           AND PRIOR tt.id = tt.id
           AND PRIOR DBMS_RANDOM.value () IS NOT NULL;

То же, но чуть легче. TEMP всё равно в печали.

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

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

📦 Вариант 3 — CROSS APPLY

SELECT DISTINCT c."CHAR"
  FROM test_texts  tt
       CROSS APPLY (SELECT SUBSTR (tt.text, LEVEL, 1) AS "CHAR"
                      FROM DUAL
                    CONNECT BY LEVEL <= LENGTH (tt.text)) c;

Красиво, логично, TEMP не бомбится.
Но не чемпион.

📦 Вариант 4 — Ассоциативный массив

DECLARE
  TYPE chars_tab IS TABLE OF BOOLEAN
    INDEX BY VARCHAR2 (1 CHAR);

  chars        chars_tab;
  char_index   VARCHAR2 (1);
BEGIN
  FOR texts IN (SELECT tt.text
                  FROM test_texts tt)
  LOOP
    FOR index# IN 1 .. LENGTH (texts.text)
    LOOP
      chars (SUBSTR (texts.text, index#, 1)) := TRUE;
    END LOOP;
  END LOOP;

  chars.delete ();
EXCEPTION
  WHEN OTHERS THEN
    IF chars IS NOT NULL OR chars.COUNT > 0 THEN
      chars.delete ();
    END IF;
END;

Просто, надёжно, как автомат Калашникова.

📦 Вариант 5 — Ассоциативный массив + REPLACE

DECLARE
  TYPE chars_tab IS TABLE OF BOOLEAN
    INDEX BY VARCHAR2 (1 CHAR);

  chars           chars_tab;
  replaced_text   VARCHAR2 (4000 CHAR);

  char_index      VARCHAR2 (1 CHAR);
BEGIN
  FOR texts IN (SELECT tt.text
                  FROM test_texts tt)
  LOOP
    char_index := chars.FIRST;
    replaced_text := chars.NEXT (char_index);

    WHILE char_index IS NOT NULL AND replaced_text IS NOT NULL
    LOOP
      replaced_text := REPLACE (replaced_text, char_index, NULL);
      char_index := chars.NEXT (char_index);
    END LOOP;

    FOR index# IN 1 .. LENGTH (texts.text)
    LOOP
      chars (SUBSTR (texts.text, index#, 1)) := TRUE;
    END LOOP;
  END LOOP;

  chars.delete ();
EXCEPTION
  WHEN OTHERS THEN
    IF chars IS NOT NULL OR chars.COUNT > 0 THEN
      chars.delete ();
    END IF;
END;

Хорош, когда набор символов ограничен.
В случае Unicode-зоопарка — разницы почти нет.

📦 Вариант 6 — Рекурсивный WITH

WITH
  text_split ("CHAR", remaining_text)
  AS
    (SELECT SUBSTR (tt.text, 1, 1) AS "CHAR", SUBSTR (tt.text, 2) AS remaining_text
       FROM test_texts tt
     UNION ALL
     SELECT SUBSTR (ts.remaining_text, 1, 1)   AS "CHAR",
            SUBSTR (ts.remaining_text, 2)      AS remaining_text
       FROM text_split ts
      WHERE ts.remaining_text IS NOT NULL)
SELECT DISTINCT "CHAR"
  FROM text_split;

С самого начала подозрительный вариант.
И действительно — TEMP взрывается как дрожжевое тесто.

📊 Результаты
variant1 (SYS_GUID) 00:41:32 ❌
variant2 (RANDOM) 00:05:23 ❌
variant3 (CROSS APPLY) 00:04:22 ⚠️
variant4 (indexed table) 00:01:39 ⭐
variant5 (indexed + replace) 00:01:38 ⭐
variant6 (recursive with) ORA-01652 ❌ TEMP умер героем

🧠 Итог
❌ Варианты 1, 2, 6 — слишком прожорливы по TEMP.
⚠️ Вариант 3 — рабочий, но не лучший.
🏆 Варианты 4 и 5 — оптимальны.

Разницы почти нет, но если набор символов ограничен реальным алфавитом — REPLACE даёт небольшой бонус.

Все скрипты теста в приложенном файле.

🏁 Занавес
Я пошёл зализывать раны.
Надеюсь, «хитрый татарин» получил то, на что рассчитывал.

👍 Страшно интересно!
👎 Страшно скучно!

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

💬 Вот хотя бы что-то напишите - я же старался!👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

🎵 «По первому взгляду и виду
Нетрудная, кажется, вещь:
Роскошную эту равнину
Верхом, не спеша, пересечь

Но нам, к сожаленью, известно,
Как ястребы рвутся с цепи,
Как до смерти может быть тесно
И в самой бескрайней степи» — Юлий Ким.

📦 Архив без партиций или жизнь полна приключений!

Есть задача архивирования данных на Oracle Standard Edition.
Тем, кто всю жизнь работал на Enterprise, трудно представить, но в этой версии нет возможности использовать партиции.
И задача по переводу таблиц в архив заиграла другими красками 🎨

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

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

Эта задача относительно легко решается через партиции, но их нет ❌
А как можно решить без них?

Вот "законный" список требований:
1️⃣ Данные из оперативного диска перенести в архивный диск.
2️⃣ В таблицах убрать лишнее, т.е. понизить HWM.
3️⃣ Предоставить чтение архивных данных.
4⃣ Без downtime, т.е. поток записи в таблицы останавливать нельзя.

Как бы Вы решали такую задачу концептуально?

👍 Задача стоит внимания!
👎 Это что, задача что ли?

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

💬 Ваши мысли туда!👇
#Cases #SQL #Oracle #PLSQL

🎵 «Механик, крути киноленту,
Дежурные лампы туши!
Вноси свою светлую лепту
В суровые будни души!
Свети нам лучом из окошка
И сам вместе с нами поверь,
Что эту равнину возможно
Проехать почти без потерь!..» — Юлий Ким.

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

🧠 Концепт архитектуры архивирование таблиц (SE Edition) или мир полон приключений.

1️⃣ Две рабочие таблицы вместо одной
Вместо одной журнальной таблицы используются две идентичные.
Текущая активная выбирается через синоним.
т.е. синоним это переключатель, который указывает, какая из таблиц активная для записи.

Зачем?
поток инсёртов всегда идёт в одну из них;
можно безопасно переключать поток;
неактивная таблица становится источником архивации;
после экспорта таблица очищается через TRUNCATE → низкий HWM.
Этот механизм позволяет организовать архивацию без блокировок и без downtime.

2️⃣ Периодический процесс архивации
Раз в период (День, Неделя, Месяц, Год):
Проверяем, не прервался ли предыдущий запуск архивации. Если прервался — продолжаем старый процесс.
Переключает синоним на другую рабочую таблицу.
Экспортируем данные.
Переносим остатки данных, которые могли попасть в таблицу во время переключения и за предыдуший период.
Полностью очищаем использованную таблицу через TRUNCATE.
Все действия фиксируются в логах и статусах.

3️⃣ Организация хранение архива
Видится 2 варианта:
Через отдельное архивное табличное пространство.
Через дамп файлы.

4️⃣ Самовосстановление после ошибок
Если произошёл сбой (например, возникла ошибка или пропало питание):
смена синонима не происходит.
при следующем запуске процесс сам определяет, на каком шаге остановился,
повторяет только незавершённый шаг,
продолжает дальше.

Ручное вмешательство практически не требуется в идеале.

5️⃣ Что это даёт?
✔ Архивация без партиций
✔ без downtime
✔ Минимальный рост рабочих таблиц
✔ Простое хранение архивов
✔ Прозрачное восстановление после ошибок
✔ Лёгкое удаление устаревших данных
✔ Доступ к архиву разный в зависимости от вариантов архивации

🎨 Итог:
Даже на Standard Edition можно построить надёжный процесс архивации,
который работает как "ручная партиционизация".
Хотя повторюсь - мир полон приключений, с партициями, это было бы проще.

👍 Толково!
👎 Мусор!

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

💬 Мне важно Ваше мнение. Может быть, я не вижу очевидных возможностей! 👇
#Cases #CodeArchitecture

Уважаемые читатели!

Хочу узнать ваше мнение.

В моём канале нет «пустых» постов ни о чём.
Я не публикую свои фото среди пальм и моря (хотя живу в курортном городе 🌴).
Я не хейчу «товарищей по опасному бизнесу» ради поднятия самооценки (с ней всё в порядке, спасибо).

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

Лично я считаю, что подобные посты — это вода.
И публиковать такое в профессиональном канале — проявление неуважения к профи.
Но… возможно, я ошибаюсь.

Может быть, вам действительно интересно увидеть «внутреннюю кухню» канала, ретроспективу, цифры, историю, немного личного?

Поэтому прошу высказаться в комментариях:
нужны ли такие посты или нет?

Эмодзи в этот раз не считаются — только текстовые комменты.

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

С уважением,
Поликарпов Кирилл.

✨ «Все это, видите ль, слова, слова, слова.
Иные, лучшие, мне дороги права;
Иная, лучшая, потребна мне свобода:
Зависеть от царя, зависеть от народа —
Не всё ли нам равно? Бог с ними.
Никому
Отчёта не давать, себе лишь самому
Служить и угождать; для власти, для ливреи
Не гнуть ни совести, ни помыслов, ни шеи.»
— Александр Сергеевич Пушкин

Работа с текстовыми шаблонами — задача интересная от джуна до синьера 👨‍💻

Читатель Максим Феликсович меня справедливо осудил за то, что в последнее время задачи, которые я предлагаю, не для новичков.
Я подумал и нашёл тему, которая будет полезна для всех и при этом несложная для понимания

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

Решать такую задачу можно по-разному:

🔹 а) Самый интуитивно простой способ
Сделать несколько шаблонов и каждый использовать в отдельном случае.

Метод простой, но у него есть несколько недостатков:
1️⃣ Очень трудно поддерживать такой код.
Если шаблонов несколько десятков и в каждый нужно внести одну и ту же правку —
очень легко что-то пропустить.
И тогда ошибка становится плавающей:
в одном кейсе есть, в другом — нет.
На практике такие ошибки очень трудно отлавливать.
2️⃣ Такой код трудно читать.
Если шаблоны сложные и их много — рябит в глазах

🔹 б) Другой простой метод — метод «матрёшки»
или метод подшаблонов,
когда один общий шаблон разбивается на множество подшаблонов,
каждый заполняется отдельно,
а в конце всё собирается в готовый текст с помощью REPLACE.

Давайте рассмотрим на примере 👇

📌 Первый подход

DECLARE
  TYPE pupil_rec IS RECORD
  (
    pupil_id            NUMBER,
    name                VARCHAR2 (100),
    sex                 VARCHAR2 (1),
    birthday            DATE,
    average_score       NUMBER,
    favorite_subject    VARCHAR2 (100)
  );

  pupil        pupil_rec
                 := pupil_rec (1,
                     'Ivan Ivanоv',
                     'Y',
                     TO_DATE ('08.09.2012', 'DD.MM.YYYY'),
                     3.9,
                     'Maths');
  pupil_info   VARCHAR2 (4000);

  PROCEDURE get_pupil_info (pupil IN pupil_rec, pupil_info OUT VARCHAR2)
  IS
  BEGIN
    IF pupil.average_score >= 4
    THEN
      pupil_info :=
           'As of '
        || TO_CHAR (SYSDATE, 'dd.mm.yyyy')
        || ', we can report the following:
We are pleased to report that pupil '
        || pupil.name
        || ' ('
        || TO_CHAR (TRUNC (MONTHS_BETWEEN (SYSDATE, pupil.birthday) / 12))
        || ' years old) is doing very well academically. 
'
        || CASE WHEN pupil.sex = 'M' THEN 'He' ELSE 'She' END
         ' shows great interest in '  pupil.favorite_subject || '.
'
        || CASE WHEN pupil.sex = 'M' THEN 'His' ELSE 'Her' END
         ' average score in all subjects is '  TO_CHAR(pupil.average_score) || '.
The school is proud of this pupil.';
    ELSE
      pupil_info :=
           'As of '
        || TO_CHAR (SYSDATE, 'dd.mm.yyyy')
        || ', we can report the following:
We inform you that the pupil''s studies are '
        || pupil.name
        || ' ('
        || TO_CHAR (TRUNC (MONTHS_BETWEEN (SYSDATE, pupil.birthday) / 12))
        || ' years old) it causes concern. 
We noticed that the pupil shows interest in ' 
 TO_CHAR(pupil.favorite_subject)  ', but the general approach to learning is unsatisfactory.
'
        || CASE WHEN pupil.sex = 'M' THEN 'His' ELSE 'Her' END
         ' average score in all subjects is '  TO_CHAR(pupil.average_score) || '.
We ask you to pay attention.';
    END IF;
  END get_pupil_info;
BEGIN
  get_pupil_info (pupil => pupil, pupil_info => pupil_info);
  DBMS_OUTPUT.put_line (pupil_info);
END;

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

Работа с текстовыми шаблонами — задача интересная от джуна до синьера 👨‍💻. Продолжение.

📌 Второй подход — метод подшаблонов

Разбиваем текст на подшаблоны:

DECLARE
  TYPE pupil_rec IS RECORD
  (
    pupil_id            NUMBER,
    name                VARCHAR2 (100),
    sex                 VARCHAR2 (1),
    birthday            DATE,
    average_score       NUMBER,
    favorite_subject    VARCHAR2 (100)
  );

  pupil        pupil_rec
                 := pupil_rec (1,
                               'Ivan Ivanоv',
                               'Y',
                               TO_DATE ('08.09.2012', 'DD.MM.YYYY'),
                               4,
                               'Maths');
  pupil_info   VARCHAR2 (4000);

  PROCEDURE get_pupil_info (pupil IN pupil_rec, pupil_info OUT VARCHAR2)
  IS
    common_template            VARCHAR2 (4000)
      := '[HEADER_TEMPLATE][ESTIMATION_TEMPLATE][AVERAGE_SCORE_TEMPLATE][CONCLUSION_TEMPLATE]';
    header_template            VARCHAR2 (4000)
      := 'As of [SYSDATE], we can report the following:' || CHR (10);
    estimate_template          VARCHAR2 (4000)
      := '[GOOD_ESTIMATE_TEMPLATE][BAD_ESTIMATE_TEMPLATE]' || CHR (10);

    good_estimate_template     VARCHAR2 (4000)
      := 'We are pleased to report that pupil [PUPIL_NAME] ([PUPIL_AGE] years old) is doing very well academically.
[PUPIL_HE] shows great interest in [FAVOURITE_SUBJECT].';

    bad_estimate_template      VARCHAR2 (4000)
      := 'We inform you that the pupil''s studies are [PUPIL_NAME] ([PUPIL_AGE] years old) it causes concern.
We noticed that the pupil shows interest in [FAVOURITE_SUBJECT], but the general approach to learning is unsatisfactory.';

    average_score_template     VARCHAR2 (4000)
      :=    '[HIS] average score in all subjects is [AVARAGE_SCORE].'
         || CHR (10);
    conclusion_template        VARCHAR2 (4000)
      := '[GOOD_CONCLUSION_TEMPLATE][BAD_CONCLUSION_TEMPLATE]';

    good_conclusion_template   VARCHAR2 (4000)
                                 := 'The school is proud of this pupil.';
    bad_conclusion_template    VARCHAR2 (4000)
                                 := 'We ask you to pay attention.';
  BEGIN
    header_template :=
      REPLACE (header_template, '[SYSDATE]', TO_CHAR (SYSDATE, 'dd.mm.yyyy'));

    IF pupil.average_score >= 4
    THEN
      good_estimate_template :=
        REPLACE (good_estimate_template, '[PUPIL_NAME]', pupil.name);
      good_estimate_template :=
        REPLACE (
          good_estimate_template,
          '[PUPIL_AGE]',
          TO_CHAR (TRUNC (MONTHS_BETWEEN (SYSDATE, pupil.birthday) / 12)));
      good_estimate_template :=
        REPLACE (good_estimate_template,
                 '[PUPIL_HE]',
                 CASE WHEN pupil.sex = 'M' THEN 'He' ELSE 'She' END);
      good_estimate_template :=
        REPLACE (good_estimate_template,
                 '[FAVOURITE_SUBJECT]',
                 pupil.favorite_subject);

      estimate_template :=
        REPLACE (estimate_template,
                 '[GOOD_ESTIMATE_TEMPLATE]',
                 good_estimate_template);
      estimate_template :=
        REPLACE (estimate_template, '[BAD_ESTIMATE_TEMPLATE]', NULL);

      conclusion_template :=
        REPLACE (conclusion_template,
                 '[GOOD_CONCLUSION_TEMPLATE]',
                 good_conclusion_template);
      conclusion_template :=
        REPLACE (conclusion_template, '[BAD_CONCLUSION_TEMPLATE]', NULL);

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

Работа с текстовыми шаблонами — задача интересная от джуна до синьера 👨‍💻. Продолжение.

    ELSE
      bad_estimate_template :=
        REPLACE (bad_estimate_template, '[PUPIL_NAME]', pupil.name);
      bad_estimate_template :=
        REPLACE (
          bad_estimate_template,
          '[PUPIL_AGE]',
          TO_CHAR (TRUNC (MONTHS_BETWEEN (SYSDATE, pupil.birthday) / 12)));
      bad_estimate_template :=
        REPLACE (bad_estimate_template,
                 '[FAVOURITE_SUBJECT]',
                 pupil.favorite_subject);

      estimate_template :=
        REPLACE (estimate_template, '[GOOD_ESTIMATE_TEMPLATE]', NULL);
      estimate_template :=
        REPLACE (estimate_template,
                 '[BAD_ESTIMATE_TEMPLATE]',
                 bad_estimate_template);

      conclusion_template :=
        REPLACE (conclusion_template, '[GOOD_CONCLUSION_TEMPLATE]', NULL);
      conclusion_template :=
        REPLACE (conclusion_template,
                 '[BAD_CONCLUSION_TEMPLATE]',
                 bad_conclusion_template);
    END IF;

    average_score_template :=
      REPLACE (average_score_template,
               '[HIS]',
               CASE WHEN pupil.sex = 'M' THEN 'His' ELSE 'Her' END);
    average_score_template :=
      REPLACE (average_score_template,
               '[AVARAGE_SCORE]',
               TO_CHAR (pupil.average_score));

    common_template :=
      REPLACE (common_template, '[HEADER_TEMPLATE]', header_template);
    common_template :=
      REPLACE (common_template, '[ESTIMATION_TEMPLATE]', estimate_template);
    common_template :=
      REPLACE (common_template,
               '[AVERAGE_SCORE_TEMPLATE]',
               average_score_template);
    common_template :=
      REPLACE (common_template, '[CONCLUSION_TEMPLATE]', conclusion_template);

    pupil_info := common_template;
  END get_pupil_info;
BEGIN
  get_pupil_info (pupil => pupil, pupil_info => pupil_info);
  DBMS_OUTPUT.put_line (pupil_info);
END;

Метод подшаблонов простой, но неочевидный 🤔
Я, например, его не придумал —
а встретил у коллег лет 5 назад, и он мне понравился.

👍 Интересный подход!
👎 Это каждый школьник знает!

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

💬 Эмоции, мысли, страхи, анекдоты туда!👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL

✨ «Так всех нас в трусов превращает мысль,
И вянет, как цветок, решимость наша
В бесплодье умственного тупика,
Так погибают замыслы с размахом,
В начале обещавшие успех,
От долгих отлагательств.» — Вильям Шекспир (перевод Бориса Пастернака)

Задача на пять минут или есть ещё порох в пороховницах?
Сколько существует способов строки превратить в столбцы?
И какой способ наиболее оптимальный?

Вот представим: есть таблица

CREATE TABLE test
(
  name   VARCHAR2 (100),
  amount NUMBER
);

INSERT INTO test (name, amount) VALUES ('name1', 1);
INSERT INTO test (name, amount) VALUES ('name2', 2);
INSERT INTO test (name, amount) VALUES ('name3', 3);
COMMIT;

Как нам строки 'name1', 'name2', 'name3' превратить в столбцы? 🔄

name1_amount  name2_amount  name3_amount
     1              2              3

Есть же конструкция PIVOT.
Да, есть. Но если этих строк много?
Оптимальна ли она? ⚖️

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

💬 Каждый коммент идет в общую копилку!👇
#RealInterviewTasks #Cases #SQL #Oracle #PLSQL