Задача: Требуется возвратить разницу между двумя датами в секундах, минутах, часах.

Запрос:

select ... diff_in_sec
       ... diff_in_min
       ... diff_in_hours
  from (select to_date('01.01.2020 12:00', 'dd.mm.YYYY hh24:mi') date2
              ,date '2020-01-01' date1
          from dual);

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

#sql #задача

Oracle Live SQL - онлайн инструмент от Oracle для выполнения SQL-запросов, написания PL/SQL-кода.

Если у вас нет под рукой БД, а вам необходимо где-то выполнить запрос, то этот инструмент вполне подойдет. Например, задачки из моего канала, можно выполнять в нем💡

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

Вход осуществляется под Oracle-учеткой. Регистрация проста и бесплатна.

Live SQL работает в Oracle Database 19c Enterprise Edition - 19.5.0.0.0.

Если вам интересно могу записать видео про этот сервис и как его использовать👍🏻

#ide

Задача: Требуется возвратить разницу между двумя датами в секундах, минутах, часах.

Принцип решения:
В Oracle разница между двумя датами - количество часов. Следовательно, полученное значение можно выразить в секундах, минутах, используя умножение.

Запрос:

select (date2 - date1) * 24 * 60 * 60 diff_in_sec
      ,(date2 - date1) * 24 * 60 diff_in_min
      ,(date2 - date1) diff_in_hours
  from (select to_date('01.01.2020 12:00', 'dd.mm.YYYY hh24:mi') date2
              ,date '2020-01-01' date1
          from dual);

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

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

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

Назначение меток:
1. Для понимания кода - циклы в циклах в циклах.
2. Для уточнения имени переменных.
3. Для использования в exit/continue для указания конкретного цикла.

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

Примеры:

{{main_loop}}
for i in 1..10 loop
   
   {{inner_loop}}
   for j in 1..10 loop
      if (...) then
          continue main_loop;
      elsif(...) then
         exit main_loop;
      end if; 
   end loop;

end loop;
/

К сожалению, телеграм-разметка никак не хочет пропускать угловые скобки 😒 Я заменил их на фигурные. Смотрите скриншот, как правильно использовать синтаксис.

#plsql #теория #циклы

Исключения — ситуации, которые не должны возникать при нормальном выполнении программы.
PL/SQL предоставляет достаточно мощный и гибкий механизм перехвата и обработки ошибок (блок обработки исключений).

Документация по исключениям. - тут (18с).

Раздел исключений - раздел в котором обрабатываются исключения, возникшие в исполняемом разделе.

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

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

PL/SQL-блок:

begin
   -- здесь спать 10 секунд  
end;
/

Смотрите готовый PL/SQL-код в посте в четверг 🎓

#plsql #задача

Из чего состоят исключения

Исключение в общем виде состоит из двух частей:
- код ошибки (строка-число)
- сообщение об ошибки

Код состоит из двух частей: строка - число. Строка - определяет к какой части/функционалу/компонентам СУБД относится исключение. Отрицательное число - номер ошибки внутри части определенной строкой.
Сообщение - краткое информативное сообщение о сути проблемы. Может содержать шаблон, в котором в run-time подставляется значения.

В документации подробно разжевано: причины и как можно исправить.

Примеры:
-- ORA - часть отвечающая за ядро СУБД, “-00001” - уникальный номер ошибки внутри ядра.
1) ORA-00001: unique constraint (string.string) violated

-- Ошибка в утилите экспорта. “-00002” - уникальный номер ошибки внутри этой утилиты
2) EXP-00002: error in writing to export file

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

Задача: Требуется, используя стандартный механизм СУБД, остановить (заснуть) выполнение 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