Задача: Требуется, используя стандартный механизм СУБД, остановить (заснуть) выполнение PL/SQL программы.

Принцип решения:
Для того чтобы остановить выполнение программы на N-секунд, используются функции в системных пакетах:
- dbms_lock.sleep(N) - для версии ниже 12с.
- dbms_session.sleep(N) - для версии 12с и выше.

N - может быть как целым, так и дробным. Например, 0.1.

PL/SQL-блок:

begin
   -- спать 10 секунд
  dbms_session.sleep(10);
end;
/

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

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

Виды исключений

1. Системные - определены в Oracle, инициируются ядром PL/SQL.

В свою очередь разделяются на:
- именованные
- неименованные

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

Разделяются на:
- объект exception
- raise_application_error

Если интересна тема с исключениями в PL/SQL могу записать видео. Ставь палец вверх, если интересно 👍🏻

#plsql #теория #исключения

Задача: Требуется получить список всех процедур/функций в пакете, в текущей схеме.

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

#sql #задача

Всем привет!
Как и обещал, я записал видео про сервис Oracle Live SQL 😉

Что в нем будет:
* немного о сервисе, его преимущества;
* регистрация/вход в сервис;
* написание простых запросов и использование предустановленных схем;
* загрузка и сохранение своих скриптов;
* использование библиотек скриптов от сторонних разработчиков.

После просмотра, вы вполне сможете пользоваться сервисом 👌🏻

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

#видео #plsql #ide #oraclelivesql

Задача: Требуется получить список всех процедур/функций в пакете, в текущей сехе.

Принцип решения:
В СУБД Oracle существует набор системных представлений, из которых можно получить очень разную информацию. Фактически при наличии прав, можно получить любую информацию об объектах, работе СУБД и др.
Для решения данной задачи, воспользуемся некоторыми из них.

Запросы:

select *
  from user_procedures t
 where t.object_name = 'НАЗВАНИЕ_ПАКЕТА'
   and t.subprogram_id <> 0
 order by t.subprogram_id;

или

select *
  from all_procedures t
 where t.object_name = 'НАЗВАНИЕ_ПАКЕТА'
   and t.subprogram_id <> 0
   and t.owner = user
 order by t.subprogram_id;

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

#sql #решениезадачи #системныепредставления

Системные исключения

По умолчанию в Oracle существуют тысячи системных исключений почти на любую исключительную ситуацию. В документации можно всегда уточнить по коду исключения подробности и методы решения.

Системные исключения делятся на два типа
1. Неименованные - не имеют какого-то определенного названия

Примеры:
* ORA-06502: PL/SQL: numeric or value error: number precision too large
* ORA-06513: PL/SQL: index for PL/SQL table out of range for host language array
* ORA-00021: session attached to some other process; cannot switch session

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

2. Именованные - наиболее часто возникающие исключения имеют фиксированное имя. Их около 30 (пакет sys.dbms_standard). Именованные являются подмножеством неименованных.

Примеры:
* NO_DATA_FOUND - не найдены данные
* TOO_MANY_ROWS - слишком много строк
* ZERO_DIVIDE - деление на “0”.
* DUP_VAL_ON_INDEX - нарушение ограничения уникальности
* INVALID_NUMBER - ошибка преобразование
и т.д.

Можно возбуждать исключение через это имя.

Подробности в видео про исключения 🎥

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

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

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

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 #задача