Агрегатные функции и результаты запросов
Агрегатные функции (в стандарте SQL/89 они называются функциями над множествами) определяются следующими синтаксическими правилами:
COUNT(*) |
|
{ AVG | MAX | MIN | SUM | COUNT }
(DISTNICT
{ AVG | MAX | MIN | SUM }
([ALL]
Как видно из этих правил, в стандарте SQL/89 определены пять стандартных агрегатных функций: COUNT - число строк или значений, MAX - максимальное значение, MIN - минимальное значение, SUM - суммарное значение и AVG - среднее значение.
набор операторов SQL предназначен
Описанный в стандарте 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.
Динамический SQL в стандарте SQL/
Набор операторов динамического SQL в стандарте SQL/92 существенно шире того, который был реализован в Oracle V.6. В основном это связано с тем, что введены операторы для работы с дескрипторами, а также появились подготавливаемые операторы позиционного удаления и позиционной модификации.
ДИНАМИЧЕСКИЙ SQL
В стандарте определены операторы динамического SQL. См. разд. 4.1.
Другая разновидность оператора определения курсора над динамически подготовленным оператором выборки
Для этого оператора действуют следующие синтаксические правила:
ALLOCATE
CURSOR FOR
[
Курсоры, определяемые с помощью оператора ALLOCATE CURSOR, фактически создаются при выполнении такого оператора и уничтожаются при выполнении оператора DEALLOCATE PREPARE или при конце транзакции. В этом операторе имена курсора и подготовленного оператора SQL могут задаваться не только в литеральной форме, но и через переменные (т.е. может использоваться косвенное именование).
ИНТЕРАКТИВНЫЙ (ПРЯМОЙ) SQL
В SQL/92 специфицирован набор операторов SQL, которые должны поддерживаться в интерактивном режиме, хотя некоторые решения по-прежнему отдаются на откуп реализациям.
Этим не исчерпываются расширения SQL/92 по сравнению с SQL/89. Однако не упомянутые выше новые возможности SQL не кажутся настолько важными, чтобы о них стоило писать в журнальной статье (может быть, я и ошибаюсь...).
ИНТЕРНАЦИОНАЛИЗАЦИЯ И НАЦИОНАЛИЗАЦИЯ
При определении схемы базы данных или впоследствии можно определить особенности национального набора символов, включая правила упорядочения. Могут определяться наборы символов, используемые как в хранимых текстовых строках, так и в идентификаторах.
Язык баз данных SQL/
В этом разделе мы опишем некоторые черты языка SQL/89, сопровождая описание соображениями о целесообразности и/или способе использования тех или иных конструкций при программировании потенциально мобильных прикладных систем.
Язык модулей или встроенный 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 (еще раз заметим, что формально правила встраивания не являются частью стандарта).
Язык модулей
Структура модуля SQL в стандарте SQL/89 определяется следующими синтаксическими правилами:
[
< procedure > ...
MODULE [
LANGUAGE { COBOL | FORTRAN | PASCAL | PLI }
AUTHORIZATION::=
Существенно, что каждый модуль SQL ориентирован на использование в программах, написанных на конкретном языке программирования. Если в модуле присутствуют процедуры работы с курсорами (см. п. 2.6.1), то все курсоры должны быть специфицированы в начале модуля. Заметим, что объявление курсора не погружается в какую-либо процедуру, поскольку это описательный, а не выполняемый оператор SQL (другими словами, все курсоры будут автоматически объявлены в самом начале выполнения прикладной программы, связанной с модулем SQL).
КУРСОРЫ
При определении курсора можно указывать ключевые слова SCROLL и INSENSITIVE. Указание SCROLL означает, что курсор можно явно позиционировать: на первую строку результирующего множества запроса, на последнюю строку, на строку с позицией с положительным или отрицательным смещением от текущей строки, на строку с явно указанным абсолютным номером позиции. Наличие ключевого слова INSENSITIVE означает, что какие бы изменения в базовых таблицах не производились в той же транзакции, в которой определен курсор, они не должны влиять на результирующее множество строк курсора после его открытия. Заметим, что, хотя внешне эти возможности выглядят очень привлекательно, их реализация стоит недешево. И в том и в другом случае требуется явное и почти всегда полное построение результирующего множества запроса, связанного с курсором.
Набор операторов манипулирования данными
В стандарте SQL/89 определен очень ограниченный набор операторов манипулирования данными. Их можно классифицировать на группы операторов, связанных с курсором; одиночных операторов манипулирования данными; и операторов завершения транзакции. Все эти операторы можно использовать как в модулях SQL, так и во встроенном SQL. Заметим, что в SQL/89 не определен набор операторов интерактивного SQL (т.е. отсутствуют явные спецификации набора операторов SQL, которые могут задаваться в интерактивном режиме).
Некоторые черты SQL/
Огромный объем стандарта SQL/92 и ограниченный объем этой статьи не позволяют нам описать этот стандарт сколько-нибудь подробно. Кроме того, как отмечалось выше, на сегодняшний день все еще отсутствует какая бы то ни было полная реализация SQL/92. Тем не менее мы считаем полезным сравнительно подробно описать стандартные средства динамического SQL (это описание можно использовать хотя бы в качестве эталона при сравнении различных реализаций) и привести сводку основных отличий SQL/92 от SQL/89 (в этом мы будем следовать последнему изданию книги Дейта "Стандарт SQL").
Несовместимости
В стандарте SQL/92 содержится приложение, в котором устанавливаются несовместимости между SQL/92 и SQL/89. Формально SQL/92 не включает в себя полностью SQL/89, т.е. некоторые конструкции SQL/89 не соответствуют стандарту SQL/92. Конечно, при переходе от SQL/89 к SQL/92 нужно внимательно отнестись к этим несоответствиям. Однако, во-первых, эти несоответствия являются слишком техническими и непринципиальными, чтобы описывать их в этой статье. Во-вторых, ни один производитель СУБД никогда не пойдет на то, чтобы с использованием его нового продукта перестали работать ранее разработанные прикладные системы. Поэтому можно быть почти уверенным (или уверенной), что в реализациях SQL/89 по-прежнему будет поддерживаться. Так что по поводу несоответствий мы отсылаем читателя к тексту стандарта SQL/92.
Одиночные операторы манипулирования данными
Каждый из операторов этой группы является абсолютно независимым от какого бы то ни было другого оператора, т.е. для выполнения любого оператора этой группы не требуется предварительное выполнение какого бы то ни было другого оператора.
Ограничение по ссылкам
Ограничение по ссылкам от заданного набора столбцов 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. По-человечески это можно сформулировать так: ограничение по ссылкам удовлетворяется, если для каждой корректной ссылки существует объект, на который она ссылается. В привычной программистам терминологии, ограничение по ссылкам не позволяет производить "висячие" ссылки, не ведущие ни к какому объекту.
Ограничение уникальности
Каждое имя столбца в списке уникальности должно именовать столбец T и не должно входить в этот список более одного раза. При определении столбца, входящего в список уникальности, должно быть указано ограничение столбца NOT NULL. Среди ограничений уникальности T не должно быть более одного определения первичного ключа (ограничения уникальности с ключевым словом PRIMARY KEY).
Действие ограничения уникальности состоит в том, что в таблице T не допускается появление двух или более строк, значения столбцов уникальности которых совпадают.
ОГРАНИЧЕНИЯ ЦЕЛОСНОСТИ
В добавление к возможностям SQL/89 определения ограничений целостности на уровне столбца и/или таблицы в SQL/92 допустимо отдельное определение ограничений, распространяющееся в общем случае на несколько таблиц.
Появилась возможность определения отложенных (проверяемых при завершении транзакции) ограничений целостности.
Расширены возможности определения ограничений внешнего ключа (ограничений ссылочной целостности).
Введены средства определения (CREATE DOMAIN), изменения (ALTER DOMAIN) и отмены определения (DROP DOMAIN) домена. (На всякий случай напомним читателям, что домены имеют непосредственную связь с ограничениями целостности, поскольку домен определяет потенциально возможное множество значений некоторого типа данных, а при определении столбца таблицы можно указать, к какому домену будут относиться значения этого столбца. Тем самым другие значения допускаться не должны.)
ОПЕРАТОР ЧТЕНИЯ ОЧЕРЕДНОЙ СТРОКИ КУРСОРА
Синтаксис оператора чтения следующий:::=
FETCHINTO
::=
[{, }...]
В операторе чтения указывается имя курсора и обязательный раздел INTO, содержащий список спецификаций назначения (список имен переменных основной программы в случае встроенного SQL или имен "выходных" параметров в случае модуля SQL). Число и типы данных в списке назначений должны совпадать с числом и типами данных списка выборки спецификации курсора.
Любой открытый курсор всегда имеет позицию: он может быть установлен перед некоторой строкой результирующей таблицы (перед первой строкой сразу после открытия курсора), на некоторую строку результата или за последней строкой результата.
Если таблица, на которую указывает курсор, является пустой, или курсор позиционирован на последнюю строку или за ней, то при выполнении оператора чтения курсор устанавливается в позицию после последней строки, параметру SQLCODE присваивается значение 100, никакие значения не присваиваются целям, указанным в разделе INTO.
Если курсор установлен в позицию перед строкой, то при выполнении оператора чтения он устанавливается на эту строку, и значения строки присваиваются соответствующим целям.
Если курсор установлен на строку r, отличную от последней строки, то курсор устанавливается на строку, непосредственно следующую за строкой r (в порядке, определенном реализацией, если запрос не содержит раздела ORDER BY), и значения из этой следующей строки присваиваются соответствующим целям.
Возникает естественный вопрос, каким образом можно параметризовать курсор неопределенным значением или узнать, что выбранное из очередной строки значение является неопределенным. Это достигается в SQL/89 за счет использования так называемых индикаторных параметров и переменных. Если известно, что значение, передаваемое из основной программы СУБД или принимаемое основной программой от СУБД, может быть неопределенным, и этот факт интересует прикладного программиста, то спецификация параметра в операторе SQL имеет вид:[INDICATOR] , а спецификация переменной - [INDICATOR] . Отрицательное значение индикаторного параметра или индикаторной переменной (они должны быть целого типа) соответствует неопределенному значению параметра или переменной.
Оператор чтения строки по курсору, связанному с динамически подготовленным оператором выборки
Синтаксис:::=
FETCH [[] FROM]
На самом деле оператор чтения по курсору, связанному с динамически подготовленным оператором SQL, отличается от статического случая только возможным наличием раздела using, в котором задается размещение значений текущей строки результирующей таблицы. Кроме того, имя курсора может задаваться через переменную.
Оператор объявления курсора над динамически подготовленным оператором выборки
Оператор определяется следующим синтаксисом:::=
DECLARE[INSENSITIVE] [SCROLL]
CURSOR FOR
Как определяется в новом стандарте, для всех операторов DECLARE CURSOR курсоры фактически создаются при начале транзакции и уничтожаются при ее завершении. Заметим, что в этом оператореи прямо (литерально) заданные идентификаторы.
ОПЕРАТОР ОБЪЯВЛЕНИЯ КУРСОРА
Для удобства мы повторим здесь синтаксические правила объявления курсора, приведенные в подразделе 2.3.1:::=
DECLARECURSOR
FOR
::=
[ ...]
::=
|UNION [ALL]
::=
| ( )
::=
ORDER BY
[{,}...]
::=
{| }
[ASC | DESC]
В объявлении курсора могут задаваться запросы наиболее общего вида с возможностью выполнения операции UNION и сортировки конечного результата. Этот оператор не является выполняемым, он только связывает имя курсора со спецификацией курсора.
Оператор определения схемы
В соответствии с правилами SQL/89 каждая таблица данной БД имеет простое и квалифицированное (уточненное) имена. В качестве квалификатора имени выступает "идентификатор полномочий" таблицы, который обычно в реализациях совпадает с именем некоторого пользователя. Квалифицированное имя таблицы имеет вид:
<идентификатор полномочий>.<простое имя>
Подход к определению схемы в SQL/89 состоит в том, что все таблицы с одним идентификатором полномочий создаются (определяются) путем выполнения одного оператора определения схемы. При этом в стандарте не определяется способ выполнения оператора определения схемы: должен ли он выполняться только в интерактивном режиме или может быть встроен в программу, написанную на традиционном языке программирования. (Собственно, поэтому трудно говорить о том, поддерживается ли в конкретной реализации стандарт SQL/89 в части средств определения схемы БД.)
В операторе определения схемы содержится идентификатор полномочий и список элементов схемы, каждый из которых может быть определением таблицы, определением представления (view) или определением привилегий. Каждое из этих определений представляется отдельным оператором SQL/89, но все они, как уже говорилось, должны быть встроены в оператор определения схемы.
Для этих операторов мы приведем синтаксис, поскольку это позволит более четко описать их особенности.
Оператор освобождения памяти из-под дескриптора
Синтаксис оператора:::=
DEALLOCATE DESCRIPTOR
Выполнение этого оператора приводит к освобождению памяти из-под ранее выделенного дескриптора. После этого использование имени дескриптора незаконно в любом операторе, кроме ALLOCATE DESCRIPTOR.
Оператор отказа от подготовленного оператора
Синтаксис оператора следующий:::=
DEALLOCATE PREPARE
Выполнение этого оператора приводит к тому, что ранее подготовленный оператор SQL, связанный с указанным именем оператора, ликвидируется, и, соответственно, имя оператора становится неопределенным. Если подготовленный оператор являлся оператором выборки, и к моменту выполнения оператора DEALLOCATE существовал открытый курсор, связанный с именем подготовленного оператора, то оператор DEALLOCATE возвращает код ошибки. Если же для подготовленного оператора выборки существовал неоткрытый курсор, образованный с помощью оператора ALLOCATE CURSOR, то этот курсор ликвидируется. Если курсор объявлялся оператором DECLARE CURSOR, то такой курсор переходит в состояние, существовавшее до выполнения оператора PREPARE. Если с курсором был связан подготовленный оператор (динамический DELETE или UPDATE), то для этих операторов выполняется неявный оператор DEALLOCATE.
Оператор открытия курсора, связанного с динамически подготовленным оператором выборки
Синтаксис оператора открытия курсора следующий:::=
OPEN[ ]
По сути оператор открытия курсора, связанного с динамически подготовленным оператором SQL, отличается от статического случая только возможным наличием раздела using, в котором задаются фактические параметры оператора выборки. Кроме того, имя курсора может задаваться через переменную (т.е. косвенным образом).
ОПЕРАТОР ОТКРЫТИЯ КУРСОРА
Оператор описывается следующим синтаксическим правилом:::=
OPEN
В реализациях встроенного SQL обычно требуется, чтобы объявление курсора текстуально предшествовало оператору открытия курсора. Оператор открытия курсора должен быть первым в серии выполняемых операторов, связанных с заданным курсором. При выполнении этого оператора производится подготовка курсора к работе над ним. В частности, в этот момент производится связывание спецификации курсора со значениями переменных основного языка в случае встроенного SQL или параметров в случае модуля (это значит, что после выполнения оператора открытия курсора любые изменения переменных основной программы не будут оказывать какие-либо действия на результат запроса, связанного с курсором).
В большинстве реализаций в случае встроенного SQL именно выполнение оператора открытия курсора приводит к компиляции спецификации курсора и подготовке выполняемого плана запроса. Можно считать (хотя фактически это делается далеко не всегда), что во время выполнения оператора открытия курсора производится построение временной таблицы, содержащей результат запроса, который связан с этим курсором.
Следующие операторы можно выполнять над открытым курсором в произвольном порядке.
Оператор подготовки с немедленным выполнением
Синтаксис оператора:::=
EXECUTE IMMEDIATE
При выполнении оператора EXECUTE IMMEDIATE производится подготовка и немедленное выполнение заданного в текстовой форме оператора SQL. При этом подготавливаемый оператор не должен быть оператором выборки, не должен содержать формальных параметров и комментариев.
Оператор подготовки
Оператор PREPARE имеет синтаксис:::=
PREPAREFROM
::=
Во время выполнения оператора PREPARE символьная строка, содержащаяся в host-string-variable, передается компилятору SQL, который обрабатывает ее почти таким же образом, как если бы получил в статике. Построенный при выполнении оператора PREPARE код остается действующим до конца транзакции или до повторного выполнения данного оператора PREPARE в пределах этой же транзакции.
В отличие от операторов SQL, статически подставляемых в программу на обычном языке программирования, в которых связь с переменными включающей программы производится по именам (т.е. в соответствии со стандартом во встроенном операторе SQL могут употребляться просто имена переменных включающей программы), динамическая природа операторов, подготавливаемых с помощью оператора PREPARE, заставляет рассматривать эти имена как имена формальных параметров. Соответствие этих формальных параметров адресам переменных включающей программы устанавливается позиционно во время выполнения подготовленного оператора.
ОПЕРАТОР ПОИСКОВОГО УДАЛЕНИЯ
Оператор описывается следующим синтаксическим правилом:::=
DELETE FROM
WHERE []
Таблица T, указанная в разделе FROM оператора DELETE, должна быть изменяемой. На условие поиска накладывается то условие, что на столбцы таблицы T не должны содержаться ссылки ни в каком вложенном подзапросе предикатов раздела WHERE.
Фактически, оператор выполняется следующим образом: последовательно просматриваются все строки таблицы T, и те строки, для которых результатом вычисления условия выборки является true, удаляются из таблицы T. При отсутствии раздела WHERE удаляются все строки таблицы T (обычно при выполнении поискового оператора DELETE без раздела WHERE в интерактивном режиме до удаления всех строк запрашивается подтверждение правильности такого действия).
ОПЕРАТОР ПОИСКОВОЙ МОДИФИКАЦИИ
Оператор обладает следующим синтаксисом:::=
UPDATE
SET
[{,}...]
[WHERE]
::=