Установка СУБД 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

Задача: Требуется подсчитать количество дней недели в текущем году. Дни недели должны быть на русском.

Формат вывода:
Среда 54
Понедельник 44
и т.д.

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

#sql #задача

Именование и комментарии

Вы пришли на новое место работы. Открыли БД. Иииии… ничего не понятно. Таблицы именованы как попало, нет комментариев к столбцам, таблицам и view и т.д. Знакомо? 🙃

Большую часть времени код читается. Фактически код - это первоисточник. Так и база данных. Должна быть читаема без лишних подглядываний в документацию, если она вообще есть и актуальна.

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

Как мы можем самодокументировать БД?

▫️ Название объекта - говорящее название. Об этом можно написать не одну статью, просто скажу, что команда должна выработать практики в именовании объектов и четко им следовать.
▫️Связи, ограничения - говорит о взаимосвязях объектов, характере данных.
▫️Комментарии к таблице и представлениям - кратко и по делу, отражающие суть.

▫️Названия столбцов - аналогично, отражает суть. Не надо добавлять тип столбца в название. Иногда они все же меняются, и тогда поимеите ооочень много геморроя по переименованию. Или того хуже оставить так как есть.
▫️Комментарии к столбцам - обязательны, аналогично отражается суть данных хранящихся в этом столбце, посмотрев на которую можно сразу понять без лишних приседаний, что в нем хранится.
▫️PL/SQL-код - коллеги-джависты, против такого подхода, однако чаще всего, когда смотришь на витиеватую бизнес-логику или запрос с трехэтажный дом, очень хочется увидеть, хотя бы маааленький комент, что здесь собственно происходит 😊

“Встречают по одежке” = “встречают по таблице”. Как только видишь разгильдяйство в определениях таблиц, начинаешь понимать как ведется система в целом, какого уровня был разработчик 💁🏻‍♂️

Очень рекомендую прочитать книгу «Читаемый код, или Программирование как искусство» (“The Art of Readable Code”) авторы Дастин Босуэлл (Dustin Boswell), Тревор Фаучер (Trevor Foucher). Её легко найти на просторах интернета совершенно бесплатно. Просветляет по многим моментам 📚

#теория #clearcode

Задача:
Требуется подсчитать количество дней недели в текущем году. Дни недели должны быть на русском.

Принцип решения:
1. Для того чтобы получить все дни в году, используем иерархический запрос. Для указания сколько строк нужно сгенерировать получить количество дней в текущем году.
- add_months(trunc(sysdate, 'YYYY'), 12) - первый день следующего года.
- trunc(sysdate, 'YYYY') - первый день текущего года.

2. Для каждой строки используя level - 1 получаем дату от начала года.

3. И преобразовываем в формат день недели через функцию to_char с указанием необходимой локали (nls_date_language).

4. Далее группируем.

Финальный запрос:

select day
      ,count(*)
  from (select trim(to_char(trunc(sysdate, 'YYYY') + level - 1,
                            'DAY',
                            'nls_date_language = Russian')) day
          from dual
        connect by level <= (add_months(trunc(sysdate, 'YYYY'), 12) -
                   trunc(sysdate, 'YYYY')))
 group by day;

Дополнительное задание: отсортировать результат в порядке дней недели (понедельник, вторник и т.д.).

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

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