6.1. Использование математической логики в SQL

Запросы и предикаты

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

Работа с БД предполагает получение ответов на определенные вопросы. Вопрос, с точки зрения логики, есть средство извлечения информации о выполнимости отношения между объектами, которое можно сформулировать в виде предиката.

Пусть, например, вам необходимо получить ответ на вопрос, какие игры в 2018 году проходили на стадионе города Ростов на Дону?

Для идентификации игры нужно знать группу и номер игры внутри группы, то есть ключевые атрибуты всегда должны участвовать в формулировке запросов, чтобы однозначно определить необходимые строки. Обозначим их через X и Y соответственно. Удобно ввести переменную-кортеж R для обозначения текущего кортежа отношения РАЗМ_СТАДИОНОВ. Тогда запрос в обозначениях исчисления предикатов будет выглядеть как

(∃X,Y) (R ⊂ РАЗМ_СТАДИОНОВ ) ∧ X = R.группа ∧ Y=R.игра ∧ R.год = 2018 ∧ R.стадион = "Ростов на Дону".

На SQL этот предикат можно записать с помощью оператора SELECT как:

SELECT  группа, игра
FROM    РАЗМ_СТАДИОНОВ
WHERE   год = 2018 AND стадион = ‘Ростов на Дону’;

При этом переменные предиката образуют список предложения SELECT, имя отношения переходит в список предложения FROM, а собственно условия выборки размещаются в предложении WHERE.

В списке предложения SELECT может быть указано любое допустимое реализацией SQL выражение. Здесь же, можно переопределить имя колонки для результирующей таблицы или назначить имя результату выполнения вычислений над полями согласно синтаксису имя=выражение или выражение AS имя.

Несколько переменных-кортежей в одном запросе

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

Обозначая за X и Y переменные предиката стадион и команда, а за SA и GP переменные-кортежи в отношениях РАЗМ_СТАДИОНОВ и МЕСТО_В_ГРУППЕ, вы можете записать в исчислении предикатов выражение:

(∃X,Y) (∃ SA,GP) (SAÌ РАЗМ_СТАДИОНОВ) ∧ (GP ⊂ MEСТО_В_ГРУППЕ) ∧ X = SA.стадион ∧ Y = GP.команда ∧ GP.место = 1 ∧ GP.команда = ‘Бразилия’ ∧ GP.год = SA.год ∧ GP.группа = SA.группа.

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

SELECT DISTINCT стадион
FROM РАЗМ_СТАДИОНОВ, МЕСТО_В_ГРУППЕ
WHERE место = 1 AND команда = ‘Бразилия’ AND РАЗМ_СТАДИОНОВ.год = МЕСТО_В_ГРУППЕ.год AND РАЗМ_СТАДИОНОВ.гуппа = МЕСТО_В_ГРУППЕ.группа.

В одном запросе может быть обработано несколько отношений. Что касается опции DISTINCT, то она не может быть использована, когда команда SELECT обрабатывает значения полей типа LONG VARCHAR, а также в некоторых ситуациях для конкретных реализаций SQL.

Корреляционные имена таблиц и вложенные подзапросы

Корреляционные имена таблиц в SQL используются для присвоения дополнительного (алиасного) имени таблице или отношению. С точки зрения исчисления предикатов дополнительное имя необходимо для обозначения переменной-кортежа в соединениях одинаковых отношений. Здесь требуется два переменных-кортежа на одно отношение, чтобы различать кортежи для двух копий одного и того же отношения.

Предположим, например, что необходимо ответить на вопрос, какие команды играли в той же группе, что и Мексика? Тогда на SQL можно записать (дополнительные имена определяются указанием некоторого определяемого вами идентификатора к имени таблицы справа через пробел и должны быть уникальны):

SELECT DISTINCT S1.команда 
FROM МЕСТО_В_ГРУППЕ S1, МЕСТО_В_ГРУППЕ S2
WHERE S1.команда <> ‘Мексика’ AND S2.команда = ‘Мексика’ AND S1.год = S2.год AND  S1.группа = S2.группа

В предложении SELECT квалификация S1 колонки «команда» обязательна, чтобы избежать двусмысленности ее определения. Можно записать этот запрос с помощью вложенного подзапроса, который фактически генерирует непоименованное промежуточное отношение, тем самым, избавляя нас от использования дополнительных имен таблиц. Тот же запрос вы можете записать на SQL как

