Аналитические материалы Центра Информационных Технологий



страница12/18
Дата22.04.2016
Размер3.93 Mb.
1   ...   8   9   10   11   12   13   14   15   ...   18

Лекция 15. Язык SQL. Средства манипулирования данными

15.1. Структура запросов


Для того, чтобы можно было более или менее точно рассказать про структуру запросов в стандарте SQL/89, необходимо начать со сводки синтаксических правил:

::=
[]
::=

| UNION [ALL]
::=

| ()
::=
(SELECT [ALL | DISTINCT] )

INTO

::=
(SELECT [ALL | DISTINCT]

::=

[]
[]
[]

Язык допускает три типа синтаксических конструкций, начинающихся с ключевого слова SELECT: спецификация курсора (cursor specification), оператор выборки (select statement) и подзапрос (subquery). Основой всех них является синтаксическая конструкция "табличное выражение (table expression)". Семантика табличного выражения состоит в том, что на основе последовательного применения разделов from, where, group by и having из заданных в разделе from таблиц строится некоторая новая результирующая таблица, порядок следования строк которой не определен и среди строк которой могут находиться дубликаты (т.е. в общем случае таблица-результат табличного выражения является мультимножеством строк). На самом деле именно структура табличного выражения наибольшим образом характеризует структуру запросов языка SQL/89. Мы рассмотрим ниже структуру и смысл разделов табличного выражения ниже, но до этого немного подробнее обсудим три упомянутые конструкции, включающие табличные выражения.


15.1.1. Спецификация курсора

Наиболее общей является конструкция "спецификация курсора". Курсор - это понятие языка SQL, позволяющее с помощью набора специальных операторов получить построчный доступ к результату запроса к БД. К табличным выражениям, участвующим в спецификации курсора, не предъявляются какие-либо ограничения. Как видно из сводки синтаксических правил, при определении спецификации курсора используются три дополнительных конструкции: спецификация запроса, выражение запросов и раздел ORDER BY.

Спецификация запроса

В спецификации запроса задается список выборки (список арифметических выражений над значениями столбцов результата табличного выражения и констант). В результате применения списка выборки к результату табличного выражения производится построение новой таблицы, содержащей то же число строк, но вообще говоря другое число столбцов, содержащих результаты вычисления соответствующих арифметических выражений из списка выборки. Кроме того, в спецификации запроса могут содержаться ключевые слова ALL или DISTINCT. При наличии ключевого слова DISTINCT из таблицы, полученной применением списка выборки к результату табличного выражения, удаляются строки-дубликаты; при указании ALL (или просто при отсутствии DISTINCT) удаление строк-дубликатов не производится.



Выражение запросов

Выражение запросов - это выражение, строящееся по указанным синтаксическим правилам на основе спецификаций запросов. Единственной операцией, которую разрешается использовать в выражениях запросов, является операция UNION (объединение таблиц) с возможной разновидностью UNION ALL. К таблицам-операндам выражения запросов предъявляется то требование, что все они должны содержать одно и то же число столбцов, и соответствующие столбцы всех операндов должны быть одного и того же типа. Выражение запросов вычисляется слева направо с учетом скобок. При выполнении операции UNION производится обычное теоретико-множественное объединение операндов, т.е. из результирующей таблицы удаляются дубликаты. При выполнении операции UNION ALL образуется результирующая таблица, в которой могут содержаться строки-дубликаты.



Раздел ORDER BY

Наконец, раздел ORDER BY позволяет установить желаемый порядок просмотра результата выражения запросов. Синтаксис ORDER BY следующий:



::=
ORDER BY
[{,}...]
::=
{ | }
[ASC | DESC]

Как видно из этих синтаксических правил, фактически задается список столбцов результата выражения запросов, и для каждого столбца указывается порядок просмотра строк результата в зависимости от значений этого столбца (ASC - по возрастанию (умолчание), DESC - по убыванию). Столбцы можно задавать их именами в том и только в том случае, когда (1) выражение запросов не содержит операций UNION или UNION ALL и (2) в списке выборки спецификации запроса этому столбцу соответствует арифметическое выражение, состоящее только из имени столбца. Во всех остальных случаях в разделе ORDER BY должен указываться порядковый номер столбца в таблице-результате выражения запросов.


15.1.2. Оператор выборки

Оператор выборки - это отдельный оператор языка SQL/89, позволяющий получить результат запроса в прикладной программе без привлечения курсора. Поэтому оператор выборки имеет синтаксис, отличающийся от синтаксиса спецификации курсора, и при его выполнении возникают ограничения на результат табличного выражения. Фактически, и то, и другое диктуется спецификой оператора выборки как одиночного оператора SQL: при его выполнении результат должен быть помещен в переменные прикладной программы. Поэтому в операторе появляется раздел INTO, содержащий список переменных прикладной программы, и возникает то ограничение, что результирующая таблица должна содержать не более одной строки. Соответственно, результат базового табличного выражения должен содержать не более одной строки, если оператор выборки не содержит спецификации DISTINCT, и таблица, полученная применением списка выборки к результату табличного выражения, не должна содержать более одной несовпадающих строк, если спецификация DISTINCT задана.

Замечание: В диалекте SQL СУБД Oracle поддерживается расширенный вариант оператора выборки, результатом которого не обязательно является таблица из одной строки. Такое расширение не поддерживается ни в SQL/89, ни в SQL/92.


15.1.3. Подзапрос

Наконец, последняя конструкция SQL/89, которая может содержать табличные выражения, - это подзапрос, т.е. запрос, который может входить в предикат условия выборки оператора SQL. В SQL/89 к подзапросам применяется то ограничение, что результирующая таблица должна содержать в точности один столбец. Поэтому в синтаксических правилах, определяющих подзапрос, вместо списка выборки указано "выражение, вычисляющее значение", т.е. арифметическое выражение. Заметим еще, что поскольку подзапрос всегда вложен в некоторый другой оператор SQL, то в качестве констант в арифметическом выражении выборки и логических выражениях разделов WHERE и HAVING можно использовать значения столбцов текущих строк таблиц, участвующих в (под)запросах более внешнего уровня. Более подробно об этом см. ниже, при описании семантики табличных выражений.

15.2. Табличное выражение


Стандарт SQL/89 рекомендует рассматривать вычисление табличного выражения как последовательное применение разделов FROM, WHERE, GROUP BY и HAVING к таблицам, заданным в списке FROM. Раздел FROM имеет следующий синтаксис:

::=
FROM

({,
}...]
::=
[]
15.2.1. Раздел FROM

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

"Расширенное произведение R есть мультимножество всех строк r таких, что r является конкатенацией строк из всех идентифицированных таблиц в том порядке, в котором они идентифицированы. Мощность R есть произведение мощностей идентифицированных таблиц. Порядковый номер столбца в R есть n+s, где n - порядковый номер порождающего столбца в именованной таблице T, а s - сумма степеней всех таблиц, идентифицированных до T в разделе FROM".

Как видно из синтаксиса, рядом с именем таблицы можно указывать еще одно имя "correlation name". Фактически, это некоторый синоним имени таблицы, который можно использовать в других разделах табличного выражения для ссылки на строки именно этого вхождения таблицы.

Если табличное выражение содержит только раздел FROM (это единственный обязательный раздел табличного выражения), то результат табличного выражения совпадает с результатом раздела FROM.


15.2.2. Раздел WHERE

Если в табличном выражении присутствует раздел WHERE, то следующим вычисляется он. Синтаксис раздела WHERE следующий:

::= WHERE
::=

( OR
::=

( AND
::= [NOT]
::=
| ()

Вычисление раздела WHERE производится по следующим правилам: Пусть R - результат вычисления раздела FROM. Тогда условие поиска применяется ко всем строкам 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-ориентированных СУБД такие технические детали стоит иметь в виду.



Предикат сравнения

Синтаксис предиката сравнения определяется следующими правилами:



::=

{ | }
::=
= | <> | < | > | <= | >=

Через "<>" обозначается операция "неравенства". Арифметические выражения левой и правой частей предиката сравнения строятся по общим правилам построения арифметических выражений и могут включать в общем случае имена столбцов таблиц из раздела FROM и константы. Типы данных арифметических выражений должны быть сравнимыми (например, если тип столбца a таблицы A является типом символьных строк, то предикат "a = 5" недопустим).

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

Заметим, что значение арифметического выражения не определено, если в его вычислении участвует хотя бы одно неопределенное значение. Еще одно важное замечание из стандарта SQL/89: в контексте GROUP BY, DISTINCT и ORDER BY неопределенное значение выступает как специальный вид определенного значения, т.е. возможно, например, образование группы строк, значение указанного столбца которых является неопределенным. Для обеспечения переносимости прикладных программ нужно внимательно оценивать специфику работы с неопределенными значениями в конкретной СУБД.



Предикат 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)".



Предикат in

Предикат in определяется следующими синтаксическими правилами:



::=
[NOT] IN
{ | ()}
::=

{,}...

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

Значение предиката равно true в том и только в том случае, когда значение левого операнда совпадает хотя бы с одним значением списка правого операнда. Если список правого операнда пуст (так может быть, если правый операнд задается подзапросом), или значение "подразумеваемого" предиката сравнения x = y (где x - значение арифметического выражения левого операнда) равно false для каждого элемента y списка правого операнда, то значение предиката in равно false. В противном случае значение предиката in равно unknown. По определению значение предиката "x NOT IN S" равно значению предиката "NOT (x IN S)".

Предикат 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".

Предикат null

Предикат null описывается синтаксическим правилом:



::=
IS [NOT] NULL

Этот предикат всегда принимает значения true или false. При этом значение "x IS NULL" равно true тогда и только тогда, когда значение x не определено. Значение предиката "x NOT IS NULL" равно значению "NOT x IS NULL".



Предикат с квантором

Предикат с квантором имеет следующий синтаксис:



::=

::=
|
::= 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.



Предикат exists

Предикат exists имеет следующий синтаксис:



::=
EXISTS

Значением этого предиката всегда является true или false, и это значение равно true тогда и только тогда, когда результат вычисления подзапроса не пуст.


15.2.3. Раздел GROUP BY

Если в табличном выражении присутствует раздел GROUP BY, то следующим выполняется он. Синтаксис раздела GROUP BY следующий:

::=
GROUP BY
[{,}...]

Если обозначить через R таблицу, являющуюся результатом предыдущего раздела (FROM или WHERE), то результатом раздела GROUP BY является разбиение R на множество групп строк, состоящего из минимального числа групп таких, что для каждого столбца из списка столбцов раздела GROUP BY во всех строках каждой группы, включающей более одной строки, значения этого столбца равны. Для обозначения результата раздела GROUP BY в стандарте используется термин "сгруппированная таблица".


15.2.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, то результатом его выполнения будет либо пустая таблица, либо результат выполнения предыдущих разделов табличного выражения, рассматриваемый как одна группа без столбцов группирования.

15.3. Агрегатные функции и результаты запросов


Агрегатные функции (в стандарте SQL/89 они называются функциями над множествами) определяются в SQL/89 следующими синтаксическими правилами:

::=
COUNT(*) |
|
::=
{ AVG | MAX | MIN | SUM | COUNT }
(DISTNICT )
::=
{ AVG | MAX | MIN | SUM } ([ALL] )

Как видно из этих правил, в стандарте SQL/89 определены пять стандартных агрегатных функций: COUNT - число строк или значений, MAX - максимальное значение, MIN - минимальное значение, SUM - суммарное значение и AVG - среднее значение.


15.3.1. Семантика агрегатных функций

Агрегатные функции предназначены для того, чтобы вычислять некоторое значение для заданного множества строк. Таким множеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или вся таблица. Для всех агрегатных функций, кроме COUNT(*), фактический (т.е. требуемый семантикой) порядок вычислений следующий: на основании параметров агрегатной функции из заданного множества строк производится список значений. Затем по этому списку значений производится вычисление функции. Если список оказался пустым, то значение функции COUNT для него есть 0, а значение всех остальных функций - null.

Пусть T обозначает тип значений из этого списка. Тогда результат вычисления функции COUNT - точное число с масштабом и точностью, определяемыми в реализации. Тип результата значений функций MAX и MIN совпадает с T. При вычислении функций SUM и AVG тип T не должен быть типом символьных строк, а тип результата функции - это тип точных чисел с определяемыми в реализации масштабом и точностью, если T - тип точных чисел, и тип приблизительных чисел с определяемой в реализации точностью, если T - тип приблизительных чисел.

Вычисление функции COUNT(*) производится путем подсчета числа строк в заданном множестве. Все строки считаются различными, даже если они состоят из одного столбца со значением null во всех строках.

Если агрегатная функция специфицирована с ключевым словом DISTINCT, то список значений строится из значений указанного столбца. (Подчеркнем, что в этом случае не допускается вычисление арифметических выражений!) Далее из этого списка удаляются неопределенные значения, и в нем устраняются значения-дубликаты. Затем вычисляется указанная функция.

Если агрегатная функция специфицирована без ключевого слова DISTINCT (или с ключевым словом ALL), то список значений формируется из значений арифметического выражения, вычисляемого для каждой строки заданного множества. Далее из списка удаляются неопределенные значения, и производится вычисление агрегатной функции. Обратите внимание, что в этом случае не допускается применение функции COUNT!

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


15.3.2. Результаты запросов

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

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

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

Аналогично обстоит дело в том случае, когда R представляет собой сгруппированную таблицу, но табличное выражение не содержит раздела GROUP BY (и, следовательно, содержит раздел HAVING). Если в случае предыдущего абзаца было два варианта формирования списка выборки: только с прямым указанием столбцов R или только с указанием их внутри спецификаций агрегатных функций, то в данном случае возможен только второй вариант. Результат табличного выражения явно объявлен сгруппированной таблицей, состоящей из одной группы, и результат запроса можно формировать только путем применения агрегатных функций к этой группе строк. Опять результатом запроса является таблица, состоящая не более чем из одной строки, полученной путем применения агрегатных функций к R.

Наконец, рассмотрим случай, когда R представляет собой "настоящую" сгруппированную таблицу, т.е. табличное выражение содержит раздел GROUP BY и, следовательно, определен по крайней мере один столбец группирования. В этом случае правила формирования списка выборки полностью соответствуют правилам формирования условия выборки раздела HAVING: допускает прямое использование спецификации столбцов группирования, а спецификации остальных столбцов R могут появляться только внутри спецификаций агрегатных функций. Результатом запроса является таблица, число строк в которой равно числу групп в R, и каждая строка формируется на основе значений столбцов группирования и агрегатных функций для данной группы.

Лекция 16. Использование SQL при прикладном программировании

16.1. Язык модулей или встроенный 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 предоставляет возможность производства более "самосодержащихся" прикладных программ. Имеется больше оснований рассчитывать на простоту переноса такой программы в среду другой СУБД, поскольку стандарт встраивания более или менее соблюдается. Основным недостатком является некоторый PL-подобный вид таких программ, независимо от выбранного основного языка. И конечно, нужно учитывать замечания, содержащиеся в предыдущих абзацах.

Далее мы коротко опишем язык модулей и правила встраивания в соответствии со стандартом SQL/89 (еще раз заметим, что формально правила встраивания не являются частью стандарта).

16.2. Язык модулей


Структура модуля SQL в стандарте SQL/89 определяется следующими синтаксическими правилами:

::=



[...]
< procedure > ...
::= MODULE []
::= LANGUAGE { COBOL | FORTRAN | PASCAL | PLI }
::=
AUTHORIZATION
::=

Существенно, что каждый модуль SQL ориентирован на использование в программах, написанных на конкретном языке программирования. Если в модуле присутствуют процедуры работы с курсорами, то все курсоры должны быть специфицированы в начале модуля. Заметим, что объявление курсора не погружается в какую-либо процедуру, поскольку это описательный, а не выполняемый оператор SQL.


16.2.1. Определение процедуры

Процедуры в модуле SQL определяются следующими синтаксическими конструкциями:

::=
PROCEDURE


...;
;

::=


|
::= SQLCODE
::=

|
|
|
|
|
|
|
|


WHERE CURRENT OF

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



Оператор позиционной модификации

Оператор описывается следующими синтаксическими правилами:



::=
UPDATE

SET
[{,}...]
WHERE CURRENT OF
::=
=
{ | NULL }
::=

Если указанный в операторе курсор открыт и установлен на некоторую строку, и курсор определяет изменяемую таблицу, то текущая строка курсора модифицируется в соответствии с разделом SET. Позиция курсора не изменяется. Таблица, указанная в разделе FROM оператора DELETE, должна быть таблицей, указанной в самом внешнем разделе FROM спецификации курсора.



Оператор закрытия курсора

Синтаксис этого оператора следующий:



::= CLOSE

Если к моменту выполнения этого оператора курсор находился в открытом состоянии, то оператор переводит курсор в закрытое состояние. После этого над курсором возможно выполнение только оператора OPEN.


16.4.2. Одиночные операторы манипулирования данными

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

Оператор выборки

Для удобства мы повторяем синтаксис этого оператора еще раз:




INTO


WHERE []

Таблица T, указанная в разделе FROM оператора DELETE, должна быть изменяемой. На условие поиска накладывается то условие, что на таблицу T не должны содержаться ссылки ни в каком вложенном подзапросе предикатов раздела WHERE.

Фактически оператор выполняется следующим образом: последовательно просматриваются все строки таблицы T, и те строки, для которых результатом вычисления условия выборки является true, удаляются из таблицы T. При отсутствии раздела WHERE удаляются все строки таблицы T.

Оператор поисковой модификации

Оператор обладает следующим синтаксисом:



::=
UPDATE

SET
[{,}...]
[WHERE ]
::=
=
{ | NULL }
::=

Таблица T, указанная в операторе UPDATE, должна быть изменяемой. На условие поиска накладывается то условие, что на таблицу T не должны содержаться ссылки ни в каком вложенном подзапросе предикатов раздела WHERE.

Оператор фактически выполняется следующим образом: таблица T последовательно просматривается, и каждая строка, для которой результатом вычисления условия поиска является true, изменяется в соответствии с разделом SET. Если арифметическое выражение в разделе SET содержит ссылки на столбцы таблицы T, то при вычислении арифметического выражения используются значения столбцов текущей строки до их модификации.

Операторы окончания транзакции

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


16.5. Динамический 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.

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

16.5.1. Оператор подготовки

Оператор PREPARE имеет синтаксис:

::=
PREPARE FROM


::=

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

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

16.5.2. Оператор получения описания подготовленного оператора

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

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


16.5.3. Оператор выполнения подготовленного оператора

Оператор EXECUTE служит для выполнения ранее подготовленного оператора SQL типа 'N' (не требующего применения курсора) или для совмещенной подготовки и выполнения такого оператора. Синтаксис оператора EXECUTE:

::=
EXECUTE
{ [USING ]
( IMMEDIATE }

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

Второй вариант оператора EXECUTE предназначен в Oracle для совмещенной подготовки и выполнения оператора SQL типа 'N'. В этом случае параметром оператора EXECUTE является строка, которая должна содержать текст оператора SQL (эту строку разрешается также задавать литерально). Запрещается использование в этом операторе переменных включающей программы (формальных параметров).

16.5.4. Работа с динамическими операторами SQL через курсоры

Для использования таких операторов используется расширение механизма курсоров стандарта SQL. Во-первых, при определении курсора можно указывать не только литеральную спецификацию курсора, но и имя оператора, вводимое с помощью оператора PREPARE (в этом случае оператор PREPARE должен текстуально находиться выше оператора DECLARE). Тем самым полный синтаксис оператора DECLARE становится следующим:

::=
DECLARE CURSOR
FOR { | }

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

Полный синтаксис этих операторов становится следующим:

::=
OPEN
[USING { | DESCRIPTOR }]
::=
FETCH
{ INTO
( USING
( USING DESCRIPTOR }

Как видно, предлагается два способа задания фактических входных и выходных параметров: прямое с указанием в операторах OPEN и/или FETCH списков имен переменных включающей программы и косвенное, когда число параметров и их адреса сообщаются через дополнительную структуру-дескриптор.

Первый способ предлагается использовать для работы с операторами выборки, для которых фиксирован набор формальных входных и выходных параметров. Точнее говоря, что касается выходных параметров, должны быть фиксированы число и типы элементов списка выборки.

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


Лекция 17. Некоторые черты SQL/92 и SQL-3


Мы не будем даже поверхностно описывать новые возможности языка SQL в стандарте SQL/92. Это сейчас не очень осмысленно, поскольку единственной доступной реализацией SQL/92 является дорогостоящая версия Oracle V.7. Однако кажется полезным включить в наше руководство сводку операторов динамического SQL с небольшими комментариями, поскольку в SQL/92 предпринята первая попытка стандартизовать эту часть языка SQL, и это описание можно использовать хотя бы в качестве эталона при сравнении различных реализаций. В конце лекции приводится краткая сводка новых возможностей, ожидаемых в новом стандарте SQL-3, работа над которым все еще продолжается.

17.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 столбцов результата запроса), то это приводит к возникновению исключительной ситуации.


17.2. Оператор освобождения памяти из-под дескриптора


::=
DEALLOCATE DESCRIPTOR

Комментарий:

Выполнение этого оператора приводит к освобождению памяти из-под ранее выделенного дескриптора. После этого использование имени дескриптора незаконно в любом операторе, кроме ALLOCATE DESCRIPTOR.


17.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. За одно выполнение оператора можно получить либо число заполненных элементов дескриптора (COUNT), либо информацию, содержащуюся в одном из заполненных элементов.


17.4. Оператор установки дескриптора


::=
SET DESCRIPTOR

::=

( VALUE
[{ }...]
::= COUNT
::=



::=
::=
::=

Комментарий:

Оператор SET DESCRIPTOR служит для заполнения элементов дескриптора с целью его использования в разделе USING. За одно выполнение оператора можно поместить значение в поле COUNT (число заполненных элементов), либо частично или полностью сформировать один элемент дескриптора.


17.5. Оператор подготовки

::=
PREPARE


FROM
::=

::=

|

|

|



|

::=


|
|
|
|
|

|


::=

::=

::=
::=
::=
::= |
::= [scope option]
::=
[]
[]
::=
FOR { READ ONLY | UPDATE [ OF ] }
::=

|
::=
SELECT []


::= DISTINCT | ALL

Комментарий:

Оператор PREPARE вызывает компиляцию и построение плана выполнения заданного в текстовой форме оператора SQL. После успешного выполнения оператора PREPARE с подготовленным оператором связывается указанное (литерально или косвенно) имя этого оператора, которое потом может быть использовано в операторах DESCRIBE, EXECUTE, OPEN CURSOR, ALLOCATE CURSOR и DEALLOCATE PREPARE. Эта связь сохраняется до явного выполнения оператора DEALLOCATE PREPARE.


17.6. Оператор отказа от подготовленного оператора


::=
DEALLOCATE PREPARE

Комментарий:

Выполнение этого оператора приводит к тому, что ранее подготовленный оператор SQL, связанный с указанным именем оператора, ликвидируется, и, соответственно, имя оператора становится неопределенным. Если подготовленный оператор являлся оператором выборки, и к моменту выполнения оператора DEALLOCATE существовал открытый курсор, связанный с именем подготовленного оператора, то оператор DEALLOCATE возвращает код ошибки. Если же для подготовленного оператора выборки существовал неоткрытый курсор, образованный с помощью оператора ALLOCATE CURSOR, то этот курсор ликвидируется. Если курсор объявлялся оператором DECLARE CURSOR, то такой курсор переходит в состояние, существовавшее до выполнения оператора PREPARE. Если с курсором был связан подготовленный оператор (динамический DELETE или UPDATE), то для этих операторов выполняется неявный оператор DEALLOCATE.


17.7. Оператор запроса описания подготовленного оператора


::=

|
::=
DESCRIBE INPUT
::=
DESCRIBE [OUTPUT]
::= |
::=
{ USING | INTO } [{ }...]
::=
::=
{ USING | INTO } SQL DESCRIPTOR
::=

|

::=

[]


::= [INDICATOR]

::=
[]
::= [INDICATOR]

Комментарий:

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


17.8. Оператор выполнения подготовленного оператора


::=
EXECUTE
(]
(
]
::=

::=



Комментарий:

Оператор EXECUTE может быть применен к любому ранее подготовленному оператору SQL, кроме . Если это оператор , то оператор EXECUTE должен содержать раздел с ключевым словом INTO. В любом случае число фактических параметров, задаваемых через разделы using, должно соответствовать числу формальных параметров, определенных в подготовленном операторе SQL.


17.9. Оператор подготовки с немедленным выполнением


::=
EXECUTE IMMEDIATE

Комментарий:

При выполнении оператора EXECUTE IMMEDIATE производится подготовка и немедленное выполнение заданного в текстовой форме оператора SQL. При этом подготавливаемый оператор не должен быть оператором выборки, не должен содержать формальных параметров и комментариев.


17.10. Оператор объявления курсора над динамически подготовленным оператором выборки


::=
DECLARE [INSENSITIVE] [SCROLL]
CURSOR FOR

Комментарий:

Как определяется в новом стандарте, для всех операторов DECLARE CURSOR, курсоры фактически создаются при начале транзакции и уничтожаются при ее завершении. Заметим, что в этом операторе и - прямо заданные идентификаторы.


17.11. Оператор определения курсора над динамически подготовленным оператором выборки


::=
ALLOCATE [INSENSITIVE] [SCROLL]
CURSOR FOR
::=
(]

Комментарий:

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


17.12. Оператор открытия курсора, связанного с динамически подготовленным оператором выборки


::=
OPEN []

Комментарий:

По сути, оператор открытия курсора, связанного с динамически подготовленным оператором SQL, отличается от статического случая только возможным наличием раздела using, в котором задаются фактические параметры оператора выборки. Кроме того, имя курсора может задаваться через переменную.


17.13. Оператор чтения строки по курсору, связанному с динамически подготовленным оператором выборки


::=
FETCH [[] FROM]


Комментарий:

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


17.14. Оператор закрытия курсора, связанного с динамически подготовленным оператором выборки


::=
CLOSE

Комментарий:

По сути, оператор закрытия курсора, связанного с динамически подготовленным оператором SQL, отличается от статического случая только тем, что имя курсора может задаваться через переменную.


17.15. Оператор позиционного удаления по курсору, связанному с динамически подготовленным оператором выборки


::=
DELETE FROM

WHERE CURRENT OF

Комментарий:

По сути, оператор позиционного удаления по курсору, связанному с динамически подготовленным оператором SQL, отличается от статического случая только тем, что имя курсора может задаваться через переменную.


17.16. Оператор позиционной модификации по курсору, связанному с динамически подготовленным оператором выборки


::=
UPDATE

SET [{ }...]
WHERE CURRENT OF

Комментарий:

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


17.17. Подготавливаемый оператор позиционного удаления

::=
DELETE [FROM

]


WHERE CURRENT OF

Комментарий:

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


17.18. Подготавливаемый оператор позиционной модификации

::=
UPDATE [

]


SET [{ }...]
WHERE CURRENT OF

Комментарий:

Смотри предыдущий пункт.

Если внимательно сравнивать средства динамического SQL СУБД Oracle V.6 и стандарта SQL/92, то видно, что Oracle практически вкладывается в стандарт, если не считать небольших синтаксических различий и (что существенно более важно) разного стиля работы с дескрипторами. Думается, что примерно такая же ситуация имеет место в других СУБД, поддерживающих динамический SQL.

17.19. Сводка новых возможностей SQL-3


В стандарте SQL/92 по сравнению со стандартом SQL/89 язык был расширен главным образом количественно, хотя даже этих количественных расширений оказалось достаточно для того, чтобы стандарт SQL/92 не удалось полностью реализовать до сих пор в большинстве коммерческих СУБД. Поскольку SQL/92 не удовлетворял значительной части претензий, исторически предъявляемых к языку SQL, был сформирован новый комитет, который должен выработать стандарт языка с качественными расширениями. Язык SQL-3 пока не сформирован полностью, многие аспекты продолжают обсуждаться. Поэтому к приводимой здесь сводке возможностей нужно относиться как к сугубо предварительной.
17.19.1. Типы данных

Набор встроенных типов данных предполагается расширить типами BOOLEAN и ENUMERATED. Хотя по причине поддержки неопределенных значений языку SQL свойственно применение трехзначной логики, тип BOOLEAN содержит только два возможных значения true и false. Для представления значения unknown рекомендуется использовать NULL, что, конечно, не вполне естественно. Перечисляемый тип ENUMERATED обладает свойствами, подобными свойствам перечисляемых типов в языках программирования.

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

Предполагается включение в язык возможности использования определенных пользователями типов данных. Видимо, будут иметься возможности определения абстрактных типов данных с произвольно сложной внутренней структурой на основе таких традиционных возможностей агрегирования и структуризации как LIST, ARRAY, SET, MULTISET и TUPLE, а также возможности определения объектных типов с соответствующими методами в стиле объектно-ориентированного подхода.

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


17.19.2. Некоторые другие свойства SQL-3

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

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

Что касается управления транзакциями, то происходит возврат к старой идее System R о возможности установки внутри транзакции точек сохранения (savepoints). В операторе ROLLBACK можно указать идентификатор ранее установленной точки сохранения, и тогда будет произведен откат транзакции не к ее началу, а к этой точке сохранения.

Как видно, можно ожидать наличия в SQL-3 многих интересных и полезных возможностей. Однако даже промежуточные проекты стандарта включают почти в два раза больше страниц, чем стандарт SQL/92. Поэтому трудно ожидать быстрой реализации этого стандарта после его принятия (а многие вообще сомневаются, что этот стандарт будет когда-либо реализован).


1   ...   8   9   10   11   12   13   14   15   ...   18


База данных защищена авторским правом ©bezogr.ru 2016
обратиться к администрации

    Главная страница