Всем привет! Как и обещал, я записал видео про исключения в PL/SQL.

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

Материал относится к начальному-уровню.

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

#видео #plsql #исключения

Задача: Написать запрос, который выводит день программиста в текущем году (256 день) в формате день-название_месяца-годTномер_недели

Пример вывода: 12-СЕНТЯБРЬ-2020T37

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

#sql #задача

Групповые функции (GROUP BY)
Групповые функции работают с множеством строк и возвращают один результат на группу.

Синтаксис:

select [столбец, ] [групп_фунция(...),]
  from таблица
where  условие
group by столбец;

Функции:
* AVG - среднее значение
* COUNT - количество
* MAX - максимальное
* MIN - минимальное
* SUM - сумма
* VARIANCE - дисперсия
* STDDEV - среднеквадратическое отклонение

Групповые функции игнорируют неопределенные значения в столбцах (null).

#sql #groupby

Задача: Написать запрос, который выводит день программиста в текущем году (256 день) в формате день-название_месяца-годTномер_недели

Принцип решения:
* sysdate - дата в текущем году.
* trunc(sysdate) - 1 января текущего года.
* + 255 - получаем день программиста. Именно, 255, а не 256, т.к. 1 января это уже первый день.
* to_char + форматная маска - для вывода в требуемом формате.
* nls_date_language - дата в РУ формате.

Запрос:

select to_char(trunc(sysdate, 'YYYY') + 255, 'DD-MONTH-YYYY"T"WW', 'NLS_DATE_LANGUAGE=RUSSIAN')
  from dual;

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

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

Групповые функции. Создание групп данных.

Правила:
* Все столбцы, которые входят в список SELECT и к которым не применяются групповые функции, должны быть указаны в предложении GROUP BY.
* Столбец, указанный в GROUP BY, может отсутствовать в списке SELECT.
* Возможна группировка по нескольким столбцам

Синтаксис:

select [столбец, ] [групп_фунция(...),]
  from таблица
 where  условие
 group by столбец
 order by столбец;

#sql #groupby

SQL – Structured Query Language

Structured Query Language (SQL) — язык структурированных запросов, с помощью него пишутся специальные запросы (SQL инструкции) к базе данных с целью получения этих данных из базы и для манипулирования этими данными.

Язык SQL представляет собой набор операторов, которые делятся на определенные группы и у каждой группы есть свое назначение. В сокращенном виде эти группы называются DDL, DML, DCL и TCL.

Data Definition Language (DDL) – это группа операторов определения данных.

Data Manipulation Language (DML) – это группа операторов для манипуляции данными.

Data Control Language (DCL) – группа операторов определения доступа к данным.

Transaction Control Language (TCL) – группа операторов для управления транзакциями.

Очень частый вопрос на собеседованиях "какие группы команд вы знаете?".

О некоторых из них будет информация в следующих постах 😉

#теория #sql #собеседование

DDL – Data Definition Language

Data Definition Language (DDL) – это группа операторов определения данных. Другими словами, с помощью операторов, входящих в эту группы, мы определяем структуру базы данных и работаем с объектами этой базы, т.е. создаем, изменяем и удаляем их.

В эту группу входят следующие операторы:
▫️CREATE – используется для создания объектов базы данных;
▫️ALTER – используется для изменения объектов базы данных;
▫️DROP – используется для удаления объектов базы данных.

А еще, операторы DDL в Oracle фиксируют транзакцию (commit). Сколько граблей было собрано людьми, которые этого не знают.
В PostgreSQL, кстати, не фиксируют 😊

Довольно, частый вопрос на собеседованиях: "Какие команды DDL вы знаете?"🎓

#теория #sql #собеседование

DML – Data Manipulation Language

Data Manipulation Language (DML) – это группа операторов для манипуляции данными. С помощью этих операторов мы можем добавлять, изменять, удалять и получать данные из базы, т.е. манипулировать ими.

В эту группу входят самые распространенные операторы языка SQL:
▫️SELECT – осуществляет выборку данных;
▫️INSERT – добавляет новые данные;
▫️UPDATE – изменяет существующие данные;
▫️DELETE – удаляет данные.

Когда кандидаты перечисляли мне на собеседованиях эти операторы, я спрашивал "знаете ли вы еще операторы DML в Oracle?"
Примерно, половина кандидатов забывала про немаловажный оператор MERGE.

▫️MERGE – комбинация из INSERT, UPDATE, DELETE

Будете проходить собеседование, не забудьте про него 😉

#теория #sql #собеседование

DCL – Data Control Language

Data Control Language (DCL) – группа операторов определения доступа к данным. Иными словами, это операторы для управления разрешениями, с помощью них мы можем разрешать или запрещать выполнение определенных операций над объектами базы данных.

Сюда входят:
▫️GRANT – предоставляет пользователю или группе разрешения на определённые операции с объектом;
▫️REVOKE – отзывает выданные разрешения;

❗️Аналогично операторам DDL, операторы DCL в Oracle фиксируют транзакцию (commit). Имейте это ввиду при написании кода.

Примеры:

grant select on tab1 to user;
grant select, insert on tab1 to user1;
grant all on tab1 to user;
grant execute on proc1 to user1;
revoke execute on proc1 from user1;

#теория #sql #собеседование

Задача: что вернет запрос в конце блока кода, при условии, что таблица table1 была пустой до выполнения.

Код:

insert into table1(id) values(1);
insert into table1(id) values(2);
drop table another_my_table2;
rollback;
select * from table1;

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

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

#sql #задача

TCL – Transaction Control Language

Transaction Control Language (TCL) – группа операторов для управления транзакциями. Transaction Control Language (TCL) – группа операторов для управления транзакциями. Транзакция – это команда или блок команд (инструкций), которые успешно завершаются как единое целое, при этом в базе данных все внесенные изменения фиксируются на постоянной основе или отменяются.

Группа операторов TCL предназначена как раз для реализации и управления транзакциями.

Сюда можно отнести:
▫️COMMIT – применяет транзакцию;
▫️ROLLBACK – откатывает все изменения, сделанные в контексте текущей транзакции;
▫️SAVEPOINT – устанавливает промежуточную точку сохранения внутри транзакции.
▫️SET TRANSACTION - установка свойств транзакций (read only, уровень транзакций и др). Так же применяется для явного начала транзакции.

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

#теория #sql #собеседование

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

Код:
смотрите в посте вторника.

Принцип решения:
Разберем пошагово.
1. Происходит две вставки в таблицу. Неявно создается транзакция. И она не зафиксирована.
2. Дальше происходит удаление другой таблицы. DROP - это оператор DDL, который неявно оборачивается commit до и после выполнения.
Таким образом, на этом шаге происходит фиксация (commit) нашей транзакции.
3. rollback - ничего не делает. Откатывать нечего.
4. select возвращает содержимое таблицы table1. До выполнения она была пустой и в процессе выполнения было вставлено две строки.

Ответ:
Вернет две строки с id=1, id = 2.

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

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

Многотабличные команды 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