SELECT DISTINCT команда 
FROM МЕСТО_В_ГРУППЕ 
WHERE команда <> ‘Мексика’ AND группа||год  IN (
	SELECT группа, год
	FROM МЕСТО_В_ГРУППЕ
	WHERE команда = ‘Мексика’);

Обратим ваше внимание на использование операции конкатенации строк ‘группа||год’. В операции IN не допускается использование списка столбцов, можно использовать только одну колонку. В этом запросе для идентификации строк необходимо использовать два столбца, составляющие первичный ключ отношения. В данном случае использование операции конкатенации строк SQL позволяет вам рассматривать два столбца как один, но во вложенном операторе SELECT они должны быть указаны в списке предложения SELECT.

Подзапросы, которые во вложенном запросе ссылаются на таблицу из внешнего запроса, называются связанными подзапросами (correlated subquery). В этом случае подзапрос выполняется повторно для каждой строки внешнего запроса.

Связанные запросы имеют сходство с соединениями (включают сравнение каждой строки одной таблицы с каждой строкой другой). В этом отношении большинство запросов, выполняемых с помощью соединений, могут быть выполнены с помощью связанных запросов и наоборот.

Так запрос (см. выше) о стадионах, использовавшихся той же группой, где Хорватия заняла первое место, может быть записан как

SELECT DISTINCT стадион
FROM РАЗМ_СТАДИОНОВ
WHERE год||группа IN 
	(SELECT стадион
	FROM МЕСТО_В_ГРУППЕ
WHERE место = 1 AND команда = ‘Хорватия’);

Однако такой подход не применим, если мы хотим получить значения из обоих отношений (скажем, что мы хотим еще получить и значение колонки команда). Тогда необходимо использовать вариант команды без вложенного подзапроса:

SELECT DISTINCT стадион, команда
FROM РАЗМ_СТАДИОНОВ, МЕСТО_В_ГРУППЕ
WHERE место = 1 AND команда = ‘Хорватия' AND РАЗМ_СТАДИОНОВ.год = МЕСТО_В_ГРУППЕ.год AND РАЗМ_СТАДИОНОВ.группа = МЕСТО_В_ГРУППЕ.группа.

В подзапросах в диалектах SQL Oracle нельзя использовать предложение ORDER BY (оно может быть применено только к самой внешней команде SELECT) и колонки типа LONG VARCHAR. К ограничениям также следует отнести невозможность ссылки на таблицу, изменяемую в подзапросе командой обновления. Так невозможно построить подзапрос, вычисляющий среднее количество забитых мячей на игру в отношении Игры, а затем удалить все строки из таблицы со значениями ниже этого среднего.

DELETE 
FROM ИГРЫ
WHERE рейтинг=(голыА+голыВ) < 
	(SELECT AVG(голыА+голыВ)
	FROM ИГРЫ);

Моделирование кванторов

В рассмотренных примерах вы использовали квантор существования для построения предикатов и соответствующих команд SELECT. В SQL считается, что все переменные находятся под квантором существования. А если вам необходимо получить ответ на вопрос: выдать все группы, в которых ни одна игра не игралась в Самаре. Ясно, что переменная предиката связана квантором всеобщности (слово «все»).

Обратим внимание на то, что в предложении типа WHERE IN R, где R является предикатом некоторого отношения, неявно используется квантор существования. То есть, это предложение эквивалентно записи на языке исчисления предиката ∃х r(x). Используя законы двойственности де Моргана из классической логики, вы можете, сделав операцию отрицания, получить выражение с квантором всеобщности ¬(∃х r(x)) = ∀x r(x).

Переходя к алгебре отношений, теперь можно записать соответствующее предложение WRERE X NOT IN R, моделируя тем самым квантор всеобщности. Теперь вы убедились, понимание теории, на которой построено «здание» SQL, весьма полезно. Выражение на SQL будет иметь вид:

SELECT DISTINCT группа
FROM  MEСТО_В_ГРУППЕ
WHERE  ‘Самара’ NOT IN (
	SELECT стадион 
	FROM РАЗМ_СТАДИОНОВ 
	WHERE группа = МЕСТО_В_ГРУППЕ.группа)

 или

