Автономные транзакции (autonomouse_transaction, АТ)

АТ позволяют создавать новые подтранзакции, которые можно сохранять или отменять вне зависимости от родительской. Достигается это путем указания специальной директивы PL/SQL компилятору:

pragma autonomous_transaction;

Автономную транзакцию можно использовать в любом PL/SQL-блоке (анонимном блоке, функции, процедуры, триггеры).
АТ - обязательно необходимо завершить оператором фиксации/отмены - commit/rollback.

Когда стоит применять:
1. Логирование/аудит - запись действий будет зафиксирована, даже если в родительской транзакции произойдет откат.
2. Выполнение DDL операторов - используется, когда влияние на текущую транзакцию недопустимо или необходимо изменять данные в операторах SQL (что не очень хорошая практика).

#теория #транзакции

Выбираем IDE для работы с Oracle

IDE - интегрированная среда разработки

1. PLSQL Developer - на мой взгляд, одна из удобнейших средств разработки PL/SQL-кода. Интерфейс комфортен, все под рукой, ничего лишнего, управлять объектами одно удовольствие. Можно настроить автоматическое форматирование кода. Недостаток работает только в Windows. Платная. Лично я использую только эту IDE 😌

2. Quest Toad (лягушка) - занимает второе место в моем рейтинге. Отличный инструмент для управления БД, т.е. в большей степени подходит DBA, но я знаю некоторых разработчиков, которые только в ней и работают. Платная.

3. Oracle SQL Developer - отношения с этой IDE у меня не сложились, в виду крайне не удобного интерфейса, редактора и огромного количества странностей. Единственные плюсы - бесплатна и есть версии под все ОС.

4. JetBrains Datagrip - создана в стиле всех продуктов от JetBrains. Многие Java-разработчики используют именно её ввиду кроссплатформенности и привычности интерфейса. На мой взгляд, пока сыровата, доступ к объектам не очень удобен. Если команда JetBrains приложит достаточно усилий по её доработке, однозначно займет пальму первенства.
Платная.

5. DBeaver - для кого-то может оказаться "золотой серединой". Достаточно удобная IDE, заточена под разные СУБД, в меру удобный редактор кода и объектов. Я бы сказал, что-то между Oracle SQL Developer и PL/SQL Developer. Есть платная и бесплатная версии. Лично я использую DBeaver для работы с PostgreSQL. Однозначно рекомендую попробовать, тем более что есть версии под все ОС.

Так какую же IDE выбрать? Я думаю, стоит попробовать каждую, написать пару пакетов, создать несколько десятков таблиц и сделать свой выбор 😉

#ide

ORA-01555: Snapshot too old

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


Решение:
1. Увеличивать параметр undo_retention - сколько держать принудительно в UNDO старые блоки при возможности расширяться.
2. Читать меньшими по продолжительности порциями. Например, в цикле пачками.
3. Увеличиnm табличное пространство UNDO для хранения большего количества измененных данных

Таким образом, ошибку можно решить как со стороны DBA, так и со стороны DBD.

#теория #ошибки_oracle

Задача: Требуется разложить текущую дату на шесть частей: день, месяц, год, секунды, минуты и часы. Результаты должны быть возвращены в численном виде.

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

#sql #задача

Функция EXTRACT

Oracle SQL функция EXTRACT извлекает значение из типов date и timestamp [with timezone].
Временную составляющую можно извлечь только из timestamp.
Иногда бывает очень полезна, в виду своей лаконичности и простоты.

Примеры:

--Результат:  2020
select extract(year from date '2020-03-13')
  from dual;

--Результат:  3
select extract(month from date '2020-03-13')
  from dual;

--Результат:  номер дня текущей даты
select extract(day from sysdate)
  from dual;

--Результат:  2
select extract(minute from timestamp '2020-03-13 10:02:03')
  from dual;

#sql #функции #extract

Задача: Требуется разложить текущую дату на шесть частей: день, месяц, год, секунды, минуты и часы. Результаты должны быть возвращены в численном виде.

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

В данном случае используем функции TO_CHAR и TO_NUMBER.

Запрос:

select to_number(to_char(sysdate, 'dd')) day
      ,to_number(to_char(sysdate, 'mm')) month
      ,to_number(to_char(sysdate, 'yyyy')) year
      ,to_number(to_char(sysdate, 'hh24')) hour
      ,to_number(to_char(sysdate, 'mi')) min
      ,to_number(to_char(sysdate, 'ss')) sec
  from dual

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

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

