Наименование 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

Ç

 

Раздел II. Создание и работа с хранимыми (на сервере) процедурами.

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

Заголовок хранимой процедуры содержит название процедуры и, возможно, ее идентификационный номер, а также может включать в себя как входные параметры (передающие значения в процедуру), так и выходные параметры (завершающиеся служебным словом 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

IF @Страна IS NOT NULL

BEGIN

IF @Город IS NOT NULL

SELECT *

FROM Регион

WHERE Страна = @Страна AND Город = @Город

ELSE

SELECT *

FROM Регион

WHERE Страна = @Страна

END

ELSE

IF @Город IS NOT NULL

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

INSERT INTO Валюта

VALUES(@Код, @Имя, @Шаг, @Курс)

GO

/* Проверка работы хранимой процедуры pr_ВставкаВалюты */

EXEC pr_ВставкаВалюты 'WWW', 'Валюта страны W'

EXEC pr_ВставкаВалюты 'XXX', 'Валюта страны X', 500, 1

EXEC pr_ВставкаВалюты 'YYY', 'Валюта страны Y', 250

EXEC pr_ВставкаВалюты 'ZZZ', 'Валюта страны Z', @Шаг = 10

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

SET @Code = 'USD'

SELECT @Code AS [Код валюты], @Course AS [Курс валюты]

GO

 

/* Выбор имени клиента и ФИО руководителя по коду клиента */

CREATE PROCEDURE pr_ДанныеКлиента;1

@Код INT,

@Имя VARCHAR(40) OUTPUT,

@ФИО 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

IF @Страна IS NOT NULL

IF @Город IS NOT NULL

SELECT *

INTO ##Регион

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

SET @Code = 'USD'

SELECT @Code AS [Код валюты], @Course AS [Курс валюты]

EXEC pr_ВыборКурсаВалюты DEDAULT, @Course OUTPUT

SELECT @Code 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 2005. После этого нужно импортировать созданный DLL-файл в базу данных SQL Server. И, наконец, необходимо связать созданную сборку с хранимой процедурой или функцией, которая позволит вызывать функции DLL-файла (см. [3]).