SELECT DISTINCT группа
FROM МЕСТО_В_ГРУППЕ
WHERE NOT EXISTS (
	SELECT игра
	FROM РАЗМ_СТАДИОНОВ
WHERE группа = МЕСТО_В_ГРУППЕ.группа AND стадион = ‘Самара’)

Второе выражение использует оператор EXISTS (устанавливает существование искомого множества). Как видите, использование операций с множествами на SQL и концепции вложенных подзапросов позволяет нам сформулировать запрос с использованием квантора всеобщности.

Теоретико-множественные операции

Как мы уже знаем, в SQL существует специальное предложение UNION для построения объединения двух или более отношений, получаемых в результате выполнения команд SELECT. Согласно теории, отношения, участвующие в операции объединения, должны иметь одинаковое количество столбцов, согласованных по типу. Однако на практике бывает нужно строить объединение исходных отношений, не удовлетворяющих таким ограничениям. При этом мы должны позаботиться о том, чтобы результирующие таблицы команд SELECT имели одинаковое число колонок, согласованных по своим доменам.

Предположим, что вам необходимо получить ответ на вопрос: выдать все группы, которые включают в себя Бразилию, и те, у которых хотя бы одна игра была в Санкт- Петербурге. Для этого вы можете написать следующую команду на SQL:

SELECT DISTINCT группа
FROM МЕСТО_В_ГРУППЕ
WHERE команда = ‘Бразилия’ 
    UNION
SELECT DISTINCT группа)
FROM РАЗМ_СТАДИОНОВ
WHERE стадион = ‘Санкт-Петербург’

Отношения МЕСТО_В_ГРУППЕ и РАЗМ_СТАДИОНОВ имеют различное число столбцов, но в результирующих таблицах команд SELECT присутствует по одной колонке одинакового типа. По определению операции объединения таблицы-операнды должны иметь одинаковое число атрибутов. В нашем примере исходные таблицы имеют различное число атрибутов, но команды SELECT строят таблицы (промежуточные) с одинаковым числом атрибутов и, поэтому применение операции объединения корректно. Применение операции объединения к таблицам с различным числом колонок приводит к построению декартова произведения.

В настоящее время многие конкретные реализации SQL не поддерживают теоретико-множественные операции в явном виде. В диалекте SQL Oracle 11g есть операции разности и пересечения множеств.

В настоящей лекции был моделирован квантор всеобщности, который в явном виде также не поддерживается. Для этого использовались вложенные подзапросы и законы классической логики для исчисления предикатов. Попробуем промоделировать операции разности и пересечения множеств. Предположим, что необходимо получить перечень всех групп, в которых ни одна игра не проходила в Ростове на Дону. Если создать перечень всех групп и перечень групп, в которых хотя бы одна игра состоялась в Ростове на Дону, а затем вычесть последний перечень из перечня всех групп, то можно получить требуемый результат. Выражение на языке SQL имеет следующий вид:

SELECT DISTINCT группа
FROM РАЗМ_СТАДИОНОВ SA
WHERE NOT EXISTS
	 (SELECT год,группа, игра
	FROM РАЗМ_СТАДИОНОВ 
WHERE РАЗМ_СТАДИОНОВ.год = год AND РАЗМ_СТАДИОНОВ. группа = группа   AND стадион = ‘Ростов на Дону’)

На языке исчисления предикатов разности отношений (экстенсионалов) P-Q будет отвечать выражение p(x) AND NOT q(x), которое и записано в команде SELECT. Это еще один пример практического применения теории.

Операция пересечения естественным образом реализуется посредством соединения (если все атрибуты соединяемых таблиц являются общими). Пусть требуется найти те команды, которые выступали на играх в качестве гостей, т.е. были заявлены вторыми на матч. Для этого нужно построить пересечение двух отношений, построенных на отношении ИГРЫ и МЕСТО_В_ГРУППЕ:

SELECT год, группа, команда
FROM МЕСТО_В_ГРУППЕ, ИГРЫ
WHERE МЕСТО_В_ГРУППЕ.год = ИГРЫ.год AND МЕСТО_В_ГРУППЕ.группа = ИГРЫ.группа AND МЕСТО_В_ГРУППЕ.команда = ИГРЫ.командаB;

