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

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

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

Вот версия для СУБД Oracle:

CREATE PROCEDURE MailingListCount
(ListCount OUT NUMBER)
IS
BEGIN
SELECT * FROM Customers WHERE NOT cust_email IS NULL; ListCount := SQL%ROWCOUNT;
END;

Эта хранимая процедура принимает один параметр, называемый ListCount. Вместо того чтобы передавать значение в хранимую процедуру, этот параметр передает значение из нее. Ключевое слово OUT указывает ей вести себя подобным образом. СУБД Oracle поддерживает параметры типов IN (которые передаются в хранимые процедуры), OUT (они передаются из хранимых процедур) и INOUT (они используются для передачи параметров в хранимые процедуры и из них). Собственно код хранимой процедуры заключен между BEGIN и END, и здесь для выборки клиентов, имеющих адреса электронной почты, выполняется простой оператор SELECT. Затем передаваемому выходному параметру ListCount присваивается значение, равное количеству строк в выборке.

А вот версия для Microsoft SQL:

CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;

Эта хранимая процедура вообще не принимает параметров. Вызываемое приложение выбирает нужное значение, пользуясь тем, что в СУБД SQL Server поддерживается возвращение кода. Здесь посредством оператора DECLARE объявлена локальная переменная @cnt (имена всех локальных переменных в SQL Server начинаются с символа @). Эта переменная затем используется в операторе SELECT, так что он содержит значение, возвращаемое функцией COUNT (). Наконец, оператор RETURN используется для возвращения результатов подсчета в вызывающее приложение — RETURN Ocnt.

Приведем еще один пример, на этот раз мы будем добавлять новый заказ в таблицу Orders. Этот пример подходит только для SQL Server, но он хорошо показывает, как нужно использовать хранимые процедуры:

CREATE PROCEDURE NewOrder @cust_id CHAR(IO)
AS
- - Объявление переменной для номера заказа
DECLARE @order_num INTEGER
-- Получение текущего наибольшего номера заказа
SELECT @order_num=MAX (order_num)
FROM Orders

Determine next order number
SELECT @order_num=@order_num+l
-- Добавление нового заказа
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(®order_num, GETDATEO, @cust_id)
-- Возвращение номера заказа
RETURN ©order num;

Эта хранимая процедура создает новый заказ в таблице Orders. Она принимает один параметр — идентификатор клиента, сделавшего заказ. Два других столбца таблицы, номер и дата заказа, генерируются автоматически в самой хранимой процедуре. Вначале в коде объявляется локальная переменная для хранения номера заказа. Затем выбирается текущий наибольший номер заказа (посредством функции МАХ ()) и увеличивается на единицу (с помощью оператора SELECT). После этого добавляется заказ посредством оператора INSERT с использованием только что сгенерированного номера заказа, выбирается текущая системная дата (с помощью функции GETDATE ()) и передается идентификатор клиента. Наконец, номер заказа (необходимый для обработки предметов заказа) возвращается как RETURN @order_num. Отметим, что код снабжен комментариями, это всегда следует делать при написании хранимых процедур.

Комментируйте ваш код

Весь ваш код должен быть снабжен комментариями, и хранимая процедура не исключение. Добавление комментариев не окажет никакого влияния на производительность, так что здесь нет "обратной стороны медали" (время тратится только на написание комментариев). Многочисленные преимущества включают, например, облегчение понимания кода другими (и вами тоже), а также удобство его изменения через некоторое время.

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

Вот несколько различных версий одного и того же кода для SQL Server:

CREATE PROCEDURE NewOrder @cust_id CHAR (10)
AS
-- Добавление нового заказа
INSERT INTO Orders(cust_id)
VALUES(@cust_id)
-- Возвращение номера заказа
SELECT order_num = ©©IDENTITY;

Эта хранимая процедура также создает новый заказ в таблице Orders. На этот раз СУБД сама генерирует номер заказа. Большинство СУБД поддерживают такой тип функциональности; SQL Server обращается к этим автоинкре-ментируемым столбцам как к полям Identity (другие СУБД используют такие имена, как Auto Number или Sequences). Опять же, передается только один параметр: идентификатор клиента, сделавшего заказ. Номер и дата заказа не указываются вообще — СУБД использует значение по умолчанию для даты (функция GETDATE ()), а номер заказа генерируется автоматически. Как можно узнать, кокой идентификатор пользователя был сгенерирован? В СУБД SQL Serer для этого используется глобальная переменная ©©IDENTITY, возвращаемая в вызывающее приложение (на этот раз с использованием оператора SELECT).

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