Операторы управления ветвлением

Основным недостатком стандарта SQL-86, не позволяющим считать SQL полноценным процедурным языком, являлось отсутствие управляющих структур. До появления SQL/PSM строгую последовательность выполнения команд нельзя было нарушить без использования базового языка, такого как С или Basic. SQL/PSM получил в свое распоряжение аналогичные управляющие структуры, позволяя тем самым решать многие задачи без привлечения других языков программирования.

IF…THEN…ELSE…END IF

Основной управляющей структурой является IF…THEN…ELSE…END IF. Если условие IF истинно, выполняются команды предложения THEN. В противном случае выполняются команды предложения ELSE. Например:

IFvfname = 'Brandon'THENUPDATE studentsSET Fname = 'Brandon'WHERE StudentID = 314159;ELSEDELETE FROM studentsWHERE StudentID = 314159;END IF

В приведенном примере, если переменная vfname содержит значение 'Brandon', в списке студентов произойдет обновление записи с идентификатором 314159– в поле Fname будет помещено значение 'Brandon1. Если же переменная содержит какое-либо другое значение, отличное от Brandon', запись с идентификатором 314159 будет удалена из таблицы Students.

Выражение IF…THEN…ELSE…END IF больше всего подходит, если в зависимости от выполнения некоторого условия существуют два варианта дальнейшего поведения программы. Однако очень часто приходится иметь дело с большим количеством вариантов. В таких случаях следует использовать оператор CASE.

CASE…END CASE

Существуют два вида оператора CASE: простой оператор CASE и оператор CASE с поиском. В зависимости от выполнения нескольких условий выполнение программы происходит по одному из нескольких вариантов.

Простой оператор CASE

Простой оператор CASE вычисляет одно выражение. В зависимости от его значения выполнение программы происходит одним из нескольких возможных путей. Например:

CASE vinajorWHEN 'Computer Science'THEN INSERT INTO geeks (StudentID, Fname, Lname)VALUES(:sid,:sfname,:slname);WHEN 'Sports Medicine'THEN INSERT INTO jocks (StudentID, Fname, Lname)VALUES(:sid, rsfname,:slname);ELSE INSERT INTO undeclared (StudentID, Fname, Lname)VALUES(:sid,:sfname,:slname);END CASE

Команды предложения ELSE выполняются, если vmajor не попадает ни в одну из категорий, заданных предложениями THEN.

Внимание:
Оператор ELSE является необязательным. Однако, если он отсутствует и выражение CASE не соответствует ни одному предложению THEN, SQL генерирует исключение.

Оператор CASE с поиском

Оператор CASE с поиском аналогичен простому оператору CASE, за исключением того, что используется несколько условных выражений, а не одно. Например:

CASEWHEN vmajorIN ('Computer Science', 'Electrical Engineering')THEN INSERT INTO geeks (StudentID, Fname, Lname)VALUES(:sid,:sfname,:slname);WHEN vclubIN ('Amateur Radio', 'Rocket1,'Computer')THEN INSERT INTO geeks (StudentID, Fname, Lname)VALUES(:sid,:sfname,:slname);ELSEINSERT INTO poets (StudentID, Fname, Lname)VALUES(:sid,:sfname,:slname);END CASE

Чтобы избежать возможности возникновения исключения, следует поместить всех студентов, которые не являются "физиками" (geeks), в таблицу poets ("лирики") – Конечно, не все "не физики" являются "лириками" – ведь есть же еще и "футболисты", не говоря уже о "химиках". Это не меняет сути дела, так как в оператор CASE всегда можно добавить еще несколько предложений WHEN.

LOOP…END LOOP

Структура LOOP позволяет многократно выполнять некоторую последовательность команд SQL. После выполнения последней команды SQL, находящейся внутри структуры LOOP…ENDLOOP, цикл передает управление первой команде цикла, и все команды цикла выполняются еще раз. Синтаксис структуры LOOP…ENDLOOP имеет следующий вид:

LOOPSET vcount=vcount+1;INSERT INTO asteroid (AsteroidID)VALUES (vcount);END LOOP

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

Необходимо отметить, что данный фрагмент кода имеет один существенный недостаток. Созданный им цикл является бесконечным. В нем нет кода окончания, следовательно, добавление в таблицу строк будет продолжаться до тех пор, пока СУБД будет располагать местом на диске. Затем система в лучшем случае сгенерирует исключение, а в худшем – просто аварийно завершит работу.

Для практического использования оператора LOOP необходимо иметь возможность выхода из цикла без возникновения исключения. Для этого имеется оператор LEAVE.

LEAVE

Оператор LEAVE (покинуть) действует согласно названию. Как только выполнение программы доходит до оператора LEAVE с меткой, он передает управление команде, расположенной сразу после помеченной. Например:

AsteroidPreload:SER vcount = 0;LOOPSET vcount = vcount+1;IF vcount > 1000THENLEAVE AsteroidPreload;END IF;INSERT INTO asteroid (AsteroidID)VALUES (vcount);END LOOP AsteroidPreload

Приведенный выше код создает 1000 последовательно пронумерованных записей в таблице астероидов и затем завершает цикл.

WHILE…DO…END WHILE

Оператор WHILE предоставляет другой метод многократного выполнения последовательности команд SQL. Если условие оператора WHILE истинно, цикл продолжает выполняться. Если нет – выполнение цикла тут же прекращается. Например:

AsteroidPreload2:SET vcount = 0;WHILEvcount < 100 0 DOSET vcount = vcount+1;INSERT INTO asteroid (AsteroidID)VALUES (vcount);END WHILE AsteroidPreload2

Результат работы этого кода точно такой же, как в предыдущем случае. Это еще раз подтверждает то, что в SQL существует много методов решения поставленной задачи. Забота программиста – выбрать наиболее подходящий.

REPEAT…UNTIL..END REPEAT

Цикл REPEAT очень похож на цикл WHILE. Различие между ними заключается в том, что условие проверяется после выполнения команд цикла. Например:

AsteroidPreload3:SET vcount = 0;REPEATSET vcount = vcount+1;INSERT INTO asteroid (AsteroidID)VALUES (vcount);UNTIL vcount = 1000END REPEAT AsteroidPreload3

Совет:
Хотя во всех трех приведенных выше примерах одна и та же операция с одинаковой легкостью выполнялась тремя различными методами (с помощью операторов LOOP, WHILE и REPEAT), очень часто встречаются ситуации, когда один из методов имеет явные преимущества перед другими. Поэтому лучше хорошо знать все три метода и в конкретных обстоятельствах выбрать наиболее подходящий.

FOR…DO…END FOR

Цикл FOR в SQL объявляет и открывает курсор, выполняет выборку строк курсора, выполняет команды тела цикла FOR для каждой строки, а затем закрывает курсор. Такой цикл позволяет построчно обрабатывать данные в SQL без обращения к базовому языку. Если реализация SQL поддерживает циклы FOR, их можно использовать в качестве альтернативы: курсорам, описанным в главе 18. Ниже приведен пример цикла FOR.

FOR vcount AS Cursl CURSOR FORSELECT AsteroidID FROM asteroidDOUPDATE asteroid SET Description = 'stony iron1WHERE CURRENT OF Cursl;END FOR

В данном примере происходит обновление каждой строки таблицы Asteroid путем ввода 'stony iron' в поле Description. Такой способ ввода данных очень быстрый, но не совсем правильный. Вероятно, следовало бы вначале проверить спектральные характеристики астероидов, а уже затем индивидуально вводить эту информацию в базу данных.

ITERATE

Оператор ITERATE позволяет изменять последовательность выполнения команд в циклах SQL. Циклы SQL представлены операторами LOOP, WHILE, REPEAT и FOR. Если условие в операторах циклов является истинным или не задано, тут же оператор ITERATE начинает следующую итерацию цикла. Если же условие итерации является ложным или неопределенным, цикл завершается и выполняются следующие за ним команды:

AsteroidPreload4:SET vcount = 0;WHILEvcount < 1000 DOSET vcount=vcount+l;INSERT INTO asteroid (AsteroidID)VALUES (vcount);ITERATE AsteroidPreload4;SET vpreload = 'DONE';END WHILE AsteroidPreload4

Оператор ITERATE все время передает управление в начало цикла WHILE, но до тех пор, пока переменная vcount не будет равна 9999. В следующей итерации переменная vcount увеличится до 10000, выполнится оператор INSERT, после чего оператор ITERATE прекратит итерации, переменная vpreload получит значение 'DONE', а выполнение перейдет к команде, следующей за циклом.

Хранимые процедуры

Хранимые процедуры (stored procedures) находятся на сервере баз данных, а не на компьютере пользователя, как это было до появления SQL/PSM. Хранимая процедура должна быть определена, после чего ее можно вызвать с помощью команды CALL. Хранение процедуры на сервере уменьшает сетевой обмен и повышает производительность. Команда CALL является единственным сообщением, передаваемым от пользователя к серверу. Ниже приведен пример создания процедуры.

EXEC SQLCREATE PROCEDURE MatchScore(IN white CHAR (20),IN black CHAR (20),IN result CHAR (3),OUT winner CHAR (5))BEGIN ATOMICCASE resultWHEN '1-0' THENSET winner='white';WHEN '0-1 ' THENSET winner='black';ELSESET winner='draw';END CASE END;

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

CALL MatchScore ('Kasparov', 'Karpov', '1-0', winner);

Три первых аргумента являются входными параметрами, которые передаются процедуре MatchScore. Четвертый аргумент является выходным параметром, который процедура Match-Score использует для возврата своих результатов в вызывающую программу. В приведенном примере она возвращает значение 'white'.

Хранимые функции

Хранимые функции (stored functions) аналогичны хранимым процедурам. Вместе они называются хранимыми подпрограммами. Они имеют ряд отличий, например, они отличаются по способу их вызова. Хранимые процедуры вызываются в операторе CALL, а хранимые функции могут являться аргументом оператора SQL. Ниже представлен пример определения функции, сопровождаемый примером обращения к этой функции.

CREATE FUNCTION PurchaseHistory (CustID)RETURNS CHAR VARYING (200)BEGINDECLARE purch CHAR VARYING (200)DEFAULT '';FOR x AS SELECT *FROM transaction tWHERE t.customerID = CustIDDOIF x.description <> ''THEN SET purch=purch || ',';END IF;SET purch=purch || x.description;END FORRETURN purch;END;

Функция, заданная подобным образом, сводит воедино описания всех покупок, произведенных клиентом с определенным идентификационным номером, выбранным из таблицы TRANSACTIONS. Следующая команда, содержащая вызов функции PurchaseHistory, заносит в таблицу информацию о всех покупках, сделанных клиентом с идентификационным номером 314259:

SET customerID=3142 59;UPDATE customerSET history=PurchaseHistory (customerlD)WHERE customerID=314259;

Полномочия

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

· На удаление записи из таблицы.

· На вставку записи в таблицу.

· На обновления записи в таблице.

· На создание ссылок на поля таблицы.

· На использование домена.

SQL/PSM добавляет к уже существующим еще один вид полномочий – полномочия на выполнение. Ниже приведены два примера.

GRANT EXECUTE on MatchScore to TournamentDirector;GRANT EXECUTE on PurchaseHistory to SalesManager;

Эти команды предоставляют возможность руководителю шахматного турнира вызывать процедуру MatchScore, а менеджеру по продажам компании – вызывать функцию Purchase-History. Пользователи, которые не имеют полномочий на выполнение подпрограммы, не могут ее использовать.

Хранимые модули

Хранимые модули (stored modules) могут содержать множество подпрограмм, т.е. процедур и (или) функций SQL. Каждый пользователь с полномочиями на выполнение модуля имеет доступ ко всем подпрограммам этого модуля. Полномочия на выполнение отдельных подпрограмм модуля предоставляться не могут. Ниже приведен пример хранимого модуля.

CREATE MODULE mod1PROCEDURE MatchScore(IN white CHAR (20),IN black CHAR (20),IN result CHAR (3),OUT winner CHAR (5))BEGIN ATOMICCASE resultWHEN '1-0' THENSET winner = 'white';WHEN '1-0' THENSET winner = 'black';ELSESET winner = 'draw';END CASEEND;FUNCTION PurchaseHistory (CustID)RETURNS CHAR VARYING (200)BEGINDECLARE purch CHAR VARYING (200)DEFAULT '';FOR X AS SELECT *FROM transaction tWHERE t.cuStomerlD = CustIDDOIF x.description <> ''THEN SET purch = purch | | '. ';END IF;SET purch = purch || x.description;END FORRETURN purch;END;END MODULE;

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

 

Обработка ошибок

В этой главе…

· Подача сигнала об ошибке

· Переход к коду обработки ошибок

· Ограничение, вызвавшее ошибку

· Ошибка какой СУБД произошла

Правда, было бы замечательно, чтобы каждое написанное вами приложение все время работало прекрасно? Еще бы! А если еще и выиграть 57 миллионов долларов в лотерею, что проводится в штате Орегон, то вообще было бы все круто! К сожалению, вероятность первого события не превышает вероятности второго. Те или иные состояния ошибки случаются неизбежно, поэтому полезно знать их причины. В SQL:2003 механизмом, передающим информацию об ошибке, является параметр состояния (или переменная базового языка) SQLSTATE (состояние SQL). С помощью этой информации можно выполнить те или иные действия, которые помогают исправить ошибку.

Скажем, предложение WHENEVER (как только) дает возможность выполнять заранее заготовленное действие, как только возникает некоторая ситуация, например, когда у параметра SQLSTATE появляется ненулевое значение. Кроме того, подробную информацию о состоянии только что выполненного оператора SQL можно найти в области диагностики. В данной главе рассказывается об этих полезных средствах и о том, как их использовать.

SQLSTATE

Параметр SQLSTATE позволяет определить множество нештатных ситуаций. Он представляет собой строку из пяти символов, в которой могут находиться буквы в верхнем регистре от А до Z и цифры от 0 до 9. Эта строка делится на две группы – двухсимвольный код класса и трехсимвольный код подкласса.

В стандарте SQL:2003 определены все коды класса, начинающиеся с букв от А до Н или с цифр от 0 до 4. Поэтому любой такой код во всех реализациях означает одно и то же. Что касается кодов классов, которые начинаются с букв от I до Z или с цифр от 5 до 9, то их значение определяется конкретными реализациями СУБД. Дело в том, что спецификация SQL не может предусмотреть все ситуации, которые могут произойти в каждой реализации. Впрочем, если говорить откровенно, разработчикам нужно пореже использовать эти нестандартные коды классов. Нестандартные коды усложняют переход с одной СУБД на другую. Лучше, конечно, вообще обойтись стандартными кодами, а нестандартные использовать только в виде исключения.

Если в параметре SQLSTATE код класса равен 00, оператор завершился успешно. А код класса, равный 01, означает уже другое: хотя оператор и завершился успешно, но вывел предупреждение. Если нет данных, значение этого кода становится равным 02. Любое другое значение кода класса, находящееся в параметре SQLSTATE, означает, что выполнение опера-гора не было успешным.

Так как после каждой операции SQL параметр SQLSTATE обновляется, проверять его можно после выполнения каждого оператора. Если значение параметра SQLSTATE – 00000 (успешное завершение), то можно приступить к выполнению следующей запланированной операции. Ну а если в нем находится что-то другое, то для обработки ситуации, возможно, придется отклониться от той обычной последовательности выполнения кода. Какое из нескольких возможных действий следует выполнить – зависит от находящихся в параметре SQLSTATE значений кодов класса и подкласса.

Чтобы параметр состояния SQLSTATE можно было использовать в программе, написанной на модульном языке (о таких программах см. в главе 15), ссылку на этот параметр надо поместить в определении процедуры. Как это делается, показано в следующем примере с процедурой NUTRIENT (питательность), которая работает с таблицей FOODS (продукты питания), имеющей столбцы Foodname (название продукта), Calories (калории), Protein (белки), Fat (жиры) и Carbohidrate (углеводы):

PROCEDURE NUTRIENT(SQLSTATE,:foodname CHAR (20),:calories SMALLINT,:rprotein DECIMAL (5.1),:fat DECIMAL (5.1),:carbo DECIMAL (5.1))INSERT INTO FOODS(Foodname, Calories, Protein, Fat, Carbohidrate)VALUES(:foodname, rcalories,:protein,:fat,:carbo);

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

foodname = "Okra, boiled";calories = 29;protein = 2.0;fat = 0.3;carbo = 6.0,-NUTRIENT(state, foodname, calories, protein, fat, carbo);

Значение SQLSTATE возвращается переменной state (состояние). Ваша программа может проверять эту переменную и, в зависимости от ее значения, выполнять то или иное действие. (Для тех, кто не знает, окра – это такое растение, которое еще называется бамией. В пищу идут недозрелые стручки, которые варят, и созревшие семена, из которых делают суррогат кофе. – Примеч. пер.)

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

А зачем, собственно, знать, что оператор SQL не выполнился успешно, если с этим уже ничего не поделаешь? Дело здесь вот в чем. Если произошла ошибка, то нельзя, чтобы приложение выполнялось так, будто ничего не случилось. Нужно иметь возможность узнать об ошибке и затем что-то предпринять, чтобы ее исправить. А если исправить ее невозможно, нужно сообщить об ошибке пользователю и корректно завершить приложение. В SQL для обработки исключительных ситуаций, или, как их еще называют, исключений, имеется такой механизм, как предложение WHENEVER.

Предложение WHENEVER фактически является объявлением, поэтому его помещают в разделе объявлений SQL-приложения перед выполняемым кодом SQL. У этого предложения такой синтаксис:

WHENEVER состояние действие;

Состояниями могут быть SQLERROR (ошибка SQL) или NOT FOUND (не найден). А действием – CONTINUE (продолжать) или GOTO адрес (перейти по адресу). Если код класса в параметре SQLSTATE не равен 00, 01 или 02, то возникает состояние SQLERROR. А если параметр SQLSTATE равен 02000, то возникает состояние NOT FOUND.

Если действием является CONTINUE, то выполнение кода происходит по обычному сценарию. Но когда вместо CONTINUE задано GOTO адрес (или GO TO адрес), точка выполнения перемещается по указанному адресу в программе. Адресом перехода может быть и условное выражение, которое проверяет значение параметра SQLSTATE и, в зависимости от результатов проверки, переводит выполнение по требуемому адресу. Вот несколько примеров предложения WHENEVER:

WHENEVER SQLERROR GO TO error_trap;

…или:

WHENEVER NOT FOUND CONTINUE;

GO TO – это макрокоманда. Реализация, точнее препроцессор встроенного языка, вставляет после каждого оператора EXEC SQL следующую проверку:

IF SQLSTATE <> '00000'AND SQLSTATE <> '00001'AND SQLSTATE <> '00002'THEN GOTO error_trap;

Опция CONTINUE означает отсутствие действий, т.е. действие "игнорируй ошибку".

Область диагностики

Хотя параметр SQLSTATE в состоянии дать информацию о том, почему неудачно завершился некоторый оператор, но такая информация все же неполная. Поэтому стандарт SQL:2003, кроме того, еще дает возможность перехватывать дополнительную информацию о состоянии и хранить ее в области диагностики. Многократное определение области диагностики работает по принципу стека LIFO (last-in-first-out – последним вошел, первым вышел). Дополнительная информация о состоянии может быть особенно полезной тогда, когда при выполнении единственного оператора SQL появилось множество ошибок. Параметр SQLSTATE сообщает только об одной из них, а область диагностики может рассказать сразу о множестве ошибок (а возможно, и обо всех).

Область диагностики – это структура данных СУБД, состоящая из двух компонентов.

Заголовок. В нем находится общая информация о последнем выполнявшемся операторе SQL.

Информационная область. В ней находится подробная информация о каждом коде (ошибка, предупреждение или успешное выполнение), который был сгенерирован в результате выполнения оператора.