Таблица DUAL

Таблица DUAL - специальная таблица для выполнения операций. В Oracle нельзя из ниоткуда получить данные, поэтому специальная таблица доступная каждому пользователю через которую можно выполнять выражения.
Заполнена всегда одной строкой со значением “X”.

Определение:

create table sys.dual
(
  dummy varchar2(1)
);
grant select on sys.dual to public with grant option;

Использование:

select функция(...) from dual;

Например, получить текущее время и пользователя БД

select sysdate, user from dual;

🚫 Удалить данную таблицу нельзя.

ℹ️ При переезде на PostgreSQL, специально создают таблицу DUAL, т.к. там не нужна таблица для выполнения выражений, а множество запросов в Oracle используют DUAL.

#sql #dual

Псевдостолбец ROWNUM

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

Порядок выполнения запроса:
1. Выборка и фильтрация (FROM/WHERE)
2. Присвоение к каждой получившейся строки номера ROWNUM.
3. SELECT
4. GROUP BY
5. HAVING
6. ORDER BY

⚠️Таким образом, ROWNUM не порядковый номер финального результата.

Если нужен именно финальный, то:
- либо оборачиваем в запрос-обертку. Сортировка во внутреннем запросе.
- либо используем аналитическую функцию row_number() over (sort by ...) с требуемой сортировкой.

Пример 1. Запрос вернет 5 строк и уже после этого отсортирует результат.
❗️Очень частая ошибка. Разработчик хотел получить первых 5 строк отсортированных по salary.

select * 
  from employees
 where rownum <= 5 
 order by salary desc;

Пример 2. Пропуск первой строки. Не даст никаких результатов вообще (неправильный запрос).

select *
  from employees
  where rownum > 1;

Пример 3. Пропуск первой строки. Даст верный результат.

select *
 from (select rownum rn,
                     a.*
         from (select *
                     from employees e
                    order by e.hire_date) a)
where rn > 1;

#sql #rownum

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

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

#sql #задача

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

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

Итого. Для формирования четырех строк используем системное представление all_objects. Для представления квартала, которому соответствуют та или иная начальная и конечная даты, используем псевдостолбец ROWNUM (номер строки).
С помощью функции ADD_MONTHS находим начальную и конечную даты каждого квартала.

Запрос:

select rownum qtr
      ,add_months(trunc(sysdate, 'y'), (rownum - 1) * 3) q_start
      ,add_months(trunc(sysdate, 'y'), rownum * 3) - 1 q_end
  from all_objects
 where rownum <= 4;

Запрос можно усовершенствовать, если использовать в качестве генерации четырех строк иерархический запрос.

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

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

Генерация последовательностей значений от 1 до N

Очень часто для генерации N-строк в Oracle используются иерархические запросы.
Конструкция - connect by.

Проще всего показать на примере:

select level id
  from dual
connect by level <= 4;

Данный запрос сгенерирует четыре строки со столбцом num, в котором будет значение от 1 до 4х.

Генерация до десятка миллионов строк на обычной машине вполне возможна.

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

Теперь вы знаете, что ответить на вопрос на собеседовании - "Как сгенерировать строки в Oracle одним запросом?" 🤓

#sql #иерархическиезапросы #собеседование

Курсоры

Курсор - область данных PGA в private SQL Area (результирующий набор строк), заполненную в результате выполнения SQL-оператора. Некоторая информация записывается в SGA.

Виды курсоров:
Явные - мы сами определяем его в разделе объявления PL/SQL-блока, открываем его, выбираем из него данные, закрываем. Применимо к командам select.

Неявные - автоматически создаются, выбираются данные и закрываются СУБД. Фактически, это все команды, которые выполняются в БД.

У курсоров есть атрибуты.
Есть специальные курсорные переменные - это указатели на курсоры, их можно передавать между программами.
Подробности в следующих постах.

Теперь вы знаете, что ответить на вопросы на собеседовании - "Что такое курсор? Какие виды курсоров бывают?" 🤓

#собеседование #курсоры

Задача: Требуется разбить на страницы или обеспечить возможность «прокрутки» результирующего множества. Например, необходимо возвратить первые пять заработных плат из таблицы EMPLOYEES (сотрудники), затем следующие пять и т. д.

