Многотабличные команды INSERT

Многотабличные команды INSERT могут использоваться в системах поддержки хранилищ данных для передачи информации из одного или более источников данных в набор целевых таблиц.

Такие команды обеспечивают существенное повышение производительности за счет:
▫️использования одной команды DML вместо нескольких команд INSERT...SELECT ;
▫️использования одной команды DML вместо процедуры, в которой выполняются вставки при условии (IF...THEN).

Виды:
▫️INSERT ALL - вставка идет во все таблицы в запросе.
▫️INSERT FIRST - вставка идет при первом совпадении с условием.

Поскольку мало кто знает про данный вид операции INSERT, постараюсь подробно рассмотреть их в следующих постах.

#sql #insert

Задача: К какому результату приведет выполнения pl/sql-кода?
При условии, что дисковое пространство бесконечно, память бесконечна, на колонке col1 - нет никаких ограничений и т.д.

PL/SQL код:

declare
  i number :=1;
begin
  loop
    insert into table1(col1) values (i);
    commit;
    if i=8 then exit; end if;
    dbms_output.put_line(‘Value ’|| i || ‘!’);
  end loop;
end;
/

💡Кстати, это реальный пример из задачки на собеседовании, которую нужно решить “в уме”. Поэтому рекомендую не пользоваться СУБД, а постараться решить самим.

Смотрите решение в посте в четверг 🎓

#plsql #задача

INSERT ALL

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

1) Безусловная вставка - вставка происходит во все таблицы сразу.

2) Вставка с условием - вставка происходит только тогда, когда соблюдается указанное условие.

Синтаксис смотрите на скриншоте.

❗️Поведение последовательностей не всегда то, которое мы ожидаем.
NEXTVALUE будет вызываться для каждой обрабатываемой строки вне зависимости от ситуации (подходит или нет условие). Аккуратно работаем с этим.
Выход из ситуации: работа через pl/sql-процедуру c pragma udf.

#sql #insert

Задача: к какому результату приведет выполнения pl/sql-кода (Col1 - без ограничений)?
PL/SQL-код смотрите в посте вторника.

Принцип решения:
1. Это бесконечный цикл, т.к. i нигде не инкрементируется.
Особой беды в этом нет, кроме того что в промышленном коде это может вызвать проблемы.
2. Поскольку ограничений на колонке col1 нет, в неё можно бесконечно вставлять одинаковые значения.
3. Ресурсы безграничны - не закончится ни диск, ни память и т.д.
И даже, если бы они были не бесконечны этот код сломается гораздо раньше, чем закончатся ресурсы самой простой машины.

Суть в том, что dbms_output.put_line выводит информацию в буфер вывода. А вот он то как раз и не безграничен. По умолчанию он равен 10000 байт. Соответственно, не пройдет и пары секунд, как этот буфер будет переполнен и произойдет ошибка
ORA-20000: ORU-10027: buffer overflow, limit of *** bytes

Ответ:
Будет ошибка переполнения буфера вывода.

Надеюсь, вам понравилась задачка 😉

#plsql #решениезадачи

Откуда вообще взялась такая задача на собеседовании?

Это жизнь и грабли в ней.

Разработчик, желая отладить свой код вставляет вывод в буфер. Далее, невнимательно мерджит исходники, после чего это уходит в ПРОД. Фактически, бомба замедленного действия. Будет ли переполнен буфер или нет, зависит от программы. Стрельнуть может не сразу, а например, ночью 😊

Потому и задают такие вопросы на собеседованиях 🎓

INSERT FIRST

Вставка будет осуществляться в первую таблицу, удовлетворяющую условиям.

Синтаксис смотрите на скриншоте.

❗️Поведение последовательностей не всегда то, которое мы ожидаем.
NEXTVALUE будет вызываться для каждой обрабатываемой строки вне зависимости от ситуации (подходит или нет условие). Аккуратно работаем с этим.
Выход из ситуации: работа через pl/sql-процедуру c pragma udf.

#sql #insert

MERGE