Представления и множества

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

Концепция представления особенно важна, когда требуется извлекать информацию из нескольких отношений. Во-первых, она является средством формирования пользователем своей виртуальной (внешней) схемы базы данных. Во-вторых, это средство формирования производных или выводимых атрибутов отношений БД, то есть таких атрибутов, которые непосредственно не хранятся в базе данных. Представление можно трактовать как макроопределение: любой запрос по отношению к нему подвергается макрорасширению и преобразуется SQL для ссылки на исходные базовые отношения. Чтобы представление (как производное отношение) стало доступно, вам необходимо дать ему уникальное имя и определить атрибуты.

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

CREATE VIEW СТАДРАЗН (стад, разн_мячей ) AS 
SELECT стадион, голыА - голыВ
FROM ИГРЫ, РАЗМ_СТАДИОНОВ
WHERE ИГРЫ.год = РАЗМ-СТАДИОНОВ.год AND ИГРЫ.группа = 2 АND РАЗМ_СТАДИОНОВ.группа=2 АND ИГРЫ.игра=РАЗМ_СТАДИОНОВ.игра

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

Теперь мы можем выполнить запрос к виртуальной таблице, такой, чтобы получить ответ на вопрос: выдать все стадионы, на которых игры закончились с разностью мячей больше 4. Команда SQL в этом случае тривиальна:

SELECT стад
FROM СТАДРАЗН
WHERE разн_мячей > 4

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

Однако во многих реализациях SQL представления имеют сильные ограничения на выполнение операций обновления данных над ними. Они используются только для чтения. Представление используется только для чтения (read-only view), если в определяющей команде SELECT

• предложение FROM использует имена более одной таблицы или представления;
• используется опция DISTINCT;
• предложение GROUP BY;
• предложение HAVING;
• функция агрегирования.

Иногда запрещается использовать и подзапросы.

В противном случае представление считается обновляемым (updatable view). Для обновляемых представлений предусмотрена опция WITH CHECK OPTION. Когда она указана, любая вставка и обновление через данное представление будет выполняться только, если представление отвечает своему определению (данные в таблице могут быть изменены непосредственно). В противном случае такой проверки не делается. Если представление предназначено только для чтения или использует подзапрос, то данная опция не должна использоваться.

Множества и предложение GROUP BY

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

Предложение GROUP BY определяет, каким образом строить разбиение исходного множества на подмножества в соответствии с заданным критерием. Полученное разбиение представляет исходное отношение как объединение конечного числа непересекающихся подмножеств. В частности, агрегатные функции применяются последовательно к каждому подмножеству в отдельности, а результат функции выводится в результирующем множестве. Например, допустим, что вы хотите получить максимальные количества забитых мячей в каждой группе, а также вывести среднее по забитым мячам по группе. Тогда вам необходимо записать на SQL команду:

SELECT год, группа, MAX(голыА), MAX(голыВ), AVG (голыА+голыВ) 
FROM ИГРЫ
GROUP BY год, группа

На использование колонок группировки существуют ограничения. Если колонка в списке предложения SELECT есть производная колонка, то ее можно использовать в качестве колонки группировки, указав ее порядковый номер в списке. Но, если эта производная колонка есть результат применения функции агрегирования, то этого сделать нельзя (так эти функции возвращают только одно значение). Следует также иметь в виду, что наличие NULL-значений колонки порождает отдельную группу в разбиении.

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

Предложение GROUP BY ведет себя как проекция с производными колонками. Она разбивает значения в заданных колонках на подмножества в соответствии со списком колонок группировки. Эти подмножества характеризуются одинаковыми значениями из списка проекции. Затем осуществляется проекция на эти атрибуты, причем при этом вычисляется какая-либо производная колонка и помещается в дополнительную колонку результирующего множества.

Например, в учебной БД «Управление кадрами» (Лекция 1) мы можем создать проекцию таблицы EMPLOYEES, состоящую из количества служащих по отделам и общей зарплаты по отделу, которая иллюстрирует приведенную выше интерпретацию предложения GROUP BY.

SELECT DEPARTMENT_ID, COUNT(*),  SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

