Таблица 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 #теория #курсоры

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

Смотрите готовый 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 #задача