Команда MERGE совмещает в себе сразу три операции:
- INSERT, если в таблице нет записи удовлетворяющей условиям.
- UPDATE, если в таблице есть записи удовлетворяющие условиям.
- DELETE, если в таблице есть записи удовлетворяющие условиям.

Особенности:
- Позволяет избежать внесения отдельных изменений.
- Повышает производительность и упрощает использование.
- Эффективна для приложений, работающих с хранилищами данных.
- Можно опускать некоторые части - insert/update/delete.

Синтаксис:

смотрите скриншот

Используется часто. Знать надо.

Поведение последовательностей не всегда то, которое мы ожидаем.
NEXTVALUE будет вызываться для каждой обрабатываемой строки вне зависимости от ситуации (подходит или нет условие). Аккуратно работаем с этим.
Выход из ситуации: работа через pl/sql-процедуру c pragma udf.

Не забывайте называть MERGE, когда у вас спрашивают команды DML в Oracle.

#sql #merge

Задача: Какая строка содержит ошибку?

1. SELECT T1.ID NUM, T1.VAL AS NAME
2.   FROM TABLE1 T1
3.  WHERE NUM > 13042
4.  ORDER BY NUM;

💡Кстати, это реальный пример из задачки на собеседовании, которую нужно решить “в уме”. Поэтому рекомендую не пользоваться СУБД, а постараться решить самим.

Смотрите ответ в посте в четверг 🎓

#sql #задача

TRUNCATE

Команда TRUNCATE является DDL командой, со всеми вытекающими моментами. Тем не менее, нужно помнить, что это самый быстрый способ очистки таблицы.

Особенности:
- Удаляет все строки из таблицы, оставляя таблицу пустой с сохранением структуры таблицы
- Перед выполнением происходит фиксация текущей транзакции.
- Так как это команда языка определения данных (DDL), а не языка DML, ее действие нельзя отменить простым образом.
- Если есть включенные FK, воспользоваться ей не получится.

Синтаксис:

truncate table имя_таблицы;

Очень часто используется. Знать необходимо.

Если вам зададут вопрос на собеседовании “какой самый быстрый способ очистки таблицы”, смело отвечайте командой truncate с учетом её особенностей 🎓

#sql #truncate

Задача: Какая строка содержит ошибку?

1. SELECT T1.ID NUM, T1.VAL AS NAME
2.   FROM TABLE1 T1
3.  WHERE NUM > 13042
4.  ORDER BY NUM;

Принцип решения:
Алиас, дается столбцу, практически на финальном этапе выполнения запроса. Его можно использовать только в конструкции order by. Таким образом, ошибка будет на 3й строке в условии where. Алиас NUM пока не присвоен.

Правильно написать: t1.id > 13042

Надеюсь, вам понравилась задачка 😉

#sql #решениезадачи

Инструкция LOG ERRORS

Если DML-операция при массовой обработке данных вызывает ошибки при добавлении или изменении, какой либо записи, то вся операция DML будет отменена.

Инструкция LOG ERRORS, предназначена для обхода подобных ситуаций. Если в процессе выполнения DML операции возникает ошибка, то информация о ней запишется в заранее созданную (при помощи пакета DBMS_ERRLOG) таблицу логов, на которую указывает конструкция LOG ERRORS.

Таблица для логирования ошибок, создается через процедуру:

dbms_errlog.create_error_log(‘название_исходной_табл, ‘название_табл_ошибок’);

Пример применения с INSERT:

insert into data_tab
select ...       
  from ….
  log errors into error_log_data_tab reject limit unlimited;

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

Если интересно, могу запилить видос с демонстрацией функционала 🎥

#sql #dml #logerrors

Том Кайт (Tom Kyte)

Томас Кайт - американский специалист в IT, вице-президент Oracle (работал в ней с 1993 г), старший архитектор отдела серверных технологий. Известен как эксперт в области разработки приложений для СУБД Oracle. Автор журнала Oracle Magazine и нескольких бестселлеров по Oracle Database. Самые известные:
- Effective Oracle by Design (Эффективное проектирование приложений Oracle)
- Expert One on One Oracle (Oracle для профессионалов в 2-х томах)
и др.
Рекомендую к прочтению.

