Библиотека сайта rus-linux.net
Глава 25
ИСПОЛЬЗОВАНИЕ SQL
С ДРУГИМ ЯЗЫКОМ
( ВЛОЖЕННЫЙ SQL )
В ЭТОЙ ГЛАВЕ ВЫ УЗНАЕТЕ
КАК SQL ИСПОЛЬЗУЕТСЯ для расширения программ написанных на других
языках. Хотя непроцедурность языка SQL делает его очень мощным,
в то же время это накладывает на него большое число ограничений.
Чтобы преодолеть эти ограничения,
вы можете включать SQL в программы написанные на том или другом
процедурном языке( имеющем определенный алгоритм). Для наших примеров,
мы выбрали Паскаль, считая что этот язык наиболее прост в понимании
для начинающих, и еще потому, что Паскаль - один из языков для
которых ANSI имеет полуофициальный стандарт.
ЧТО ТАКОЕ - ВЛОЖЕНИЕ
SQL
Чтобы вложить SQL в другой
язык, вы должны использовать пакет программ который бы обеспечивал
поддержку вложения SQL в этот язык и конечно же, поддержку самого
языка. Естественно, вы должны быть знакомы с языком который вы
используете. Главным образом, вы будете использовать команды SQL
для работы в таблицах базы данных, передачи результатов вывода
в программу и получение ввода из программы в которую они вкладываются,
обобщенно ссылаясь к главной программе ( которая может или не
может принимать их из диалога или посылать обратно в диалог пользователя
и программы ).
ЗАЧЕМ ВКЛАДЫВАТЬ
SQL?
Хотя и мы потратили некоторое
время на то чтобы показать что умеет делать SQL , но если вы -
опытный программист, вы вероятно отметили, что сам по себе, он
не очень полезен при написании программ.
Самое очевидное ограничение
- это то, что в то время как SQL может сразу выполнить пакет команды,
интерактивный SQL в основном выполняет по одной команде в каждый
момент времени.
Типы логических конструкций
типа if ... then ( "если ... то" ),
for ... do ( "для ... выполнить") и
while ... repeat( "пока ... повторять" )
- используемых для структур большинства компьютерных программ,
здесь отсутствуют, так что вы не сможете принять решение - выполнять
ли, как выполнять, или как долго выполнять одно действие в результате
другого действия. Кроме того, интерактивный SQL не может делать
многого со значениями, кроме ввода их в таблицу, размещения или
распределения их с помощью запросов, и конечно вывода их на какое-то
устройство.
Более традиционные языки,
однако, сильны именно в этих областях. Они разработаны так чтобы
программист мог начинать обработку данных, и основываясь на ее
результатах, решать, делать ли это действие или другое, или же
повторять действие до тех пока не встретится некоторое условие,
создавая логические маршруты и циклы. Значения сохраняются в переменных,
которые могут использоваться и изменяться с помощью любого числа
команд. Это дает вам возможность указывать пользователям на ввод
или вывод этих команд из файла, и возможность форматировать вывод
сложными способами ( например, преобразовывать числовых данных
в диаграммы ).
Цель вложенного SQL состоит
в том, чтобы объединить эти возможности, позволяющие вам создавать
сложные процедурные программы которые адресуют базу данных посредством
SQL - позволяя вам устранить сложные действия в таблицах на процедурном
языке который не ориентирован на такую структуру данных, в тоже
время поддерживая структурную строгость процедурного языка.
КАК ДЕЛАЮТСЯ ВЛОЖЕНИЯ
SQL.
Команды SQL помещаются
в исходный текст главной программы, которой предшествует фраза
- EXEC SQL ( EXECute SQL ). Далее устанавливаются
некоторые команды которые являются специальными для вложенной
формы SQL, и которые будут представлены в этой главе. Строго говоря,
стандарт ANSI не поддерживает вложенный SQL как таковой. Он поддерживает
понятие, называемое - модуль, который более точно, является
вызываемым набором процедур SQL, а не вложением в другой язык.
Официальное определение синтаксиса вложения SQL, будет включать
расширение официального синтаксиса каждого языка в который может
вкладываться SQL, что весьма долгая и неблагодарная задача, которую
ANSI избегает. Однако, ANSI обеспечивает четыре приложения ( не
являющиеся частью стандарта ), которые определяют синтаксис вложения
SQL для четырех языков: КОБОЛ, ПАСКАЛЬ, ФОРТРАН, и ПЛ/1. Язык
C - также широко поддерживается как и другие языки.
Когда вы вставляете команды
SQL в текст программы написанной на другом языке, вы должны выполнить
предкомпиляцию прежде, чем вы окончательно ее скомпилируете.
Программа называемая
прекомпилятором ( или препроцессором ), будет просматривать
текст вашей программы и преобразовывать команды SQL, в форму удобную
для использования базовым языком.
Затем вы используете обычный
транслятор чтобы преобразовывать программу из исходного текста
в выполняемый код.
Согласно подходу к модульному
языку определенному ANSI, основная программа вызывает процедуры
SQL. Процедуры выбирают параметры из главной программы, и возвращают
уже обработанные значения, обратно в основную программу. Модуль
может содержать любое число процедур, каждая из которых состоит
из одиночной команды SQL. Идея в том, чтобы процедуры могли работать
тем же самым способом что и процедуры на языке в который они были
вложены( хотя модуль еще должен идентифицировать базовый язык
из-за различий в типах данных различных языков ).
Реализации могут удовлетворить
стандарту, выполнив вложение SQL таким способом, как если бы модули
уже были точно определены. Для этой цели прекомпилятор будет создавать
модуль, называемый модулем доступа. Только один модуль,
содержащий любое число процедур SQL, может существовать для данной
программы. Размещение операторов SQL непосредственно в главном
коде, происходит более просто и более практично чем непосредственно
создание самих модулей.
Каждая из программ использующих
вложение SQL, связана с ID доступа, во время ее выполнения.
ID доступа, связанный с программой, должен иметь все привилегии
чтобы выполнять операции SQL, выполняемые в программе.
Вообще то, вложенная программа
SQL регистрируется в базе данных, также как и пользователь, выполняющий
программу. Более подробно, это определяет проектировщик, но вероятно
было бы неплохо для включить в вашу программу команду CONNECT
или ей подобную.
ИСПОЛЬЗОВАНИЕ
ПЕРЕМЕННЫХ
======= ОСНОВНОГО
ЯЗЫКА В SQL =======
Основной способ которым
SQL и части базового языка ваших программ будут связываться друг
с другом - это с помощью значений переменных.
Естественно, что разные
языки распознают различные типы данных для переменных. ANSI определяет
эквиваленты SQL для четыре базовых языков - ПЛ/1, Паскаль, КОБОЛ,
и ФОРТРАН.
Эквиваленты для других
языков - определяет проектировщик.
Имейте в виду, что типы,
такие как DATE, не распознаются ANSI; и следовательно никаких
эквивалентных типов данных для базовых языков, не существуют в
стандарте ANSI. Более сложные типы данных базового языка, такие
как матрицы, не имеют эквивалентов в SQL. Вы можете использовать
переменные из главной программы во вложенных операторах SQL везде,
где вы будете использовать выражения значений. ( SQL, используемый
в этой главе, будет пониматься как к вложенный SQL, до тех пор
пока это не будет оговорено особо. )
Текущим значением переменной,
может быть значение, используемое в команде. Главные переменные
должны -
* быть обьявленными в
SQL DECLARE SESSION ( РАЗДЕЛ ОБЪЯВЛЕНИЙ ) который
будет описан далее.
* иметь совместимый тип
данных с их функциями в команде SQL ( например, числовой тип
если они вставляется в числовое поле )
* быть назначеными значению
во время их использования в команде SQL, если команда SQL самостоятельно
не может сделать назначение.
* предшествовать двоеточию
(:) когда они упоминаются в команде SQL
Так как главные переменные
отличаются от имен столбцов SQL наличием у них двоеточия, вы можете
использовать переменные с теми же самыми именами что и ваши столбцы,
если это конечно нужно.
Предположим что вы имеете
четыре переменных в вашей программе, с именами: id_num, salesperson,
loc, и comm. Они содержат значения которые вы хотите
вставить в таблицу Продавцов. Вы могли бы вложить следующую команду
SQL в вашу программу:
EXEC SQL INSERT
INTO Salespeople
VALUES ( :id_num,
:salesperson, :loc, :comm)
Текущие значения этих
переменных будут помещены в таблицу. Как вы можете видеть, переменная
comm имеет то же самое имя что и столбец в который это значение
вкладывается. Обратите внимание, что точка с запятой в конце команды
отсутствует. Этопотому, что соответствующее завершение для вложенной
команды SQL зависит от языка для которого делается вложение.
Для Паскаля и PL/1, это
будет точка с запятой; для КОБОЛА, слово END-EXEC ; и для
ФОРТРАНА не будет никакого завершения.
В других языках это зависит
от реализации, и поэтому мы договоримся что будем использовать
точку с запятой (в этой книге) всегда, чтобы не противоречить
интерактивному SQL и Паскалю. Паскаль завершает вложенный SQL
и собственные команды одинаково - точкой с запятой.
Способ сделать команду
полностью такой как описана выше, состоит в том, чтобы включать
ее в цикл и повторять ее, с различными значениями переменных,
как например показано в следующем примере:
while not end-ot-file
(input) do
begin
readln (id_num,
salesperson, loc, comm);
EXEC SOL
INSERT INTO Salespeople
VALUES
(:id_num, :salesperson, :loc, :comm);
end;
Фрагмент программы на
ПАСКАЛЕ, определяет цикл, который будет считывать значения из
файла, сохранять их в четырех проименованных переменных, сохранять
значения этих переменных в таблице Продавцов, и затем считывать
следующие четыре значения, повторяя этот процесс до тех пор пока
весь входной файл не прочитается. Считается, что каждый набор
значений завершается возвратом каретки ( для незнакомых с Паскалем,
функция readln считывает вводимую информацию и переходит
на следующую строку источника этой информации). Это дает вам простой
способ передать данные из текстового файла в реляционную структуру.
Конечно, вы можете
сначала обработать данные любыми возможными способами на вашем
главном языке, например для исключения всех комиссионных ниже
значения .12
while not
end-ot-file (input) do
begin
readln
(id_num, salesperson, loc, comm);
if comm
> = .12 then
EXEC SQL
INSERT INTO Salespeople
VALUES
(:id_num, :salesperson, :loc, :comm);
end;
Только строки которые
встретят условие comm >= .12 будут вставлены в вывод. Это показывает
что можно использовать и циклы и условия как нормальные для главного
языка.
ОБЪЯВЛЕНИЕ ПЕРЕМЕННЫХ
Все переменные на которые
имеется ссылка в предложениях SQL, должны сначала быть обьявлены
в SQL DECLARE SECTION ( РАЗДЕЛе ОБЪЯВЛЕНИЙ ), использующем
обычный синтаксис главного языка. Вы можете иметь любое число
таких разделов в программе, и они могут размещаться где-нибудь
в коде перед используемой переменной, подчиненной ограничениям
определенным в соответствии с главным языком.
Раздел объявлений
должен начинать и кончаться вложенными командами SQL - BEGIN
DECLARE SECTION ( Начало Раздела Объявлений ) и END
DECLARE SECTION ( Конец Раздела Объявлений ), которым
предшествует, как обычно EXEC SQL (Выполнить).
Чтобы обьявить переменные
используемые в предыдущем примере, вы можете ввести следующее:
EXEC SQL
BEGIN DECLARE SECTION;
Var
id-num:
integer;
Salesperson:
packed array (1 . .10) ot char;
loc:
packed array (1. .10) ot char;
comm:
real;
EXEC SQL
END DECLARE SECTION;
Для незнакомых с ПАСКАЛем,
Var - это заголовок который предшествует ряду обьявляемых
переменных, и упакованным ( или распакованным ) массивам являющимися
серией фиксированных переменных значений различаемых с помощью
номеров( например, третий символ loc будет loc (3) ).
Использование точки с
запятой после каждой переменной указывает на то что это - Паскаль,
а не SQL.
ИЗВЛЕЧЕНИЕ ЗНАЧЕНИЙ
ПЕРЕМЕННЫХ
Кроме помещения значений
переменных в таблицы используя команды SQL, вы можете использовать
SQL чтобы получать значения для этих переменных. Один из способов
делать это - с помощью разновидности команды SELECT которая содержит
предложение INTO. Давайте вернемся к нашему предыдущему
примеру и переместим строку Peel из таблицы Продавцов в наши переменные
главного языка.
EXEC SQL
SELECT snum, sname, city, comm
INTO
:id_num, :salesperson, :loc, :comm
FROM
Salespeople
Выбранные значения помещаются
в переменные с упорядоченными именами указанными в предложении
INTO. Разумееется, переменные с именами указанными в предложении
INTO должны иметь соответствующий тип чтобы принять эти значения,
и должна быть своя переменная для каждого выбранного столбца.
Если не учитывать присутствие
предложения INTO, то этот запрос - похож на любой другой. Однако,
предложение INTO добавляет значительное ограничение к запросу.
Запрос должен извлекать не более одной строки. Если он извлекает
много строк, все они не могут быть вставлены одновременно в одну
и ту же переменную. Команда естественно потерпит неудачу. По этой
причине, SELECT INTO должно использоваться только при следующих
условиях:
* когда вы используете
предикат проверяющий значения, которое как вы знаете, могут
быть уникальным, как в этом примере. Значения которые, как вы
знаете, могут быть уникальными - это те значения которые имеют
принудительное ограничение уникальности или уникальный индекс,
как это говорилось в Главах 17 и 18.
* когда вы используете
одну или более агрегатных функций и не используете GROUP BY.
* когда вы используете
SELECT DISTINCT во внешнем ключе с пре дикатом ссылающимся на
единственное значение родительского ключа (обеспечивая вашей
системе предписание справочной целостность), как в следующем
примере:
EXEC SQL
SELECT DISTINCT snum
INTO :salesnum
FROM Customers
WHERE
snum =
(SELECT
snum
FROM Salespeople
WHERE sname = 'Motika');
Предпологалось что Salespeople.sname
и Salespeople.snum - это соответственно, уникальный
и первичный ключи этой таблицы, а Customers.snum - это внешний
ключ ссылающийся на Salespeople.snum, и вы предполагали что этот
запрос произведет единственную строку.
Имеются другие случаи,
когда вы можете знаете, что запрос должен произвести единственную
строку вывода, но они мало известны и, в большинстве случаев,
вы основываетесь на том что ваши данные имеют целостность, которая
не может быть предписана с помощью ограничений. Не полагайтесь
на это! Вы создаете программу которая, вероятно, будет использоваться
в течение некоторого времени, и лучше всего проиграть ее чтобы
быть гарантированным в будущем от возможных отказов. Во всяком
случае, нет необходимости группировать запросы которые производут
одиночные строки, поскольку SELECT INTO - используется только
для удобства.
Как вы увидите, вы можете
использовать запросы выводящие многочисленные строки, используя
курсор.
КУРСОР
Одна из сильных качеств
SQL - это способность функционировать на всех строках таблицы,
чтобы встретить определенное условие как блок запись, не зная
сколько таких строк там может быть. Если десять строк удовлетворяют
предикату, то запрос может вывести все десять строк. Если десять
миллионов строк определены, все десять миллионов строк будут выведены.
Это несколько затруднительно,
когда вы попробуете связать это с другими языками. Как вы сможете
назначать вывод запроса для переменных когда вы не знаете как
велик будет вывод ? Решение состоит в том, чтобы использовать
то, что называется - курсором.
Вы вероятно знакомы с
курсором, как с мигающей черточкой, которая отмечает вашу позицию
на экране компьютера. Вы можете рассматривать SQL курсор как устройство,
которое аналогично этому, отмечает ваше место в выводе запроса,
хотя аналогия не полная.
Курсор - это
вид переменной, которая связана с запросом. Значением этой переменной
может быть каждая строка, которая выводится при запросе. Подобно
главным переменным, курсоры должны быть обьявлены прежде, чем
они будут использованы. Это делается командой DECLARE CURSOR,
следующим образом:
EXEC
SQL DECLARE CURSOR Londonsales FOR
SELECT
*
FROM
Salespeople
WHERE
city = 'London';
Запрос не выполнится немедленно;
он - только определяется. Курсор немного напоминает представление,
в котором курсор содержит запрос, а содержание курсора - напоминает
любой вывод запроса, каждый раз когда курсор становится открытым.
Однако, в отличие от базовых таблиц или представлений, строки
курсора упорядочены: имеются первая, вторая...... и последняя
строка курсора. Этот порядок может быть произвольным с явным управлением
с помощью предложения ORDER BY в запросе, или же по умолчанию
следовать какому-то упорядочению определяемому инструментально-определяемой
схемой.
Когда вы находите точку
в вашей программе в которой вы хотите выполнить запрос, вы открываете
курсор с помощью следующей команды:
EXEC SQL OPEN
CURSOR Londonsales;
Значения в курсоре могут
быть получены, когда вы выполняете именно эту команду, но не предыдущую
команду DECLARE и не последующую команду FETСH. Затем, вы используете
команду FETCH чтобы извлечь вывод из этого запроса, по одной строке
в каждый момент времени.
EXEC SQL FETCH
Londonsales INTO :id_num,
:salesperson,
:loc, :comm;
Это выражение переместит
значения из первой выбраной строки, в переменные. Другая команда
FETCH выводет следующий набор значений. Идея состоит в
том, чтобы поместить команду FETCH внутрь цикла, так чтобы выбрав
строку, вы могли переместив набор значений из этой строки в переменные,
возвращались обратно в цикл чтобы переместить следующий набор
значений в те же самые переменные.
Например, возможно вам
нужно чтобы вывод выдавался по одной строке, спрашивая каждый
раз у пользователя, хочет ли он продолжить чтобы увидеть следующую
строку
Look_at_more:=
True;
EXEC SQL
OPEN CURSOR Londonsales;
while
Look_at_more do
begin
EXEC
SQL FETCH Londonsales
INTO
:id_num, :Salesperson, :loc, :comm;
writeln
(id_num, Salesperson, loc, comm);
writeln
('Do you want to see more data? (Y/N)');
readln
(response);
it response
= 'N' then Look_at_more: = False
end;
EXEC SQL
CLOSE CURSOR Londonsales;
В Паскале, знак :
= означает - " является назначенным значением из ",
в то время как = еще имеет обычное значение " равно
". Функция writeln записывает ее вывод, и затем переходит
к новой строке. Одиночные кавычки вокруг символьных значений во
втором writeln и в предложении if ... then - обычны для
Паскаля, что случается при дубликатах в SQL.
В результате этого
фрагмента, Булева переменная с именем Look_at_more должна
быть установлена в состояние верно, открыт курсор, и введен цикл.
Внутри цикла, строка выбирается из курсора и выводится на экран.
У пользователя спрашивают, хочет ли он видеть следующую строку.
Пока он не ответил N ( Нет ), цикл повторяется, и следующая
строка значений будет выбрана.
Хотя переменные Look_at_more
и ответ должны быть обьявлены как Булева переменная и символьная(char)
переменная, соответственно, в разделе обьявлений переменных в
Паскаля, они не должны быть включены в раздел обьявлений SQL,
потому что они не используются в командах SQL.
Как вы можете видеть,
двоеточия перед именами переменных не используются для не-SQL
операторов. Далее обратите внимание, что имеется оператор CLOSE
CURSOR соответствующий оператору OPEN CURSOR. Он, как
вы поняли, освобождает курсор значений, поэтому запрос будет нужно
выполнить повторно с оператором OPEN CURSOR, прежде чем перейти
в выбору следующих значений.
Это необязательно для
тех строк которые были выбраны запросом после закрытия курсора,
хотя это и обычная процедура.
Пока курсор закрыт, SQL
не следит за тем, какие строки были выбраны. Если вы открываете
курсор снова, запрос повторно выполняется с этой точки, и вы начинаете
все сначала.
Этот пример не обеспечивает
автоматический выхода из цикла, когда все строки уже будут выбраны.
Когда у FETCH нет больше строк которые надо извлекать, он просто
не меняет значений в переменных предложения INTO. Следовательно,
если данные исчерпались, эти переменные будут неоднократно выводиться
с идентичными значениями, до тех пор пока пользователь не завершит
цикл, введя ответ - N.
============== SQL
КОДЫ ===================
Хорошо было бы знать,
когда данные будут исчерпаны, так чтобы можно было сообщить об
этом пользователю и цикл завершился бы автоматически.
Это - даже более важно
чем например знать что команда SQL выполнена с ошибкой. Переменная
SQLCODE ( называемая еще SQLCOD в ФОРТРАНе ) предназначена
чтобы обеспечить эту функцию. Она должна быть определена как переменная
главного языка и должна иметь тип данных который в главном языке
соответствует одному из точных числовых типов SQL, как это показано
в Приложении B.
Значение SQLCODE устанавливается
каждый раз, когда выполняется
команда SQL. В основном
существуют три возможности:
1. Команда выполнилась
без ошибки, но не произвела никакого действия. Для различных
команд это выглядит по разному:
а) Для SELECT, ни
одна строка не выбрана запросом.
б) Для FETCH, последняя
строка уже была выбрана, или ни одной строки не выбрано
запросом в курсоре.
в) Для INSERT, ни
одной строки не было вставлено (подразуме вается что запрос
использовался чтобы сгенерировать значения для вставки, и был
отвергнут при попытке извлечения лю бой строки.
г) Для UPDATE и DELETE,
ни одна строка не ответила усло вию предиката, и следовательно
никаких изменений сделано в таблице не будет.
В любом случае, будет
установлен код SQLCODE = 100.
2. Команда выполнилась
нормально, не удовлетворив ни одному из выше указанных условий.
В этом случае, будет установлен код SQLCOD = 0.
3. Команда сгенерировала
ошибку. Если это случилось, изменения сделанные к базе данных
текущей транзакцией, будут восстанов лены( см. Главу 23 ). В
этом случае будет установлен код SQLCODE = некоторому отрицательному
числу, определяемому проектировщиком. Задача этого числа, идентифицировать
проблему, так точно насколько это возможно. В принципе, ваша система
должна быть снабжена подпрограммой, которая в этом случае, должна
выполниться чтобы выдать для вас информацию расшифровывающее значение
негативного числа определенного вашим проектировщиком. В этом
случае некоторое сообщение об ошибке будет выведено на экран или
записано в файл протокола, а программа в это время выполнит восстановление
изменений для текущей транзакции, отключится от базы данных и
выйдет из нее. Теперь мы можем усовершенствовать
ИСПОЛЬЗОВАНИЕ SQLCODE
ДЛЯ УПРАВЛЕНИЯ ЦИКЛАМИ
наш предыдущий пример
для выхода из цикла автоматически, при условии что курсор пуст,
все строки выбраны, или произошла ошибка:
Look_at_more:
= lhe;
EXEC SQL
OPEN CURSOR Londonsales;
while
Look_at_more
and SQLCODE
= O do
begin
EXEC
SQL FETCH London$ales
INTO :id_num, :Salesperson, :loc, :comm;
writeln
(id_num, Salesperson, loc, comm);
writeln
('Do you want to see more data? (Y/N)');
readln
(response);
If
response = 'N' then Look_at_more: = Fabe;
end;
EXEC SQL
CLOSE CURSOR Londonsales;
ПРЕДЛОЖЕНИЕ WHENEVER
Это удобно для выхода
при выполненом условии - все строки выбраны. Но если вы получили
ошибку, вы должны предпринять нечто такое, что описано для третьего
случая, выше. Для этой цели, SQL предоставляет предложение
GOTO. Фактически, SQL позволяет вам применять его достаточно
широко, так что программа может выполнить команду GOTO автоматически,
если будет произведено определенное значение SQLCODE. Вы можете
сделать это соввместно с предложением WHENEVER.
Имеется кусгок из примера
для этого случая:
EXEC SQL WHENEVER
SQLERROR GOTO Error_handler;
EXEC SQL WHENEVER
NOT FOUND CONTINUE;
SQLERROR-
это другой способ сообщить что SQLCODE < 0; а NOT FOUND
- это другой способ сообщить что SQLCODE = 100. ( Некоторые реализации
называют последний случай еще как - SQLWARNING.)
Error_handler
- это имя того места в программе в которое будет перенесено выполнение
программы если произошла ошибка ( GOTO может состоять из одного
или двух слов ). Такое место определяется любым способом соответствующим
для главного языка, например, с помощью метки в Паскале, или имени
раздела или имени параграфа в КОБОЛЕ ( в дальнейшем мы будем использовать
термин - метка ). Метка более удачно идентифицирует
стандартную процедуру распространяемую проектировщиком для включения
во все программы.
CONTINUE
не делает чего-то специального для значения SQLCODE. Оно также
является значением по умолчанию. если вы не используете команду
WHENEVER, определяющую значение SQLCODE. Однако, эти неактивные
определения дают вам возможность переключаться вперед и назад,
выполняя и не выполняя действия, в различных точках(метках) вашей
программы.
Например, если ваша программа
включает в себя несколько команд INSERT,использующих запросы,
которые реально должны производить значения, вы могли бы напечатать
специальное сообщение или сделать что-то такое, что поясняло бы,
что запросы возвращаются пустыми и никакие значения не были вставлены.
В этом случае, вы можете ввести следующее:
EXEC SQL WHENEVER
NOT FOUND GOTO No_rows;
No_rows
- это метка в некотором коде, содержащем определенное действие.
С другой стороны, если вам нужно сделать выборку в программе позже,
вы можете ввести следующее в этой точке,
EXEC SQL WHENEVER
NOT FOUND CONTINUE;
что бы выполнение выборки
повторялось до тех пор пока все строки не будут извлечены, что
является нормальной процедурой не требующей специальной обработки.
========= МОДИФИЦИРОВАНИЕ
КУРСОРОВ ========
Курсоры могут также быть
использованы, чтобы выбирать группу строк из таблицы, которые
могут быть затем модифицированы или удалены одна за другой. Это
дает вам возможность, обходить некоторые ограничения предикатов
используемых в командах UPDATE и DELETE. Вы можете ссылаться на
таблицу задействованную в предикате запроса курсора или любом
из его подзапросов, которые вы не можете выполнить в предикатах
самих этих команд. Как подчеркнуто в Главе 16, стандарт SQL отклоняет
попытку удалить всех пользователей с рейтингом ниже среднего,
в следующей форме:
EXEC
SQL DELETE FROM Customers
WHERE rating <
( SELECT AVG (rating)
FROM Customers);
Однако, вы можете получить
тот же эффект, используя запрос для выбора соответствующих строк,
запомнив их в курсоре, и выполнив DELETE с использованием курсора.
Сначала вы должны обьявить курсор:
EXEC
SQL DECLARE Belowavg CURSOR FOR
SELECT
*
FROM Customers
WHERE rating <
(SELECT AVG (rating)
FROM Customers);
Затем вы должны создать
цикл, чтобы удалить всех заказчиков выбранных курсором:
EXEC SQL
WHENEVER SQLERROR GOTO Error_handler;
EXEC SQL
OPEN CURSOR Belowavg;
while
not SOLCODE = 100 do
begin
EXEC
SOL FETCH Belowavg INTO :a, :b, :c, :d, :e;
EXEC
SOL DELETE FROM Customers
WHERE
CURRENT OF Belowavg;
end;
EXEC SOL
CLOSE CURSOR Belowavg;
Предложение WHERE CURRENT
OF означает что DELETE применяется к строке которая в настоящее
время выбрана курсором. Здесь подразумевается, что и курсор и
команда DELETE, ссылаются на одну и ту же таблицу, и следовательно,
что запрос в курсоре - это не обьединение.
Курсор должен также
быть модифицируемым. Являясь модифицируемым, курсор должен удовлетворять
тем же условиям что и представления( см. Главу 21 ). Кроме того,
ORDER BY и UNION, которые не разрешены в представлениях, в курсорах
- разрешаются, но предохраняют курсор от модифицируемости. Обратите
внимание в вышеупомянутом примере, что мы должны выбирать строки
из курсора в набор переменных, даже если мы не собирались использовать
эти переменные. Этого требует синтаксис команды FETCH. UPDATE
работает так же.
Вы можете увеличить значение
комиссионных всем продавцам, которые имеют заказчиков с оценкой=300,
следующим способом. Сначала вы обьявляете курсор:
EXEC SOL DECLARE
CURSOR High_Cust AS
SELECT *
FROM
Salespeople
WHERE
snum IN
(SELECT
snum
FROM Customers
WHERE rating = 300);
Затем вы выполняете модификации
в цикле:
EXEC SQL
OPEN CURSOR High_cust;
while SQLCODE
= 0 do
begin
EXEC
SOL FETCH High_cust
INTO
:id_num, :salesperson, :loc, :comm;
EXEC
SQL UPDATE Salespeople
SET
comm = comm + .01
WHERE
CURRENT OF High_cust;
end;
EXEC SQL
CLOSE CURSOR High_cust;
Обратите внимание: что
некоторые реализации требуют, чтобы вы указывали в определении
курсора, что курсор будет использоваться для выполнения команды
UPDATE на определенных столбцах. Это делается с помощью заключительной
фразы определения курсора - FOR UPDATE <column list>. Чтобы
обьявить курсор High_cust таким способом, так чтобы вы могли модифицировать
командой UPDATE столбец comm, вы должны ввести следующее предложение:
EXEC SQL
DECLARE CURSOR High_Cust AS
SELECT
*
FROM Salespeople
WHERE snum IN
(SELECT snum
FROM Customers
WHERE rating = 300)
Это обеспечит вас определенной
защитой от случайных модификаций, которые могут разрушить весь
порядок в базе данных.
========= ПЕРЕМЕННАЯ
INDICATOR ==========
Пустые (NULLS) значения
- это специальные маркеры определяемые самой SQL. Они не могут
помещаться в главные переменные. Попытка вставить NULL значения
в главную переменную будет некорректна, так как главные языки
не поддерживают NULL значений в SQL, по определению. Хотя результат
при попытке вставить NULL значение в главную переменную определяет
проектировщик, этот результат не должен противоречить теории базы
данных, и поэтому обязан произвести ошибку: код SQLCODE
в виде отрицательного числа, и вызвать подпрограмму управления
ошибкой. Естеcтвенно, вам нужно этого избежать. Поэтому, вы можете
выбрать NULL значения с допустимыми значениями, не приводящими
к разрушению вашей программы. Даже если программа и не разрушится,
значения в главных переменных станут неправильными, потому что
они не могут иметь NULL значений. Альтернативным методом предоставляемым
для этой ситуацией является - функция переменной indicator(указатель).
Переменная indicator -
обьявленная в разделе объявлений SQL напоминает другие переменные.
Она может иметь тип главного языка который соответствует числовому
типу в SQL. Всякий раз, когда вы выполняете операцию, которая
должна поместить NULL значение в переменную главного языка, вы
должны использовать переменную indicator, для надежности.
Вы помещаете переменную
indicator в команду SQL непосредственно после переменной главного
языка которую вы хотите защитить, без каких-либо пробелов или
запятых, хотя вы и можете, при желании, вставить слово - INDICATOR.
Переменной indicator в
команде, изначально присваивается значение 0. Однако, если производится
значение NULL , переменная indicator становится равной отрицательному
числу. Вы можете проверить значение переменной indicator, чтобы
узнать, было ли найдено значение NULL. Давайте предположим, что
поля city и comm, таблицы Продавцов, не имеют ограничения NOT
NULL, и что мы объявили в разделе обьявлений SQL, две ПАСКАЛЬевские
переменные целого типа, i_a и i_b. ( Нет ничего такого в разделе
обьявлений, что могло бы представить их как переменные indicator.
Они станут переменными indicator, когда будут использоваться как
переменные indicator. )
Имеется одна возможность:
EXEC SQL OPEN
CURSOR High_cust;
while SQLCODE
= O do
begin
EXEC SQL FETCH
High_cust
INTO :id_num,
:salesperson,
:loc:i_a,
:commINDlCATOR:i_b;
If i_a >
= O and i_b > = O then
{no NULLs produced}
EXEC
SQL UPDATE Salespeople
SET
comm = comm + .01
WHERE
CURRENT OF Hlgh_cust;
else
{one or both NULL}
begin
If i_a
< O then
writeln
('salesperson ', id_num, ' has no city');
If i_b
< O then
writeln
('salesperson ', id_num, ' has no
commission');
end;
{else}
end; {while}
EXEC SQL CLOSE
CURSOR High_cust;
Как вы видите, мы включили,
ключевое слово INDICATOR в одном случае, и исключили его в другом
случае, чтобы показать, что эффект будет одинаковым в любом случае.
Каждая строка будет выбрана, но команда UPDATE выполнится только
если NULL значения не будут обнаружены. Если будут обнаружены
NULL значения, выполнится еще одна часть программы, которая распечатает
предупреждающее сообщение, где было найдено каждое NULL значение.
Обратите внимание: переменные
indicator должны проверяться в главном языке, как указывалось
выше, а не в предложении WHERE команды SQL.
Последнее в принципе
не запрещено, но результат часто бывает непредвиденным.
ИСПОЛЬЗОВАНИЕ ПЕРЕМЕННОЙ
INDICATOR
ДЛЯ ЭМУЛЯЦИИ NULL
ЗНАЧЕНИЙ SQL
Другая возможность состоит
в том, чтобы обрабатывать переменную indicator, связывая ее с
каждой переменной главного языка, специальным способом, эмулирующим
поведение NULL значений SQL.
Всякий раз, когда вы
используете одно из этих значений в вашей программе, например
в предложении if ... then, вы можете сначала проверить связанную
переменную indicator, является ли ее значение=NULL.
Если это так, то вы обрабатываете
переменную по-другому. Например, если NULL значение было извлечено
из поля city для главной переменной city, которая связана с переменной
indicator - i_city, вы должны установить значение city равное
последовательности пробелов. Это будет необходимо, только если
вы будете распечатывать его на принтере; его значение не должно
отличаться от логики вашей программы. Естественно, i_city автоматически
установливается в отрицательное значение. Предположим, что вы
имели следующую конструкцию в вашей программе:
If sity = 'London'
then
comm:
= comm + .01
else comm:
= comm - .01
Любое значение, вводимое
в переменную city, или будет равно "London" или не будет
равно. Следовательно, в каждом случае значение комиссионных будет
либо увеличино либо уменьшено. Однако, эквивалентные команды в
SQL выполняются по разному:
EXEC SQL UPDATE
Salespeople
SET comm
= comm + .01
WHERE sity
= 'London';
и
EXEC SQL UPDATE
Salespeople
SET comm
= comm .01;
WHERE sity
< > 'London';
( Вариант на ПАСКАЛе работает
только с единственным значением, в то время как вариант на SQL
работает со всеми таблицами. ) Если значение city в варианте
на SQL будет равно значению NULL , оба предиката будут неизвестны,
и значение comm, следовательно, не будетизменено
в любом случае.
Вы можете использовать
переменную indicator чтобы сделать поведение вашего главного языка
непротиворечащим этому, с помощью создания условия, которое исключает
NULL значения:
If i_city >
= O then
begin
If city
= 'London' then
comm:
= comm + .01
else comm:
= comm - .01;
end;
{begin and end
нужны здесь только для понимания}
ПРИМЕЧАНИЕ: Последняя
строка этого примера сожержит ремарку { begin и end необходимы
только для понимания }
В более сложной программ,
вы можете захотеть установить Булеву переменную в "верно"
, чтобы указать что значение city =NULL. Затем вы можете просто
проверять эту переменную всякий раз, когда вам это необходимо.
ДРУГОЕ ИСПОЛЬЗОВАНИЕ
ПЕРЕМЕННОЙ INDICATOR
Переменная indicator также
может использоваться для назначения значения NULL. Просто добавьте
ее к имени главной переменной в команде UPDATE или INSERT тем
же способом что и в команде SELECT. Если переменная indicator
имеет отрицательное значение, значение NULL будет помещено в поле.
Например, следующая команда помещает значения NULL в поля city
и comm, таблицы Продавцов, всякий раз, когда переменные indicator
- i_a или i_b будут отрицательными; в противном случае она помещает
туда значения главных переменных:
EXEC SQL INSERT
INTO Salespeople
VALUES (:Id_num,
:salesperson, :loc:i_a, :comm:i_b);
Переменная indicator используется
также, чтобы показывать отбрасываемую строку. Это произойдет если
вы вставляете значения символов SQL в главную переменную которая
не достаточно длинна чтобы вместить все символы.
Это особая проблема с
нестандартным типами данных - VARCHAR и LONG. В этом случае, переменная
будет заполнена первыми символами строки, а последние символы
будут потеряны. Если используется переменная indicator, она будет
установлена в положительное значение, указывающее на длину отбрасываемой
части строки, позволяя таким образом вам узнать, сколько символов
было потеряно. В этом случае, Вы можете проверить с помощью просмотра
-значение переменной indicator > 0, или < 0.
================ РЕЗЮМЕ
=================
Команды SQL вкладываются
в процедурные языках, чтобы объединить силы двух подходов. Некоторые
дополнительные средства SQL необходимы, чтобы выполнить эту работу.
Вложенные команды SQL транслируемые программой, называемой прекомпилятором,
в форму пригодную для использования транслятором главного языка,
и используемые в этом главном языке, как вызовы процедуры к подпрограммам
которые создает прекомпилятор, называются - модулями доступа.ANSI
поддерживает вложение SQL в языки: ПАСКАЛЬ, ФОРТРАН, КОБОЛ, и
PL/I. Другие языки также используются, особенно Си. В попытке
кратко описать вложенный SQL, имеются наиболее важные места в
этой главе:
* Все вложенные команды
SQL начинаются словами EXEC SQL и заканчиваются способом который
зависит от используемого главного языка.
* Все главные переменные
доступные в командах SQL, должны быть обьявлены в разделе объявлений
SQL прежде, чем они будут использованы.
* Всем главным переменным
должно предшествовать двоеточие когда они используются в команде
SQL.
* Запросы могут сохранять
свой вывод непосредственно в главных переменных, используя предложение
INTO, если и только если, они выбирают единственную строку.
* Курсоры могут использоваться
для сохранения вывода запроса, и доступа к одной строке в каждый
момент времени. Курсоры бывают обьявлеными ( если определяют
запрос в котором будут содержаться), открытыми( если
выполняют запрос ),
и закрытыми ( если удаляют вывод запроса из курсора ). Если курсор
открыт, команда FETCH, используется чтобы перемещать его по очереди
к каждой строке вывода запроса.
* Курсоры являются модифицируемыми
или только-чтение. Чтобы стать модифицируемым, курсор должен
удовлетворять всем критериям которым удовлетворяет просмотр; кроме
того, он не должен использовать предложений ORDER BY или UNION,
которые в любом случае не могут использоваться просмотрами. Не
модифицируемый курсор является курсором только-чтение.
* Если курсор модифицируемый,
он может использоваться для определения, какие строки задействованы
вложенными командами UPDATE и DELETE через предложение WHERE
CURRENT OF. DELETE или UPDATE должны быть вне той таблицы к которой
курсор обращается в запросе.
* SQLCODE должен быть
обьявлен как переменная числового типа для каждой программы которая
будет использовать вложенный SQL. Его значение установливается
автоматически после выполнения каждой команды SQL.
* Если команда SQL выполнена
как обычно, но не произвела вывода или жидаемого изменения в базе
данных, SQLCODE = 100. Если команда произвела ошибку, SQLCODE
будет равняться некоторому аппаратно-определенному отрицательному
числу которое описывает ошибку. В противном случае, SQLCODE =
0.
* Предложение WHENEVER
может использоваться для определения действия которое нужно предпринять
когда SQLCODE = 100 (не найдено) или когда SQLCODE равен отрицательному
числу ( SQLERROR ). Действием может быть или переход к некоторой
определенной метке в программе ( GOTO <label> ) или отсутствие
какого-либо действия вообще( продолжить). Последнее, установлено
по умолчанию.
* Числовые переменные
могут также использоваться как переменные indicator. Переменные
indicator следуют за другим именами переменных в команде SQL,
без каких бы то ни было посторонних символов кроме (необязательного)
слова INDICATOR.
* Обычно, значение переменной
indicator = 0. Если команда SQL пытается поместить NULL значение
в главную переменную которая использует indicator, indicator
будет установлен в отрицательное значение. помечания NULL значений
SQL для специальной обработки их в главной программе.
* Переменная indicator
может использоваться для вставки NULL значений в команды SQL -
INSERT или UPDATE. Она также может принимать положительное
значение указывающее на длинну отбрасы ваемой части строки не
поместившейся в предельные границы какой
нибудь переменной,
куда эта строка помещалась.
************ РАБОТА
С SQL ***************
Обратите внимание: Ответы
для этих упражнений написаны в псевдокодах, являющихся английским
языком описания логики, которой должна следовать программа. Это
сделано для того чтобы помомочь читателям которые могут быть незнакомы
с Паскалем(или любым другим языком). Кроме того это лучше сфокусирует
ваше внимание на включемых понятиях, опуская частности того или
другого языка. Чтобы не противоречить нашим примерам, стиль псевдокода
будет напоминать Паскаль.
Мы опустим из программ
все что не относится напрямую к рассматриваемым вопросам, например,
определение устройств ввода-вывода, подключение к базе данных,
и так далее. Конечно, имеется много способов чтобы выполнять такие
упражнения; и совсем не обязательно что представленые варианты
решений являются самыми удачными.
1. Разработайте простую
программу которая выберет все комбинации полей snum и cnum из
таблиц Порядков и Заказчиков и выясните, всегда ли предыдущая
комбинация такая же как последующая. Если комбинация из таблицы
Порядков не найдена в таблице Заказчиков, значение поля snum для
этой строки будет изменено на удовлетворяющее условию совпадения.
Вы должны помнить, что курсор с подзапросом - модифицируем (
ANSI ограничение, также применимо к просмотрам, и что базисная
целостность базы данных это не тоже самое что проверка на ошибку(
т.е. первичные ключи уникальны, все поля cnums в таблице Порядков
правильны, и так далее ). Проверьте раздел обьявлений, и убедитесь
что там обьявлены все используемые курсоры.
2. Предположим, что ваша
программа предписывает ANSI запрещение курсоры или просмотры
использующие модифицируемые подзапро сы. Как вы должны изменить
вышеупомянутую программу?
3. Разработайте программу
которая подсказывает пользователям изменить значения поля city
продавца, автоматически увеличивает комиссионные на .01 для продавца
переводимого в Барселону и уменьшает их на .01 для продавца переводимого
в Сан Хосе. Кроме того, продавец находящийся в Лондоне должен
потерять .02 из своих комиссионных, независимо от того меняет
он город или нет, в то время как продавец не находящийся в Лондоне
должен иметь увеличение комиссионных на .02. Изменение в комиссионных
основывающееся на нахождении прода вца в Лондоне, может применяться
независимо от того куда тот переводится.
Выясните могут ли
поле city или поле comm содержать NULL значения, и обработайте
их, как это делается в SQL.
Предупреждение! : эта
программа имеет некоторые сокращения.
( См. Приложение A
- для ответов. )