Хранимые процедуры. 
Базы данных

Хранимая процедура (англ. stored procedure) — это именованный программный объект БД. В SQL Server есть хранимые процедуры нескольких типов.

Системные хранимые процедуры (англ. system stored procedure) поставляются разработчиками СУБД и используются для выполнения действий с системным каталогом или получения системной информации. Их названия обычно начинаются с префикса «sp_». Запускаются хранимые процедуры всех типов с помощью команды EXECUTE, которую можно сократить до ЕХЕС. Например, хранимая процедура sp_helplogins, запущенная без параметров, формирует два отчета об именах учетных записей (англ. logins) и соответствующих им в каждой БД пользователях (англ. users).

EXEC sp_helplogins;

Чтобы дать представление о действиях, выполняемых с помощью системных хранимых процедур, в табл. 10.6 приведены некоторые примеры. Всего же системных хранимых процедур в SQL Server более тысячи.

Таблица 10.6

Примеры системных хранимых процедур SQL Server

Пользователю доступно создание хранимых процедур в пользовательских БД и в БД для временных объектов. В последнем случае хранимая процедура будет являться временной. Так же как в случае с временными таблицами, название временной хранимой процедуры должно начинаться с префикса «#», если это локальная временная хранимая процедура, или с «##» — если глобальная. Локальная временная процедура может использоваться только в рамках соединения, в котором ее создали, глобальная — и в рамках других соединений.

Программируемые объекты SQL Server могут создаваться как с использованием средств Transact-SQL, так и с помощью сборок (англ. assembly) в среде CRL (Common Language Runtime) платформы Microsoft.Net Framework [24]. В данном учебнике будет рассматриваться только первый способ.

Для создания хранимых процедур используется оператор CREATE PROCEDURE (можно сократить до PROC), формат которого приведен ниже:

CREATE {PROC I PROCEDURE) [schema_name.]proc_name [; number ].

[{gparameter [type_schema_name.] data_type }.

[VARYING] ["default] [OUT|OUTPUT]|[READONLY].

] [ ,…n ].

[ WITH [ ,…n ] ].

[ FOR REPLICATION ].

AS {[ BEGIN ] sql_statement [;] [ …n ] [ END ] }.

[;].

где.

:=.

[ENCRYPTION][RECOMPILE][EXECUTE AS Clause].

Если хранимая процедура (или триггер, функция, представление) создается с опцией ENCRYPTION, ее код преобразуется таким образом, что текст становится нечитаемым. В то же время, как отмечается в [20], используемый алгоритм перенесен из ранних версий SQL Server и не может рассматриваться в качестве надежного алгоритма защиты — существуют утилиты, позволяющие быстро выполнить обратное преобразование.

Опция RECOMPILE указывает на то, что при каждом вызове процедуры система будет перекомпилировать текст. В обычном случае скомпилированная при первом запуске процедура сохраняется в кэше, что позволяет увеличить быстродействие.

EXECUTE AS определяет контекст безопасности, в котором должна быть выполнена процедура. Далее указывается одно из значений f CALLER | SELF | OWNER | ' user_name'). CALLER является значением по умолчанию и означает, что код будет выполняться в контексте безопасности пользователя, вызывающего этот модуль. Соответственно, пользователь должен иметь разрешения не только на сам программируемый объект, но и на другие затрагиваемые им объекты БД. EXECUTE AS SELF означает использование контекста пользователя, создающего или изменяющего программируемый объект. OWNER указывает, что код будет выполняться в контексте текущего владельца процедуры. Если для нее не определен владелец, то подразумевается владелец схемы, к которой она относится. EXECUTE AS 'user_name' позволяет явно указать имя пользователя (в одинарных кавычках).

Для процедуры могут указываться параметры. Это локальные переменные, используемые для передачи значений в процедуру. Если параметр объявлен с ключевым словом OUTPUT (или сокращенно OUT), он является выходным: заданное ему в процедуре значение после ее окончания может быть использовано вызвавшей процедуру программой. Ключевое слово READONLY означает, что значение параметра не может быть изменено внутри хранимой процедуры.

