/* Обработка остальных исключений */ when others

then raise application error (-20199, Неизвестная ошибка);

and;

Рис. 20.16. Хранимая функция с обработчиком ошибок в Orach PL/SQL

Аналогичным образом обрабатываются исключения и в Informix. На рис. 20.17 приведена версия хранимой функции GET TOT ORDS для Informix. Объявление ON EXCEPTION определяет последовательность инструкций, которые должны быть выполнены, если произоргцет одно из указанных исключений (перечень кодов исключений приведен в скобках через запятую).

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

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

/* Определяем обработчик исключений для ошибок -123 и -121 */ on exception m (-121, -123)

/* Выполняем соответствующие действия */

end exception; on exception

/* Обрабатываем остальные исключения */

end exception;

begin

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

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

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

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

end function;

Рис. 20.17. Хранимая фун с обработчиком ошибок в fnformoc SPL

Преимущества хранимых процедур

Перенос профаммного кода из клиентских приложений прямо в базу данных в виде хранимых процедур приносит много пользы и админисфаторам базы данных, и программистам, и конечным пользователям. Вот основные npemiyinecTBa этого подхода: Производительность. Многие популярные СУБД компилируют хранимые процедуры (либо автоматически, либо по запросу), создавая их некоторое внутреннее

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

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

Инкапсуляция. Идея хранимых процедур хорошо соответствует одной из главных целей объектно-ориентированного профаммирования (ООП) - инкапсуляции данных и кода в защищенные структуры, доступные только посредством использования Офаниченного и строго определенного набора интерфейсов. По терминологии ООП, хранимые процедуры можно назвать методами, посредством которых пользователи или внешние профаммы могут манипулировать объектами СУБД. Если вы хотите строго придерживаться объектно-ориентированного подхода, нужно с помощью системы защиты СУБД полностью запретить непосредственный доступ к данным через SQL и оставить для доступа к базе данных только хранимые процедуры. Однако столь суровые офаничения мало кем практикуются (если вообще практикуются хоть кем-то).

Производительность хранимых процедур

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

Именно по причине низкой производительности интерпретируемых процедур некоторые СУБД предпочитают их заранее компилировать, генерируя некий про.ме-жуточный код, который выполняется гораздо быстрее. Компиляция может происходить автоматически сразу после создания процедуры или по запросу пользователя. Однако у предварительной компиляции хранимых процедур есть свой недостаток: точный процесс вьтолнения процедуры фиксируется во время ее компиляции и уже не может быть изменен. Чем же это плохо? Предположим, что после компиляции процедуры были определены дополнительные индексы для таблиц, с которыми она

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

Для решения этой проблемы некоторые СУБД автоматически помечают все скомпилированные процедуры, на которых могли отразиться изменения объектов базы данных, как нуждаюшиеся в повторной компиляции. Когда такая процедура в очередной раз вызывается, СУБД видит пометку и компилирует процедуру перед ее выполнением. Так сохраняется преимушество предварительной компиляции, и процедуры выполняются оптимальным образом. Но и у этого подхода остается один недостаток: непредсказуемые задержки в выполнении процедур, связанные с их динамической перекомпиляцией. Когда повторная компиляция не нужна, хранимая процедура может вьшолниться очень быстро; если же процедуру потребовалось перекомпилировать, перед ее вьшолнением может быть довольно ошутимая задержка.

Чтобы выяснить, как компилируются хранимые процедуры конкретной СУБД, можно посмотреть, какие опции имеются для инструкций create procedure, execute procedure, alter procedure и др.

Системные хранимые процедуры

Если СУБД подцерживает хранимые процедуры, то, скорее всего, в ней есть и некоторый набор готовых системных процедур, которые автоматизируют различные операции с базой данньгх и некоторые функции ее администрирования. Пионером создания системных хранимьгх процедур была Sybase, включившая их в свой продукт Sybase SQL Server. Сегодня их уже сотни, и они облегчают вьшолнение множества полезных функций, таких как, например, управление учетными записями пользователей, заданиями, распределенньпчи серверами, репликацией и т.д. Большинство системных процедур Transact-SQL названо в соответствии со следующими соглашениями:

ЗРАООимя - добавление нового объекта (пользователя, сервера, реплики и т.п.);

5Р 0Р0Римя - удаление существующего объекта;

sp HELPимя - получение информации об объекте или объектах. Например, процедура sp helpuser возвращает информацию о пользователях

текущей базы данных.

Внешние хранимые процедуры

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

Внешняя хранимая процедура - это процедура, написанная на одном из фади-Ционных языков профаммирования (например, на С или Pascal) и скомпилированная