GO
Пример. Пусть необходимо определить наименование товара, по которому был наибольший спрос за последние N дней. Один из вариантов решения этой задачи заключается в выполнении следующей последовательности шагов.
1). Объявляем переменные, требуемые для решения данной задачи:
DECLARE @Name VARCHAR(50), @Code INT, @Quantity NUMERIC(12,3),
@N INT
2). Задаем значение интервала в днях:
SET @N = 60
3). Выполняем выборку данных с группировкой по кодам товаров и подсчетом для каждой группы суммарного количества заказанного товара (т.е. величины спроса). При этом результирующий набор данных будем сортировать в порядке возрастания величины спроса и, следовательно, последняя его строка будет задавать товар с наибольшим спросом. Результат выполнения этой команды на экран не выдается, а вместо этого данные последней строки сохраняются в переменных @Code и @Quantity:
SELECT @Code = КодТовара, @Quantity = SUM(Количество)
FROM Заказ
WHERE ДатаЗаказа BETWEEN GetDate() - @N AND GetDate()
GROUP BY КодТовара
ORDER BY SUM(Количество)
4). По найденному в предыдущем пункте коду товара с наибольшим спросом находим соответствующее ему наименование товара:
SELECT @Name = Наименование
FROM Товар
WHERE КодТовара = @Code
5). Выдаем найденный результат на экран:
SELECT @Name AS [Наименование товара], @Quantity AS
[Итоговое кол-во], @N AS [Временной интервал]
GO
Примечание. Все команды, рассмотренные на этих пяти шагах, выполняются в рамках одного пакета (поясняющий текст нужно удалить или закомментировать).
Программный код на языке Transact-SQL может включать в себя также битовые и логические операторы, операторы перехода (GOTO) и приостанова (WAITFOR), команды организации ветвлений (IF, CASE) и циклов (WHILE), операторные скобки (BEGIN…END) и др. (см. [1], стр. 1057 – 1087).
Временные таблицы. Они создаются в системной базе данных tempdb и бывают двух типов: локальные и глобальные. Локальные временные таблицы видны только из того соединения, в котором были созданы, и автоматически уничтожаются при закрытии этого соединения. Глобальные временные таблицы также уничтожаются при закрытии соединения, в котором были созданы, однако, когда они существуют, то видны и из любых других соединений. Это предоставляет удобный механизм для обмена данными между различными приложениями, например, между различными копиями хранимых процедур (одна и та же хранимая процедура, запускаемая различными пользователями, может обращаться к глобальной временной таблице для получения информации).
Имена локальных временных таблиц начинаются символом «#», а глобальных – двумя такими символами.
Примеры создания и использования локальных временных таблиц:
CREATE TABLE #Goods (
КодТовара INT PRIMARY KEY,
Наименование VARCHAR(50) NOT NULL,
ЕдиницаИзм CHAR(10) NULL
)
INSERT #Goods
SELECT КодТовара, Наименование, ЕдиницаИзм
FROM Товар
SELECT * FROM #Goods
GO
SELECT Наименование, Цена, КодВалюты
INTO #Goods_2
FROM Товар
SELECT * FROM #Goods_2
GO
Хранимые процедуры – это подпрограммы, хранящиеся в базах данных и представляющие собой один из видов их общих ресурсов.
Заголовок хранимой процедуры содержит название процедуры и, возможно, ее идентификационный номер, а также может включать в себя как входные параметры (передающие значения в процедуру), так и выходные параметры (завершающиеся служебным словом OUTPUT и возвращающие результат работы процедуры вызывающему программному объекту). Также можно использовать параметры, которые являются одновременно и входными и выходными. Кроме того, параметрам можно присваивать значения по умолчанию, которые могут быть только константами, но не выражениями, подлежащими вычислению.
Тело хранимой процедуры представляет собой последовательность SQL-команд, таких, например, как выборка данных (SELECT), их обновление (UPDATE) или удаление (DELETE), создание объектов базы данных, управление транзакциями, операторы цикла, условные операторы, вызовы других хранимых процедур и ряд других. Локальные переменные и локальные временные таблицы, которые создаются в теле процедуры, автоматически уничтожаются при выходе из нее, т.е. время их существования ограничено периодом исполнения команд, составляющих тело процедуры.
Процедуры вызываются командой EXEC[UTE] , в том числе из процедур, функций и других типов программных объектов. При вызове процедуры можно не указывать значения тех параметров, для которых заданы значения по умолчанию (можно также использовать зарезервированное слово DEDAULT). Однако при этом нужно придерживаться следующего правила: если значение какого-либо параметра не указывается (и нет слова DEDAULT), то значения последующих параметров нужно обязательно сопровождать указанием имен этих параметров (@parameter = value или @parameter = @variable). Такие поименованные параметры можно задавать в произвольном порядке.
Необходимость существования хранимых процедур диктуется тем, что не всякий результат можно получить с помощью одного запроса. Хранимые процедуры могут содержать большое число команд, а вызываться из клиентской программы всего одной строкой, что существенно снижает сетевой трафик. Кроме того, модификация хранимой процедуры приводит к тому, что сразу все клиентские приложения будут работать с новой редакцией процедуры.
Синтаксис команды создания хранимой процедуры (см. [1], стр. 1204):
CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
Примеры хранимых процедур:
/* Выбор всех регионов Беларуси */
CREATE PROCEDURE pr_ДанныеРегионов;1
AS
SELECT *
FROM Регион
WHERE Страна = 'Беларусь'
GO
/* Проверка работы хранимой процедуры pr_ДанныеРегионов;1 */
EXEC pr_ДанныеРегионов;1
GO
/* Выбор всех регионов конкретной страны */
CREATE PROCEDURE pr_ДанныеРегионов;2
@Страна VARCHAR(20) = 'Беларусь'
AS
SELECT *
FROM Регион
WHERE Страна = @Страна
GO
/* Проверка работы хранимой процедуры pr_ДанныеРегионов;2 */
EXEC pr_ДанныеРегионов;2 -- выбираются регионы Беларуси
EXEC pr_ДанныеРегионов;2 'Россия' -- выбираются регионы России
EXEC pr_ДанныеРегионов;2 'Украина' -- выбираются регионы Украины
GO
/* Выбор регионов, связанных со страной и/или городом */
CREATE PROCEDURE pr_ДанныеРегионов;3
@Страна VARCHAR(20) = 'Беларусь',
@Город VARCHAR(20) = NULL
AS
BEGIN
SELECT *
FROM Регион
WHERE Страна = @Страна AND Город = @Город
ELSE
SELECT *
FROM Регион
WHERE Страна = @Страна
END
ELSE
SELECT *
FROM Регион
WHERE Город = @Город
GO
/* Проверка работы хранимой процедуры pr_ДанныеРегионов;3 */
EXEC pr_ДанныеРегионов;3
EXEC pr_ДанныеРегионов;3 @Город = 'Минск'
EXEC pr_ДанныеРегионов;3 DEFAULT, 'Воложин'
EXEC pr_ДанныеРегионов;3 'Россия'
EXEC pr_ДанныеРегионов;3 NULL, 'Алушта'
GO
/* Вставка новой строки в таблицу "Валюта" */
CREATE PROCEDURE pr_ВставкаВалюты
@Код CHAR(3),
@Имя VARCHAR(30),
@Курс SMALLMONEY = 1000,
@Шаг NUMERIC(10, 4) = 0.01
AS
GO
/* Проверка работы хранимой процедуры pr_ВставкаВалюты */
EXEC pr_ВставкаВалюты 'WWW', 'Валюта страны W'
EXEC pr_ВставкаВалюты 'XXX', 'Валюта страны X', 500, 1
EXEC pr_ВставкаВалюты 'YYY', 'Валюта страны Y', 250
EXEC pr_ВставкаВалюты 'ZZZ', 'Валюта страны Z', @Шаг = 50
SELECT * FROM Валюта
GO
/* Выбор курса валюты по ее коду */
CREATE PROCEDURE pr_ВыборКурсаВалюты
@Код CHAR(3),
@Курс SMALLMONEY OUTPUT
AS
IF @Код IS NOT NULL
SELECT @Курс = КурсВалюты
FROM Валюта
WHERE КодВалюты = @Код
ELSE
SELECT @Курс = КурсВалюты
FROM Валюта
WHERE КодВалюты = 'USD'
GO
/* Проверка работы хранимой процедуры pr_ВыборКурсаВалюты */
DECLARE @Code CHAR(3), @Course SMALLMONEY
SET @Code = 'EUR'
EXEC pr_ВыборКурсаВалюты @Code, @Course OUTPUT
SELECT @Code AS [Код валюты], @Course AS [Курс валюты]
EXEC pr_ВыборКурсаВалюты NULL, @Course OUTPUT
SELECT 'USD' AS [Код валюты], @Course AS [Курс валюты]
GO
/* Выбор имени клиента и ФИО руководителя по коду клиента */
CREATE PROCEDURE pr_ДанныеКлиента;1
@Код INT,
@Имя VARCHAR(40) OUTPUT,