Дейт К. Д27 Руководство по реляционной субд db2/ Пер с англ и предисл. М. Р. Когаловского

Вид материала

Руководство

Содержание Глава 12 прикладное программирование iii: динамический sql 12.1. введение 12.2. Обработка предложении, отличных от select Предложение PREPARE Предложение EXECUTE Аргументы и параметры 12.3. Обработка предложений select

Подобный материал:

• Рич Р. К. Политология. Методы исследования: Пер с англ. / Предисл. А. К. Соколова, 6313.17kb.
• Рич Р. К. Политология. Методы исследования: Пер с англ. / Предисл. А. К. Соколова, 6313.29kb.
• Сорокин П. А. С 65 Человек. Цивилизация. Общество / Общ ред., сост и предисл., 11452.51kb.
• The guilford press, 6075kb.
• The guilford press, 6075.4kb.
• Курс лекций "Базы данных и субд" Ульянов В. С. Лекция. Целостная часть реляционной, 213.79kb.
• Дэвид Дайчес, 1633.42kb.
• Mathematics and the search for knowledge morris kline, 498.28kb.
• Лекция №1: Стандарты языка sql, 1420.56kb.
• Курс лекций "Базы данных и субд" Ульянов В. С. Лекция. Манипулирование реляционными, 276.31kb.

ГЛАВА 12

ПРИКЛАДНОЕ ПРОГРАММИРОВАНИЕ III:

ДИНАМИЧЕСКИЙ SQL

12.1. ВВЕДЕНИЕ

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

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

1. Прием команды с терминала.

2. Анализ этой команды.

3. Обращение к базе данных с помощью соответствующих предложений языка SQL.

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

Между прочим, только что описанный процесс—это как раз то, что происходит, когда сами предложения SQL вводятся в интерактивном режиме с помощью DB2I и QMF (если нужно вспомнить смысл этих двух обозначений, см. раздел 1.3). В обоих случаях исполняется интерактивное приложение, которое готово принимать чрезвычайно широкое многообразие входных данных, а именно любое правильное (или неправильное!) предложение SQL. Для формирования подходящих предложений встроенного SQL, соответствующих его входным данным, их связывания и исполнения, а также для возвращения сообщений и результатов на терминал, это приложение использует возможности динамического SQL.

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

12.2. ОБРАБОТКА ПРЕДЛОЖЕНИИ, ОТЛИЧНЫХ ОТ SELECT

Два основных предложения динамического SQL — PREPARE (подготовить) и EXECUTE (выполнить). Их использование проиллюстрируем следующим (правильным, но нереалистичным) примером на языке ПЛ/1:

DCL ИСХОДНЫЙ_SQL CHAR(256) VARYING;

EXEC SQL DECLARE ОБЪЕКТНЫЙ_SQL STATEMENT;

ИСХОДНЫЙ_SQL = 'DELETE FROM SP WHERE

КОЛИЧЕСТВО < 100';

EXEC SQL PREPARE ОБЪЕКТНЫЙ_SQL FROM : ИСХОДНЫЙ-SQL;

EXEC SQL EXECUTE ОБЪЕКТНЫЙ_SQL;

Пояснение. Здесь ИСХОДНЫЙ-SQL это переменная ПЛ/1 типа строки символов переменной длины, в которой программа построит некоторое предложение SQL в исходном формате, т. е. представление его в виде строки символов. Напротив, ОБЪЕКТНЫЙ_SQL это переменная SQL, а не ПЛ/1, которая будет содержать для предложения SQL, заданного в исходном формате в строке ИСХОДНЫИ_SQL, его запись в объектном формате, т. е. представление этого предложения в машинном коде. Имена ИСХОДНЫЙ_SQL и ОБЪЕКТНЫИ_SQL выбраны произвольно. Оператор присваивания «ИСХОДНЫЙ_SQL =...;» присваивает переменной ИСХОДНЫЙ_SQL запись в исходном формате предложения DELETE языка SQL. (Как уже отмечалось в разделе 12.1, на практике процесс построения такого исходного предложения является, вероятно, несколько более сложным, требует ввода нескольких команд с терминала и их анализа.) Далее предложение PREPARE осуществляет прекомпиляцию и связывание этого исходного предложения и на этой основе продуцирует некоторый вариант программы в коде машины, который она запоминает как значение переменной ОБЪЕКТНЫЙ_SQL. Наконец, предложение EXECUTE исполняет этот вариант программы и, таким образом, приводит (в данном примере) к фактическому исполнению операции DELETE. Информация обратной связи, порождаемая этой операцией, будет, как обычно, возвращаться в область связи SQLCA.