Параметрам могут быть назначены значения, но умолчанию, которые будут использованы, если при вызове процедуры значение параметра не будет указано в явном виде. Рассмотрим пример:

CREATE PROC surma (@а int, @b int=0,.

(c)result int OUTPUT) AS.

SET @result=0a+0b.

Мы создали процедуру с тремя параметрами, причем у параметра @b значение по умолчанию =0, а параметр @result — выходной: через него возвращается значение в вызвавшую программу. Выполняемые действия достаточно просты — выходной параметр получает значение суммы двух входных.

При работе в SQL Server Management Studio созданную хранимую процедуру можно найти в разделе программируемых объектов БД (англ. Programmability) в подразделе для хранимых процедур (рис. 10.2).

При вызове процедуры в качестве входных параметров можно использовать как переменные, так и константы. Рассмотрим два примера. В первом входные параметры процедуры явно заданы константами, для выходного параметра в вызове указано ключевое слово OUTPUT. Во втором варианте в качестве первого входного параметра используется значение переменной, а для второго параметра с помощью ключевого слова DEFAULT указано, что должно быть использовано значение по умолчанию:

Рис. 10.2. Созданная хранимая процедура.

вар. 1.

DECLARE @с int;

EXEC summa 10,5,@c OUTPUT;

PRINT 0c; - будет выведено 15.

вар. 2.

DECLARE Gi int = 5;

— при вызове используем значение по умолчанию.

EXEC summa Gi, DEFAULT, 0с OUTPUT;

PRINT 0c; - будет выведено 5.

Рассмотрим теперь пример с анализом кода возврата, с которым заканчивается процедура. Пусть надо подсчитать, сколько в таблице Bookl книг, изданных в заданном диапазоне лет. При этом если начальный год оказался больше конечного, процедура возвращает «1» и подсчет не проводит, иначе — считаем количество книг и возвращаем 0:

CREATE PROC dbo. rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS.

IF 0FirsYear>0LastYear RETURN 1.

ELSE BEGIN.

SET @result= (SELECT COUNT (*) FROM dbo.Bookl.

WHERE [Year] BETWEEN 0FirsYear AND 0LastYear) ;

RETURN 0;

END.

Рассмотрим вариант вызова данной процедуры, в котором код возврата сохраняется в целочисленной переменной 0ret, после чего анализируется его значение (в данном случае это будет 1). Используемая в операторе PRINT функция CAST служит для преобразования значения целочисленной переменной Gres к строковому типу:

DECLARE 0ret int, Gres int.

EXEC Gret = rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT 'Начальный год больше конечного'.

ELSE.

PRINT 'Число книг '+ CAST (Gres as varchar (20)).

Хранимые процедуры могут не только считывать данные из таблицы, но и изменять данные и даже создавать таблицы и ряд других объектов БД.

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

Следующий пример иллюстрирует как эти возможности, так и вопросы, связанные с областью видимости временных объектов. Приведенная ниже хранимая процедура проверяет наличие временной таблицы #ТаЬ2; если этой таблицы нет, то создает ее. После этого в таблицу #ТаЬ2 заносятся значения двух столбцов, и содержимое таблицы выводится оператором SELECT:

CREATE PROC My_Procl (@id int, @name varchar (30)).

AS.

BEGIN.

