8.8.1. Единичный select. Получить статус и город для поставщика, чей номер поставки задан базовой переменной givens#

ЕХЕС SQL SELECT STATUS, CITY

INTO :RANK, :CITY

FRОM S

WHERE S# = :GIVENS# ;

Термин "единичный SELECT"^* используется для обозначения выражения выборки, значением которого будет таблица, содержащая не более одной строки. В данном мере, если существует ровно одна строка в таблице S, удовлетворяющая условию w то значения STATUS и CITY из этой строки в соответствии с запросом будут присвоены базовым переменным RANK и CITY, а значение sqlstate будет равно 00000. Если в таблице S нет ни одной строки, удовлетворяющей условию where, то переменной sqlstate будет присвоено значение 02000; а если таких строк будет больше одной, то будет сгенерирована ошибка и переменная sqlstate будет содержать код ошибки.

8.8.2. Insert. Вставить новую деталь в таблицу р (номер детали, ее назв. И вес определены базовыми переменными р#, pname, pwt соответственно, цвет и город неизвестны)

EXEC SQL INSERT

INTO P (P PNAME, COLOR, WEIGHT, CITY)

VALUES (:P : PNAME, DEFAULT, :PWT, DEFAULT) ;

8.8.3, Update. Увеличить статус всех поставщиков из Лондона на значение, определенное базовой переменной raise

ЕХЕС SQL UPDATE S

SET STATUS = STATUS + : RAISE WHERE CITY = 'London' ;

Если нет строк поставщиков, удовлетворяющих условию where, переменной SQLSTATE присваивается значение 02000.

8.8.4. Delete. Удалить все поставки для поставщиков из города,

определенного базовой переменной CITY

EXEC SQL DELETE

FROM SP

WHERE : CITY =

( SELECT CITY

FRОM S

WHERE S.S# = SP.S# ) ;

И снова, если нет строк, удовлетворяющих условию where, переменной sqlstate при­сваивается значение 02000.

Операции, использующие курсоры

Теперь перейдем к вопросу о выборках на уровне множеств, т.е. выборках множества из нескольких строк, а не из одной строки. Как объяснялось ранее, это требует пооче­редного доступа к строкам множества, и механизмом такого доступа являются курсоры. Этот процесс схематически проиллюстрирован (рис. 8.4) на примере вы­борки информации о поставщиках (S#, SNAME и STATUS) для всех поставщиков в городе, определенном базовой переменной Y.

EXES SQL DECLARE X CURSOR -- определить курсор X */

SELECT S.S# , S. SNAME, S. STATUS

FROM S

WHERE S. CITY = : Y ;

EXEC SQL OPEN X; /* выполнить запрос */

DO for all S rows accessible via X; /* для всех строк S,*/

/*доступных через X */

EXEC SQL FETCH X INTO : S #, : SNAME, : STATUS;

/* получить следующего поставщика */

……………

END ;

EXEC SQL CLOSE X; /* деактивировать курсор X */

Рис. 8.4. Выборка нескольких строк

Пояснение. Оператор declare x cursor. .. определяет курсор с именем X, свя­занный с табличным выражением, определенным оператором select, который яв­ляется частью всего выражения declare. Причем такое табличное выражение не вычисляется в этом месте; declare cursor— чисто декларативное выражение. Табличное выражение вычисляется только при открытии курсора. Затем для вы­борки строк из результирующего множества по одной за раз используется оператор fetch, присваивающий выбранные значения базовым переменным в соответствии со спецификациями в инструкции into.

(Для простоты базовым переменным даны те же имена, что и у соответствующих столбцов базы данных. Обратите внимание, что в выражении select при определении курсора нет своей инструкции into.) Поскольку в результирующем множестве боль­шое количество строк, оператор fetch обычно используется в цикле (do. . .end в PL/I); цикл будет повторяться до тех пор, пока не закончатся строки для результирующего множества. При выходе из цикла курсор Х деактивируется.

А теперь рассмотрим курсоры и операции с курсорами более подробно. Курсор определяется с помощью оператора declare cursor, общая форма которого следующая:

ЕХЕС SQL DECLARE cursor CURSOR

FOR table-expression

( ORDER BY order-item-commalist ) ;

Здесь cursor – это имя определяемого курсора. Что такое table-expression и order-item-commalist, объяснялось ранее в этой главе. Пример показан на рис. 8.4.

Замечание. Для краткости здесь не рассматриваются некоторые необязательные спецификации. Подробности смотрите в [8.1, 8.5].

Как утверждалось ранее, оператор DECLARE CURSOR – декларативный, а не выполняемый; он предназначен для объявления курсора с определенным именем и постоянно связанного с ним табличного выражения. Табличное выражение может включать ссылки на базовые переменные. Программа может включать любое количество операторов declare cursor, каждый из которых, конечно, предназначен для разных курсоров.

Для работы с курсорами существует три выполняемых оператора: open, fetch и close.

1. Инструкция

ЕХЕС SQL OPEN cursor ;

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

2. Инструкция

ЕХЕС SQL FETCH cursor INTO host-variable-commalist ;

служит для передвижения определенного курсора cursor (который должен быть открыт) к следующей строке в активном множестве с последующим присвоением значений этой строки базовым переменным, указанным в инструкции inто. Если при выполнении оператора fetch следующей строки нет, то данные не выбираются и переменной sqlstate присваивается значение 02000.

3. Инструкция

ЕХЕС SQL CLOSE cursor ;

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

Есть еще два оператора, в которых используются курсоры, — это current-формы операторов updaie и delete. Если курсор, скажем X, в текущий момент находится в позиции определенной строки, то можно обновить или удалить «текущую строку Х» т.е. строку, на которую "указывает" X. Например:

ЕХЕС SQL UPDATE S

SET STATUS = STATUS + :RAISE

WHERE CURRENT OF X ;

Выражения update. . .where current и delete. . .where current были бы не допустимы, если бы табличное выражение в объявлении курсора было определено как необновляемое представление с помощью инструкции create view (подробнее представления обсуждаются в последующих главах книги).

Динамический SQL

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

1. Принять команду терминала.

2. Проанализировать команду.

3. Сгенерировать соответствующие SQL-операторы для базы данных.

4. Возвратить сообщение и (или) результаты на терминал.

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

Два основных динамических оператора— prepare и execute. Их использование проиллюстрировано на следующем (не реальном, но правильном) примере:

DCL SQLSOURCE CHAR VARYING (65000) ;

SQLSOURCE = 'DELETE FROM SP WHERE SP.QTY < 300' ;

EXEC SQL PREPARE SQLPREPPED FROM :SQLSOURCE ;

EXEC SQL EXECUTE SQLPREPPED ;

Пояснения:

1. Имя sqlsource идентифицирует переменную PL/I символьной строки переменной длины, в которой программой каким-либо образом сконструирована исходная форма (т.е. представление в виде символьной строки) некоторого оператора SQL, в нашем конкретном примере — оператора delete.

2. Имя sqlprepped, напротив, идентифицирует переменную SQL, а не переменную ба­зового языка PL/I, которая (концептуально) будет использоваться для хранения скомпилированной формы SQL-оператора (его исходная форма представлена в переменной sqlsource) . Конечно, имена sqlsource и sqlprepped можно изменить.

3. С помощью инструкции "sqlsource =...;" переменной sqlsource присваивается ис­ходная форма SQL-оператора delete. Конечно, на практике процесс конструирова­ния такого исходного оператора будет значительно сложнее и, возможно, в нем будет использоваться ввод и анализ некоторых запросов от конечного пользовате­ля, выраженных на обычном языке или в другой, более "дружественной для поль­зователя" форме, чем обыкновенный язык SQL.

4. Оператор prepare берет это исходное выражение и "подготавливает" его (т.е. ком­пилирует), создавая выполняемую версию, которая сохраняется в переменной SQLPREPPED.

5. И, наконец, оператор execute выполняет эту версию sqlprepped, в результате появляется настоящий оператор delete. Информация sqlstate оператора delete возвращается точно так же, как при выполнении этого оператора обыч­ным образом.

Обратите внимание, что, так как имя sqlprepped идентифицирует переменную SQL, а не PL/I, то перед ним нет двоеточия, когда оно используется в операторах prepare и execute. Заметьте также, что такие переменные SQL не объявляются явно.

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

Более подробная информация о динамическом SQL содержится в [8.1, 8.5].