Отметим, между прочим, что поскольку имя предложения ОБЪЕКТНЫЙ_SQL обозначает переменную SQL, а не ПЛ/1, оно не имеет префикса — двоеточия в предложениях PREPARE и EXECUTE.


Предложение PREPARE

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


EXEC SQL PREPARE имя — предложения FROM строковое — выражение;


Здесь «строковое — выражение» это выражение включающего языка, которое определяет представление некоторого предложения SQL в форме строки символов, а «имя—предложения»—имя переменной SQL, которая будет использоваться для того, чтобы содержать подготовленный операцией PREPARE, т. е. прекомпилированный и связанный вариант этого предложения языка SQL. Предложение, которое обрабатывается с помощью PREPARE, должно быть только одним из следующих:

UPDATE (включая форму CURRENT)

DELETE (включая форму CURRENT)

INSERT

SELECT

CREATE

DROP

ALTER

COMMENT

GRANT

REVOKE

COMMIT

ROLLBACK

LOCK

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


Предложение EXECUTE

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

ЕХЕС SQL EXECUTE имя — предложения [USING аргументы];

Исполняется предложение SQL, сформированное с помощью PREPARE в переменной SQL, которая идентифицируется «именем—предложения». Фраза USING поясняется в следующем параграфе «Аргументы и параметры».


Аргументы и параметры

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

Например:

ИСХОДНЫЙ_SQL = 'DELETE

FROM SP

WHERE КОЛИЧЕСТВО > ?

AND КОЛИЧЕСТВО < ?';

ЕХЕС SQL PREPARE ОБЪЕКТНЫЙ _ SQL FROM :ИСХОДНЫЙ_SQL;

Аргументы для замещения параметров специфицируются с помощью фразы USING предложения EXECUTE, обеспечивающего исполнение сформированного предложения. Например:

ЕХЕС SQL EXECUTE ОБЪЕКТНЫЙ_SQL USING

: НИЖНЕЕ_ЗНАЧЕНИЕ, : ВЕРХНЕЕ_ЗНАЧЕНИЕ;

Фактически исполняемое в данном примере предложение эквивалентно обычному предложению встроенного SQL:

ЕХЕС SQL DELETE FROM SP WHERE

КОЛИЧЕСТВО > : НИЖНЕЕ_ЗНАЧЕНИЕ

AND КОЛИЧЕСТВО < :ВЕРХНЕЕ_ЗНАЧЕНИЕ;

В общем случае фраза USING предложения EXECUTE имеет формат:

USING аргумент [, аргумент] . . .

где каждый «аргумент» в свою очередь имеет формат:

: переменная — включающего — языка [: переменная — включающего — языка]

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

12.3. ОБРАБОТКА ПРЕДЛОЖЕНИЙ SELECT

Как указывалось ранее, описанная в общих чертах в разделе 12.2 процедура пригодна для динамической подготовки и исполнения всех предложений SQL (точнее, всех предложений SQL, которые могут правильно обрабатываться с помощью PREPARE), за исключением SELECT. Причина такого отличия SELECT состоит в том, что оно возвращает программе данные, в то время как другие предложения возвращают только информацию обратной связи в область связи SQLCA.

Использующей SELECT программе необходимо кое-что знать о значениях тех данных, выборка которых должна производиться, поскольку в ней должно быть специфицировано множество принимающих эти значения целевых переменных. Необходимо знать но крайней мере сколько будет значений в каждой строке результата, а также каковы типы данных и длины этих значений. Если данное предложение SELECT генерируется динамически, программе обычно невозможно заранее «знать» эту информацию. Поэтому она должна получать эту информацию динамически, используя другое предложение динамического SQL, называемое DESCRIBE (описать). Реализуемая такой программой процедура в общих чертах может быть охарактеризована следующим образом.

1. Она формирует и обрабатывает с помощью PREPARE предложение SELECT без фразы INTO. (Обрабатываемое с помощью PREPARE предложение SELECT не должно включать фразу INTO.)