IF OBJECT_ID ('tempdb.dbo.#Tab21) IS NULL.

CREATE TABLE dbo.#Tab2 (id int, name varchar (30));

INSERT INTO dbo.#Tab2 (id, name) VALUES (0id, 0name).

SELECT * FROM dbo. #Tab2 -№ 1.

END.

Перед первым вызовом хранимой процедуры создадим используемую в ней временную таблицу #ТаЬ2. Обратите внимание на оператор ЕХЕС. В предыдущих примерах параметры передавались в процедуру «по позиции», а в данном случае используется другой формат передачи параметров — «по имени», явно указывается имя параметра и его значение:

CREATE TABLE dbo.#Tab2 (id int, name varchar (30));

EXEC My_Procl 0name='lvan', 0id=2;

SELECT * FROM dbo.#Tab2; -№ 2.

В приведенном примере оператор SELECT отработает дважды: первый раз — внутри процедуры, второй раз — из вызывающего фрагмента кода (отмечен комментарием «№ 2»).

Перед вторым вызовом процедуры удалим временную таблицу #ТаЬ2. Тогда одноименная временная таблица будет создана из хранимой процедуры:

DROP TABLE dbo.#Tab2;

EXEC My_Procl 0name='Ivan', 0id=2;

SELECT * FROM dbo.#Tab2; -№ 2.

В этом случае данные выведет только оператор SELECT, находящийся внутри процедуры (с комментарием «Ха 1»). Выполнение SELECT «№ 2» приведет к ошибке, так как созданная в хранимой процедуре временная таблица на момент возврата из процедуры будет уже удалена из базы tempdb.

Удалить хранимую процедуру можно с помощью оператора DROP PROCEDURE. Его формат представлен ниже. Одним оператором можно удалить несколько хранимых процедур, перечислив их через запятую:

DROP (PROC I PROCEDURE) { [schema_name.] procedure } [.

…n ].

Например, удалим ранее созданную процедуру summa:

DROP PROC summa;

Внести изменения в существующую процедуру (а фактически — переопределить ее) можно с помощью оператора ALTER PROCEDURE (допу стимо сокращение PROC). За исключением ключевого слова ALTER, формат оператора практически совпадает с форматом CREATE PROCEDURE. Например, изменим процедуру dbo. rownum, установив ей опцию выполнения в контексте безопасности владельца:

ALTER PROC dbo. rownum (SFirsYear int,.

SLastYear int, Sresult int OUTPUT).

WITH EXECUTE AS Owner — устанавливаемая опция.

AS.

IF 0FirsYear>0LastYear RETURN 1 ELSE BEGIN.

SET 0result= (SELECT COUNT (*) FROM dbo.Bookl.

WHERE [Year] BETWEEN SFirsYear AND SLastYear);

RETURN 0;

END.

В некоторых случаях может возникнуть необходимость в динамическом формировании команды и выполнении ее на сервере БД. Эта задача также может решаться с помощью оператора ЕХЕС. В приведенном ниже примере выполняется выборка записей из таблицы Bookl по условию равенства атрибута Year значению, задаваемому с помощью переменной:

DECLARE 0у int = 2000;

EXEC ('SELECT * FROM dbo. Bookl WHERE [Year] = '+@y) ;

Выполнение динамически сформированных инструкций создает предпосылки для реализации компьютерных атак типа «SQL-инъекция» (англ. SQL injection). Суть атаки заключается в том, что нарушитель внедряет в динамически формируемый запрос собственный код на SQL. Обычно это происходит, когда подставляемые параметры берут из результатов ввода данных пользователем.

Несколько изменим предыдущий пример:

DECLARE 0у varchar (100);

SET 0у='2ООО'; - это мы получили от пользователя.

EXEC ('SELECT * FROM dbo. Book2 WHERE [Year]='+0y);

Если предположить, что присваиваемое в операторе SET строковое значение мы получили от пользователя (неважно каким образом, например, через веб-приложение), то пример иллюстрирует «штатное» поведение нашего кода.

В следующем примере нарушитель добавил к введенному параметру код на SQL. В результате мы не только вывели нужные строки из таблицы, но и удалили все ее содержимое:

DECLARE 0у varchar (100);

SET 0у='2000; DELETE FROM dbo. Book2'; - инъекция.

EXEC ('SELECT * FROM dbo. Book2 WHERE [Year]='+0y);

В [20] рекомендуется по возможности использовать в подобных случаях системную хранимую процедуру sp_executcsql, которая позволяет контролировать тип параметров, что является одним из барьеров на пути SQLинъекций. Не рассматривая в подробностях ее формат, разберем пример, аналогичный представленному ранее:

EXECUTE sp_executesql.

N’SELECT * FROM dbo. Bookl WHERE [Year]=0y',.

N'@y int;

6 у = 2000;

Здесь явно указывается тип используемого в запросе параметра, и SQL Server при выполнении будет его контролировать. Буква «N» перед кавычками указывает, что это литерная константа в формате Unicode, как того требует процедура. Параметру можно присвоить не только постоянное значение, но и значение другой переменной.