Хранимые процедуры. Хранимые процедуры – это объекты базы данных, которые представляют собой небольшие программы, манипулирующие данными и выполняемые на сервере

Хранимые процедуры – это объекты базы данных, которые представляют собой небольшие программы, манипулирующие данными и выполняемые на сервере. Эти программы, кроме команд языка SQL, могут использовать немногочисленные управляющие команды.

Структура хранимой процедуры следующая:

CREATE PROC[EDURE] имя_процедуры [параметры]

AS

Код процедуры

Локальные переменные и параметры в процедуре начинаются с символа @.

Глобальные переменные начинаются с символов @@. Есть довольно много системных глобальных переменных с полезной информацией. Некоторые из них мы будем использовать в следующих темах.

Объявление переменных имеет вид

DECLARE имя_переменной тип_переменной [(длина)]

Блок операторов заключается в команды BEGIN … END

Оператор присвоения выгладит довольно странно:

SELECT переменная=значение

Зато с помощью такого синтаксиса при выполнении команды SELECT можно сохранять значения в переменных.

Альтернативный формат оператора присвоения:

SET переменная=значение

Условный оператор выглядит так:

IF условие

Оператор1

[ELSE

Оператор2]

Цикл по счетчику отсутствует, есть только цикл по условию

WHILE условие

Оператор

Для прерывания цикла используется команда BREAK.

Для прерывания итерации цикла используется команда CONTINUE.

Оператор печати имеет вид PRINT выражение

Выход из процедуры: RETURN [код_завершения]

Это первый способ возвращения значения из процедуры – можно возвращать только целочисленное значение.

Команда

RAISERROR сообщение, уровень_опасности, код_состояния

применяется для вывода сообщений об ошибках и прочих предупреждений в стандартной для SQL server форме.

Выражение CASE применяется для селективного выбора на основании нескольких опций:

CASE выражение

WHEN вариант1 THEN выражение1

WHEN вариант2 THEN выражение2

…

ELSE выражениеN

END

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

Это второй способ возвращения значений из процедуры - печать результата выполнения команды SELECT.

CREATE PROCEDURE show_contracts @name Varchar(30)

AS

SELECT contract_num, contract_date, contract_type

FROM k_contract c JOIN k_staff s ON c.staff_num=s.staff_num

WHERE s.staff_name=@name

GO

Для запуска этой процедуры нужно выполнить, например, команду

EXEC show_contracts 'Иванов'

В результате получим:

contract_num contract_date contract_type

------------ --------------------------- -------------

1 2006-03-03 16:42:55.170 A

3 2006-03-03 16:42:55.190 C

6 2006-03-03 16:42:55.190 C

7 2006-03-03 16:42:55.190 A

(4 row(s) affected)

Создадим процедуру “Распродажа”, которая находит самый непродаваемый (по количеству) товар и уценивает его на заданный процент (по умолчанию задается 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

(5 row(s) affected)

Для запуска этой процедуры нужно выполнить, например, команду

EXEC clearance 10

Содержимое таблицы "Прайс-лист" после выполнения процедуры:

price_num price_name price_sum

------------------------------------------

1 Материализация духов 1000.00

2 Раздача слонов 100.00

3 Слоновий бивень 3000.00

4 Моржовый клык 1350.00

5 Копыто Пегаса 5000.00

(5 row(s) affected)

Как видим, товар с номером 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.

Для удаления хранимой процедуры используется команда:

DROP PROCEDURE имя_процедуры

Задание для индивидуальной работы 8

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

Дата добавления: 2015-05-09