В статье содержатся некоторые рекомендации, направленные на то, чтобы облегчить создание мобильных прикладных информационных систем, опирающихся на использование реляционных систем управления базами данных (СУБД), которые поддерживают международный стандарт языка баз данных (БД) SQL. Чтобы лучше прояснить смысл статьи, необходимо сделать несколько предварительных замечаний.
Под мобильностью прикладной системы мы понимаем не только возможность ее простого переноса на другую аппаратную платформу, но и возможность сравнительно легкого приспособления к использованию другой СУБД. Мы не рассматриваем в этом документе проблемы переносимости, связанные с особенностями операционных систем. Заметим, что в общем случае проблемы переноса будут существенно проще, если целевыми аппаратными средствами являются UNIX-компьютеры, причем в качестве операционной системы используются современные версии ОС UNIX, соответствующие международным стандартам (например системы семейства System V Release 4.x), а в качестве языка программирования используется хорошо стандартизованный язык (далее мы предполагаем использование языка ANSI Си). Конечно, при некоторых дополнительных ограничениях на программирование (если это позволяет специфика прикладной системы) иногда можно добиться возможности несложного переноса прикладной системы в среду другой операционной системы.
Когда мы говорим о возможности приспособления прикладной системы к использованию различных СУБД, то, конечно, имеем в виду не произвольные СУБД, а системы, поддерживающие международный стандарт языка SQL. Другими словами, мы предполагаем прямое использование языка SQL при разработке прикладной системы, а также то, что все взаимодействия с системой БД производятся только с использованием этого языка. На самом деле, это существенно ограничивает возможный набор СУБД. Например, если в некоторой СУБД поддерживается доступ к БД на основе некоторого подмножества SQL, из этого не следует автоматически, что прикладная система может быть легко приспособлена к использованию этой СУБД. Реализация стандарта SQL, вообще говоря, означает, что для работы с БД не требуется привлечение никакого другого языка.
К сожалению, на практике дела обстоят не совсем так, и в разных СУБД, производители которых объявляют их соответствующими стандарту SQL, достаточно часто реализуются немного разные языки. Частично это объясняется недостатками самого стандарта, частично - историческими и конъюнктурными обстоятельствами. К сожалению, такова текущая реальность, и к ней нужно приспосабливаться.
Дополнительной трудностью при подготовке этой статьи было то, что в настоящее время происходит постепенный (жаль только, слишком затянутый) переход от одного стандарта языка SQL к другому. Первый международный стандарт языка SQL был принят в 1989 г. (далее мы будем называть его SQL/89), и подавляющее большинство доступных на рынке СУБД поддерживают именно этот стандарт. Все было бы в порядке, если бы этот стандарт был достаточно полным. Но, к сожалению, он обладает по крайней мере двумя недостатками.
Во-первых, очень многие важные свойства языка стандарт устанавливает как определяемые в реализации или зависимые от нее. Это дало большой простор к расхождениям между различными реализациями SQL. Во-вторых, некоторые практически важные аспекты языка вообще не упоминаются в стандарте SQL/89. К ним прежде всего относятся правила встраивания языка SQL в язык программирования Си и так называемый динамический SQL. Естественно, во всех коммерческих СУБД реализованы какие-то варианты этих возможностей. Как правило, они очень близки, но отсутствие стандарта не гарантирует их идентичность.
В конце 1992 г. был принят новый международный стандарт языка SQL (SQL/92). И он не лишен недостатков, но в то же время является существенно более точным и полным, чем SQL/89. SQL/92 не только восполняет недостатки SQL/89, но содержит также много новых свойств. К настоящему времени наиболее известные производители реляционных СУБД вплотную подошли к полной реализации SQL/92, однако еще ее не достигли. Но даже если ориентироваться на использование СУБД, поддерживающей стандарт SQL/89, необходимо некоторое знакомство со стандартом SQL/92, поскольку этот стандарт во многом базировался на расширениях языка, имеющихся в различных реализациях. При создании прикладных систем обойтись без использования этих расширений иногда невозможно, а единственным способом анализа совместимости разных расширений SQL/89 является SQL/92.
Естественно, эта статья не может служить заменой текстов стандартов языка SQL и фирменной документации какой-либо конкретной СУБД. Цель статьи состоит в том, чтобы по возможности облегчить работу по освоению этих основных документов, отметить некоторые тонкие места, связанные с мобильностью. В некоторых случаях, когда вероятность расхождения между разными реализациями может быть особенно велика, будут предлагаться возможные решения локализации проблем. Кроме того, будут приведены некоторые (неполные, но наиболее важные по мнению автора) сведения по поводу SQL/89, SQL/92, а также нового разрабатываемого стандарта SQL-3.
2. Язык баз данных SQL/89
В этом разделе мы опишем некоторые черты языка SQL/89, сопровождая описание соображениями о целесообразности и/или способе использования тех или иных конструкций при программировании потенциально мобильных прикладных систем.
2.1 Структура стандарта и его характеристика
Стандарт SQL/89 состоит из 9 глав и 6 приложений. Первые три главы ("Назначение и область применения", "Ссылки" и "Обзор") содержат достаточно формальную информацию, не существенную для пользователей.
В четвертой главе ("Понятия") на неформальном уровне описываются основные концепции языка, в том числе типы данных, столбцы, таблицы, ограничения целостности, схемы, привилегии, транзакции и т.д.
Пятая глава ("Общие элементы") содержит формальные определения (описание синтаксиса и семантики) элементов языка. К наиболее важным разделам этой главы относятся определения типов данных языка SQL/89; предикатов, которые допускается использовать в условиях выборки; общей структуры запросов.
Шестая глава ("Язык определения схем") посвящается средствам определения схемы БД в SQL/89.
В седьмой главе ("Язык модулей") описывается один из видов сопряжения SQL с традиционными языками программирования, наиболее близкий к так называемым хранимым процедурам (термин, широко используемый в большинстве современных коммерческих СУБД, но не определенный в стандарте).
Восьмая глава ("Язык манипулирования данными") содержит формальное описание синтаксиса и семантики наиболее важной для прикладного программирования части языка SQL - набора операторов непосредственного манипулирования хранимыми в БД данными.
Наконец, в девятой главе ("Уровни") специфицируются два уровня языка SQL/89. В основном это сделано для того, чтобы можно было объявить соответствующей стандарту какую-либо более старую реализацию, в которой не поддерживаются все свойства стандарта.
В приложениях (формально не являющихся частью стандарта) определяются общие правила встраивания конструкций языка SQL в программу, написанную на традиционном языке программирования, а также конкретные правила встраивания для языков программирования Кобол, Фортран, Паскаль и ПЛ/1.
Если характеризовать текст стандарта с точки зрения практически заинтересованного читателя, нужно заметить, что читать его (даже в переводе на русский язык) - это трудная и неприятная задача. Стремление добиться точных и недвусмысленных формулировок часто приводит к появлению совершенно неудобочитаемых предложений. Из имеющихся более просто читаемых толкований стандарта SQL/89 следует отметить одно из первых изданий книги Дейта "Стандарт SQL" (в последнем издании описан стандарт SQL/92). Лучшим способом изучения стандарта было бы чтение этой книги с параллельным заглядыванием в текст стандарта по мере необходимости. К сожалению, на русском языке эти книги не изданы (и, насколько мне известно, даже не переведены).
2.2 Типы данных
В языке SQL/89 поддерживаются следующие типы данных: CHARACTER, NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION. Эти типы данных классифицируются на типы строк символов, точных чисел и приблизительных чисел.
К первому классу относится тип CHARACTER. Спецификатор типа имеет вид CHARACTER (lenght), где lenght задает длину строк данного типа. Заметим, что в SQL/89 нет типа строк переменного размера, хотя во многих реализациях они допускаются. Литеральные строки символов изображаются в виде "последовательность-символов" (например "example").
Представителями второго класса типов являются NUMERIC, DECIMAL (или DEC), INTEGER (или INT) и SMALLINT. Спецификатор типа NUMERIC имеет вид NUMERIC [(precision [, scale])]. Специфицируются точные числа, представляемые с точностью precision и масштабом scale. Здесь и далее, если опущен масштаб, то он полагается равным 0, а если опущена точность, то ее значение по умолчанию определяется в реализации.
Спецификатор типа DECIMAL (или DEC) имеет вид DECIMAL [(precision [, scale])]. Специфицируются точные числа, представленные с масштабом scale и точностью, равной или большей значения precision.
INTEGER специфицирует тип данных точных чисел с масштабом 0 и определяемой в реализации точностью. SMALLINT специфицирует тип данных точных чисел с масштабом 0 и определямой в реализации точностью, не большей, чем точность чисел типа INTEGER.
Литеральные значения точных чисел в общем случае представляются в форме [+|-] <целое-без-знака> [.<целое-без-знака>].
Наконец, в классу типов данных приблизительных чисел относятся типы FLOAT, REAL и DOUBLE PRECISION. Спецификатор типа FLOAT имеет вид FLOAT [(precision)]. Специфицируются приблизительные числа с двоичной точностью, равной или большей значения precision.
REAL специфицирует тип данных приблизительных чисел с точностью, определенной в реализации. DOUBLE PRECISION специфицирует тип данных приблизительных чисел с точностью, определенной в реализации и большей, чем точность типа REAL.
Литеральные значения приблизительных чисел в общем случае представляются в виде <литеральное-значение-точного-числа>E<целое-со-знаком>.
Заметим, что, хотя с использованием языка SQL можно определить схему БД, содержащую данные любого из перечисленных типов, возможность использования этих данных в прикладных системах зависит от применяемого языка программирования. Весь набор типов данных можно прямо (без потребности в специальных библиотечных функциях) использовать, только если программировать на ПЛ/1. Поэтому в некоторых реализациях SQL типы данных с масштабом и точностью вообще не поддерживаются.
Хотя правила встраивания SQL в программы на языке Си не определены в SQL/89, в большинстве реализаций, поддерживающих такое встраивание, имеется следующее соответствие между типами данных SQL и типами данных Си: CHARACTER соответствует строкам Си; INTEGER соответствует long; SMALLINT соответствует short; REAL соответствует float; DOUBLE PRECISION соответствует double (именно такое соответствие утверждено в стандарте SQL/92).
Заметим еще, что в большинстве реализаций SQL поддерживаются некоторые дополнительные типы данных, например DATE, TIME, INTERVAL, MONEY. Некоторые из этих типов специфицированы в стандарте SQL/92, но в текущих реализациях синтаксические и семантические свойства таких типов могут различаться.
2.3 Структура запросов
Для того чтобы было можно более или менее точно рассказать про структуру запросов в стандарте SQL/89, необходимо начать со сводки синтаксических правил:
::=
[
::=
| UNION [ALL]
::=
| ()
::=
(SELECT [ALL | DISTINCT]
)
SELECT [ALL | DISTINCT]
INTO
::=
(SELECT [ALL | DISTINCT]
::=
[]
[]
[]
Язык допускает три типа синтаксических конструкций, начинающихся с ключевого слова SELECT: спецификация курсора (cursor specification), оператор выборки (select statement) и подзапрос (subquery). В основе каждой из них лежит синтаксическая конструкция "табличное выражение (table expression)". Семантика табличного выражения состоит в том, что на основе последовательного применения разделов from, where, group by и having из заданных в разделе from-таблиц строится некоторая новая результирующая таблица, порядок следования строк которой не определен и среди строк которой могут находиться дубликаты (т.е. в общем случае таблица-результат табличного выражения является мультимножеством строк). На самом деле именно структура табличного выражения в наибольшей степени характеризует структуру запросов языка SQL/89. Мы рассмотрим структуру и смысл разделов табличного выражения ниже, но до этого немного подробнее обсудим три упомянутые конструкции, включающие табличные выражения.
2.3.1 Спецификация курсора
Наиболее общей является конструкция "спецификация курсора". Курсор - это средство языка SQL, позволяющее с помощью набора специальных операторов получить построчный доступ к результату запроса к БД. К табличным выражениям, участвующим в спецификации курсора, не предъявляются какие-либо ограничения. Как видно из сводки синтаксических правил, при определении спецификации курсора используются три дополнительных конструкции: спецификация запроса, выражение запросов и раздел ORDER BY.
2.3.1.1 СПЕЦИФИКАЦИЯ ЗАПРОСА
В спецификации запроса задается список выборки (список арифметических выражений над значениями столбцов результата табличного выражения и констант). В результате применения списка выборки к результату табличного выражения производится построение новой таблицы, содержащей то же число строк, но, вообще говоря, другое число столбцов, значения которых формируются на основе вычисления соответствующих арифметических выражений из списка выборки. Кроме того, в спецификации запроса могут содержаться ключевые слова ALL или DISTINCT. При наличии ключевого слова DISTINCT из таблицы, полученной применением списка выборки к результату табличного выражения, удаляются строки-дубликаты; при указании ALL (или просто при отсутствии DISTINCT) удаление строк-дубликатов не производится.
2.3.1.2 ВЫРАЖЕНИЕ ЗАПРОСОВ
Выражение запросов - это выражение, строящееся по указанным синтаксическим правилам на основе спецификаций запросов. Единственной операцией, которую разрешается использовать в выражениях запросов SQL/89, является операция UNION (объединение таблиц) с возможной разновидностью UNION ALL. К таблицам-операндам выражения запросов предъявляется то требование, что все они должны содержать одно и то же число столбцов, и соответствующие столбцы всех операндов должны быть одного и того же типа. Выражение запросов вычисляется слева направо с учетом скобок. При выполнении операции UNION производится обычное теоретико-множественное объединение операндов, т.е. из результирующей таблицы удаляются дубликаты. При выполнении операции UNION ALL образуется результирующая таблица, в которой могут содержаться строки-дубликаты.
2.3.1.3 РАЗДЕЛ ORDER BY
Наконец, раздел ORDER BY позволяет установить желаемый порядок просмотра результата выражения запросов. Синтаксис ORDER BY следующий:
::=
ORDER BY
[{,}...]
::=
{ | }
[ASC | DESC]
Как видно из этих синтаксических правил, фактически задается список столбцов результата выражения запросов, и для каждого столбца указывается порядок просмотра строк результата в зависимости от значений этого столбца (ASC - по возрастанию (умолчание), DESC - по убыванию). Столбцы можно задавать их именами тогда и только тогда, когда (1) выражение запросов не содержит операций UNION или UNION ALL и (2) в списке выборки спецификации запроса этому столбцу соответствует арифметическое выражение, состоящее только из имени столбца. Во всех остальных случаях в разделе ORDER BY должен указываться порядковый номер столбца в таблице-результате выражения запросов.
2.3.2 Оператор выборки
Оператор выборки - это отдельный оператор языка SQL/89, позволяющий получить результат запроса в прикладной программе без использования курсора. Поэтому оператор выборки имеет синтаксис, отличающийся от синтаксиса спецификации курсора, и при выполнении оператора возникают ограничения на результат табличного выражения. Фактически, и то и другое диктуется спецификой оператора выборки как одиночного оператора SQL: при его выполнении результат должен быть помещен в переменные прикладной программы. Поэтому в операторе появляется раздел INTO, содержащий список переменных прикладной программы, и возникает то ограничение, что результирующая таблица должна содержать не более одной строки. Соответственно, результат базового табличного выражения должен содержать не более одной строки, если оператор выборки не содержит спецификации DISTINCT, и таблица, полученная применением списка выборки к результату табличного выражения, должна состоять только из строк-дубликатов, если спецификация DISTINCT задана.
Замечание: в диалекте SQL СУБД Oracle имеется расширенный вариант оператора выборки, результатом которого не обязательно является таблица из одной строки. Такое расширение не поддерживается ни в SQL/89, ни в SQL/92.
2.3.3 Подзапрос
Наконец, последняя конструкция SQL/89, которая может содержать табличные выражения, - это подзапрос, т.е. запрос, который может входить в предикат условия выборки оператора SQL. В SQL/89 к подзапросам применяется то ограничение, что результирующая таблица должна содержать в точности один столбец. Поэтому в синтаксических правилах, определяющих подзапрос, вместо списка выборки указано "выражение, вычисляющее значение", т.е. арифметическое выражение. Заметим еще, что поскольку подзапрос всегда вложен в некоторый другой оператор SQL, то вместо констант в арифметическом выражении выборки и логических выражениях разделов WHERE и HAVING можно использовать значения столбцов текущих строк таблиц, участвующих в (под)запросах более внешнего уровня. Более подробно мы обсудим это ниже при описании семантики табличных выражений.
2.3.4 Табличное выражение
Стандарт SQL/89 рекомендует рассматривать вычисление табличного выражения как последовательное применение разделов FROM, WHERE, GROUP BY и HAVING к таблицам, заданным в списке FROM. Раздел FROM имеет следующий синтаксис:
::=
FROM
[{,
}...]
::=
[]
2.3.4.1 РАЗДЕЛ FROM
Результатом выполнения раздела FROM является расширенное декартово произведение таблиц, заданных списком таблиц раздела FROM. Расширенное декартово произведение (расширенное, потому что в качестве операндов и результата допускаются мультимножества) в стандарте определяется следующим образом:
"расширенное произведение R есть мульти-множество всех строк r, таких, что r является конкатенацией строк из всех идентифицированных таблиц в том порядке, в котором они идентифицированы. Мощность R есть произведение мощностей идентифицированных таблиц. Порядковый номер столбца в R есть n+s, где n - порядковый номер порождающего столбца в именованной таблице T, а s - сумма степеней всех таблиц, идентифицированных до T в разделе FROM". (Возможно, читатель не испытает особого восторга от приведенного абзаца, но таков стиль стандарта.)
Как видно из синтаксиса, рядом с именем таблицы можно указывать еще одно имя "correlation name". Фактически, это некоторый синоним имени таблицы, который можно использовать в других разделах табличного выражения для ссылки на строки именно этого вхождения таблицы. (Одна и та же таблица может участвовать несколько раз в списке одного раздела FROM и/или входить в списки разделов FROM нескольких (под)запросов.)
Если табличное выражение содержит только раздел FROM (это единственный обязательный раздел табличного выражения), то результат табличного выражения совпадает с результатом раздела FROM.
2.3.4.2 РАЗДЕЛ WHERE
Если в табличном выражении присутствует раздел WHERE, то далее вычисляется он. Синтаксис раздела WHERE следующий:
::=
WHERE
::=
| OR
::=
| AND
::=
[NOT]
::=
| ()
Вычисление раздела WHERE производится по следующим правилам: пусть R - результат вычисления раздела FROM. Тогда условие поиска (search condition) применяется ко всем строкам R, и результатом раздела WHERE является таблица, состоящая из тех строк R, для которого результатом вычисления условия поиска является true. Если условие выборки включает подзапросы, то каждый подзапрос вычисляется для каждого кортежа таблицы R (в стандарте используется термин "effectively" в том смысле, что результат должен быть таким, как если бы каждый подзапрос действительно вычислялся заново для каждого кортежа R, хотя реально это требуется далеко не всегда).
Заметим, что поскольку SQL/89 допускает наличие в базе данных неопределенных значений, то вычисление условия поиска должно производиться не в булевой, а в трехзначной логике со значениями true, false и unknown (неизвестно). Для любого предиката известно, в каких ситуациях он может порождать значение unknown. Булевские операции AND, OR и NOT работают в трехзначной логике следующим образом:
true AND unknown = unknown
unknown AND true = unknown
unknown AND unknown = unknown
true OR unknown = true
unknown OR true = true
unknown OR unknown = unknown
NOT unknown = unknown
Среди предикатов условия поиска в соответствии с SQL/89 могут находиться следующие предикаты: предикат сравнения, предикат between, предикат in, предикат like, предикат null, предикат с квантором и предикат exists. Сразу заметим, что во всех реализациях SQL на эффективность выполнения запроса существенно влияет наличие в условии поиска простых предикатов сравнения (предикатов, задающих сравнение столбца таблицы с константой). Наличие таких предикатов позволяет СУБД использовать индексы при выполнении запроса, т.е. избегать полного просмотра таблицы. Хотя в принципе язык SQL дает возможность пользователям не заботиться о конкретном наборе предикатов в условии выборки (лишь бы они были синтаксически и семантически правильны), при реальном использовании SQL-ориентированных СУБД такие технические детали стоит иметь в виду.
2.3.4.2.1 Предикат сравнения
Синтаксис предиката сравнения определяется следующими правилами:
::=
{ | }
::=
= | <> | < | > | <= | >=
Через "<>" обозначается операция "неравенства". Арифметические выражения левой и правой частей предиката сравнения строятся по общим правилам построения арифметических выражений и могут включать в общем случае имена столбцов таблиц из раздела FROM и константы (не обязательно литеральные; вместо литеральной константы может использоваться имя столбца таблицы, указанной в разделе FROM более внешнего подзапроса, или имя переменной программы, написанной на объемлящем языке). Типы данных арифметических выражений должны быть сравнимыми (например, если тип столбца a таблицы A является типом символьных строк, то предикат "a = 5" недопустим).
Если правый операнд операции сравнения задается подзапросом, то дополнительным ограничением является то, что мощность результата подзапроса должна быть не более единицы. Если хотя бы один из операндов операции сравнения имеет неопределенное значение или если правый операнд является подзапросом с пустым результатом, то значение предиката сравнения равняется unknown.
Заметим, что значение арифметического выражения не определено, если в его вычислении участвует хотя бы одно неопределенное значение. Еще одно важное замечание из стандарта SQL/89: в контексте GROUP BY, DISTINCT и ORDER BY неопределенное значение выступает как специальный вид определенного значения, т.е. возможно, например, образование группы строк, значение указанного столбца которых является неопределенным. Для обеспечения переносимости прикладных программ нужно внимательно анализировать специфику работы с неопределенными значениями в конкретной СУБД.
2.3.4.2.2 Предикат between
Предикат between имеет следующий синтаксис:
::=
[NOT] BETWEEN AND
По определению результат "x BETWEEN y AND z" тот же самый, что результат логического выражения "x >= y AND x <= z". Результат "x NOT BETWEEN y AND z" тот же самый, что результат "NOT (x BETWEEN y AND z)".
2.3.4.2.3 Предикат in
Предикат in определяется следующими синтаксическими правилами:
::=
[NOT] IN
{ | ()}
::=
{,}...
Типы левого операнда и значений из списка правого операнда (напомним, что результирующая таблица подзапроса должна содержать ровно один столбец) должны быть сравнимыми.
Значение предиката равно true в том и только в том случае, когда значение левого операнда совпадает хотя бы с одним значением списка правого операнда. Если список правого операнда пуст (так может быть, если правый операнд задается подзапросом) или значение "подразумеваемого" предиката сравнения x = y (где x - значение арифметического выражения левого операнда) равно false для каждого элемента y списка правого операнда, то значение предиката in равно false. В противном случае значение предиката in равно unknown (например, так может быть, если значение левого операнда есть NULL). По определению значение предиката "x NOT IN S" равно значению предиката "NOT (x IN S)".
2.3.4.2.4 Предикат like
Предикат like имеет следующий синтаксис:
::=
[NOT] LIKE
[ESCAPE ]
::=
::=
Типы данных столбца левого операнда и образца должны быть типами символьных строк. В разделе ESCAPE должен специфицироваться одиночный символ.
Значение предиката равно true, если pattern является подстрокой заданного столбца. При этом если раздел ESCAPE отсутствует, то при сопоставлении шаблона со строкой производится специальная интерпретация двух символов шаблона: символ подчеркивания ("_") обозначает любой одиночный символ; символ процента ("%") обозначает последовательность произвольных символов произвольной длины (может быть, нулевой).
Если же раздел ESCAPE присутствует и специфицирует некоторый одиночный символ x, то пары символов "x_" и "x%" представляют одиночные символы "_" и "%", соответственно.
Значение предиката like есть unknown, если значение столбца либо шаблона не определено.
Значение предиката "x NOT LIKE y ESCAPE z" совпадает со значением "NOT x LIKE y ESCAPE z".
2.3.4.2.5 Предикат null
Предикат null описывается синтаксическим правилом
::=
IS [NOT] NULL
Этот предикат всегда принимает значения true или false. При этом значение "x IS NULL" равно true тогда и только тогда, когда значение x не определено. Значение предиката "x NOT IS NULL" равно значению "NOT x IS NULL".
2.3.4.2.6 Предикат с квантором
Предикат с квантором имеет следующий синтаксис:
::=
::=
|
::= ALL
::= SOME | ANY
Обозначим через x результат вычисления арифметического выражения левой части предиката, а через S результат вычисления подзапроса.
Предикат "x ALL S" имеет значение true, если S пусто или значение предиката "x s" равно true для каждого s, входящего в S. Предикат "x ALL S" имеет значение false, если значение предиката "x s" равно false хотя бы для одного s, входящего в S. В остальных случаях значение предиката "x ALL S" равно unknown.
Предикат "x SOME S" имеет значение false, если S пусто или значение предиката "x s" равно false для каждого s, входящего в S. Предикат "x SOME S" имеет значение true, если значение предиката "x s" равно true хотя бы для одного s, входящего в S. В остальных случаях значение предиката "x SOME S" равно unknown.
2.3.4.2.7 Предикат exists
Предикат exists имеет следующий синтаксис:
::=
EXISTS
Значением этого предиката всегда является true или false, и это значение равно true тогда и только тогда, когда результат вычисления подзапроса не пуст.
2.3.4.3 РАЗДЕЛ GROUP BY
Если в табличном выражении присутствует раздел GROUP BY, то далее выполняется он. Синтаксис раздела GROUP BY следующий:
::=
GROUP BY
[{,}...]
Если обозначить через R таблицу, являющуюся результатом предыдущего раздела (FROM или WHERE), то результатом раздела GROUP BY является разбиение R на множество групп строк, которое состоит из минимального числа таких групп, в которых для каждого столбца из списка столбцов раздела GROUP BY во всех строках каждой группы, включающей более одной строки, значения этого столбца совпадают. Для обозначения результата раздела GROUP BY в стандарте используется термин "сгруппированная таблица".
2.3.4.4 РАЗДЕЛ HAVING
Наконец, последним при вычислении табличного выражения используется раздел HAVING (если он присутствует). Синтаксис этого раздела следующий:
::=
HAVING
Раздел HAVING может осмысленно появиться в табличном выражении только в том случае, когда в нем присутствует раздел GROUP BY. Условие поиска этого раздела задает условие на группу строк сгруппированной таблицы. Формально раздел HAVING может присутствовать и в табличном выражении, не содержащем GROUP BY. В этом случае полагается, что результат вычисления предыдущих разделов представляет собой сгруппированную таблицу (правильнее сказать, псевдосгруппированную), состоящую из одной группы без выделенных столбцов группирования.
Условие поиска раздела HAVING строится по тем же синтаксическим правилам, что и условие поиска раздела WHERE, и может включать те же самые предикаты. Однако имеются специальные синтаксические ограничения по части использования в условии поиска спецификаций столбцов таблиц из раздела FROM данного табличного выражения. Эти ограничения следуют из того, что условие поиска раздела HAVING задает условие на целую группу, а не на индивидуальные строки.
Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.
Результатом выполнения раздела HAVING является сгруппированная таблица, содержащая только те группы строк, для которых результат вычисления условия поиска есть true. В частности, если раздел HAVING присутствует в табличном выражении, не содержащем GROUP BY, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования.
2.3.5 Агрегатные функции и результаты запросов
Агрегатные функции (в стандарте SQL/89 они называются функциями над множествами) определяются следующими синтаксическими правилами:
::=
COUNT(*) |
|
::=
{ AVG | MAX | MIN | SUM | COUNT }
(DISTNICT )
::=
{ AVG | MAX | MIN | SUM }
([ALL] )
Как видно из этих правил, в стандарте SQL/89 определены пять стандартных агрегатных функций: COUNT - число строк или значений, MAX - максимальное значение, MIN - минимальное значение, SUM - суммарное значение и AVG - среднее значение.
2.3.5.1 СЕМАНТИКА АГРЕГАТНЫХ ФУНКЦИЙ
Агрегатные функции предназначены для того, чтобы вычислять некоторое значение для заданного множества строк. Таким множеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или вся таблица. Для всех агрегатных функций, кроме COUNT(*), фактический (т.е. требуемый семантикой) порядок вычислений следующий: на основании параметров агрегатной функции из заданного множества строк производится список значений. Затем по этому списку значений производится вычисление функции. Если список оказался пустым, то значение функции COUNT для него есть 0, а значение всех остальных функций null.
Пусть T обозначает тип значений из этого списка. Тогда результат вычисления функции COUNT - точное число с масштабом и точностью, определяемыми в реализации. Тип результата значений функций MAX и MIN совпадает с T. При вычислении функций SUM и AVG тип T не должен быть типом символьных строк, а тип результата функции - это тип точных чисел с определяемыми в реализации масштабом и точностью, если T - тип точных чисел и тип приблизительных чисел с определяемой в реализации точностью, если T - тип приблизительных чисел.
Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном множестве. Все строки считаются различными, даже если они состоят из одного столбца со значением null во всех строках.
Если агрегатная функция специфицирована с ключевым словом DISTINCT, то список значений строится из значений указанного столбца. (Подчеркнем, что в этом случае не допускается вычисление арифметических выражений!) Далее из этого списка удаляются неопределенные значения, и в нем устраняются значения-дубликаты. Затем вычисляется указанная функция.
Если агрегатная функция специфицирована без ключевого слова DISTINCT (или с ключевым словом ALL), то список значений формируется из значений арифметического выражения, вычисляемого для каждой строки заданного множества. Далее из списка удаляются неопределенные значения, и производится вычисление агрегатной функции. Обратите внимание, что в этом случае не допускается применение функции COUNT!
Замечание: оба ограничения, указанные в двух предыдущих абзацах, являются более техническими, чем принципиальными, и могут отсутствовать в конкретных реализациях. Тем не менее это ограничения стандарта SQL/89, и их нужно придерживаться при мобильном программировании.
2.3.5.2 РЕЗУЛЬТАТЫ ЗАПРОСОВ
Агрегатные функции можно разумно использовать в спецификации курсора, операторе выборки и подзапросе после ключевого слова SELECT (будем называть в этом подразделе все такие конструкции списком выборки, не забывая о том, что в случае подзапроса этот список состоит только из одного элемента), и в условии выборки раздела HAVING. Стандарт допускает более экзотические случаи использования агрегатных функций в подзапросах (агрегатная функция на группе кортежей внешнего запроса), но на практике они встречаются очень редко.
Рассмотрим различные случаи применения агрегатных функций в списке выборки в зависимости от вида табличного выражения.
Если результат табличного выражения R не является сгруппированной таблицей, то появление хотя бы одной агрегатной функции от множества строк R в списке выборки приводит к тому, что R неявно рассматривается как сгруппированная таблица, состоящая из одной (или нуля) групп с отсутствующими столбцами групирования. Поэтому в этом случае в списке выборки не допускается прямое использование спецификаций строк R: все они должны находиться внутри спецификаций агрегатных функций. Результатом запроса является таблица, состоящая не более чем из одной строки, полученной путем применения агрегатных функций к R.
Аналогично обстоит дело в том случае, когда R представляет собой сгруппированную таблицу, но табличное выражение не содержит раздела GROUP BY (и, следовательно, содержит раздел HAVING). Если в предыдущем случае существовало два варианта формирования списка выборки: только с прямым указанием столбцов R или только с указанием их внутри спецификаций агрегатных функций, то в данном случае возможен только второй вариант. Результат табличного выражения явно объявлен сгруппированной таблицей, состоящей из одной группы, и результат запроса можно формировать только путем применения агрегатных функций к этой группе строк. Опять результатом запроса является таблица, состоящая не более чем из одной строки, полученной путем применения агрегатных функций к R.
Наконец, рассмотрим случай, когда R представляет собой "настоящую" сгруппированную таблицу, т.е. табличное выражение содержит раздел GROUP BY и, следовательно, определен по крайней мере один столбец группирования. В этом случае правила формирования списка выборки полностью соответствуют правилам формирования условия выборки раздела HAVING: допускается прямое использование имен столбцов группирования, а имена остальных столбцов R могут появляться только внутри спецификаций агрегатных функций. Результатом запроса является таблица, число строк в которой равно числу групп в R, и каждая строка формируется на основе значений столбцов группирования и агрегатных функций для данной группы.
2.4 Средства определения схемы
Средства определения схемы БД в стандарте SQL/89 относятся к наиболее слабым и допускающим различную интерпретацию частям стандарта. Более того, мне неизвестна ни одна реализация, в которой поддерживался бы в точности такой набор средств определения схемы.
Поэтому, чтобы добиться мобильности прикладной системы в достаточно широком классе реализаций SQL/89, необходимо тщательно локализовать компоненты определения схемы БД. Думаю, что лучше всего сосредоточить всю работу со схемой БД в одном модуле и иметь в виду, что при переходе к другой СУБД очень вероятно потребуется переделка этого модуля.
Особо отметим, что в SQL/89 вообще отсутствуют какие-либо средства изменения схемы БД: нет возможности удалить схему таблицы, добавить к схеме таблицы новый столбец и т.д. Во всех реализациях такие средства поддерживаются, но они могут различаться и синтаксисом, и семантикой. В SQL/92 средства манипулирования схемой специфицированы, и по мере перехода к этому стандарту производители СУБД будут вынуждены начать поддерживать стандартные средства.
Несмотря на отсутствие особых надежд на то, что удастся встретить реализацию, поддерживающую в точности и только язык определения схем SQL/89, мы коротко опишем этот язык (без синтаксических деталей), чтобы оценить на содержательном уровне возможности SQL/89 в этой части и получить хотя бы какие-то средства сравнения разных реализаций.
2.4.1 Оператор определения схемы
В соответствии с правилами SQL/89 каждая таблица данной БД имеет простое и квалифицированное (уточненное) имена. В качестве квалификатора имени выступает "идентификатор полномочий" таблицы, который обычно в реализациях совпадает с именем некоторого пользователя. Квалифицированное имя таблицы имеет вид:
<идентификатор полномочий>.<простое имя>
Подход к определению схемы в SQL/89 состоит в том, что все таблицы с одним идентификатором полномочий создаются (определяются) путем выполнения одного оператора определения схемы. При этом в стандарте не определяется способ выполнения оператора определения схемы: должен ли он выполняться только в интерактивном режиме или может быть встроен в программу, написанную на традиционном языке программирования. (Собственно, поэтому трудно говорить о том, поддерживается ли в конкретной реализации стандарт SQL/89 в части средств определения схемы БД.)
В операторе определения схемы содержится идентификатор полномочий и список элементов схемы, каждый из которых может быть определением таблицы, определением представления (view) или определением привилегий. Каждое из этих определений представляется отдельным оператором SQL/89, но все они, как уже говорилось, должны быть встроены в оператор определения схемы.
Для этих операторов мы приведем синтаксис, поскольку это позволит более четко описать их особенности.
2.4.2 Определение таблицы
Оператор определения таблицы имеет следующий синтаксис:
::=
CREATE TABLE
(
[{,
}...])
::=
|
Кроме имени таблицы в операторе указывается список элементов таблицы, каждый из которых служит либо для определения столбца, либо для определения ограничения целостности определяемой таблицы. Требуется наличие хотя бы одного определения столбца. Оператор CREATE TABLE определяет так называемую базовую таблицу, т.е. реальное хранилище данных.
Для определения столбцов таблицы и ограничений целостности используются специальные операторы, которые должны быть вложены в оператор определения таблицы (не правда ли, это похоже на матрешку?).
2.4.2.1 ОПРЕДЕЛЕНИЕ СТОЛБЦА
Оператор определения столбца описывается следующими синтаксическими правилами:
::=
[]
[...]
::=
DEFAULT { | USER | NULL }
::=
NOT NULL []
|
| CHECK ()
Как видно, кроме обязательной части, в которой задается имя столбца и его тип данных, определение столбца может содержать два необязательных раздела: раздел значения столбца по умолчанию и раздел ограничений целостности столбца.
В разделе значения по умолчанию указывается значение, которое должно быть помещено с строку, заносимую в данную таблицу, если значение данного столбца явно не указано. Значение по умолчанию может быть указано в виде литеральной константы с типом, соответствующим типу столбца; путем задания ключевого слова USER, которому при выполнении оператора занесения строки соответствует символьная строка, содержащая имя текущего пользователя (в этом случае столбец должен иметь тип символьных строк); или путем задания ключевого слова NULL, означающего, что значением по умолчанию является неопределенное значение. Если значение столбца по умолчанию не специфицировано, и в разделе ограничений целостности столбца указано NOT NULL (т.е. наличие неопределенных значений запрещено), то попытка занести в таблицу строку с неспецифицированным значением данного столбца приведет к ошибке.
Указание в разделе ограничений целостности NOT NULL приводит к неявному порождению проверочного ограничения целостности для всей таблицы (см. п. 2.4.2.2) "CHECK (C IS NOT NULL)" (где C - имя данного столбца). Если ограничение NOT NULL не указано, и раздел умолчаний отсутствует, то неявно порождается раздел умолчаний DEFAULT NULL. Если указана спецификация уникальности, то порождается соответствующая спецификация уникальности для таблицы.
Если в разделе ограничений целостности указано ограничение по ссылкам данного столбца (), то порождается соответствующее определение ограничения по ссылкам для таблицы: FOREIGN KEY(C) .
Наконец, если указано проверочное ограничение столбца, то условие поиска этого ограничения должно ссылаться только на данный столбец и неявно порождается соответствующее проверочное ограничение для всей таблицы.
2.4.2.2 ОПРЕДЕЛЕНИЕ ОГРАНИЧЕНИЙ ЦЕЛОСНОСТИ ТАБЛИЦЫ
Раздел определения ограничений целостности таблицы обладает следующим синтаксисом:
::=
|
|
::=
()
::=
UNIQUE | PRIMARY KEY
::=
[{,}...]
::=
FOREIGN KEY ()
::=
REFERENCES
::=
::=
[()]
::=
[{,}...]
::=
CHECK ()
Для одной таблицы может быть задано несколько ограничений целостности, в том числе те, которые неявно порождаются ограничениями целостности столбцов. Стандарт SQL/89 устанавливает, что ограничения таблицы фактически проверяются при выполнении каждого оператора SQL.
Замечание: наличие правильно подобранного набора ограничений БД очень важно для надежного функционирования прикладной информационной системы. Вместе с тем в некоторых СУБД ограничения целостности практически не поддерживаются. Поэтому при проектировании прикладной системы необходимо принять решение о том, что более существенно: рассчитывать на поддержку ограничений целостности, но ограничить набор возможных СУБД или отказаться от их использования на уровне SQL, сохранив возможность применения не самых современных СУБД.
Далее T обозначает таблицу, для которой определяются ограничения целостности.
2.4.2.2.1 Ограничение уникальности
Каждое имя столбца в списке уникальности должно именовать столбец T и не должно входить в этот список более одного раза. При определении столбца, входящего в список уникальности, должно быть указано ограничение столбца NOT NULL. Среди ограничений уникальности T не должно быть более одного определения первичного ключа (ограничения уникальности с ключевым словом PRIMARY KEY).
Действие ограничения уникальности состоит в том, что в таблице T не допускается появление двух или более строк, значения столбцов уникальности которых совпадают.
2.4.2.2.2 Ограничение по ссылкам
Ограничение по ссылкам от заданного набора столбцов CT таблицы T на заданный набор столбцов CT1 некоторой определенной, к этому моменту таблицы T1 задает условие на содержимое обеих этих таблиц, при котором ссылки можно считать корректными.
Если список столбцов CT1 явно специфицирован в определении ограничения по ссылкам, то требуется, чтобы этот список явно входил в какое-либо определение уникальности таблицы T1. Если же список CT1 не специфицирован явно в определении ограничения по ссылкам таблицы T, то требуется, чтобы в определении таблицы T1 присутствовало определение первичного ключа, и список CT1 неявно полагается совпадающим со списком имен столбцов из определения первичного ключа таблицы T1. Имена столбцов списков CT и CT1 должны именовать столбцы таблиц T и T1, соответственно, и не должны появляться в списках более одного раза. Списки столбцов CT и CT1 должны содержать одинаковое число элементов, и столбец таблицы T, идентифицируемый i-м элементом списка CT должен иметь тот же тип, что столбец таблицы T1, идентифицируемый i-м элементом списка CT1.
По определению таблицы T и T1 удовлетворяют заданному ограничению по ссылкам, если для каждой строки s таблицы T такой, что все значения столбцов s, идентифицируемых списком CT, не являются неопределенными, существует строка s1 таблицы T1 такая, что значения столбцов s1, идентифицируемых списком CT1, позиционно равны значениям столбцов s, идентифицируемых списком CT. По-человечески это можно сформулировать так: ограничение по ссылкам удовлетворяется, если для каждой корректной ссылки существует объект, на который она ссылается. В привычной программистам терминологии, ограничение по ссылкам не позволяет производить "висячие" ссылки, не ведущие ни к какому объекту.
2.4.2.2.3 Проверочное ограничение
Проверочное ограничение специфицирует условие, которому должна удовлетворять в отдельности каждая строка таблицы T. Это условие не должно содержать подзапросов, спецификаций агрегатных функций, а также ссылок на внешние переменные или параметров. В него могут входить только имена столбцов данной таблицы и литеральные константы.
Таблица удовлетворяет проверочному ограничению целостности в том и только в том случае, когда вычисление условия для каждой строки таблицы дает true.
Замечание: в некоторых реализациях допускаются расширенные механизмы ограничений по ссылкам и проверочных ограничений. Следует быть внимательным, если не хотите выходить за пределы возможностей стандарта.
2.4.3 Определение представлений
Механизм представлений (view) является мощным средством языка SQL, позволяющим скрыть реальную структуру БД от некоторых пользователей за счет определения представления БД, которое реально является некоторым хранимым в БД запросом с именованными столбцами, а для пользователя ничем не отличается от базовой таблицы БД (с учетом технических ограничений). Любая реализация должна гарантировать, что состояние представляемой таблицы точно соответствует состоянию базовых таблиц, на которых определено представление. Обычно вычисление представляемой таблицы (материализация соответствующего запроса) производится каждый раз при использовании представления.
В стандарте SQL/89 оператор определения представления имеет следующий синтаксис:
::=
CREATE VIEW
[()]
AS
[WITH CHECK OPTION]
::=
[{,}...]
Определяемая представляемая таблица V является изменяемой (т.е. по отношению к V можно использовать операторы DELETE и UPDATE) в том и только в том случае, если выполняются следующие условия для спецификации запроса:
в списке выборки не указано ключевое слово DISTINCT;
каждое арифметическое выражение в списке выборки представляет собой
одну спецификацию столбца, и спецификация одного столбца не появляется
более одного раза;
в разделе FROM указана только одна таблица, являющаяся либо базовой
таблицей, либо изменяемой представляемой таблицей;
в условии выборки раздела WHERE не используются подзапросы;
в табличном выражении отсутствуют разделы GROUP BY и HAVING.
Замечание: эти ограничения являются очень сильными. В реализациях они могут быть ослаблены. Но если стремиться к мобильности, не следует пользоваться расширенными возможностями.
Если в списке выборки спецификации запроса имеется хотя бы одно арифметическое выражение, состоящее не из одной спецификации столбца, или если имя хотя бы одного столбца участвует в списке выборки более одного раза, определение представления должно содержать список имен столбцов представляемой таблицы. Более просто, нужно явно именовать столбцы представляемой таблицы, если эти имена не наследуются от столбцов таблиц раздела FROM спецификации запроса.
Требование WITH CHECK OPTION в определении представления имеет смысл только в случае определения изменяемой представляемой таблицы, которая определяется спецификацией запроса, содержащей раздел WHERE. При наличии этого требования не допускаются изменения представляемой таблицы, приводящие к появлению в базовых таблицах строк, не видимых в представляемой таблице (т.е. таких строк, которые не удовлетворяют условию поиска раздела WHERE спецификации запроса). Если WITH CHECK OPTION в определении представления отсутствует, такой контроль не производится.
2.4.4 Определение привилегий
В соответствии с идеологией языка SQL контроль прав доступа данного пользователя к таблицам БД производится на основе механизма привилегий. Фактически, этот механизм состоит в том, что для выполнения любого действия над таблицей пользователь должен обладать соответствующей привилегией (реально все возможные действия описываются фиксированным стандартным набором привилегий). Пользователь, создавший таблицу, автоматически становится владельцем всех возможных привилегий на выполнение операций над этой таблицей. В число этих привилегий входит привилегия на передачу всех или некоторых привилегий по отношению к данной таблице другому пользователю, включая привилегию на передачу привилегий. Иногда поддерживается и обратная операция изъятия привилегий от пользователя, ранее их получившего.
В SQL/89 определяется упрощенная схема механизма привилегий. Во-первых, "раздача" привилегий возможна только при определении таблицы. Во-вторых, пользователь, получивший некоторые привилегии от других пользователей, может передать их дальше только при определении схемы.
Оператор определения привилегий обладает следующим синтаксисом:
::=
GRANT ON
TO [{,}...]
[WITH GRANT OPTION]
::=
ALL PRIVILEGES
| [{,}...]
::=
SELECT | INSERT | DELETE
| UPDATE [()]
| REFERENCES [(]
::=
[{,}...]
::=
PUBLIC |
Эти синтаксические правила достаточно ясно показывают смысл механизма определения привилегий в SQL/89. Заметим лишь, что необходимо обладать привилегией REFERENCES по отношению к указанным столбцам таблицы T1, чтобы иметь возможность при определении таблицы T специфицировать ограничение по ссылкам между этой таблицей и существующей к этому моменту таблицей T1.
Еще раз заметим, что, хотя в общем смысле во всех SQL-ориентированных СУБД поддерживается механизм защиты данных на основе привилегий доступа, реализации могут различаться в деталях. Это опять то место, которое нужно локализовывать, если стремиться к созданию мобильной прикладной системы.
2.5 Язык модулей или встроенный SQL?
В стандарте SQL/89 определены два способа взаимодействия с БД из прикладной программы, написанной на традиционном языке программирования (как мы уже упоминали, SQL/89 ориентирован на использование совместно с языками Кобол, Фортран, Паскаль и ПЛ/1, но в реализациях обычно поддерживается и язык Си). Первый способ состоит в том, что все операторы SQL, с которыми может работать данная прикладная программа, собраны в один модуль и оформлены как процедуры этого модуля. Для этого SQL/89 содержит специальный подъязык - язык модулей. При использовании такого способа взаимодействия с БД прикладная программа содержит вызовы процедур модуля SQL с передачей им фактических параметров и получением ответных параметров.
Второй способ состоит в использовании так называемого встроенного SQL, когда с использованием специального синтаксиса в программу на традиционном языке программирования встраиваются операторы SQL. В этом случае, с точки зрения прикладной программы, оператор SQL выполняется "по месту". Явная параметризация операторов SQL отсутствует, но во встроенных операторах SQL могут использоваться имена переменных основной программы, и за счет этого обеспечивается связь между прикладной программой и СУБД.
Концептуально эти два способа эквивалентны. Более того, в стандарте устанавливаются правила порождения неявного модуля SQL по программе со встроенным SQL. Однако в большинстве реализаций операторы SQL, содержащиеся в модуле SQL, и встроенные операторы SQL обрабатываются существенно по-разному. Модуль SQL обычно компилируется отдельно от прикладной программы, в результате чего порождается набор так называемых хранимых процедур (в стандарте этот термин не используется, но распространен в коммерческих реализациях). Т.е. в случае использования модуля SQL компиляция операторов SQL производится единожды, и затем соответствующие процедуры сколько угодно раз могут вызываться из прикладной программы.
В отличие от этого, для операторов SQL, встроенных в прикладную программу, компиляция этих операторов обычно производится каждый раз при их использовании (правильнее сказать, при каждом первом использовании оператора при данном запуске прикладной программы).
Конечно, пользователи не обязаны знать об этом техническом различии в обработке двух видов взаимодействия с СУБД. Существуют и такие системы, которые производят одноразовую компиляцию встроенных операторов SQL и сохраняют откомпилированный код. Но все-таки лучше иметь это в виду (в частности, при компиляции оператора SQL непосредственно перед его выполнением вероятно получение более оптимального плана выполнения оператора).
Приведем некоторые соображения за и против каждого из этих двух способов. При использовании языка модулей текст прикладной программы имеет меньший размер, взаимодействия с СУБД более локализованы за счет наличия явных параметров вызова процедур. С другой стороны, для понимания смысла поведения прикладной программы потребуется одновременное чтение двух текстов. Кроме того, как кажется, синтаксис модуля SQL может существенно различаться в разных реализациях. Встроенный SQL предоставляет возможность производства более "самосодержащихся" прикладных программ. Имеется больше оснований рассчитывать на простоту переноса такой программы в среду другой СУБД, поскольку стандарт встраивания более или менее соблюдается. Основным недостатком является некоторый PL-подобный вид таких программ, независимо от выбранного основного языка. И конечно, нужно учитывать замечания, содержащиеся в предыдущих абзацах.
Далее мы коротко опишем язык модулей и правила встраивания в соответствии со стандартом SQL/89 (еще раз заметим, что формально правила встраивания не являются частью стандарта).
2.5.1 Язык модулей
Структура модуля SQL в стандарте SQL/89 определяется следующими синтаксическими правилами:
::=
[...]
< procedure > ...
::=
MODULE []
::=
LANGUAGE { COBOL | FORTRAN | PASCAL | PLI }
::=
AUTHORIZATION
::=
Существенно, что каждый модуль SQL ориентирован на использование в программах, написанных на конкретном языке программирования. Если в модуле присутствуют процедуры работы с курсорами (см. п. 2.6.1), то все курсоры должны быть специфицированы в начале модуля. Заметим, что объявление курсора не погружается в какую-либо процедуру, поскольку это описательный, а не выполняемый оператор SQL (другими словами, все курсоры будут автоматически объявлены в самом начале выполнения прикладной программы, связанной с модулем SQL).
2.5.1.1 ОПРЕДЕЛЕНИЕ ПРОЦЕДУРЫ
Процедуры в модуле SQL определяются в следующем синтаксисе:
::=
PROCEDURE
...;
;
::=
|
::=
SQLCODE
::=
|
|
|
|
|
|
|
|
|
|
Имена всех процедур в одном модуле должны быть различны. Любое имя параметра, содержащегося в операторе SQL процедуры, должно быть специфицировано в разделе объявления параметров. Число фактических параметров при вызове процедуры должно совпадать с числом формальных параметров, указанных при ее объявлении. Список формальных параметров каждой процедуры должен содержать ровно один параметр SQLCODE - код ответа процедуры; возможные значения кодов ответа стандартизованы, но некоторые (правильнее сказать, абсолютное большинство) из них определяются в реализации.
2.5.2 Встроенный SQL
Поскольку в стандарте SQL/89 не специфицированы (даже в приложениях) правила встраивания SQL в язык Си, мы приведем только общие синтаксические правила встраивания, используемые для любого языка. Это поможет оценить "уровень стандартности" конкретной реализации.
::=
{
|
| }
[]
::=
EXEC SQL
::=
END EXEC | ;
::=
[...]
::=
BEGIN DECLARE SECTION
[]
::=
END DECLARE SECTION
[]
::=
:
::=
WHENEVER
::=
SQLERROR | NOT FOUND
::=
CONTINUE |
::=
{ GOTO | GO TO }
::= : |
Встраиваемые операторы SQL, включая объявления курсора, а также разделы объявления исключительных ситуаций и переменных основной программы, должны быть окружены скобками EXEC SQL и END EXEC. Объявление курсора должно встречаться текстуально раньше любого оператора, ссылающегося на этот курсор. Все переменные основной программы, используемые во встроенных операторах SQL, должны быть объявлены в текстуально предшествующем этому оператору разделе объявления переменных основной программы. При этом синтаксис объявления переменной соответствует синтаксису основного языка программирования, но имени переменной предшествует двоеточие.
Механизм обработки исключительных ситуаций в SQL/89 крайне прост (можно сказать, примитивен). Можно задавать реакцию на возникновение двух видов условий: SQLERROR - это условие появления отрицательного значения в переменной SQLCODE после выполнения встроенного оператора; NOT FOUND - условие появления в SQLCODE значения +100 (этот код означает исчерпание результирующего множества запроса при его просмотре через курсор). Реакция может состоять в выполнении безусловного перехода на метку основной программы (действие GO TO) или отсутствовать (действие CONTINUE). Срабатывает тот оператор определения реакции на исключительную ситуацию, который текстуально ближе от начала программы к данному оператору SQL.
Заметим, что во многих реализациях поддерживается два вида кодов ответа при выполнении операторов SQL (встроенных или взятых из модуля): через переменную SQLCODE с кодами ответа, представляемыми целыми числами, и через переменную SQLSTATE с кодами ответа, которые кодируются десятичными числами, представленными в текстовой форме. Имеется тенденция к переходу на использование только механизма SQLSTATE, но в стандартных реализациях должен поддерживаться и механизм SQLCODE.
2.6 Набор операторов манипулирования данными
В стандарте SQL/89 определен очень ограниченный набор операторов манипулирования данными. Их можно классифицировать на группы операторов, связанных с курсором; одиночных операторов манипулирования данными; и операторов завершения транзакции. Все эти операторы можно использовать как в модулях SQL, так и во встроенном SQL. Заметим, что в SQL/89 не определен набор операторов интерактивного SQL (т.е. отсутствуют явные спецификации набора операторов SQL, которые могут задаваться в интерактивном режиме).
2.6.1 Операторы, связанные с курсором
Операторы этой группы объединяет то, что все они работают с некоторым курсором, объявление которого должно содержаться в том же модуле или программе со встроенным SQL. Если говорить неформально, курсор - это механизм языка SQL, предназначенный для того, чтобы позволить прикладной программе последовательно, строка за строкой, просмотреть результат связанного с курсором запроса.
2.6.1.1 ОПЕРАТОР ОБЪЯВЛЕНИЯ КУРСОРА
Для удобства мы повторим здесь синтаксические правила объявления курсора, приведенные в подразделе 2.3.1:
::=
DECLARE CURSOR
FOR
::=
[...]
::=
| UNION [ALL]
::=
| ()
::=
ORDER BY
[{,}...]
::=
{ | }
[ASC | DESC]
В объявлении курсора могут задаваться запросы наиболее общего вида с возможностью выполнения операции UNION и сортировки конечного результата. Этот оператор не является выполняемым, он только связывает имя курсора со спецификацией курсора.
2.6.1.2 ОПЕРАТОР ОТКРЫТИЯ КУРСОРА
Оператор описывается следующим синтаксическим правилом:
::=
OPEN
В реализациях встроенного SQL обычно требуется, чтобы объявление курсора текстуально предшествовало оператору открытия курсора. Оператор открытия курсора должен быть первым в серии выполняемых операторов, связанных с заданным курсором. При выполнении этого оператора производится подготовка курсора к работе над ним. В частности, в этот момент производится связывание спецификации курсора со значениями переменных основного языка в случае встроенного SQL или параметров в случае модуля (это значит, что после выполнения оператора открытия курсора любые изменения переменных основной программы не будут оказывать какие-либо действия на результат запроса, связанного с курсором).
В большинстве реализаций в случае встроенного SQL именно выполнение оператора открытия курсора приводит к компиляции спецификации курсора и подготовке выполняемого плана запроса. Можно считать (хотя фактически это делается далеко не всегда), что во время выполнения оператора открытия курсора производится построение временной таблицы, содержащей результат запроса, который связан с этим курсором.
Следующие операторы можно выполнять над открытым курсором в произвольном порядке.
2.6.1.3 ОПЕРАТОР ЧТЕНИЯ ОЧЕРЕДНОЙ СТРОКИ КУРСОРА
Синтаксис оператора чтения следующий:
::=
FETCH INTO
::=
[{,}...]
В операторе чтения указывается имя курсора и обязательный раздел INTO, содержащий список спецификаций назначения (список имен переменных основной программы в случае встроенного SQL или имен "выходных" параметров в случае модуля SQL). Число и типы данных в списке назначений должны совпадать с числом и типами данных списка выборки спецификации курсора.
Любой открытый курсор всегда имеет позицию: он может быть установлен перед некоторой строкой результирующей таблицы (перед первой строкой сразу после открытия курсора), на некоторую строку результата или за последней строкой результата.
Если таблица, на которую указывает курсор, является пустой, или курсор позиционирован на последнюю строку или за ней, то при выполнении оператора чтения курсор устанавливается в позицию после последней строки, параметру SQLCODE присваивается значение 100, никакие значения не присваиваются целям, указанным в разделе INTO.
Если курсор установлен в позицию перед строкой, то при выполнении оператора чтения он устанавливается на эту строку, и значения строки присваиваются соответствующим целям.
Если курсор установлен на строку r, отличную от последней строки, то курсор устанавливается на строку, непосредственно следующую за строкой r (в порядке, определенном реализацией, если запрос не содержит раздела ORDER BY), и значения из этой следующей строки присваиваются соответствующим целям.
Возникает естественный вопрос, каким образом можно параметризовать курсор неопределенным значением или узнать, что выбранное из очередной строки значение является неопределенным. Это достигается в SQL/89 за счет использования так называемых индикаторных параметров и переменных. Если известно, что значение, передаваемое из основной программы СУБД или принимаемое основной программой от СУБД, может быть неопределенным, и этот факт интересует прикладного программиста, то спецификация параметра в операторе SQL имеет вид: [INDICATOR], а спецификация переменной - [INDICATOR] . Отрицательное значение индикаторного параметра или индикаторной переменной (они должны быть целого типа) соответствует неопределенному значению параметра или переменной.
2.6.1.4 ОПЕРАТОР ПОЗИЦИОННОГО УДАЛЕНИЯ
Синтаксис этого оператора следующий:
::=
DELETE FROM
WHERE CURRENT OF
Если указанный в операторе курсор открыт и установлен на некоторую строку, и курсор определяет изменяемую таблицу, то текущая строка курсора удаляется, а он позиционируется перед следующей строкой. Таблица, указанная в разделе FROM оператора DELETE, должна быть таблицей, указанной в самом внешнем разделе FROM спецификации курсора.
Если указанный в операторе курсор открыт и установлен на некоторую строку и курсор определяет изменяемую таблицу, то текущая строка курсора модифицируется в соответствии с разделом SET. Позиция курсора не изменяется. Таблица, указанная в разделе FROM оператора DELETE, должна быть таблицей, указанной в самом внешнем разделе FROM спецификации курсора.
Замечание: требование указывать имя таблицы в операторах позиционного удаления и позиционной модификации является, очевидно, избыточным, поскольку до имени таблицы можно добраться через имя курсора. Единственной возможной причиной этой избыточности может быть упрощение реализации (хотя не очень понятно, что именно упрощается).
2.6.1.6 ОПЕРАТОР ЗАКРЫТИЯ КУРСОРА
Синтаксис этого оператора следующий:
::=
CLOSE
Если к моменту выполнения этого оператора курсор находился в открытом состоянии, то оператор переводит курсор в закрытое состояние. После этого над курсором возможно выполнение только оператора OPEN.
2.6.2 Одиночные операторы манипулирования данными
Каждый из операторов этой группы является абсолютно независимым от какого бы то ни было другого оператора, т.е. для выполнения любого оператора этой группы не требуется предварительное выполнение какого бы то ни было другого оператора.
2.6.2.1 ОПЕРАТОР ВЫБОРКИ
Для удобства читателей мы повторяем синтаксис этого оператора еще раз:
SELECT [ALL | DISTINCT]
INTO
[{,}...]
Поскольку, как мы уже объясняли в подразделе 2.3.2, результатом одиночного оператора выборки должна являться таблица, состоящая не более, чем из одной строки, список целей специфицируется в самом операторе. После выполнения оператора цели содержат соответствующие поля (столбцы) результирующей строки (если, конечно, результирующая таблица не является пустой).
2.6.2.2 ОПЕРАТОР ПОИСКОВОГО УДАЛЕНИЯ
Оператор описывается следующим синтаксическим правилом:
::=
DELETE FROM
WHERE []
Таблица T, указанная в разделе FROM оператора DELETE, должна быть изменяемой. На условие поиска накладывается то условие, что на столбцы таблицы T не должны содержаться ссылки ни в каком вложенном подзапросе предикатов раздела WHERE.
Фактически, оператор выполняется следующим образом: последовательно просматриваются все строки таблицы T, и те строки, для которых результатом вычисления условия выборки является true, удаляются из таблицы T. При отсутствии раздела WHERE удаляются все строки таблицы T (обычно при выполнении поискового оператора DELETE без раздела WHERE в интерактивном режиме до удаления всех строк запрашивается подтверждение правильности такого действия).
2.6.2.3 ОПЕРАТОР ПОИСКОВОЙ МОДИФИКАЦИИ
Оператор обладает следующим синтаксисом:
::=
UPDATE
SET
[{,}...]
[WHERE ]
::=
{ | NULL }
Таблица T, указанная в операторе UPDATE, должна быть изменяемой. На условие поиска накладывается то условие, что на столбцы таблицы T не должны содержаться ссылки ни в каком вложенном подзапросе предикатов раздела WHERE.
Оператор фактически выполняется следующим образом: таблица T последовательно просматривается, и каждая строка, для которой результатом вычисления условия поиска является true, изменяется в соответствии с разделом SET. Если арифметическое выражение в разделе SET содержит ссылки на столбцы таблицы T, то при вычислении арифметического выражения используются значения столбцов текущей строки до их модификации.
2.6.3 Операторы окончания транзакции
Текущая транзакция может быть завершена успешно (с фиксацией в базе данных произведенных изменений) путем выполнения оператора COMMIT WORK или аварийно (с удалением из базы данных изменений, произведенных текущей транзакцией) путем выполнения оператора ROLLBACK WORK. При выполнении любого из этих операторов производится принудительное закрытие всех курсоров, открытых к моменту выполнения оператора завершения транзакции.
3. Динамический SQL в Oracle V.6
Описанный в стандарте SQL/89 набор операторов SQL предназначен для встраивания в программу на обычном языке программирования. Поэтому в этом наборе перемешаны операторы "истинного" реляционного языка запросов (например оператор удаления из таблицы части строк, удовлетворяющих заданному условию) и операторы работы с курсорами, позволяющими обеспечить построчный доступ к таблице-результату запроса.
Понятно, что в диалоговом режиме набор операторов SQL и их синтаксис должен быть несколько другим. Весь вопрос состоит в том, как реализовывать такую диалоговую программу. Правила встраивания стандартного SQL в программу на обычном языке программирования предусматривают, что вся информация, касающаяся операторов SQL, известна в статике (за исключением значений переменных, используемых в качестве констант в операторах SQL). Не предусмотрены стандартные средства компиляции с последующим выполнением операторов, которые становятся известными только во время выполнения (например вводятся с терминала). Поэтому, опираясь только на стандарт, невозможно реализовать диалоговый монитор взаимодействия с БД на языке SQL или другую прикладную программу, в которой текст операторов SQL возникает во время выполнения, т.е. фактически так или иначе стандарт необходимо расширять.
Один из возможных путей расширения состоит в использовании специальной группы операторов, обеспечивающих динамическую компиляцию (во время выполнения прикладной программы) базового подмножества операторов SQL и поддерживающих их корректное выполнение. Некоторый набор таких операторов входил в диалект SQL, реализованный в System R, несколько отличный набор входит в реализацию Oracle V.6, и, наконец, в новом стандарте SQL/92 появилась стандартная версия динамического SQL.
Поскольку в СУБД Oracle средства динамического SQL реализованы уже сравнительно давно, имеет смысл рассмотреть сначала их, чтобы иметь основу для сравнения с SQL/92.
Замечание: мы говорим здесь именно об Oracle V.6, а не о последней, седьмой версии, поскольку в Oracle V.7 имеется реализация динамического SQL, соответствующая стандарту SQL/92.
В дополнительный набор операторов, поддерживающих динамическую компиляцию базовых операторов SQL, входят операторы: PREPARE, DESCRIBE и EXECUTE.
3.1 Оператор подготовки
Оператор PREPARE имеет синтаксис:
::=
PREPARE FROM
::=
Во время выполнения оператора PREPARE символьная строка, содержащаяся в host-string-variable, передается компилятору SQL, который обрабатывает ее почти таким же образом, как если бы получил в статике. Построенный при выполнении оператора PREPARE код остается действующим до конца транзакции или до повторного выполнения данного оператора PREPARE в пределах этой же транзакции.
В отличие от операторов SQL, статически подставляемых в программу на обычном языке программирования, в которых связь с переменными включающей программы производится по именам (т.е. в соответствии со стандартом во встроенном операторе SQL могут употребляться просто имена переменных включающей программы), динамическая природа операторов, подготавливаемых с помощью оператора PREPARE, заставляет рассматривать эти имена как имена формальных параметров. Соответствие этих формальных параметров адресам переменных включающей программы устанавливается позиционно во время выполнения подготовленного оператора.
3.2 Оператор получения описания подготовленного оператора
Оператор DESCRIBE предназначен для того, чтобы определить тип ранее подготовленного оператора, узнать количество и типы формальных параметров (если они есть) и количество и типы столбцов результирующей таблицы, если подготовленный оператор является оператором выборки (SELECT). Мы не приводим синтаксис оператора DESCRIBE, поскольку этот синтаксис мало что проясняет.
Действие оператора DESCRIBE состоит в том, что в указанную область памяти прикладной программы (структура этой области фиксирована и известна пользователям) помещается информация, характеризующая ранее подготовленный оператор с заданным именем.
3.3 Оператор выполнения подготовленного оператора
Оператор EXECUTE служит для выполнения ранее подготовленного оператора SQL типа "N" (не требующего применения курсора) или для совмещенной подготовки и выполнения такого оператора. Синтаксис оператора EXECUTE:
::=
EXECUTE
{ [USING ]
| IMMEDIATE }
Для выполнения подготовленного оператора служит первый вариант оператора EXECUTE. В этом случае должен задавать имя, употреблявшееся ранее в операторе PREPARE. Если в подготовленном операторе присутствуют формальные параметры, то в операторе EXECUTE должен задаваться список фактических параметров . Число и типы фактических параметров должны соответствовать числу и типам формальных параметров подготовленного оператора.
Второй вариант оператора EXECUTE предназначен для совмещенной подготовки и выполнения оператора SQL типа "N". В этом случае параметром оператора EXECUTE является строка, которая должна содержать текст оператора SQL (эту строку разрешается также задавать литерально). Запрещается использование в этом операторе переменных включающей программы (формальных параметров).
3.4 Работа с динамическими операторами SQL через курсоры
Для использования таких операторов используется расширение механизма курсоров стандарта SQL. Во-первых, при определении курсора можно указывать не только литеральную спецификацию курсора, но и имя оператора, вводимое с помощью оператора PREPARE (в этом случае оператор PREPARE должен текстуально находиться выше оператора DECLARE). Тем самым полный синтаксис оператора DECLARE становится следующим:
::=
DECLARE CURSOR
FOR { | }
Далее, поскольку для такого курсора в статике неизвестна информация о входных и выходных переменных включающей программы, то используется другая форма операторов OPEN и FETCH.
Полный синтаксис этих операторов становится следующим:
::=
OPEN
[USING { | DESCRIPTOR }]
::=
FETCH
{ INTO |
USING |
USING DESCRIPTOR }
Как видно, предлагается два способа задания фактических входных и выходных параметров: прямое с указанием в операторах OPEN и/или FETCH списков имен переменных включающей программы и косвенное, когда число параметров и их адреса сообщаются через дополнительную структуру-дескриптор.
Первый способ предлагается использовать для работы с операторами выборки, для которых фиксирован набор формальных входных и выходных параметров. Точнее говоря, что касается выходных параметров, должны быть фиксированы число и типы элементов списка выборки.
Второй способ работы с динамически откомпилированными операторами, требующими использования курсоров, состоит в использовании дескрипторов динамически формируемых списков параметров. В этом случае вся ответственность за соответствие типов фактических и формальных параметров ложится на программиста. В результате ошибки при формировании такого списка, в частности, может быть испорчена память Си-программы.
4. Некоторые черты SQL/92
Огромный объем стандарта SQL/92 и ограниченный объем этой статьи не позволяют нам описать этот стандарт сколько-нибудь подробно. Кроме того, как отмечалось выше, на сегодняшний день все еще отсутствует какая бы то ни было полная реализация SQL/92. Тем не менее мы считаем полезным сравнительно подробно описать стандартные средства динамического SQL (это описание можно использовать хотя бы в качестве эталона при сравнении различных реализаций) и привести сводку основных отличий SQL/92 от SQL/89 (в этом мы будем следовать последнему изданию книги Дейта "Стандарт SQL").
4.1 Динамический SQL в стандарте SQL/92
Набор операторов динамического SQL в стандарте SQL/92 существенно шире того, который был реализован в Oracle V.6. В основном это связано с тем, что введены операторы для работы с дескрипторами, а также появились подготавливаемые операторы позиционного удаления и позиционной модификации.
4.1.1 Оператор выделения памяти под дескриптор
Оператор имеет следующий синтаксис:
::=
ALLOCATE DESCRIPTOR
[WITH MAX ]
::=
::=
[]
::= GLOBAL | LOCAL
::=
|
|
Дескриптор - это динамически выделяемая часть памяти прикладной программы, служащая для принятия информации о результате или параметрах динамически подготовленного оператора SQL или задания параметров такого оператора. Смысл того, что для выделения памяти используется оператор SQL, а не просто стандартная функция alloc или какая-нибудь другая функция динамического запроса памяти, состоит в том, что прикладная программа может теперь не знать структуру дескриптора и даже его адрес. Это позволяет не привязывать SQL к особенностям какой-либо системы программирования или ОС. Все обмены информацией между собственно прикладной программой и дескрипторами производятся также с помощью специальных операторов SQL (GET и SET, см. ниже).
Далее возникает вопрос: зачем вообще выделять память под дескрипторы динамически? Это нужно потому, что в общем случае прикладная программа, использующая динамический SQL, не знает в статике число одновременно действующих динамических операторов SQL, описание которых может потребоваться. С этим же связано то, что имя дескриптора может задаваться как литеральной строкой символов, так и через строковую переменную включающего языка, т.е. его можно генерировать во время выполнения программы.
В операторе ALLOCATE DESCRIPTOR, помимо прочего, может указываться число описательных элементов, на которое он рассчитан. Если, например, при выделении памяти под дескриптор в разделе WITH MAX указано целое положительное число N, а потом дескриптор используется для описания M (M>N) элементов (например M столбцов результата запроса), то это приводит к возникновению исключительной ситуации.
4.1.2 Оператор освобождения памяти из-под дескриптора
Синтаксис оператора:
::=
DEALLOCATE DESCRIPTOR
Выполнение этого оператора приводит к освобождению памяти из-под ранее выделенного дескриптора. После этого использование имени дескриптора незаконно в любом операторе, кроме ALLOCATE DESCRIPTOR.
4.1.3 Оператор получения информации из области дескриптора SQL
Оператор определяется следующими синтаксическими правилами:
::=
GET DESCRIPTOR
::=
| VALUE
[{}...]
::=
COUNT
::=
::=
::=
::=
::=
TYPE
| LENGHT
| OCTET_LENGHT
| RETURNED_LENGHT
| RETURNED_OCTET_LENGHT
| PRECISION
| SCALE
| DATETIME_INTERVAL_CODE
| DATATIME_INTERVAL_PRECISION
| NULLABLE
| INDICATOR
| DATA
| NAME
| UNNAMED
| COLLATION_CATALOG
| COLLATION_SCHEMA
| COLLATION_NAME
| CHARACTER_SET_CATALOG
| CHARACTER_SET_SCHEMA
| CHARACTER_SET_NAME
::=
|
Оператор GET DESCRIPTOR служит для выборки описательной информации, ранее размещенной в дескрипторе с помощью оператора DESCRIBE (см. п.
4.1.7). За одно выполнение оператора можно получить либо число заполненных элементов дескриптора (COUNT), либо информацию, содержащуюся в одном из заполненных элементов.
4.1.4 Оператор установки дескриптора
Оператор установки имеет следующий синтаксис:
::=
SET DESCRIPTOR
::=
| VALUE
[{}...]
::=
COUNT
::=
::=
::=
::=
Оператор SET DESCRIPTOR служит для заполнения элементов дескриптора с целью его будущего использования в разделе USING. За одно выполнение оператора можно поместить значение в поле COUNT (число заполненных элементов) либо частично или полностью сформировать один элемент дескриптора.