На рис. 20 14 приведен эквивалент этой же процедуры на диалекте Informix Здесь результаты запроса помещаются в обычные переменные, а специальный тип данных для хранения всей сзроки набора записей не используется Инструкция foreach многофункциональна Она определяет запрос, отмечает начало цикла (конец помечается объявлением end foreach), вьтолняет запрос, а гакже извлекает строки из таблицы результатов запроса, помещая значения столбцов в указанные локальные переменные Обработка каждой записи происходит в теле цикла foreach. Когда все записи обработаны, набор записей автоматически закрывается и работа хранимой процедуры продолжается с инструкции, следующей за циклом Обратите внимание на то, что в этом примере набору записей не присваивается отдельное имя, поскольку все операции по созданию щ обработке набора записей интефированы в инструкцию foreach

create procedure sort orders()

/* Локальные переменные для кранения результатов запроса */ define ord amt money(16, 2), /* стоимость заказа */ define c naine varchart20); /* имя клиента */

detme r name varchar (15); /* имя служащего */

/* Выполняем запрос и обрабатываем гаждую строку в таблице результатов запроса foreach select amount, company, name

into ord amt, c name, r name from orders, customers, salesreps where cust = cust num and rep = empl num;

begin

/* Обработка малых заказов */

if (ord amt < 1000.00)

then insert into smallorders

values (r narae, ord amt) ;

/* Обработка больших заказов */ elif (ord amt > 10000.00) then insert into bigorders

values (c name, ord amt);

end if, end; end foreach, end procedure;

Рис 2($iiKiT (FOKESACBJ обработкн набора записей в Informix SPL

В Transact-SQL нет специального цикла for, предназначенного для обработки результатов запроса Зато вы .можете пользоваться инструкциями declare cursor, open, fetch и close, аналогичными тем, которые применяются для работы с наборами записей во встроенном SQL На рис 20 15 приведена версия процедуры sort ORDERS этого диалекта Для управления циклом используется системная переменная &@sqlstatus*, являющаяся аналогом переменной sqlstate встроенного SQL Эта переменная возвращает О, если инструкция fetch успещно извлекла следующую запись, и ненулевое значение, когда все записи получены

Эта переменная существует в диалекте Transact-SQL, применяемом в СУБД Sybase В SQL Server она называется @@fetch STAtus и принимает несколько иные значения

create procedure sort orders() as

/* Локальные переменные для хранения результатов запроса */ declare @ord amt money{16, 2), /* стоимость заказа */ declare @c nanie varchar (20), /* имя клиента */ declare er name varchar(15); /* имя служащего */

/* Объявляем набор записей для запроса */ declare o curs cursor for

select amount, company, name

from orders, customers, salesreps where cust = cust num and rep = empl num

oegm

/ Открываем набор записей и извлекаем первую запись */ open o curs

fetch o curs into @ord amt, @c name, @r name

/* Если набор записей пуст, выходим из процедуры */

if (@@sqlstatus = 2)

begin

close o curs

return

/* Цикл обработки всех строк в таблице результатов запроса */

while (@@sqlstatus = 0)

begin

/* Обработка малых заказов */ if (@ord amt < 1000.00) then insert into smallorders

values (@r name, (3ord amt)

/* Обработка больших заказов *j else If {@ord amt > 10000.00) then insert into bigorders

values (@c name, @ord amt)

/* Bee записи обработаны,- закрываем набор записей */ close о curs

Рис 20.15. фкл pfSIXMl обработки набора записей в Transad-SQL

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

Когда приложение работает с базой данных через интерфейс встроенного SQL или SQL API, оно само отвечает за обработку возникающих при этом ошибок. Коды ошибок возвращаются приложению сервером баз данных, а дополнительную информацию можно получить, вызвав дополнительные API-функции или обратившись к специальной структуре, содержащей диагностическую информацию. Если же операции над данными выполняются хранимой процедурой, она же и должна обрабатывать Ошибки

в Transact-SQL информацию о произошедших ошибках можно получить из специальных системных переменных. Имеется огромное количество глобальных системных переменных, хранящих информацию о состоянии сервера и транзакции, открытых подключениях и т.п. Однако для обработки ошибок чаше всего используются только две из них:

ш @(аerror - код ошибки, произошсдшей при выполнении последней инструкции SQL;

ш oqsqlstatus - состояние последней операции fetch.

Признаком нормального завершения в обеих переменных является значение О Другие значения указывают на ошибки или нестандартные ситуации. В хранимы> процедурах Transact-SQL глобальные переменные используются точно так же, как локальные. В частности, их можно применять в условиях циклов и в инструкции if.

В Oracle PL/SQL принят другой стиль обработки ошибок. Эта СУБД предоставляет профаммистам набор системных исключений - ошибок и предупреждений, которые могут возникать в ходе выполнения инструкций SQL. В хранимой процедуре (или блоке инструкций) может присутствовать раздел exception, указывающий СУБД, как ей обрабатывать любые исключительные ситуации, возникающие в этой процедуре (блоке). Всего их в Oracle предусмотрено более десяти. Кроме того, можно создавать и собственные исключения.

В большинстве предыдущих примеров этой главы не выполнялась вообще никакая обработка ошибок. На рис. 20.16 представлена доработанная версия хранимой процедуры, которую мы приводили на рис. 20.7. В ней обрабатывается особая ситуация, когда с заданным клиентом не связано ни одного заказа (т.е. когда запрос, вычисляющий стоимость заказов клиента, порождает исключение no data found). В ответ приложению, которое вызвало данную хранимую процедуру, возвращается код ошибки (уже не системный, а прикладной) и соответствующее сообшение. Все остальные исключительные ситуации обрабатываются в блоке when others.

/* Возвращает общую стоимость заказов клиента */ create function get tot ords (c num m integer) returns number(16,2)

/* Объявляем локальную переменную для хранения итога */ declare tot ord number(16, 2);

begin

/* Простой запрос, возвращающий

единственную запись с итоговой суммой */ select sum(amount) into tot ord from orders wliere cust = c num;

/* Возвращаем полученную сумму

в качестве значения функции */ return tot ord;

exception

/* Обработка ситуации, когда не найдено ни одного заказа */ vilien no data found

then raise application error (-20123, Неверный номер клиента)

Рис. 20.16. Хранимая функция с обра&огчтдм ошиббк в OmcleL/3QL

...... . л