Том Кайт - человек-легенда в мире Oracle.

Почему я вдруг про него написал?

Он ведущий популярного сайта AskTom («Спроси у Тома»), посвященного проблемам и методам их решений в Oracle Database. Его сайт содержит тонны вопросов, порой достаточно заковыристых и, самое главное, ответы на них.
У вас есть какой-то вопрос или вы чего-то не понимаете? Прямая дорога на этот сайт.

Я иногда буду публиковать в нашем телеграм-канале, посты с его сайта. Вероятно, это поможет вам в вашей профессиональной деятельности 📚

#asktom

Задача:
Есть ли разница между следующими запросами (col1 содержит различные значения в т.ч. NULL):
1. select count(1) from table1;
2. select count(*) from table1;
3. select count(col1) from table1;

💡Кстати, это реальный пример из задачки на собеседовании, которую нужно решить “в уме”. Поэтому рекомендую не пользоваться СУБД, а постараться решить самим.

Смотрите готовый SQL запрос в решении в посте в четверг 🎓

#sql #задача

You Asked

Мне нужно экспортировать данные из таблицы в файл .csv.
Заголовки моих столбцов должны быть между "". Данные с разделителем ",".
Также в зависимости от значений столбца строка может быть напечатана до 5 раз с данными, отличающимися только в одном поле.
А еще, мне нужно запускать эту программу ежедневно.

and we said...

1. Используйте пакет utl_file для выгрузки данных.
2. Используйте джобы/расписания для периодичности выполнения задания.

Подробности в статье Тома

#csv #plsql #asktom

Задача:
Есть ли разница между следующими запросами (col1 содержит различные значения в т.ч. NULL):
1. select count(1) from table1;
2. select count(*) from table1;
3. select count(col1) from table1;

Принцип решения:
count(*) и count(1) это одно и то же. Результат - количество строк выборки.
count(col1) - подсчитает только количество не NULL значений столбца col1. Это свойство групповых функций.

Если бы колонка “col1” являлась PK, т.е. не может содержать NULL-значений, то можно было бы сказать, что все три выражения эквивалентны. В нашей задаче, это не так.

Хотите получить 100% верное количество строк - используйте count(*) или count(константа).

Надеюсь, вам понравилась задачка 😉

#sql #решениезадачи #groupby

Кстати, насчет разницы в count(*) и count(1).

СУБД Oracle как и любое ПО, написанное человеком, обладает рядом дефектов, багов или, если хотите, особенностей.
Рекомендую обратить ваше внимание на эту статью, если вы используете Oracle 11g.

https://blog.pythian.com/oracle-11g-unexpected-difference-between-count-and-count1/

Задача: как получить список ID измененных записей в запросе UPDATE в переменную-коллекцию v_ids?

Таблица table1 состоит из трех колонок ID (PK), COL1, COL2).

PL/SQL-код:

declare
  type t_ids is table of table1.id%type;
  v_ids t_ids;
begin
  update table1
     set col1 = 2
   where col2 = 'xxx';
  ...
end;
/

Смотрите готовое решение в посте в четверг 🎓

#sql #plsql #задача

You Asked

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

and we said...

Используйте:

dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );

Например:

select dbms_lob.substr( x, 4000, 1 ) from T;

Подробности в статье Тома

#asktom #clob #varchar2

Задача: как получить список ID измененных записей в запросе UPDATE в переменную-коллекцию v_ids?
Таблица table1 состоит из трех колонок ID (PK), COL1, COL2).
Полный код PL/SQL-блока в посте вторника.

Принцип решения:
Команда DML UPDATE имеет дополнительную возможность по возврату полей обновленных записей.
Нам в помощь, конструкция returning … into.
Довольно часто применяется на практике.
Подход с дополнительным запросом на select не применим по разным причинам.

PL/SQL-код:

declare
  type t_ids is table of table1.id%type;
  v_ids t_ids;
begin
  update table1
     set col1 = 2
   where col2 = 'xxx'
  returning id bulk collect into v_ids;
end;

/

Надеюсь, вам понравилась задачка 😉

#sql #plsql #задача #bulkcollect #returning