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

Уровни изоляции транзакций

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

1. DIRTY READ или READ UNCOMMITED - сессия, может читать данные грязные/не зафиксированные другой сессии.
2. READ COMMITED - сессия, видит только зафиксированные данные других сессий. Увидит как новые строки, так и изменения в существующих.
3. REPEATABLE READ - сессия, видит только зафиксированные данные других сессий. Увидит только новые строки, изменения внутри строк - нет.
4. SERIALIZABLE - полностью изолированная среда. Не увидит, вообще никаких изменений других сессий.

В Oracle используется только READ COMMITED (по умолчанию) и SERIALIZABLE (через команду set transaction).

На практике, SERIALIZABLE используется крайне редко.

Достаточно, частный вопрос на собеседованиях: какие уровни изоляции транзакций вы знаете?

#теория #собеседование #транзакции

Сегодня будет немного необычная задачка в формате голосования 👍🏻

Какое из выражений вернет числовое значение?
1. select add_months(max(hire_date),6) from emp;
2. select round(hire_date) from emp;
3. select sysdate – hire_date from emp;
4. select to_number(hire_date+7) from emp;

Рекомендую попытаться ответить без выполнения в СУБД.

Смотрите объяснения в посте в четверг 🎓

Перезалил с удобным форматированием.

#sql #задача

Unit-тестирование в СУБД Oracle

Всем привет!
Наконец-то, свершилось 🙂
Я записал видео про Unit-тестирование в СУБД Oracle 🔥

В нем, я расскажу, что такое Unit-тестирование на уровне СУБД, какой фреймворк использовать, рассмотрим примеры.
Материал будет полезен всем кто хочет повысить уровень надежности приложений в СУБД Oracle.

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

Приятного просмотра!

🎥 Смотреть видео - 16 мин

#видео #utplsql #тестирование

Задача: Какое из выражений вернет числовое значение?

Решение:
Разберем каждый пункт.

1) select add_months(MAX(hire_date),6) from emp;
Берем максимальную дату в таблице (max) и прибавляем 6 месяцев (add_months). Результат - тип date.

2) select round(hire_date) from emp;
На практике, функция round (округление) не применяется к датам. Однако, ошибки не будет. Значение hire_date будет округлено либо в меньшую сторону (если время до обеда), либо в большую сторону (если время после обеда). Результат - тип date.

3) select sysdate – hire_date from emp;
Арифметика дат. Разница между датами (тип date) - это количество дней. Результат - тип number.

4) select to_number(hire_date+7) from emp;
+7 к date мы сделать сможем, это будет +7 дней. Но вот применить функцию to_number нельзя. Даты в Oracle хранятся в своем внутреннем формате, это не Unix timestamp с секундами от 1970 года. Результат - исключение.

Итого: правильный ответ- пункт 3.

⚠️Разница между timestamp - интервал.

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

You Asked

Как получить DDL скрипт партиционированной таблицы и индексов в Oracle?

and we said...

Используйте IDE, например, SQL Developer.
Или функцию dbms_metadata.get_ddl.

Например:

select dbms_metadata.get_ddl(object_type => 'TABLE', name => 'MYTAB') ddl
  from dual; 

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

#asktom #dbms_metadata