2. Используя DESCRIBE, она запрашивает систему о результатах, которых можно ожидать при исполнении данного предложения SELECT. Описание этих результатов возвращается в область, называемую областью дескрипторов SQL (SQLDA).

3. Далее, в соответствии с только что полученной с помощью DESCRIBE информацией распределяется память для множества целевых переменных, предназначенных для получения результатов. Адреса этих целевых переменных также помещаются в SQLDA.

4. Наконец, с помощью курсора последовательно по одной запрашиваются строки результата. Для этого используются предложения OPEN, FETCH и CLOSE. Если это требуется, в программе можно также использовать для этих строк предложения UPDATE CURRENT (обновить текущую) и DELETE CURRENT (удалить текущую). Эти предложения однако должны, вероятно, реализовываться с помощью PREPARE и EXECUTE.

Для того чтобы описанные идеи стали несколько более конкретными, приведем простой пример, показывающий, как могла бы выглядеть в общих чертах такая программа. Этот пример написан на языке ПЛ/1. Отметим, что он должен быть написан либо на ПЛ/1, либо на языке Ассемблера, поскольку должна иметься возможность динамического распределения памяти. Поэтому интерактивное приложение, осуществляющее выборку данных, должно быть написано на одном из двух указанных языков. Вместе с тем интерактивное приложение, использующее лишь средства, описанные в разделе 12.2, может быть также написано, если это требуется, на Коболе или Фортране. Перейдем теперь к примеру.

DCL ИСХОДНЫЙ_SQL CHAR(256) VARYING;

EXEC SQL DECLARE ОБЪЕКТНЫЙ_SQL STATEMENT;

EXEC SQL DECLARE X CURSOR FOR ОБЪЕКТНЫЙ_SQL;

EXEC SQL INCLUDE SQLDA;

/* Пусть максимальное число ожидаемых значений, подлежащих выборке, равно N */

SQLSIZE = N;

ALLOCATE SQLDA;

ИСХОДНЫЙ_SQL = 'SELECT * FROM SP WHERE

КОЛИЧЕСТВО > 100';

EXEC SQL PREPARE ОБЪЕКТНЫЙ_SQL FROM :ИСХОДНЫЙ_SQL;

EXEC SQL DESCRIBE ОБЪЕКТНЫЙ_SQL INTO SQLDA;

/* Теперь наряду с прочей информацией SQLDA содержит следующее: */

/* — в SQLN — фактическое число подлежащих выборке значений */

/* — в SQLVAR(i) — тип данных и длину i-го значения. */

/* Используя возвращаемую DESCRIBE информацию, программа может */

/* теперь распределить память для каждого значения, выборка */

/* которого должна быть осуществлена, и поместить адрес i-й */

/* выделенной области в SQLVAR(i). Тогда: */

EXEC SQL OPEN X;

DO WHILE (еще — поступают— записи);

EXEC SQL FETCH X

USING DESCRIPTOR SQLDA;

. . . .

END;

EXEC SQL CLOSE X;

Пояснение. Здесь ИСХОДНЫИ_SQL и ОБЪЕКТНЫЙ_SQL имеют в основном такой же смысл, как и ранее: ИСХОДНЫЙ_SQL будет содержать предложение SQL в исходном формате (в данном примере, конечно, предложение SELECT), а ОБЪЕКТНЫЙ_SQL — это же предложение в соответствующем объектном формате. X — курсор для этого SELECT. Обратите внимание, что он описывается предложением DECLARE CURSOR нового формата:

EXEC SQL DECLARE имя — курсора CURSOR FOR имя — предложения;

Объявление области дескрипторов SQL включается в программу с помощью предложения:

EXEC SQL INCLUDE SQLDA;

Это предложение генерирует объявление структуры SQLDA в языке ПЛ/1, а также объявление числовой переменной SQLSIZE. Программа должна установить SQLSIZE, равное N, где N — верхняя граница числа значений, которые должны быть выбраны в каждой строке предложением SELECT, выделить память для SQLDA. Объем выделяемой памяти будет функцией значения SQLSIZE.

Далее, в строке ИСХОДНЫЙ_SQL формируется требуемое предложение SELECT в исходном формате, которое затем обрабатывается с помощью PREPARE. В результате будет получен в ОБЪЕКТНЫИ_SQL соответствующий его объектный формат. После этого программа выдает команду DESCRIBE в связи с переменной ОБЪЕКТНЫИ_SQL с тем, чтобы получить описание значений, ожидаемых в каждой строке результата исполнения этого предложения SELECT. Такое описание состоит из двух частей:

