Пользовательские исключения - актуальны для конкретного приложения. Определяются разработчиком.

Два вида пользовательских исключений:

1. Объект типа EXCEPTION
Создаем объект типа EXCEPTION. Работаем с ним как будто оно именованное системное исключение.

Полезен для серверного кода.

Пример:

declare
  e1 exception;
begin
  raise e1;
end;
/

2. Процедура raise_application_error

В необходимом месте вызываем процедуру raise_application_error с параметрами:
- код ошибки (от -20 999 до -20 000)
- сообщение об ошибке

Полезен для выдачи в вызывающую среду.

Пример:

begin
  raise_applcation_error(-20100, ‘Ошибка’);
end;
/

Для пользовательских исключений зарезервирован диапазон -20 999 до -20 000. Raise_application_error можно отнести также к способу возбуждения исключения.

#plsql #исключения

Задача: Требуется получить стек вызова в конкретной точке PL/SQL-блока и вывести в буфер вывода.

PL/SQL-код:
begin
dbms_output.put_line(...);
end;
/

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

#sql #задача

Задача: Требуется получить стек вызова в конкретной точке PL/SQL-блока и вывести в буфер вывода.

Принцип решения:
1. Пакет dbms_utility
dbms_utility.format_error_backtrace
dbms_utility.format_call_stack
dbms_utility.format_error_stack

Основной инструмент при получении информации о стеке

2. Пакет utl_call_stack (с 12с)
Набор процедур для получения свойств стека на определенном уровне глубины (ошибки, имя модуля и т.п.).
Применяется для расширенной работы со стеком.

Для решения данной задачи, воспользуемся dbms_utility.

PL/SQL-код:

begin
dbms_output.put_line(dbms_utility.format_call_stack());
end;
/

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

#plsql #решениезадачи #стеквызова

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

Многие разработчики пренебрегают этой полезной практикой. Хорошо, если свой код протестировали хотя бы вручную.
Хотите, вывести ваш продукт на более качественный уровень? Применяйте Unit-тестирование.

Как организовать Unit-тестирование в СУБД Oracle?

Для этого был написан замечательный фреймворк utPL/SQL.
Достаточно удобный инструмент для написания тестов в БД. Есть подробная документация.

У нас в команде принято покрывать Unit-тестами на уровне БД, только те части приложения, которые невозможно покрыть из среднего слоя. Например, API-пакеты, которые вызываются из других схем.

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

Если наберем 100 👍🏻, то запилю видос про Unit-тестирование в СУБД.

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

Всем привет! Как и обещал, я записал видео про исключения в 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 #решениезадачи