Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

Чем отличается Sequence от итераторов в запросах с CONNECT BY?

Это совершенно разные сущности, хотя результат их работы в чем-то схож. SEQUENCE представляет собой реальный физический объект в базе данных, обращение к которому позволяет вывести на экран заданную последовательность значений, запрос CONNECT BY LEVEL — это просто способ сделать почти то же самое в запросе, без создания SEQUENCE.

1. Вывести с помощью запроса значение sin числа от 0 до 1 с шагом 0,1.

2. Вывести с помощью запроса число и день недели (TO_CHAR (DATE,«DY»)) всех дней за 2008 год.

3. Вывести с помощью запроса все числа от 500 до 1000, кроме тех чисел, которые делятся нацело на 10.

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

Для этого используется древовидная иерархическая система, такая, где есть идентификатор и есть идентификатор родителя (родительского узла) ID, PARENTED. Для работы с подобной иерархической структурой также используется конструкция SQL — CONNECT BY.

Оператор CONNECT BY используется для запросов, где требуется работа со сложной древовидной иерархией.

Например, уровни подчинения сотрудников компании или сложная структура отделов на предприятии.

Здесь ID — идентификатор сотрудника, NAME — наименование сотрудника, IDPARENT — идентификатор руководителя данного сотрудника.

Пример позволяет показать иерархию.

В данном примере генерируется количество символов подчеркивания, соответствующее уровню иерархии LEVEL.

Рисунок 150. Использование CONNECT BY START WITH PRIOR

В данном способе воспроизведения иерархии с использованием запросов SQL CONNECT BY не работает классическая сортировка ORDER BY, попытка сортировки запроса с помощью ORDER BY ломает иерархию, поэтому для сортировки подобных запросов используется специальная директива ORDER SIBLINGS BY.

Есть возможность представить иерархию в виде пути от каталога к каталогу. Для этого необходимо воспользоваться функцией SYS_CONNECT_BY_PATH (NAME, '/»).

Рисунок 151. CONNECT BY: пример

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

Избежать подобной проблемы позволяет использование специальной директивы NOCYCLE.

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

Такая конструкция для вывода иерархических данных существует только в диалекте ORACLE SQL?

Нет, в MS SQL и PostgreSQL также существуют подобные операторы. Это оператор WITH RECURSIVE.

Для MySQL такой конструкции не существует.

Как начать обход дерева не с первого, а со второго, третьего элемента?

Для этого в директиве START WIDTH следует указать идентификатор того элемента, с которого начинается обход древовидной структуры.

Например,

Здесь START WITH IDPARENT =1, обход начинается с первого элемента.

Рисунок 152. CONNECT BY: пример SYS_CONNECT_BY_PATH

1. Повторите материалы данного шага.

2. Создайте таблицу «Детали», где одна деталь может быть частью другой, выведите на экран иерархию деталей.

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

В языке SQL есть специальные команды для условных выражений в запросах, то есть выражений, на критерии истинности которых осуществляется ветвление запроса. Аналогичные конструкции есть в алгоритмических языках, таких, например, как JAVA, PASCAL или C++.

В алгоритмических языках это операторы IF и CASE.

В языке SQL похожая функциональность реализуется с помощью операторов DECODE, CASE.

Условные выражения в SQL ORACLE диалекта реализуются с помощью операторов CASE и DECODE.

Разберем каждый из условных операторов отдельно с примерами.

DeCODEвыводит значение в зависимости от логического выражения.

равно 2.

Пример 2

Рисунок 153. Запрос: использование DECODE

Более сложный вариант оператора DECODE:

Рисунок 154. Запрос: использование оператора DECODE

Пример 1

Пример 2

Рисунок 155. Запрос: использование оператора CASE

Еще один вариант CASE:

Пример

Если в операторе CASE нет предложения ELSE, тогда оператор CASE вернет NULL.

— результат NULL.

Если в операторе CASE несколько одинаковых сравнений, то правильным будет результат последнего из них.

Существуют ли подобные операторы в других базах данных, например в MS SQL или PostgreSQL?

Это также оператор CASE с аналогичным синтаксисом и назначением.

Как сравнивать интервалы с помощью DECODE? Например, 1—10 — ответ 1, 20—30 — ответ 2, 30—40 — ответ 3.

Вот пример такого сравнения, здесь речь идет об интервале каталогов:

1. Повторите материалы данного шага.

2. Из таблицы AUTO выберите сведения о цвете автомобиля, марке автомобиля, если номер автомобиля начинается с цифры 1 — выведите в отдельной колонке «регион 1», с цифры 2 — «регион 2», с цифры 3 — «регион 3». Используйте DECODE.

3. Сделайте предыдущее задание с использованием CASE.