Сертификационные экзамены для DBD

Различаются тестируемой областью знаний и сложностью вопросов. Список их постоянно изменяется, меняются требования, количество вопросов и т.д.

“Associate” - считается начальной ступенью, “Professional” - средний уровень, “Master” - мастер своего дела. Экзамены на “Master” доступны только для DBA.
"Professional" недоступен без сдачи экзаменов уровня “Associate”.

Актуальный список экзаменов можно посмотреть на сайте Oracle. Основные для разработчика:
* 1Z0-071. Oracle Database SQL Certified Associate
* 1Z0-144. Oracle PL/SQL Developer Certified Associate
* 1Z0-048. Oracle Database PL/SQL Developer Certified Professional

Без предварительной подготовки сдавать не стоит, только сольете кровно заработанные.

У меня уходило примерно 1.5 месяца на подготовку к каждому. Конечно, всё индивидуально.

Если тема актуальна, могу рассказать, как лучше всего готовиться, что учесть и другие важные моменты.

#сертификация

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

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

#sql #задача

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

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

Запрос

select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') from dual;

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

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

Автономные транзакции (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 #решениезадачи #аналитическиефункции