@ФИО VARCHAR(60) OUTPUT
AS
SELECT @Имя = ИмяКлиента, @ФИО = ФИОРуководителя
FROM Клиент
WHERE КодКлиента = @Код
GO
/* Проверка работы хранимой процедуры pr_ДанныеКлиента;1 */
DECLARE @Code INT, @Name VARCHAR(40), @FIO VARCHAR(60)
SET @Code = 5
EXEC pr_ДанныеКлиента;1 @Code, @Name OUTPUT, @FIO OUTPUT
SELECT @Name AS [Имя клиента], @FIO AS [ФИО руководителя]
GO
/* Выбор данных о клиенте по любому из трех параметров */
CREATE PROCEDURE pr_ДанныеКлиента;2
@Код INT = NULL OUTPUT,
@Имя VARCHAR(40) = NULL OUTPUT,
@ФИО VARCHAR(60) = NULL OUTPUT
AS
IF @Код IS NOT NULL
SELECT @Имя = ИмяКлиента, @ФИО = ФИОРуководителя
FROM Клиент
WHERE КодКлиента = @Код
ELSE
IF @Имя IS NOT NULL
SELECT @Код = КодКлиента, @ФИО = ФИОРуководителя
FROM Клиент
WHERE ИмяКлиента = @Имя
ELSE
IF @ФИО IS NOT NULL
SELECT @Код = КодКлиента, @Имя = ИмяКлиента
FROM Клиент
WHERE ФИОРуководителя = @ФИО
GO
/* Проверка работы хранимой процедуры pr_ДанныеКлиента;2 */
DECLARE @Code INT, @Name VARCHAR(40), @FIO VARCHAR(60)
SET @Code = 1
EXEC pr_ДанныеКлиента;2 @Code, @Name OUTPUT, @FIO OUTPUT
SELECT @Code AS [Код клиента], @Name AS [Имя клиента],
@FIO AS [ФИО руководителя]
SET @Code = NULL
SET @Name = 'ИП "Темп"'
EXEC pr_ДанныеКлиента;2 @Code OUTPUT, @Name, @FIO OUTPUT
SELECT @Code AS [Код клиента], @Name AS [Имя клиента],
@FIO AS [ФИО руководителя]
SET @Code = NULL
SET @Name = NULL
SET @FIO = 'Прокушев Станислав Игоревич'
EXEC pr_ДанныеКлиента;2 @Code OUTPUT, @Name OUTPUT, @FIO
SELECT @Code AS [Код клиента], @Name AS [Имя клиента],
@FIO AS [ФИО руководителя]
GO
/* Процедура создания и наполнения данными глобальной временной таблицы */
CREATE PROCEDURE pr_КопияРегионов
@Страна VARCHAR(20) = 'Беларусь',
@Город VARCHAR(20) = 'Минск'
AS
SELECT *
FROM Регион
WHERE Страна = @Страна AND Город = @Город
GO
/* Проверка работы хранимой процедуры pr_КопияРегионов */
EXEC pr_КопияРегионов
SELECT * FROM ##Регион
DROP TABLE ##Регион
EXEC pr_КопияРегионов @Город = 'Полоцк'
SELECT * FROM ##Регион
DROP TABLE ##Регион
EXEC pr_КопияРегионов DEFAULT, 'Воложин'
SELECT * FROM ##Регион
DROP TABLE ##Регион
EXEC pr_КопияРегионов 'Россия', 'Королев'
SELECT * FROM ##Регион
GO
Для внесения изменений в существующую хранимую процедуру используется та же команда, что и для ее создания, с тем лишь отличием, что вместо зарезервированного слова CREATE используется слово ALTER (см. [1], стр. 1216).
Изменим, например, текст уже существующей хранимой процедуры pr_ВыборКурсаВалюты (выбор курса валюты по ее коду):
ALTER PROCEDURE pr_ВыборКурсаВалюты
@Код CHAR(3) = 'USD',
@Курс SMALLMONEY OUTPUT
AS
SELECT @Курс = КурсВалюты
FROM Валюта
WHERE КодВалюты = @Код
GO
/* Проверка работы скорректированной хранимой процедуры pr_ВыборКурсаВалюты */
DECLARE @Code CHAR(3), @Course SMALLMONEY
SET @Code = 'EUR'
EXEC pr_ВыборКурсаВалюты @Code, @Course OUTPUT
SELECT @Code AS [Код валюты], @Course AS [Курс валюты]
EXEC pr_ВыборКурсаВалюты @Курс = @Course OUTPUT
SELECT 'USD' AS [Код валюты], @Course AS [Курс валюты]
EXEC pr_ВыборКурсаВалюты DEDAULT, @Course OUTPUT
SELECT 'USD' AS [Код валюты], @Course AS [Курс валюты]
GO
Предоставление, например членам роли Гл.бухгалтер, полномочий на выполнение хранимой процедуры pr_ВыборКурсаВалюты производится следующим образом (см. [1], стр. 254):
GRANT EXECUTE
ON pr_ВыборКурсаВалюты TO [Гл.бухгалтер] WITH GRANT OPTION
GO
Для удаления хранимой процедуры используется команда, имеющая следующий синтаксис (см. [1], стр. 1216; [2]):
DROP PROCEDURE { procedure } [ ,...n ]
Например, удалим первые три из рассмотренных выше хранимых процедур:
DROP PROCEDURE pr_ДанныеРегионов, pr_ВставкаВалюты
GO
Примечание. Первые три процедуры образуют группу одноименных процедур т.е. процедур, имеющих одинаковое имя и различающихся лишь идентификационными номерами. В команде DROP PROCEDURE всегда указывается имя группы процедур, но не отдельные процедуры, входящие в группу.
Необходимо отметить, что, по аналогии с созданием временных таблиц, можно создавать также временные хранимые процедуры, обладающие той же областью видимости и тем же временем жизни. Имена локальных временных хранимых процедур начинаются символом «#», а глобальных – двумя такими символами.
SQL Server снабжен также несколькими сотнями так называемых системных хранимых процедур с широким спектром применения, которые хранятся в системной базе данных master и имеют названия, включающие в себя префикс sp_ (см. [1], стр. 1201).
В SQL Server реализована возможность использования среды Microsoft .NET Framework и ее языков программирования C#, VB.NET, J# для разработки хранимых процедур, пользовательских функций и др. объектов базы данных. Для того, чтобы создать такой объект, нужно сначала создать DLL-файл в среде разработки Visual Studio. После этого нужно импортировать созданный DLL-файл в базу данных SQL Server. И, наконец, необходимо связать созданную сборку с хранимой процедурой или функцией, которая позволит вызывать функции DLL-файла (см. [3]).
Помимо использования встроенных функций, пользователи могут создавать свои собственные функции. Эти функции в отличие от встроенных являются обычными объектами базы данных, т.е. имеют своего владельца, который может предоставлять другим пользователям право на их вызов. Имеется три типа определяемых пользователем функций.
1).Функции типа Scalar. Функции этого типа являются наиболее привычными и возвращают скалярное значение любого из типов данных, поддерживаемых сервером, за исключением text, ntext, image, timestamp, table и cursor. Команда создания функции имеет следующий синтаксис (см. [1], стр. 1115):
CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS scalar_return_data_type
[ WITH < function_option> [ [,] ...n] ]
[ AS ]
BEGIN function_body RETURN scalar_expression END
/* Функция типа Scalar, возвращающая курс валюты по ее коду */
CREATE FUNCTION fn_ВыборКурсаВалюты
(@Код CHAR(3))
RETURNS SMALLMONEY
BEGIN
DECLARE @Курс SMALLMONEY --объявляем локальную переменную @Курс
SELECT @Курс = КурсВалюты
FROM Валюта
WHERE КодВалюты = @Код
RETURN @Курс --функция возвращает значение переменной @Курс
END
GO
/* Проверка работы функции func_ВыборКурсаВалюты */
DECLARE @Code CHAR(3)
SET @Code = 'USD'
SELECT @Code AS [Код валюты], dbo.fn_ВыборКурсаВалюты(@Code)
AS [Курс валюты]
GO
Примечание. При вызове скалярной функции, ее имя должно быть дополнено именем владельца или именем пользователя, которому предоставлено право на вызов этой функции.
2). Функции типа Inline Table-valued. Эти функции всегда возвращают значения типа данных table (таблица). Тело функции всегда состоит из одной команды SELECT. Особенностью функций этого типа является то, что код функции при выполнении программы вставляется непосредственно в исполняемый набор команд, т.е. происходит не вызов функции, а встраивание. Команда создания функции имеет следующий синтаксис (см. [1], стр. 1117):
CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS TABLE
[ WITH < function_option > [ [,] ...n ] ]
[ AS ]
RETURN [ ( ] select-stmt [ ) ]
/* Функция типа Inline Table-valued, возвращающая заказы по валюте*/
CREATE FUNCTION fn_ВыборЗаказовПоВалюте
(@Код CHAR(3))
RETURNS TABLE
AS RETURN
SELECT Заказ.*, Товар.КодВалюты
FROM Заказ INNER JOIN Товар
ON Заказ.КодТовара = Товар.КодТовара
WHERE Товар.КодВалюты = @Код
GO
/* Проверка работы функции func_ВыборЗаказовПоВалюте */
SELECT * FROM fn_ВыборЗаказовПоВалюте('USD')
GO
3). Функции типа Multi-statement Table-valued. Эти функции, как и функции предыдущего типа, возвращают значение типа table, однако тело функции этого типа может состоять более чем из одной команды, что дает возможность использовать в теле функции транзакции, курсоры, вызывать хранимые процедуры и т.д. Команда создания функции имеет следующий синтаксис (см. [1], стр. 1118):
CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ WITH < function_option > [ [,] ...n ] ]
[ AS ]
BEGIN function_body RETURN END
< function_option > ::= { ENCRYPTION | SCHEMABINDING }
< table_type_definition > :: = ( { column_definition | table_constraint } [ ,...n ]
/* Функция типа Multi-statement Table-valued, возвращающая таблицу с новым столбцом */
CREATE FUNCTION fn_ВыборРегионовСтраны
(@Страна VARCHAR(20))
RETURNS @CountryRegions TABLE (
КодРегиона INT PRIMARY KEY,
Страна VARCHAR(20) NOT NULL,
Город VARCHAR(20) NULL,
ОбластнойЦентр BIT NULL)
BEGIN
-- Объявляем локальную переменную @rowset типа таблица
DECLARE @rowset TABLE (
КодРегиона INT PRIMARY KEY,
Страна VARCHAR(20) NOT NULL,
Область VARCHAR(20) NULL,
Город VARCHAR(20) NULL,
ОбластнойЦентр BIT DEFAULT 0 NULL)
-- Заносим данные в локальную переменную @rowset