Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Использование переменных языка высокого уровня с SQL

ЕХЕС SQL FЕТСН Londonsales INTO :id_num, : salesperson, : loc, : comm;

По этой команде осуществляется ввод значений из первой строки, удовлетворяющей запросу, в набор указанных переменных. В результате выполнения команды FETCH по­ лучается следующий набор значений переменных. Идея заключается в использовании команды FETCH в цикле для получения очередной строки, удовлетворяющей запросу, в выполнении необходимых действий над значениями из этой строки, в переходе к по.::~у­

чению следующей строки курсора и сохранению ее значений в том же наборе перемен­

ных. Допустим, пользователю предоставляется возможность просмотра выходных

данных запроса по одной строке в один момент времени и выдается запрос по поводу

того, хочет ли он продолжить просмотр выходных данных запроса (получить на экране дисплея следующую строку):

Look_at_more := True;

ЕХЕС SQL OPEN CURSOR Londonsales; while Look_at_more do

begin

ЕХЕС SOL FЕТСН Londonsales

INTO :id_num, :Salesperson, :loc, :comm; writeln (id_num, Salesperson, loc, comm);

writeln ('Желаете ли вы продолжить просмотр данных? (Y/N)');

readln (response);

if response = 'N' then Look_at_more .- False; end;

ЕХЕС SOL CLOSE CURSOR Londonsales;

В Pascal := означает "присвоить значение", тогда как = имеет свое обычное назна­ чение. Функция writeln выводит на экран дисплея указанные в ней данные и затем вы­ полняет установку на новую строку. Символьное значение заключается в одиночные кавычки, используется во втором writeln и в предrюжении if ". then. Это соглашение Pascal совпадает с соглашением SQL.

Приведенный в примере фрагмент работает таким образом: сначала логической пе­ ременной с именем Look_at_more присваивается значение true (истина), затем откры­ вается курсор, затем работает цикл. Внутри цикла строка извлекается из курсора и выводится на экран дисплея. Затем появляется вопрос для пользователя: желает ли он

получить на экране дисплея следующую строку. Если ответом не является N (НЕТ), то

цикл повторяется, и извлекается следующая строка значений. Переменные Look_at_ пюrе и response должны быть объявлены с типом Boolean и char соответственно в раз­ деле объявления переменных Pascal, но их не следует включать в раздел объявления переменных SQL. В тексте программы содержится предложение CLOSE CURSOR, со­ ответствующее предложению CLOSE CURSOR. По этой команде разрывается связь

между курсором и конкретными значениями, значит следует повторно выполнить за­

прос с предложением OPEN CURSOR прежде, чем станет возможным извлечение зна­ чений из курсора. Не требуется извлекать последовательно все значения курсора прежде, чем возможно будет закрыть его, хотя на практике такой вариант использова-

287

Глава 25. Использование SQL с другими языками программирования

ния встречается очень часто. После закрытия курсора SQL не отслеживает, какие строки были извлечены. Если снова открыть курсор, то запрос выполнится повторно именно в момент повторного открытия курсора и можно начинать работу с ним зано­

во, независимо от предыдущего использования этого курсора.

Пример не предоставляет никакого автоматического выхода из цикла после обра­ ботки всех строк курсора. Когда все строки запроса извлечены с помощью команды FETCH, следующая команда FETCH не изменяет значений переменных, указанных в предложении INTO. Следовательно, поскольку данные из курсора исчерпаны, на эк­ ран дисплея повторно выводятся те же самые значения, и это будет продолжаться до

тех пор, пока пользователь не отменит выполнения цикла, введя в качестве ответа N.

SQLCODE

Вы должны знать, когда исчерпаются данные курсора; при этом можно выдать

пользователю соответствующее сообщение и выйти из цикла автоматически. Это важ­ но, так как в процессе выполнения SQL-команды может возникнуть ошибка. Перемен­ ная SQLCODE (SQLCOD - в FORTRAN) может помочь в решении этой проблемы. Ее следует определить как переменную языка высокого уровня, а ее тип данных дол­ жен соответствовать одному их из числовых типов SQL (см. приложении В). Значение SQL устанавливается всякий раз, когда выполняется команда SQL. Существуют три

основные возможности:

1.Команда выполнилась без ошибок, но не дала никакого результата. Здесь ситуа­ ция зависит от конкретной команды следующим образом:

а) Для команды SELECT это свидетельствует о том, что ни одна строка не удовле­

творяет запросу;

Ь) Для команды FETCH это свидетельствует о том, что последняя строка уже была извлечена, либо о том, что ни одна строка не удовлетворяет запросу, указанному

в курсоре;

с) Для команды INSERT это свидетельствует о том, что никакие строки не были вставлены в таблицу (это может произойти, например, в том случае, когда опреде­

пяются значения, предназначенные для вставки в таблицу с помощью запроса, но в результате выполнения запроса не найдено ни одной строки);

d)Для команд UPDATE и DELETE это свидетельствует о том, что ни одна строка не

удовлетворяет условию, указанному в предикате, и, с,1едовательно, в таблице не

было сделано никаких изменений.

В любом из этих случаев значение SQLCODE устанавливается равным 100.

2.Команда выполняется успешно, ни один из перечисленных в пункте 1 случаев не имеет места, значение SQLCODE устанавливается равным О.

288

SQLCODE

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

вершает свое выполнение.

Использование SQLCODE для управления циклами

