WHILE (пока выполняется условие)

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

Пример:

declare
  v_index pls_integer := 1;
begin  
  -- от 1 до 3х
  while v_index <= 3
  loop    
     dbms_output.put_line('simple while: '||v_index);
     v_index := v_index + 1;
  end loop;
end;
/

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

Задача: Есть таблица, в которой две колонки имеют значение по умолчанию. Обе они не допускают вставки NULL-значений.
Необходимо вставить новую строку в эту таблицу, так чтобы эти две колонки заполнились значениями по умолчанию.
В insert нельзя использовать конкретные значения из определения таблицы.

Структура таблицы:
create table tab1
(
id number(30) not null,
ttype number(1) default 0 not null ,
tname varchar2(20 char) default 'none' not null
);

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

#sql #задача

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

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

На всякий случай, скажу, что весь контент на этом канале создается лично мной, в том числе видео. Приятного просмотра 👍🏻

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

#видео #plsql #коллекции #nestedtables

Задача: Есть таблица, в которой две колонки имеют значение по умолчанию. Обе они не допускают вставки NULL-значений.
Необходимо вставить новую строку в эту таблицу, так чтобы эти две колонки заполнились значениями по умолчанию. В insert НЕ использовать конкретные значения из определения таблицы.

Принцип решения:
Ключевое слово DEFAULT в списке значений обеспечивает вставку значения, которое было определено как применяемое по умолчанию во время создания конкретного столбца. Это ключевое слово используется во всех СУБД.

Запрос:

insert into tab1
  (id
  ,ttype
  ,tname)
values
  (1
  ,default
  ,default);

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

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

FOR (цикл со счетчиком)

Классический цикл FOR с начальным и конечным значениями. Нач и конечн значения вычисляются один раз. Переменную индекса объявлять не надо. Конечное значение включается в диапазон.
Синтаксис приведен на скриншоте.
Reverse - обратный проход, причем “начальное значение” - не стартовое, а конечное, “конечное значение” - является стартовым, а не конечным :)

Примеры:

-- Цикл от 1 до 3-х (вкл). Шаг: +1
for i in 1..3 loop
   dbms_output.put_line('simple loop: '||i);
end loop;

-- Цикл от с 3-х до 1-го (вкл). Шаг: -1
for i in reverse 1..3 loop
   dbms_output.put_line('reverse loop: '||i);
end loop;

-- Цикл не выполнится
for i in reverse 3..1 loop
   dbms_output.put_line('reverse loop: '||i);
end loop;

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

WHILE (пока выполняется условие)

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

Пример:

declare
  v_index pls_integer := 1;
begin  
  -- от 1 до 3х
  while v_index <= 3
  loop    
     dbms_output.put_line('simple while: '||v_index);
     v_index := v_index + 1;
  end loop;
end;
/

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

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

Запрос:

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