Понимание SQL (775646), страница 30
Текст из файла (страница 30)
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 SQL FETCH Belowavg INTO :a, :b, :c, :d, :e;
EXEC SQL 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 SQL 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 SQL 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 . Чтобы объявить курсор High_cust таким способом, так чтобы вы могли модифицировать командой UPDATE столбец comm, вы должны ввести следующее предложение:
EXEC SQL DECLARE CURSOR High_Cust AS
SELECT *
FROM Salespeople
WHERE snum IN
(SELECT snum
FROM Customers
WHERE rating = 300)
FOR UPDATE OF comm;
Это обеспечит вас определенной защитой от случайных модификаций, которые могут разрушить весь порядок в базе данных.
ПЕРЕМЕННАЯ 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
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