Мы уже знаем, что возможно задавать условия выборки на результаты выполнения предложения GROUP BY для того, чтобы исключить определенные кортежи из построенного разбиения. Для этого предназначено специальное предложение HAVING, которое задает условие выборки к атрибутам перегруппированного, а не исходного отношения. Основное отличие в действии условия выборки предложения HAVING от аналогичного условия выборки предложения WHERE состоит в том, что первый выбирает подмножества из разбиения целиком в зависимости от его агрегируемых свойств, в то время как последний просматривает содержимое каждого из этих подмножеств построчно, не учитывая полученное разбиение. (Иногда наблюдается более быстрое выполнение команды SELECT с использованием предложения HAVING, чем с предложением WHERE)

Допустим, что вам необходимо получить среднее количество забитых мячей для групп 2018 года, имеющих большое значение максимума забитых мячей. Команда на SQL будет иметь вид:

SELECT группа, AVG  (( голыА + голыВ )/2)
FROM ИГРЫ
WHERE год = 2018
GROUP BY группа
HAVING MAX( голыА ) +  MAX ( голыВ ) > 5;

В предложении HAVING также существуют ограничения на использование колонок. Так производные колонки группировки, полученные в результате применения функций агрегирования, не могут использоваться в этом предложении.

Колонки, используемые в предложении HAVING, подчиняются тем же правилам, что и колонки группировки. Они должны иметь одно значение для каждого подмножества разбиения. Таким образом, использовать переменные типа дата нежелательно так, как обычно они имеют несколько значений для элементов группы.

Типы соединений и использование соединений с переименованием

В SQL и, в частности, в диалекте SQL Oracle 11g соединения таблиц разбиты на несколько типов. С эквисоединением мы познакомились в Лекции 1. При эквисоединении (equi-join) таблицы соединяются по условию равенства значений колонок в соединяемых таблицах, т.е., на примере учебной БД (фрагменты предложений команды SELECT используются только для иллюстрации синтаксиса):

WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID

Или в новом синтаксисе

FROM EMPLOYEES JOIN DEPARTMENTS USING(DEPARTMENT_ID)

Разновидностью эквисоединения является так называемое естественное соединение (natural join): колонки эквисоединения явно не указываются (СУБД выбирает их сама):

FROM EMPLOYEES NATURAL JOIN DEPARTMENTS

Внутреннее соединение (inner join), которое является умолчанием для СУБД Oracle, вычисляет результирующее множество из строк удовлетворяющих заданному условию:

FROM EMPLOYEES JOIN DEPARTMENTS ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID

Внешнее соединение (outer join) вычисляет результирующее множество из всех строк, которые удовлетворяют указанному условию соединения, плюс некоторых или всех строк из таблицы, в которой нет подходящих строк, удовлетворяющих указанному условию соединения. Для определения внешнего произведения необходимо указать (+) после имени колонки соединения той таблице, которая может не иметь строк, удовлетворяющих условиям соединения. Знак «+» применяется только к колонке. В зависимости от места расположения этого знака в выражении условия соединения различают левое, правое и полное соединение. При формировании условия соединения нельзя использовать логическую операцию OR. Пример:

WHERE EMPLOYEES.DEPARTMENT_ID(+) = DEPARTMENTS.DEPARTMENT_ID

В новом синтаксисе для полного внешнего соединения

FROM EMPLOYEES FULL JOIN DEPARTMENTS ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID

Соединение таблицы с самой собой иногда называются рефлексивным соединением. Все операции над отношениями приводят к созданию новых отношений. Имена столбцов уникальны во всех отношениях базы данных и либо уже существуют, либо определяются нами уникально в представлениях. Однако операция соединения может приводить к результирующим множествам с лишней колонкой, имя которой совпадает с уже существующим. Например, выполните эквисоединение отношения с самим собой по какой-либо колонке. С другой стороны, в SQL вы можете выполнять соединения по столбцам с различными именами, если домены этих столбцов не совпадают. Поэтому удобно при разработке соединений использовать операцию переименования столбцов в отношениях, для нужных вам колонок. Операция эта умозрительная и определяется нами посредством задания корреляционных имен для таблиц, но весьма полезна, как мы увидим ниже.

