Хранимые процедуры – это объекты базы данных, которые представляют собой программы, манипулирующие данными и выполняемые на сервере. Эти программы, кроме команд языка SQL, могут использовать немногочисленные управляющие команды.
Структура хранимой процедуры следующая:
CREATE PROC[EDURE] имя_процедуры [параметры]
AS
Код процедуры
Локальные переменные и параметры в процедуре начинаются с символа @.
Глобальные переменные начинаются с символов @@. Есть довольно много системных глобальных переменных с полезной информацией. Некоторые из них мы будем использовать в следующих темах.
Объявление переменных имеет вид
DECLARE имя_переменной тип_переменной [(длина)]
Блок операторов заключается в команды BEGIN … END
Оператор присвоения выгладит довольно странно:
SELECT переменная=значение
Зато с помощью такого синтаксиса при выполнении команды SELECT можно сохранять значения в переменных.
Альтернативный формат оператора присвоения:
SET переменная=значение
Условный оператор выглядит так:
IF условие
Оператор1
[ELSE
Оператор2]
Цикл по счетчику отсутствует, есть только цикл по условию
WHILE условие
Оператор
Для прерывания цикла используется команда BREAK.
Для прерывания итерации цикла используется команда CONTINUE.
Оператор печати имеет вид PRINT выражение
Выход из процедуры: RETURN [код_завершения]
Это первый способ возвращения значения из процедуры – таким образом можно возвращать только целочисленное значение.
Создадим процедуру «Распродажа», которая находит самый непродаваемый (по количеству) товар и уценивает его на заданный процент (по умолчанию задается 10 процентов).
CREATE PROCEDURE clearance @percent Int = 10
AS
DECLARE @p Int
IF @percent > 0 AND @percent < 100
BEGIN
SELECT @p=price_num FROM k_protokol
GROUP BY price_num
HAVING SUM(kolvo)<=ALL
(SELECT SUM(kolvo) FROM k_protokol
GROUP BY price_num)
UPDATE k_price
SET price_sum=price_sum*(100-@percent)/100
WHERE price_num=@p
END
GO
Содержимое таблицы "Прайс-лист" до выполнения процедуры:
price_num price_name price_sum
-------------------------------------------------
1 Материализация духов 1000.00
2 Раздача слонов 100.00
3 Слоновий бивень 3000.00
4 Моржовый клык 1500.00
5 Копыто Пегаса 5000.00
Для запуска этой процедуры нужно выполнить, например, команду
EXEC clearance 10
Содержимое таблицы "Прайс-лист" после выполнения процедуры:
price_num price_name price_sum
------------------------------------------
1 Материализация духов 1000.00
2 Раздача слонов 100.00
3 Слоновий бивень 3000.00
4 Моржовый клык 1350.00
5 Копыто Пегаса 5000.00
Как видим, товар с номером 4 в прайс-листе уценен на 10%.
В том случае, если из хранимой процедуры нужно вернуть значение переменной, нужно объявить эту переменную как выходной (OUTPUT) параметр процедуры в двух местах: в описании процедуры и в вызове процедуры. Это третий способ возвращения значений из процедуры – выходные параметры.
Пусть, например, в предыдущей процедуре мы хотим не только уценить товар, но и возвратить его номер. Описание процедуры будет выглядеть следующим образом:
CREATE PROCEDURE clearance @percent Int, @p Int OUTPUT
AS
IF @percent > 0 AND @percent < 100
BEGIN
SELECT @p=price_num FROM k_protokol
GROUP BY price_num
HAVING SUM(kolvo)<=ALL
(SELECT SUM(kolvo) FROM k_protokol
GROUP BY price_num)
UPDATE k_price
SET price_sum=price_sum*(100-@percent)/100
WHERE price_num=@p
END
А вызов процедуры будет выглядеть следующим образом (все три команды должны выполняться вместе):
DECLARE @num NUMERIC(6)
EXEC clearance 1, @num OUTPUT
PRINT 'Уценили товар с номером '+STR(@num)
И еще один пример. Предположим, у нас есть таблица для хранения списка счетов:
CREATE TABLE bill_list
(name VARCHAR(20), dat DATETIME, summa NUMERIC(9,2))
Мы хотим сформировать список выставленных за месяц счетов с названиями предприятий и с итогами по дням. Рассмотрите этот пример самостоятельно. Месяц и год передаются в процедуру в качестве параметров. Функция DATEDIFF здесь вычисляет разность между двумя датами в днях.
CREATE PROCEDURE calc_bill_list @mon Int, @year Int
AS
DECLARE @day Int, @end Int, @date DateTime
SET @day=1
IF @mon=2
IF @year%4=0
SET @end=29
ELSE
SET @end=28
ELSE IF @mon=4 OR @mon=6 OR @mon=9 OR @mon=11
SET @end=30
ELSE
SET @end=31
DELETE FROM bill_list
WHILE (@day<=@end)
BEGIN
SET @date=CONVERT(CHAR(2),@mon)+'/'+
CONVERT(CHAR(2),@day)+'/'+
CONVERT(CHAR(4),@year)
INSERT INTO bill_list (name, dat, summa)
SELECT firm_name, bill_date, bill_sum
FROM k_firm, k_contract, k_bill
WHERE k_firm.firm_num=k_contract.firm_num
AND
k_contract.contract_num=k_bill.contract_num
AND
DATEDIFF(day, k_bill.bill_date, @date)=0
INSERT INTO bill_list (name, dat, summa)
SELECT ' ИТОГО ЗА:', @date,
ISNULL(SUM(bill_sum),0) FROM k_bill
WHERE
DATEDIFF(day, k_bill.bill_date, @date)=0
SET @day=@day+1
END
Обратите внимание на формат команды INSERT. В таблицу bill_list добавляются строки, являющиеся результатом выполнения команды SELECT.
Для удаления хранимой процедуры используется команда:
