Курсоры

Курсор - область данных 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 #теория #курсоры

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

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

#sql #задача

Этапы работы с курсорами

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

В зависимости от типа курсора, либо это происходит автоматически, либо выполняем мы сами.

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

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

3. Открытие (open) - определяется результирующий набор строк. Указатель активной указывает на первую строку набора.

4. Выборка (fetch) - получение очередной строки из результирующего набора строк. После каждой выборки указатель смещается на 1 строку вперед.

5. Закрытие (close) - курсор закрывается и освобождается используемая память.

Цитата из книги “Oracle для профессионалов” Тома Кайта.

“При открытии курсора сервер Oracle не "отвечает " на запрос; он никуда не копирует данные при открытии курсора (представьте, сколько времени потребовало бы открытие курсора для таблицы с миллиардом строк в противном случае). Курсор просто открывается и дает результаты запроса по ходу обращения к данным. Другими словами, он будет читать данные из таблицы при извлечении их через курсор.”

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

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

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

Запросы:

select * 
  from user_tables;

или

select * 
  from all_tables 
 where owner = user;

или

select * 
  from user_objects 
  where object_type = ’TABLE’;

или

select * 
  from all_objects
 where object_type = 'TABLE' and owner = user;

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

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

Триггеры

Очень частый вопрос на собеседовании "что такое триггер? какие типы триггеров вы знаете?"

Триггер - PL/SQL-блок, выполняющийся по наступлению какого-то событий (логин в БД, изменение данных и др.).

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

1) DML - реакция на команды DML над таблицей. Наиболее часто создаваемые триггеры.

2) Instead of - предназначены для выполнения операций вставки, обновления и удаления элементов представлений, но не таблиц.

3) DDL - срабатывают при выполнении команд DDL (create, alter, drop) и други типа (analyze, revoke, rename и другие).

4) Системные триггеры - реагируют на события уровня базы данных. Используются, в основном, для администрирования.

Собеседование - не проблема 🤓

#plsql #теория #триггеры #собеседование

Продолжаю серию видео про установку СУБД Oracle в Docker.
На этот раз мы установим Oracle 19 в комплектации Enterprise Edition.

Зачем поднимать Enterprise Edition, если есть Express Edition 18?
К сожалению, пока сборки для версии 19 нет в комплектации Express. Поэтому, если вы хотите попробовать фишки новой версии, необходимо будет ставить Enterprise.

Установка данной версии будет немного отличаться от установки Express.
Также, мы немного затронем тему диагностики проблем производительности в Enterprise Manager.

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

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

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

#видео #docker #oracleee

Ограничение выдачи результата

В 12й версии СУБД появилась возможность лимитировать выдачу результатов. Если нужна предсказуемость результатов, то нужно использовать ORDER BY в основном запросе. Синтаксис показан выше на скриншоте.

OFFSET - индекс начального элемента (начинается с “0”). По умолчанию, “0”, т.е. считываем с начала.
ROW | ROWS - для семантической ясности, взаимозаменямы.
FIRST | NEXT - для семантической ясности, взаимозаменямы.
rowcount | percent PERCENT - количество строк или количество %.
ROW | ROWS - для семантической ясности, взаимозаменямы.
ONLY | WITH TIES: ONLY - возвращает, только то количество, которое мы указали. TIES - будет добавлены записи с тем же значением, что и последняя запись, если они есть.

Удобно использовать данный синтаксис для осуществления пагинации по выборке.

По ссылке примеры применения из документации для Oracle 18.

#sql #offset

Задача: Требуется добавить или вычесть некоторое количество дней, месяцев или лет из текущей даты.
Например, получить набор дат - текущая дата, 5 дней до, 5 дней после, 4 месяца до, 4 месяца после, 3 года до, 3 года после.

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

#sql #задача

Способы получения Top-N записей (пагинация)

Существует три способа работать с пагинацией на уровне SQL. Допустим нам надо получить строки с 10 по 20.

Способ 1. Rownum + order by

select *
  from (select rownum rn,
               a.*
          from (select *
                  from employees e
                 order by e.hire_date) a
         where rownum < 20)
 where rn >= 10;

Способ 2. Аналитическая функция row_number

select *
  from (select row_number() over(order by t.hire_date) rn,
               t.*
          from employees t
         order by t.hire_date) a
 where a.rn between 10 and 19;

Способ 3. Использование расширения SQL (c Oracle 12c).

select t.*
  from employees t
 order by hire_date
 offset 9 rows fetch next 10 rows only;

По скорости примерно одинаковы. Как-нибудь, запилю видюшку с демонстрацией.

#sql #пагинация #rowcount #аналитическиефункции #offset

Задача: Требуется добавить или вычесть некоторое количество дней, месяцев или лет из текущей даты.
Например, получить набор дат - текущая дата, 5 дней до, 5 дней после, 4 месяца до, 4 месяца после, 3 года до, 3 года после.

Принцип решения:
Для вычисления дней используем стандартные сложение и вычитание, а для операций над месяцами и годами – функцию ADD_MONTHS (добавить месяцы). Для того чтобы получить дату в прошлом - вычитаем, в будущем - складываем.

Запрос:

select sysdate now,
       sysdate - 5 b_5d,
       sysdate + 5 a_5d,
       add_months(sysdate, -4) b_4m,
       add_months(sysdate, 4) a_4m,
       add_months(sysdate, -12*3) b_3y,
       add_months(sysdate, 12*3) a_3y
 from dual;

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

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

Защита кода (обфускация)

Защита кода - сокрытие реализаций (обфускация) исходного PL/SQL кода 🛡

Обычно применяется при поставки ПО заказчикам.
На скриншоте выше, можно посмотреть как выглядит такой код.

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

Существует два способа обфусцировать код:
1. Пакет dbms_ddl

dbms_ddl.create_wrapped(ddl_text);

офусцированный код накатывается автоматом.

2. Утилита wrap

wrap iname=input_file [ oname=output_file ]

получившийся код накатываем на БД.

Обратный процесс (unwrap) не сложный, доступный любому. Поэтому, как сказано выше, не стоит шифровать подобным образом пароли и секретную информацию. Например, здесь можно разврапить код 🤓

#plsql #обфускация #wrap #dbms_ddl

Блокировки на уровне строк - select for update

В этом видео, я расскажу, что такое блокировки на уровне строк с использованием команды select for update, посмотрим основные параметры и разберем подробней на простеньких примерах.

Блокировки - это механизм, используемый для управления одновременным доступом к общему ресурсу.

Если вы используете базу данных, то с вероятностью в 99.9% одновременно будет работать более чем одна сессия. А значит есть вероятность конкурентного доступа к одному и тому же ресурсу. Это крайне важное понятие, при работе в OLTP-системах, и вообще базах данных. Обязательно используйте в реальной практике 😉

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

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

#видео #блокировки

Виртуальные поля

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

Объявление выглядит так:

create table имя_таблицы
(
  ...
  имя_столбца  generated always as (выражение) virtual
  …
);

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

Функция может быть пользовательская, но обязательно должна быть deterministic.

Альтернатива? обычный столбец, заполнение которого контролируется вручную в API, триггере и т.д.

#теория #column