Работа с хранимыми процедурами
19. Работа с хранимыми процедурами
19.1. Что такое хранимые процедуры
Очень часто приходится использовать несколько операторов для выполнения сложной операции. Например, рассмотрим следующие сценарии.
· При обработке заказа бывает необходимо удостовериться в том, что соответствующие товары есть на складе.
· Если товары есть на складе, они должны быть зарезервированы, чтобы их не продали кому-нибудь еще, а их количество, доступное другим покупателям, должно быть уменьшено соответственно изменившейся ситуации.
· Товары, отсутствующие на складе, должны быть заказаны, для этого нужно связаться с их поставщиком.
· Клиенту необходимо сообщить, какие товары есть на складе и могут быть отгружены немедленно и заказ на какие товары выполнен быть не может.
Решение подобной задачи потребует применение многих операторов SQL. Помимо того что сами SQL-операторы и их порядок не постоянны, они могут и будут изменяться в зависимости от того, какие товары имеются на складе, а каких там нет. Для этого используется хранимые процедуры.
Хранимая процедура – это совокупность нескольких операторов или даже один оператор, сохраненная для последующего использования. Ее можно рассматривать как командный файл.
Рекомендуемые материалы
Хранимые процедуры не поддерживаются в Access, а также СУБД MySQL v4.х.
19.2. Для чего используют хранимые процедуры
Основными причинами использования хранимых процедур являются.
· Для упрощения сложных операций за счет инкапсуляции процессов в один блок, простой для выполнения.
· Для обеспечения непротиворечивости данных и воспроизводить одну и ту же последовательность шагов.
· Побочным эффектом является предотвращение ошибок. Чем больше шагов необходимо выполнить, тем выше вероятность появления ошибок. Предотвращение ошибок обеспечивает целостность данных.
· Для упрощения управления изменениями. Если таблицы, имена столбцов и другое изменяются, то обновлять приходится только код хранимой процедуры. Побочным эффектом этого является повышение безопасности. Ограничение доступа к основным данным только через хранимые процедуры снижает вероятность повреждения данных.
· Поскольку хранимые процедуры обычно сохраняются в компилированном виде, СУБД тратит меньше времени на обработку их команд и это приводит к повышению производительности.
· Хранимые процедуры можно использовать для написания более гибкого и мощного кода, так как некоторые возможности реализуются только в хранимых процедурах.
Основные преимущества:
· Простота;
· Безопасность;
· Производительность.
Имеются и сложности:
· Синтаксис хранимых процедур различен для разных СУБД. Написать по-настоящему переносимый код хранимой процедуры практически невозможно.
· Хранимые процедуры сложнее в написании, чем основные операторы SQL.
Тем не менее, хранимые процедуры весьма полезны и должны использоваться.
19.3. Выполнение хранимых процедур
Для выполнения хранимой процедуры принимают оператор SQL – EXECUTE, который имеет некоторые параметры.
Пример.
EXECUTE AddNewProduct(‘JTS01’,
‘Stuffed Eiffel Tower’,
6,49,
‘Plush stuffed toy with the text La Tour Eiffel in red white and blue’)
Выполняется хранимая процедура по имени AddNewProduct, она добавляет новый продукт в таблицу Products. Хранимая процедура принимает четыре параметра: идентификатор поставщика (первичный ключ таблицы Vendors), название продукта, цена и описание. Эти четыре параметра соответствуют четырем ожидаемым переменным хранимой процедуры. Данная хранимая процедура добавляет новую строку в таблицу Products и распределяет эти передаваемые атрибуты по соответствующим столбцам.
В таблице Products усть уще один столбец, нуждающийся в присвоении значения: столбец prod_id, который является первичным ключом таблицы. Это значение не передается, так как для безопасности необходимо генерацию идентификаторов сделать автоматизированным.
Хранимая процедура выполняет следующие действия:
· Подтверждает правильность передаваемых данных.
· Генерирует уникальный идентификатор, который будет использован в качестве первичного ключа.
· Добавляет данные о новом продукте в таблицу, сохраняя созданный первичный ключ и передавая данные в соответствующие столбцы.
Такова основная форма выполнения хранимой процедуры.
19.4. Создание хранимых процедур
Создание хранимой процедуры – это задача не из тривиальных задач. Рассмотрим простой пример. Создать хранимую процедуру, которая подсчитывает в списке адресатов число клиентов, имеющих адрес электронной почты.
Для СУБД Oracle это будет.
CREATE PROCEDURE MailingListCount
(ListCount OUT NUMBER)
IS
BEGIN
SELECT *
FROM Customers
WHERE NOT cust_email IS NULL;
ListCount :=SQL%ROWCOUNT;
END;
Хранимая процедура принимает один параметр, называемый ListCount. Этот параметр передает значение из нее. Ключевое слово OUT указывает ей, как вести себя. Описатель IN – говорят о том, что параметр передается в процедуру. Код хранимой процедуры заключен между BEGIN и END. Для выборки клиентов, имеющих адреса электронной почты, выполняется простой оператор SELECT. Передаваемому выходному параметру ListCount присваивается значение, равное количеству строк в выборке.
Эта процедура в СУБД MS SQL будет иметь вид.
CREATE PROCEDURE MailingListCount
AS
Если Вам понравилась эта лекция, то понравится и эта - 8. Планирование потребностей в ресурсах.
DECLARE @cnt INTEGER
SELECT #cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;
Эта хранимая процедура вообще не принимает параметров. Вызываемое приложение выбирает нужное значение, пользуясь тем, что в SQL Server поддерживается возвращение кода. Здесь посредством оператора DECLARE объявлена локальная переменная @cnt (имена всех локальных переменных начинаются с символа @). Эта переменная используется в операторе SELECT, так что она содержит значение, возвращаемое функцией COUNT(). Оператор RETURN используется для возвращения результатов подсчета в вызывающее приложение.