Наша цель – предоставить пользователю возможность просматривать по пять строк, переходя к следующей порции результатов по нажатию кнопки «Далее».


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

#sql #задача

Курсорные переменные

Курсорная переменная - ссылка на область с результирующим набором.

Свойства:
1. Курсорные переменные обладают теми же атрибутами как и обычный курсор. Курсорная переменная - обычная переменная.
2. На один курсор может смотреть более 1 курсорной переменной (как ссылки на объекты).
3. Курсор активен пока на него ссылается хотя бы одна активная ссылка (курсорная переменная).

Типы курсорных переменных:
1. Пользовательские - определяются разработчиком как новый тип. Используется, если нужна сильная типизация результата.
2. Системные - универсальный тип sys_refcursor. Используется в большинстве случаев.

Когда использовать:
1. Курсорную переменную можно связывать с разными курсорами. Помогает в разработке гибких приложений.
2. Передавать результаты в вызывающую среду (в т.ч. внешнюю), вместо коллекций.

#курсоры #sys_refcursor

Задача: Требуется разбить на страницы или обеспечить возможность «прокрутки» результирующего множества.
Полное условие в посте прошлого вторника.

Принцип решения:
Поскольку в SQL нет понятия первой, последней или следующей строки, необходимо каким-то образом упорядочить рассматриваемые строки. Только упорядочив их, мы сможем возвращать строки строго соответственно диапазонам значений.
С помощью аналитической функции ROW_NUMBER OVER упорядочиваем строки. В предикате WHERE определяем окно записей, которое требуется возвращать.

Например, чтобы возвратить строки от 1 до 5:

select employee_id
      ,salary
  from (select row_number() over(order by salary) rn
              ,employee_id
              ,salary
          from employees) x
 where rn between 1 and 5;

Можно выбрать любой диапазон строк, просто меняя предикат WHERE внешнего запроса.

Есть, как минимум, еще два способа решить задачу, но об этом в следующий раз 🕰

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

#sql #решениезадачи #аналитическиефункции

Установка СУБД Oracle Express Edition 18 (XE) в Docker

Теория без практики - ничто.
А где практиковаться? Да в Докере, конечно! 💡

В этом ролике я рассказал, как легко и просто развернуть Oracle 18 Express Edition используя Docker.

За 15 лет работы, я ставил СУБД на разных виртуальных платформах, но установка в Docker'e самая простая и самая доступная.

Почему Oracle 18XE? По разным причинам. Например, эта версия обладает всеми опциями Enterprise Edition, а занимает ресурсов гораздо меньше.
Идеально для установки в Докер 👌🏻

Ставьте, пользуйтесь! 👍🏻

А впереди еще много интересных видео и материалов 🎓

#видео #docker #oraclexe

Смотреть видео

Атрибуты курсора

Для управления и получения свойств курсора используются атрибуты.

%FOUND - true, если успешно выбрана хотя бы одна строка; в противном случае возвращает false

%NOTFOUND - true, если команда не выбрала ни одной строки; в противном случае возвращает false

%ROWCOUNT - количество строк, выбранных из курсора на данный момент времени

%ISOPEN - true, если курсор открыт; в противном случае возвращает false

%BULK_ROWCOUNT - количество измененных записей для каждого элемента исходной коллекции, заданной в команде forall

%BULK_EXCEPTIONS - информация об исключении для каждого элемента исходной коллекции, заданной в команде forall

Использование в явных курсорах: имя_курсора%имя_атрибута
Использование в неявных курсорах: SQL%имя_атрибута

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

#plsql #теория #курсоры

Пример использования атрибута курсора %ROWCOUNT

Рассмотрим на простом примере. Нам необходимо обновить строчку по primary key, если её нет в таблице our_tab, нужно вставить в таблицу.

Для такого кейса отлично подходит атрибут неявного курсора %ROWCOUNT. После попытки обновить строку, мы проверяем сколько строк было изменено. Если таких строк нет, то производим вставку.

На практике, довольно, часто применяется такой прием, но лишь для тех случаев, когда вероятность update гораздо выше, чем insert.

begin
 
  -- обновляем
  update our_tab
     set ...
   where id = ...;
 
  -- если не обновили, то вставляем
  if sql%rowcount = 0 then
    insert into our_tab ...;
  end if;
   
end;
/

Учтите, что после update не должно быть никаких других команд, например, commit. Атрибут доступен только для предыдущего выполнения.

#plsql #теория #курсоры