Использование SQL в прикладном программировании
Введение
Накопленная
информация в современных информационных технологиях хранится и организованна в базах данных. Эти
базы данных создаются и работают с помощью систем управления базами данных
(СУБД).
Развитие
компьютерных технологий, увеличение объема и структурной сложности хранимых данных, увеличение числа
пользователей информационных систем привели к распространению реляционных СУБД.
Пользоваться базами
данных из разных уголков мира, с разных архитектур ЭВМ по сети можно
посредствами одного стандартного языка манипулирования данными. Разработанный в
1974 году язык SQL фирмой IBM для экспериментальной реляционной СУБД System R
стал стандартом (сначала де-факто), благодаря распространению двух СУБД (той же
фирмы) – SQL/DS (1981 год) и DB2 (1983 год), а признание SQL международным
стандартом произошло в 1987 году.
Реализация в SQL
концепции операций, ориентированных на табличное представление данных,
позволило создать компактный язык с небольшим (менее 30) набором предложений.
SQL может использоваться как интерактивный (для выполнения запросов) и как
встроенный (для построения прикладных программ).
Программисты
пользуются языком SQL, чтобы писать приложения, в которых содержатся обращения
к базам данных. Одни и те же операторы SQL используются как для интерактивного,
так и для программного доступа, поэтому части программ, содержащие обращения к
базе данных, можно вначале тестировать в интерактивном режиме, а затем
встраивать в программу. В традиционных базах данных для программного доступа
используются одни программные средства, а для выполнения немедленных запросов —
другие, без какой либо связи между этими двумя режимами доступа.
Многие современные
СУБД имеют собственные языки программирования, ряд которых включает в себя SQL.
Другие работают с программами, написанными на одном из распространенных
алгоритмических языков (Си, Паскаль или Фортран), в которые включаются
предложения SQL. Для обмена информацией с частями программы, написанными на
любых из этих языков, существуют специальные конструкции SQL, позволяющие
работать с переменными и (или) отдельными строками таблиц.
В письменной работе
будет рассмотрено использование SQL в прикладном
программировании.
В первой главе речь
пойдет о структуре модуля SQL и встроенном SQL. Определение процедур.
Во второй главе
описаны операторы манипулирования данными. Операторы, связанные с курсором, а
так же одиночные операторы манипулирования данными.
В третьей главе
будет рассмотрен динамический SQL в Oracle V.6.
1. Язык модулей и
встроенный SQL
В стандарте SQL/89 определены два способа
взаимодействия с БД из прикладной программы, написанной на традиционном языке
программирования.
Первый способ состоит в том, что все операторы SQL, с
которыми может работать данная прикладная программа, собраны в один модуль и
оформлены как процедуры этого модуля. Для этого SQL/89 содержит специальный
подъязык - язык модулей. При использовании такого способа взаимодействия с БД
прикладная программа содержит вызовы процедур модуля SQL с передачей им
фактических параметров и получением ответных параметров.
Второй способ состоит в использовании так называемого
встроенного SQL, когда с использованием специального синтаксиса в программу на
традиционном языке программирования встраиваются операторы SQL. В этом случае с
точки зрения прикладной программы оператор SQL выполняется "по
месту". Явная параметризация операторов SQL отсутствует, но во встроенных
операторах SQL могут использоваться имена переменных основной программы, и за
счет этого обеспечивается связь между прикладной программой и СУБД.
Концептуально эти два способа эквивалентны. Более
того, в стандарте устанавливаются правила порождения неявного модуля SQL по
программе со встроенным SQL. Однако в большинстве реализаций операторы SQL,
содержащиеся в модуле SQL, и встроенные операторы SQL обрабатываются
существенно по-разному. Модуль SQL обычно компилируется отдельно от прикладной
программы, в результате чего порождается набор так называемых хранимых процедур
(в стандарте этот термин не используется, но распространен в коммерческих
реализациях). Т.е. в случае использования модуля SQL компиляция операторов SQL
производится один раз, и затем соответствующие процедуры сколько угодно раз
могут вызываться из прикладной программы.
В отличие от этого, для операторов SQL, встроенных в
прикладную программу, компиляция этих операторов обычно производится каждый раз
при их использовании (правильнее сказать, при каждом первом использовании
оператора при данном запуске прикладной программы).
Конечно, пользователи не обязаны знать об этом
техническом различии в обработке двух видов взаимодействия с СУБД. Существуют и
такие системы, которые производят одноразовую компиляцию встроенных операторов
SQL и сохраняют откомпилированный код. Но все-таки лучше иметь это в виду.
При использовании языка модулей текст прикладной
программы имеет меньший размер, взаимодействия с СУБД более локализованы за
счет наличия явных параметров вызова процедур. С другой стороны, для понимания
смысла поведения прикладной программы потребуется одновременное чтение двух
текстов. Кроме того, как кажется, синтаксис модуля SQL может существенно
различаться в разных реализациях. Встроенный SQL предоставляет возможность
производства более "самосодержащихся" прикладных программ. Имеется
больше оснований рассчитывать на простоту переноса такой программы в среду
другой СУБД, поскольку стандарт встраивания более или менее соблюдается.
Основным недостатком является некоторый PL-подобный вид таких программ,
независимо от выбранного основного языка.
1.1. Язык модулей
Структура модуля SQL в стандарте SQL/89 определяется
следующими синтаксическими правилами:
::=
[...]
...
::= MODULE []
::= LANGUAGE { COBOL | FORTRAN
| PASCAL | PLI }
::=
AUTHORIZATION
::=
Существенно, что каждый модуль SQL ориентирован на
использование в программах, написанных на конкретном языке программирования.
Если в модуле присутствуют процедуры работы с курсорами, то все курсоры должны
быть специфицированы в начале модуля. Заметим, что объявление курсора не
погружается в какую-либо процедуру, поскольку это описательный, а не
выполняемый оператор SQL.
1.1.2. Определение процедуры
Процедуры в модуле SQL определяются следующими
синтаксическими конструкциями:
::=
PROCEDURE
...;
;
::=
|
::= SQLCODE
::=
|
|
|
|
|
|
|
|
|
|
Имена всех процедур в одном модуле должны быть
различны. Любое имя параметра, содержащегося в операторе SQL процедуры, должно
быть специфицировано в разделе объявления параметров. Число фактических
параметров при вызове процедуры должно совпадать с числом формальных
параметров, указанных при ее объявлении. Список формальных параметров каждой
процедуры должен содержать ровно один параметр SQLCODE (код ответа процедуры;
возможные значения кодов ответа стандартизованы, но некоторые из них
определяются в реализации).
1.2. Встроенный SQL
Поскольку в стандарте SQL/89 не специфицированы (даже
в приложениях) правила встраивания SQL в язык Си, мы приведем только общие
синтаксические правила встраивания, используемые для любого языка. Это поможет
оценить "степень стандартности" конкретной реализации.
::=
{
|
| }
[]
::= EXEC SQL
::= END EXEC | ;
::=
(...]
::=
BEGIN DECLARE SECTION []
::=
END DECLARE SECTION []
::= :
::=
WHENEVER
::= SQLERROR | NOT FOUND
::= CONTINUE |
::= { GOTO | GO TO }
::= : |
Встраиваемые операторы SQL, включая объявления
курсора, а также разделы объявления исключительных ситуаций и переменных
основной программы, должны быть окружены скобками EXEC SQL и END EXEC.
Объявление курсора должно встречаться текстуально раньше любого оператора,
ссылающегося на этот курсор. Все переменные основной программы, используемые во
встроенных операторах SQL, должны быть объявлены в текстуально предшествующем
этому оператору разделе объявления переменных основной программы. При этом
синтаксис объявления переменной соответствует синтаксису основного языка
программирования, но имени переменной предшествует двоеточие.
Механизм обработки исключительных ситуаций в SQL/89
исключительно прост (можно сказать, примитивен). Можно задавать реакцию на
возникновение двух видов условий: SQLERROR - это условие появления в переменной
SQLCODE после выполнения встроенного оператора отрицательного значения; NOT
FOUND - условие появления в SQLCODE значения +100 (этот код означает исчерпание
курсора). Реакция может состоять в выполнении безусловного перехода на метку
основной программы (действие GO TO), или отсутствовать (действие CONTINUE).
Срабатывает тот оператор определения реакции на исключительную ситуацию,
который текстуально ближе от начала программы к данному оператору SQL.
Заметим, что во многих реализациях поддерживается два
вида кодов ответа при выполнении операторов SQL (встроенных или взятых из
модуля): через переменную SQLCODE с кодами ответа, представляемыми целыми
числами и через переменную SQLSTATE с кодами ответа, кодируемыми десятичными
числами, представленными в текстовой форме. Имеется тенденция к переходу на
использование только механизма SQLSTATE, но в стандартных реализациях должен
поддерживаться механизм SQLCODE.
2. Набор операторов манипулирования данными
В стандарте SQL/89 определен очень ограниченный набор
операторов манипулирования данными. Их можно классифицировать на группы
операторов, связанных с курсором; одиночных операторов манипулирования данными;
и операторов завершения транзакции. Все эти операторы можно использовать как в
модулях SQL, так и во встроенном SQL. Заметим, что в SQL/89 не определен набор
операторов интерактивного SQL.
2.1. Операторы, связанные с
курсором
Операторы этой группы объединяет то, что все они
работают с некоторым курсором, объявление которого должно содержаться в том же
модуле или программе со встроенным SQL.
Оператор объявления курсора
Синтаксис объявления курсора выглядит следующим
образом:
::=
DECLARE
CURSOR FOR
::=
[...]
::=
| UNION [ALL]
::= |
()
::=
ORDER BY
[{,}...]
::=
{ | }
[ASC | DESC]
В объявлении курсора могут задаваться запросы наиболее
общего вида с возможностью выполнения операции UNION и сортировкой конечного
результата. Этот оператор не является выполняемым, он только связывает имя
курсора со спецификацией курсора.
Оператор открытия курсора
Оператор описывается следующим синтаксическим
правилом:
::= OPEN
В реализациях встроенного SQL обычно требуется, чтобы
объявление курсора текстуально предшествовало оператору открытия курсора.
Оператор открытия курсора должен быть первым в серии выполняемых операторов,
связанных с заданным курсором. При выполнении этого оператора производится
подготовка курсора к работе над ним. В частности, в этот момент производится
связывание спецификации курсора со значениями переменных основного языка в
случае встроенного SQL или параметров в случае модуля.
В большинстве реализаций в случае встроенного SQL
именно выполнение оператора открытия курсора приводит к компиляции спецификации
курсора.
Следующие операторы можно выполнять в произвольном
порядке над открытым курсором.
Оператор чтения очередной строки курсора
Синтаксис оператора чтения следующий:
::=
FETCH
INTO
::=
[{,}...]
В операторе чтения указывается имя курсора и
обязательный раздел INTO, содержащий список спецификаций назначения (список
имен переменных основной программы в случае встроенного SQL или имен
"выходных" параметров в случае модуля SQL). Число и типы данных в
списке назначений должны совпадать с числом и типами данных списка выборки
спецификации курсора.
Любой открытый курсор всегда имеет позицию: он может
быть установлен перед некоторой строкой результирующей таблицы (перед первой
строкой сразу после открытия курсора), на некоторую строку результата или за
последней строкой результата.
Если таблица, на которую указывает курсор, является
пустой, или курсор позиционирован на последнюю строку или за ней, то при
выполнении оператора чтения курсор устанавливается в позицию после последней
строки, параметру SQLCODE присваивается значение 100, никакие значения не
присваиваются целям, идентифицированным в разделе INTO.
Если курсор установлен в позицию перед строкой, то он
устанавливается на эту строку, и значения этой строки присваиваются
соответствующим целям.
Если курсор установлен на строку r, отличную от
последней строки, то курсор устанавливается на строку, непосредственно следующую
за строкой r, и значения из этой следующей строки присваиваются соответствующим
целям.
В SQL89 чтобы параметризовать курсор неопределенным
значением или узнать, что выбранное из очередной строки значение является
неопределенным, используются индикаторные параметры и переменные. Если
известно, что значение, передаваемое из основной программы СУБД или принимаемое
основной программой от СУБД, может быть неопределенным, и этот факт интересует
прикладного программиста, то спецификация параметра или переменной в операторе
SQL имеет вид: [INDICATOR] при
спецификации параметра, и [INDICATOR] при спецификации переменной. Отрицательное значение
индикаторного параметра или индикаторной переменной (они должны быть целого
типа) соответствует неопределенному значению параметра или переменной.
Оператор позиционного удаления
Синтаксис этого оператора следующий:
::=
DELETE FROM
WHERE CURRENT
OF
Если указанный в операторе курсор открыт и установлен
на некоторую строку, и курсор определяет изменяемую таблицу, то текущая строка
курсора удаляется, а он позиционируется перед следующей строкой. Таблица,
указанная в разделе FROM оператора DELETE, должна быть таблицей, указанной в
самом внешнем разделе FROM спецификации курсора.
Оператор позиционной модификации
Оператор описывается следующими синтаксическими
правилами:
::=
UPDATE
SET
[{,}...]
WHERE CURRENT
OF
::=
=
{ | NULL }
::=
Если указанный в операторе курсор открыт и установлен
на некоторую строку, и курсор определяет изменяемую таблицу, то текущая строка
курсора модифицируется в соответствии с разделом SET. Позиция курсора не
изменяется. Таблица, указанная в разделе FROM оператора DELETE, должна быть
таблицей, указанной в самом внешнем разделе FROM спецификации курсора.
Оператор закрытия курсора
Синтаксис этого оператора следующий:
::= CLOSE
Если к моменту выполнения этого оператора курсор
находился в открытом состоянии, то оператор переводит курсор в закрытое
состояние. После этого над курсором возможно выполнение только оператора OPEN.
2.2. Одиночные операторы
манипулирования данными
Каждый из операторов этой группы является абсолютно
независимым от какого бы то ни было другого оператора.
Оператор выборки
Синтаксис этого оператора выглядит так:
::=
SELECT [ALL |
DISTINCT]
INTO
::=
[{,}...]
Результатом одиночного оператора выборки является
таблица, состоящая не более чем из одной строки, список целей специфицируется в
самом операторе.
Оператор поискового удаления
Оператор описывается следующим синтаксическим
правилом:
::=
DELETE FROM
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. При выполнении
любого из этих операторов производится принудительное закрытие всех курсоров,
открытых к моменту выполнения оператора завершения транзакции.
3. Динамический SQL в ORACLE V.6
Описанный в стандарте SQL/89 набор операторов SQL
предназначен для встраивания в программу на обычном языке программирования.
Поэтому в этом наборе перемешаны операторы "истинного" реляционного
языка запросов (например, оператор удаления из таблицы части строк,
удовлетворяющих заданному значению) и операторы работы с курсорами,
позволяющими обеспечить построчный доступ к таблице-результату запроса.
Понятно, что в диалоговом режиме набор операторов SQL
и их синтаксис должен быть несколько другим. Весь вопрос состоит в том, как
реализовывать такую диалоговую программу. Правила встраивания стандартного SQL
в программу на обычном языке программирования предусматривают, что вся
информация, касающаяся операторов SQL, известна в статике (за исключением
значений переменных, используемых в качестве констант в операторах SQL). Не
предусмотрены стандартные средства компиляции с последующим выполнением
операторов, которые становятся известными только во время выполнения (например,
вводятся с терминала). Поэтому, опираясь только на стандарт, невозможно
реализовать диалоговый монитор взаимодействия с БД на языке SQL или другую
прикладную программу, в которой текст операторов SQL возникает во время
выполнения, т.е. фактически так или иначе стандарт необходимо расширять.
Один из возможных путей расширения состоит в
использовании специальной группы операторов, обеспечивающих динамическую
компиляцию (во время выполнения прикладной программы) базового подмножества
операторов SQL и поддерживающих их корректное выполнение. Некоторый набор таких
операторов входил в диалект SQL, реализованный в System R, несколько отличный
набор входит в реализацию Oracle V.6 и наконец, в новом стандарте SQL/92
появилась стандартная версия динамического SQL.
Поскольку в СУБД Oracle средства динамического SQL
реализованы уже сравнительно давно, имеет смысл рассмотреть сначала их, чтобы
иметь основу для сравнения с SQL/92.
В дополнительный набор операторов, поддерживающих
динамическую компиляцию базовых операторов SQL, входят операторы: PREPARE,
DESCRIBE и EXECUTE.
3.1. Оператор подготовки
Оператор PREPARE имеет синтаксис:
::=
PREPARE
FROM
::=
Во время выполнения оператора PREPARE символьная
строка, содержащаяся в host-string-variable, передается компилятору SQL,
который обрабатывает ее почти таким же образом, как если бы получил в статике.
Построенный при выполнении оператора PREPARE код остается действующим до конца
транзакции или до повторного выполнения данного оператора PREPARE в пределах
этой же транзакции.
В отличие от статически подставляемых в программу на
обычном языке программирования операторов SQL, в которых связь с переменными
включающей программы производится по именам (т.е. в соответствии со стандартом
во встроенном операторе SQL могут употребляться просто имена переменных
включающей программы), динамическая природа операторов, подготавливаемых с
помощью оператора PREPARE, заставляет рассматривать эти имена как имена
формальных параметров. Соответствие этих формальных параметров адресам
переменных включающей программы устанавливается позиционно во время выполнения
подготовленного оператора.
3.2. Оператор получения
описания подготовленного оператора
Оператор DESCRIBE предназначен для того, чтобы
определить тип ранее подготовленного оператора, узнать количество и типы
формальных параметров (если они есть) и количество и типы столбцов
результирующей таблицы, если подготовленный оператор является оператором
выборки (SELECT).
Действие оператора DESСRIBE состоит в том, что в
указанную область памяти прикладной программы (структура этой области
фиксирована и известна пользователям) помещается информация, характеризующая
ранее подготовленный оператор с заданным именем.
3.3. Оператор выполнения
подготовленного оператора
Оператор EXECUTE служит для выполнения ранее
подготовленного оператора SQL типа 'N' (не требующего применения курсора) или
для совмещенной подготовки и выполнения такого оператора. Синтаксис оператора
EXECUTE:
::=
EXECUTE
{ [USING
]
( IMMEDIATE
}
Для выполнения подготовленного оператора служит первый
вариант оператора EXECUTE. В этом случае должен задавать
имя, употреблявшееся ранее в операторе PREPARE. Если в подготовленном операторе
присутствуют формальные параметры, то в операторе EXECUTE должен задаваться
список фактических параметров . Число и типы фактических
параметров должны соответствовать числу и типам формальных параметров
подготовленного оператора.
Второй вариант оператора EXECUTE предназначен в Oracle
для совмещенной подготовки и выполнения оператора SQL типа 'N'. В этом случае
параметром оператора EXECUTE является строка, которая должна содержать текст
оператора SQL (эту строку разрешается также задавать литерально). Запрещается
использование в этом операторе переменных включающей программы (формальных
параметров).
3.4. Работа с динамическими
операторами SQL через курсоры
Для использования таких операторов используется
расширение механизма курсоров стандарта SQL. Во-первых, при определении курсора
можно указывать не только литеральную спецификацию курсора, но и имя оператора,
вводимое с помощью оператора PREPARE (в этом случае оператор PREPARE должен
текстуально находиться выше оператора DECLARE). Тем самым полный синтаксис
оператора DECLARE становится следующим:
::=
DECLARE
CURSOR
FOR {
| }
Далее, поскольку для такого курсора в статике
неизвестна информация о входных и выходных переменных включающей программы, то
используется другая форма операторов OPEN и FETCH.
Полный синтаксис этих операторов становится следующим:
::=
OPEN
[USING {
| DESCRIPTOR }]
::=
FETCH
{ INTO
( USING
( USING
DESCRIPTOR }
Как видно, предлагается два способа задания
фактических входных и выходных параметров: прямое с указанием в операторах OPEN
и/или FETCH списков имен переменных включающей программы и косвенное, когда
число параметров и их адреса сообщаются через дополнительную структуру-дескриптор.
Первый способ предлагается использовать для работы с
операторами выборки, для которых фиксирован набор формальных входных и выходных
параметров. Точнее говоря, что касается выходных параметров, должны быть
фиксированы число и типы элементов списка выборки.
Второй способ работы с динамически откомпилированными
операторами, требующими использования курсоров, состоит в использовании
дескрипторов динамически формируемых списков параметров. В этом случае вся
ответственность за соответствие типов фактических и формальных параметров
ложится на программиста. В результате ошибки при формировании такого списка, в
частности, может быть испорчена память Си-программы.
Заключение
Будучи стандартным языком доступа к реляционной базе
данных, SQL оказывает большое влияние на все сегменты компьютерного рынка.
Компания IBM приняла SQL в качестве унифицирующей технологии баз данных для
линии своих продуктов. Все поставщики мини-компьютеров предлагают реляционные
базы данных; такие базы данных доминируют и на рынке компьютерных систем,
работающих под управлением UNIX. По мере того как отдельные персональные
компьютеры уступают дорогу сетям с архитектурой клиент/сервер, SQL видоизменяет
рынок баз данных для персональных компьютеров. SQL применяется даже при оперативной
обработке транзакций, опровергая бытовавшее ранее мнение, что из-за низкого
быстродействия реляционные базы данных никогда не смогут использоваться в
приложениях для обработки транзакций.
Со временем язык SQL развивается и дополняется новыми
возможностями и вместе с этим немного усложняется. Вместе с этим увеличивается
число пользователей, которым необходимо
облегчить работу с БД.
Благодаря прикладному программированию облегчается
работа с БД. Пользователи получают удобный интерфейс управления.
Список литературы
Атре Ш. Структурный подход к организации баз данных. –
М.: Финансы и статистика, 1983. – 320 с
Бойко В.В., Савинков В.М. Проектирование баз данных
информационных систем. – М.: Финансы и статистика, 1989. – 351 с.
Дейт К. Руководство по реляционной СУБД DB2. - М.:
Финансы и статистика, 1988. - 320 с.
Джексон Г. Проектирование реляционных баз данных для
использования с микроЭВМ. -М.: Мир, 1991. – 252 с.
Кириллов В.В. Структуризованный
язык запросов (SQL). – СПб.: ИТМО, 1994. – 80 с.
Кириллов В.В.: Учебное пособие. - СПб.: ИТМО, 1994. -
90 с.
Материалы сайта «Сервер информационных технологий»
WEB: www.citforum.ru
Материалы сайта «SQL.ru» WEB: www.SQL.ru
Мейер М. Теория реляционных баз данных. -М.: Мир,
1987. - 608 с.
Хаббард Дж. Автоматизированное проектирование баз
данных. – М.: Мир, 1984. – 294 с.
Для подготовки данной работы были использованы
материалы с сайта http://ping.alfaspace.net