Особенно эффективна она при соединении отношения с самим собой. Необходимость таких соединений возникает тогда, когда отношение имеет своей внутренней структурой некоторую иерархию. Например, иерархию подчинения – «руководитель – служащий», которую в информатике принято представлять в виде графа древовидной структуры или дерева.

Давайте создадим такое отношение иерархии в БД «Управление кадрами». Это отношение выводимо из отношения EMPLOYEES, и, следовательно, может быть определено через представление:

CREATE VIEW SUPERVISOR AS
SELECT EMPLOYEE_ID, LAST_NAME, MANAGER_ID
FROM EMPLOYEES;

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

SELECT ENAME
FROM SUPERVISOR
WHERE MANAGER_ID = '101';

Однако, ответ на вопрос, кто руководит руководителем служащего с табельным номером 107, приводит к необходимости сделать проход по дереву на один шаг вверх. Действительно, иерархия подчинения может быть представлена структурой дерева. Можно решить поставленную задачу, используя совместно соединение отношения с самим собой и переименование. Для этого вам необходимо выполнить следующие действия в алгебре отношений:

• найти непосредственного руководителя служащего с табельным номером 107, то есть выполнить проекцию π_{EMPLOYEE_IDрук} (σ_{EMPLOYEE_ID=107} (SUPERVISOR));
• переименовать руководителя как служащего, то есть рассмотреть копию отношения SUPERVISOR (обозначим ее как SUPERVISOR’);
• выполнить проекцию π_{МАNAGER_ID}(SPERVISOR >< SUPERVISOR’), чтобы получить требуемый результат.

Соответствующий оператор SELECT имеет вид:

SELECT Q.MANAGER_ID
FROM SUPERVISOR R, SUPERVISOR Q
WHERE R.EMPLOEEY_ID=1000 AND Q. MANEGER_ID =R.MANEGER_ID;

Если бы мы не сделали переименование, то мы получили бы строки, в которых первый непосредственный руководитель появился бы в качестве руководителя (первая проекция), а затем проекция отсоединения дала бы нам номер этого руководителя. В данном примере переименование имен столбцов соответствует изменению роли личности в иерархии подчинения. Это еще один из красочных примеров применения теории на практике.

Как доказано в теории реляционных отношений, используя соединения и переименования, вы можете осуществить проход по дереву до любой заданной вершины. Однако исследовать дерево на произвольную глубину вы не сможете. Грубо говоря, нужно делать по одному соединению на каждый уровень. Когда глубина поиска неизвестна, вы не можете составить запрос на SQL. Это ограничения реализации модели реляционного исчисления. Чтобы допустить такие действия, необходимо расширить модель путем разрешения дополнительных операций. В частности, нужно допустить ссылку как тип данных и ввести оператор транзитивного замыкания для работы с этим типом.

6.2. Иерархические запросы в SQL

Для обработки таблиц БД, содержащих иерархии в виде древовидной структуры, в диалекте SQL Oracle 11g предусмотрена специальная конструкция в команде SELECT – предложение «иерархического запроса».

Синтаксис (в сокращенном виде):

[START WITH <condition> ] CONNECT BY [NOCYCLE] <condition_1>

Иерархический запрос выполняется следующим образом:

• Сначала выбирается корневая строка (строки) иерархии, т.е. та, которая удовлетворяет условию в выражении START WITH.
• Затем выбираются дочерние строки для каждой корневой строки, которая должна удовлетворять условию во фразе CONNECT BY по отношению к одной из корневых строк.
• Далее выбираются следующие поколения дочерних строк. Сначала выбираются потомки строк, выбранных на шаге 2, потом – их потомки и т.д.

Потомки выбираются исходя из условия CONNECT BY относительно текущей родительской строки. Если запрос содержит фразу WHERE, исключаются все строки, которые не удовлетворяют условию во фразе WHERE, причем проверяются эти условия для каждой строки, а не просто исключаются все потомки строки, которая не удовлетворяет этому условию. Команда SELECT, выполняющая иерархический запрос, не может содержать соединение.

Фраза START WITH – задает строку/строки, лежащие в корне иерархии. В этом выражении определяется условие, которому должны соответствовать корневые строки. Условие может содержать вложенные запросы. Если фраза START WITH не задана, то все строки таблицы являются корневыми.