Необходимо дополнить рассмотренный ранее пример средствами автоматического выхода из цикла для случая, когда курсор оказался пустым, либо из курсора извлечены все строки, либо в процессе выполнения возникла ошибка:

Look_at_more := True;

ЕХЕС SQL OPEN CURSOR Loпdoпsales; while Look_at_more

апd SQLCODE = О do

begiп

ЕХЕС SQL FЕТСН Loпdoпsales

INTO :id_пum, :Salespersoп, :loc, :comm; writelп (id_пum, Salespersoп, loc, comm);

writelп ('Желаете ли вы продолжить просмотр данных? (Y/N)'); readlп ( respoпse);

if respoпse = 'N' theп Look_at_more .- False;

епd;

ЕХЕС SQL CLOSE CURSOR Londoпsales;

WHENEVER

Прекрасно, когда есть возможность выхода из цикла после извлечения всех строк

курсора, но если допущена ошибка, на нее нужно отреагировать с учетом рассмотрен­ ного пункта 3. Для этого в SQL предназначены предложения GOTO. Фактически, они дают возможность определить данные действия глобально, в результате чего програм­ ма выполняет команду GOTO автоматически, если в процессе выполнения программы вырабатывается определенное значение SQLCODE. Это можно сделать с помощью предложения WHENEVER. Два примера по этому поводу:

ЕХЕС SQL WHENEVER SQLERROR GOTO Error_haпdler;

289

Глава 25. Использование SQL с другими языками программирования

ЕХЕС SQL WHENEVER NOT FOUND CONTINUE;

SQLERROR - один из способов выразить SQLCODE <О; NOT FOUND - один из способов выразить SQLCODE = 100. (В некоторых программных продуктах для обо­ значения последней ситуации можно использовать SQLWARNING.) Error_handler -

это имя места в программе, начиная с которого продолжается выполнение программы

в том случае, если обнаружена ошибка (GOTO можно записать как одно слово или как два слова). Это имя определяется в зависимости от конкретного включающего языка, оно может быть меткой (label) в Pascal, именем секции или именем параграфа в COBOL (здесь и далее будет использоваться термин "метка"). Наиболее предпочти­ тельным может быть указание предлагаемой разработчиками системы стандартной процедуры, которая предназначена для обработки ошибочных ситуаций и может быть

включена во все программы.

CONTINUE означает, что не требуется предпринимать никаких специальных дей­ ствий для обработки значения SQLCODE. Это значение принимается по умолчанию, если не используется команда WHENEVER для определения значения SQLCODE.

Многократно размещая такие предложения по тексту программы, можно предприни­

мать какие-либо действия в программе или не предпринимать их.

Например, если программа включает серию команд INSERT, использующих запро­ сы, которые реально выдают значения, можно напечатать специальное сообщение или

сделать что-то, что дает возможность определить, какие из запросов возвращают пус­

тые значения, и вставки значений не происходит. В этом случае можно ввести следую­

щее:

ЕХЕС SQL WHENEVER NOT FOUND GOTO No_rows;

No_rows - это метка кода, выполняющего соответствующее действие. Если далее

в этой программе выполняется извлечение данных, то можно ввести следующее

ЕХЕС SQL WHENEVER NOT FOUND CONTINUE;

поскольку повторное извлечение данных во время извлечения строк является

нормальной процедурой и не требует специального управления.

Обновление курсоров

Курсоры применяются для выбора из таблицы групп строк, которые можно обно­ вить или удалить по одной. Это дает возможность сформулировать ряд ограничений

на предикаты, используемые в командах UPDATE и DELETE. Можно сослаться на ра­

бочую таблицу, для которой сформулирован предикат запроса курсора или подзапро­ сы этого запроса, чего нельзя сделать в самих предикатах этих команд. Стандарт SQL не позволяет воспользоваться следующей формой записи для удаления всех покупате­

лей с рейтингами ниже среднего:

ЕХЕС SQL DELETE FROM Customers

290

Об11овление курсоров

WHERE rating <

(SELECT AVG (rating) FROM Customers);

Но можно получить такой же эффект, используя запрос для соответствующих

строк, хранящихся в этом курсоре, и выполнив команду DELETE с использованием этого курсора. Во-первых, следует определить курсор, например, так:

ЕХЕС SQL DECLARE Belowavg CURSOR FOR

SELECT *

FROM Customers WHERE rating <

(SELECT AVG (rating) FROM Customers);

Затем можно создать цикл для удаления всех покупателей, выбранных с помощью

этого курсора:

ЕХЕС SQL WHENEVER SQLERROR GOTO Error_handler; ЕХЕС SQL OPEN CURSOR Belowavg;

while not SQLCODE = 100 do begin

DELETE ссылаются на одну и ту же таблицу и, следовательно, запрос в курсоре не яв­ ляется соединением. Курсор может быть также обновляемым. Чтобы быть обновляе­ мым, курсор должен удовлетворять тому же критерию, что и представление. ORDER

ВУ и UNION, которые нельзя применять в представлениях, можно использовать в кур­

сорах, но это лишает курсор свойства обновляемости. Необходимо извлекать строки из курсора в множество переменных, даже если их значения не используются. Этого требует синтаксис команды FETCH.

Команда UPDATE работает аналогично. Можно увеличить комиссионные тех про­

давцов, которые имеют покупателей с рейтингом 300. Во-первых, определим курсор:

ЕХЕС SQL DECLARE CURSOR High_Cust AS SELECT *

FROM Salespeople WHERE snum IN

(SELECT snum

291