а) фактическое число значений, подлежащих выборке, указывается в поле SQLN структуры SQLDA;

б) тип данных и длина каждого такого значения указываются в массиве SQLVAR структуры SQLDA.

Используя это описание, программа может теперь распределить помять для каждого из этих значений. Далее она помещает адреса выделенных областей памяти в SQLDA, а точнее — в массив SQLVAR.

Наконец, для выборки фактических данных программа использует предложения OPEN, FETCH и CLOSE в связи с курсором X. Следует обратить внимание, что при этом используется новый формат предложения FETCH. Вместо фразы INTO оно включает фразу USING DESCRIPTOR, а структура, указанная в этой фразе (обычно SQLDA), в свою очередь идентифицирует целевые переменные для подлежащих выборке значений.

С помощью PREPARE можно также обрабатывать предложения SELECT, которые содержат параметры, указываемые вопросительным знаком. Например:

ИСХОДНЫЙ_SQL = 'SELECT *

FROM SP

WHERE КОЛИЧЕСТВО > ?

AND КОЛИЧЕСТВО < ?';

ЕXEC SQL PREPARE ОБЪЕКТНЫЙ_SQL FROM :ИСХОДНЫЙ_SQL;

Аргументы специфицируются в соответствующем предложении OPEN. Например:

ЕXEC SQL OPEN X USING НИЖНЕЕ_ЗНАЧЕНИЕ, :ВЕРХНЕЕ_ЗНАЧЕНИЕ;

К SELECT не применяется предложение EXECUTE. Если выполняемое предложение—SELECT, функция EXECUTE выполняется предложением OPEN.

12.4. ЗАКЛЮЧЕНИЕ

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

В главе 10 описаны основы подхода, связанного с встроенным SQL. Материал этой главы относится ко всему программированию с помощью SQL и представляет интерес для всех занимающихся прикладным программированием в обстановке системы DB2. В главе 11 также рассматриваются принципы, имеющие отношение ко всем пользователям, обсуждаются концепции управления транзакциями — параллельное исполнение и восстановление, показывается, как эти концепции реализуются в языке SQL. Однако благодаря природе языка SQL пользователю весьма редко приходится беспокоиться об этом в явном виде. В большинстве случаев оказываются достаточными механизмы системы DB2, действующие по умолчанию. В отличие от двух предыдущих данная глава посвящена весьма специальному вопросу — как написать интерактивное приложение в SQL. Для такого приложения необходимы средства динамического SQL, главным образом предложения PREPARE и EXECUTE, а также DESCRIBE при использовании предложения SELECT, которое должно обрабатываться с помощью PREPARE. Примечание. Чтобы отличать другие части языка от динамических средств, рассмотренных в данной главе, их называют иногда статическим SQL.

Конечно можно использовать средства динамического SQL во всех случаях, когда необходима большая варьируемость, чем позволяют обычные статические предложения. Другими словами, было бы не совсем верно утверждать, что интерактивные приложения являются единственной мотивировкой для динамического SQL, хотя такое мнение представляется не очень далеким от истины. Рассмотрим для примера предложение: EXEC SQL SELECT * FROM :TNV; где TNV — это не имя таблицы, а имя переменной включающего языка, значение которой представляет собой имя таблицы. Это предложение не является правильным в обычном статическом SQL. Но для достижения желаемого эффекта можно использовать динамический SQL.

Тот факт, что система DB2 использует по существу один и тот же язык SQL как для интерактивного, так и для программного доступа к базе данных, имеет важное следствие: части базы данных, используемые прикладной программой, могут тестироваться и отлаживаться в интерактивном режиме. Интерактивный интерфейс позволяет создать тестовые таблицы, загрузить в них данные, выполнить над ними используемые в программе предложения SQL в интерактивном варианте, выдать запрос относительно этих таблиц или каталога, чтобы увидеть результат выполнения заданных предложений, и т. д. Иначе говоря, интерактивный интерфейс обеспечивает для программиста удобные возможности отладки. Он привлекателен и по другим причинам: процесс определения данных осуществляется обычно через этот интерфейс, как и процесс предоставления и отмены полномочий; обеспечиваются рудиментарные, но полезные возможности для специальных запросов.