Предложение CONNECT BY – задает отношение между родительскими и дочерними строками в иерархии. Отношение задается «condition_1», в котором должен быть использован оператор PRIOR, относящееся к родительской строке.

Параметр NOCYCLE указывает, что строки выбираются, даже если в иерархии присутствует цикл.

Приведем примеры иерархических запросов. Как отмечалось выше, таблица EMPLOYEES схемы HR содержит отношение иерархии «руководитель-подчиненный». Следующий запрос определяет это отношение.

SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

Результат выполнения запроса (фрагмент):

EMPLOEEY_ID		LAST_NAME		MANAGER_ID
101			Kochhar			100
108			Greenberg		101
109			Faviet			108
113			Popp			108
112			Urman			108
111			Sciarra			108
……

В следующем примере предыдущий запрос дополнен псевдоколонкой (встроенной в SQL для конкретной СУБД) LEVEL, чтобы показать родительскую строку для каждой дочерней строки.

SELECT employee_id, last_name, manager_id, LEVEL
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

Результат выполнения запроса (фрагмент):

EMPLOEEY_ID	        LAST_NAME		MANAGER_ID	        LEVEL
101			Kochhar			100			1
108			Greenberg		101			2
109			Faviet			108			3
113			Popp			108			3
112			Urman			108			3
111			Sciarra			108			3
…..

В следующем запросе предыдущий запрос дополнен указанием на корневую строку иерархии (на директора организации).

SELECT last_name, employee_id, manager_id, LEVEL
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id;

Результат выполнения запроса (фрагмент):

LAST_NAME 	        EMPLOEEY_ID             MANAGER_ID		LEVEL
King			100			NULL			1
Kochhar			101			100			2
Greenberg		108			101			3
Faviet			109			108			4
Chen			110			108			4
…..

На этом мы заканчиваем знакомство с SQL на примере диалекта Oracle 11g.

Резюме

В предыдущих лекциях было предложено посмотреть на SQL с точки зрения математической логики и теории множеств. Изложение было адаптировано на неподготовленного читателя. Подробно теория реляционных баз данных и SQL изложена в Мейер Д. Теория реляционных баз данных. 1987.

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

Материал этой лекции является углублением знания SQL, полученного в лекциях I-3.

Синтаксис команды CREATE TABLE

CREATE TABLE [имя_схемы.] имя_таблицы ({ограничение_целостности_таблицы | имя_колонки тип_данных_колонки

[DEFAULT выражение] [ограничение_целостности_колонки]}, … )

[{CLUSTER имя_кластера (имя_колонки [, имя_колонки …]) /

{PCTFREE целое / PCTUSED целое / INITRANS целое / MAXTRANS целое /TABLESPACE имя_табличной_области | STORADE размер_памяти |

{RECOVERABLE / UNRECOVERABLE}} … ]

[ PARALLEL возможность_параллельной_обработки]

[{ ENABLE проверяемые_ограничения_целостности |

DISABLE игнорируемые_ограничения_целостности} …]

[AS запрос]

[CACHE/NOCACHE]

Синтаксис команды UPDATE

UPDATE имя_таблицы|имя_представления [корреляционное_имя]

SET имя_колонки = выражение|NULL

[WHERE условие_поиска|CURRENT OF имя_курсора [CHECK EXISTS]]

Синтаксис команды DELETE

DELETE имя_таблицы|имя_представления [корреляционное_имя]

[WHERE условие_поиска|CURRENT OF имя_курсора]

Синтаксис команды SELECT

SELECT [DISTINCT/ALL] {*/[имя_схемы.] {имя_таблицы | имя_представления|имя_снимка}.*/выражение [[AS] альтернативное_имя_колонки]}

[.{имя_схемы.]{имя_таблицы|имя_представления|имя_снимка}.*/выражение [[AS] альтернативное_имя_колонки]}] …}

FROM {[имя_схемы.] {{имя_таблицы/имя_представления/имя_снимка} [@имя_связиБД]} /(имя_подзапроса)} /{[локальное_альтернативное_имя]] …

[WHERE условие]

{[GROUP BY выражение [,выражение]

[HAVING условие]}