Как правильно писать хранимые процедуры в SQL Server. Создание хранимых процедур в microsoft sql server Хранимые процедуры в бд

Хранимая процедура (англ. 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 . В данном учебнике будет рассматриваться только первый способ.

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

CREATE {PROC I PROCEDURE) proc_name [ ; number ]

[{gparameter data_type }

[“default] |

[ WITH [ ,...n ] ]

[ FOR REPLICATION ]

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

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

©result int OUTPUT) AS

SET @result=0a+0b

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

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

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

Рис. 10.2.

DECLARE @с int;

EXEC summa 10,5,@c OUTPUT;

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

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

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

WHERE BETWEEN 0FirsYear AND 0LastYear) ;

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

DECLARE 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

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

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

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

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

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

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

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

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

SELECT * FROM dbo. #Tab2 –№1

Перед первым вызовом хранимой процедуры создадим используемую в ней временную таблицу #ТаЬ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) { procedure } [

Например, удалим ранее созданную процедуру 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 – устанавливаемая опция

IF 0FirsYear>0LastYear RETURN 1 ELSE BEGIN

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

WHERE BETWEEN SFirsYear AND SLastYear);

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

DECLARE 0у int = 2000;

EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

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

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

DECLARE 0у varchar(100);

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

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

DECLARE 0у varchar(100);

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

EXEC ("SELECT * FROM dbo.Book2 WHERE ="+0y);

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

EXECUTE sp_executesql

N"SELECT * FROM dbo.Bookl WHERE =0y",

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

В Microsoft SQL Server для реализации и автоматизации своих собственных алгоритмов (расчётов ) можно использовать хранимые процедуры, поэтому сегодня мы с Вами поговорим о том, как они создаются, изменяются и удаляются.

Но сначала немного теории, чтобы Вы понимали, что такое хранимые процедуры и для чего они нужны в T-SQL.

Примечание! Начинающим программистам рекомендую следующие полезные материалы на тему T-SQL:

• Для более подробного изучения языка T-SQL также рекомендую почитать книгу — Путь программиста T-SQL. Самоучитель по языку Transact-SQL;
• Профессиональные онлайн-курсы по T-SQL

Что такое хранимые процедуры в T-SQL?

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

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

В хранимых процедурах, в отличие от функций, уже можно выполнять операции модификации данных такие как: UNSERT, UPDATE, DELETE. Также в процедурах можно использовать SQL инструкции практически любого типа, например, CREATE TABLE для создания таблиц или EXECUTE, т.е. вызов других процедур. Исключение составляет несколько типов инструкций таких как: создание или изменение функций, представлений, триггеров, создание схем и еще несколько других подобных инструкций, например, также нельзя в хранимой процедуре переключать контекст подключения к базе данных (USE).

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

Хранимые процедуры очень полезны, они помогают нам автоматизировать или упростить многие операции, например, Вам постоянно требуется формировать различные сложные аналитические отчеты с использованием сводных таблиц, т.е. оператора PIVOT. Чтобы упростить формирование запросов с этим оператором (как Вы знаете, у PIVOT синтаксис достаточно сложен ), Вы можете написать процедуру, которая будет Вам динамически формировать сводные отчеты, например, в материале «Динамический PIVOT в T-SQL » представлен пример реализации данной возможности в виде хранимой процедуры.

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

Исходные данные для примеров

Все примеры ниже будут выполнены в Microsoft SQL Server 2016 Express . Для того чтобы продемонстрировать, как работают хранимые процедуры с реальными данными, нам нужны эти данные, давайте их создадим. Например, давайте создадим тестовую таблицу и добавим в нее несколько записей, допустим, что это будет таблица, содержащая список товаров с их ценой.

Инструкция создания таблицы CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Инструкция добавления данных INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1, "Мышь", 100), (1, "Клавиатура", 200), (2, "Телефон", 400) GO --Запрос на выборку SELECT * FROM TestTable

Данные есть, теперь давайте переходить к созданию хранимых процедур.

Создание хранимой процедуры на T-SQL – инструкция CREATE PROCEDURE

Хранимые процедуры создаются с помощью инструкции CREATE PROCEDURE , после данной инструкции Вы должны написать название Вашей процедуры, затем в случае необходимости в скобочках определить входные и выходные параметры. После этого Вы пишите ключевое слово AS и открываете блок инструкций ключевым словом BEGIN, закрываете данный блок словом END. Внутри данного блока Вы пишите все инструкции, которые реализуют Ваш алгоритм или какой-то последовательный расчет, иными словами, программируете на T-SQL.

Для примера давайте напишем хранимую процедуру, которая будет добавлять новую запись, т.е. новый товар в нашу тестовую таблицу. Для этого мы определим три входящих параметра: @CategoryId – идентификатор категории товара, @ProductName — наименование товара и @Price – цена товара, данный параметр будет у нас необязательный, т.е. его можно будет не передавать в процедуру (например, мы не знаем еще цену ), для этого в его определении мы зададим значение по умолчанию. Эти параметры в теле процедуры, т.е. в блоке BEGIN…END можно использовать, так же как и обычные переменные (как Вы знаете, переменные обозначаются знаком @ ). В случае если Вам нужно указать выходные параметры, то после названия параметра указывайте ключевое слово OUTPUT (или сокращённо OUT ).

В блоке BEGIN…END мы напишем инструкцию добавления данных, а также в завершении процедуры инструкцию SELECT, чтобы хранимая процедура вернула нам табличные данные о товарах в указанной категории с учетом нового, только что добавленного товара. Также в этой хранимой процедуре я добавил обработку входящего параметра, а именно удаление лишних пробелов в начале и в конце текстовой строки с целью исключения ситуаций, когда случайно занесли несколько пробелов.

Вот код данной процедуры (его я также прокомментировал ).

Создаем процедуру CREATE PROCEDURE TestProcedure (--Входящие параметры @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Инструкции, реализующие Ваш алгоритм --Обработка входящих параметров --Удаление лишних пробелов в начале и в конце текстовой строки SET @ProductName = LTRIM(RTRIM(@ProductName)); --Добавляем новую запись INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Возвращаем данные SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

Запуск хранимой процедуры на T-SQL – команда EXECUTE

Запустить хранимую процедуру, как я уже отмечал, можно с помощью команды EXECUTE или EXEC. Входящие параметры передаются в процедуры путем простого их перечисления и указания соответствующих значений после названия процедуры (для выходных параметров также нужно указывать команду OUTPUT ). Однако название параметров можно и не указывать, но в этом случае необходимо соблюдать последовательность указания значений, т.е. указывать значения в том порядке, в котором определены входные параметры (это относится и к выходным параметрам ).

Параметры, которые имеют значения по умолчанию, можно и не указывать, это так называемые необязательные параметры.

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

1. Вызываем процедуру без указания цены EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Тестовый товар 1" --2. Вызываем процедуру с указанием цены EXEC TestProcedure @CategoryId = 1, @ProductName = "Тестовый товар 2", @Price = 300 --3. Вызываем процедуру, не указывая название параметров EXEC TestProcedure 1, "Тестовый товар 3", 400

Изменение хранимой процедуры на T-SQL – инструкция ALTER PROCEDURE

Внести изменения в алгоритм работы процедуры можно с помощью инструкции ALTER PROCEDURE . Иными словами, для того чтобы изменить уже существующую процедуру, Вам достаточно вместо CREATE PROCEDURE написать ALTER PROCEDURE, а все остальное изменять по необходимости.

Допустим, нам необходимо внести изменения в нашу тестовую процедуру, скажем, параметр @Price, т.е. цену, мы сделаем обязательным, для этого уберём значение по умолчанию, а также представим, что у нас пропала необходимость в получении результирующего набора данных, для этого мы просто уберем инструкцию SELECT из хранимой процедуры.

Изменяем процедуру ALTER PROCEDURE TestProcedure (--Входящие параметры @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Инструкции, реализующие Ваш алгоритм --Обработка входящих параметров --Удаление лишних пробелов в начале и в конце текстовой строки SET @ProductName = LTRIM(RTRIM(@ProductName)); --Добавляем новую запись INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

Удаление хранимой процедуры на T-SQL – инструкция DROP PROCEDURE

В случае необходимости можно удалить хранимую процедуру, это делается с помощью инструкции DROP PROCEDURE .

Например, давайте удалим созданную нами тестовую процедуру.

DROP PROCEDURE TestProcedure

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

У меня все, надеюсь, материал был Вам интересен и полезен, пока!

Цель работы – научиться создавать и использовать хранимые процедуры на сервере БД.

1. Проработка всех примеров, анализ результатов их выполнения в утилите SQL Server Management Studio. Проверка наличия созданных про- цедур в текущей БД.

2. Выполнение всех примеров и заданий по ходу лабораторной работы.

3. Выполнение индивидуальных заданий по вариантам.

Пояснения к выполнению работы

Для освоения программирования хранимых процедур используем при- мер базы данных c названием DB_Books , которая была создана в лабора- торной работе №1. При выполнении примеров и заданий обращайте вни- мание на соответствие названий БД, таблиц и других объектов проекта.

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

Типы хранимых процедур

Системные хранимые процедуры предназначены для выполнения раз- личных административных действий. Практически все действия по адми- нистрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.

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

Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на ло- кальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При созда- нии такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Создание, изменение хранимых процедур

Создание хранимой процедуры предполагает решение следующих за- дач: планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь; определение параметров храни- мой процедуры, хранимые процедуры могут обладать входными и выход- ными параметрами; разработка кода хранимой процедуры. Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур.

Синтаксис оператора создания новой или изменения имеющейся хранимой процедуры в обозначениях MS SQL Server:

{ CREATE | ALTER } PROC[ EDURE] имя_процедуры [ ;номер] [ { @имя_параметра тип_данных } [ VARYING ] [ = DEFAULT ] [ OUTPUT] ] [ ,... n] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [ FOR REPLICATION] AS sql_оператор [ ... n]

Рассмотрим параметры данной команды.

Используя префиксы sp_, #, ##, создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

Для передачи входных и выходных данных в создаваемой хранимой процедуре имена параметров должны начинаться с символа @. В одной хранимой процедуре можно задать множество параметров, разделенных запятыми. В теле процедуры не должны применяться локальные переменные, чьи имена совпадают с именами параметров этой процедуры. Для определения типа данных параметров хранимой процедуры подходят любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры, т.е. с указанием ключевого слова OUTPUT.

Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры. Однако это вовсе не означает, что параметр не подходит для передачи значений в хранимую процедуру. Указание ключевого слова OUTPUT предписывает серверу при выходе из хранимой процедуры присвоить текущее значение параметра локальной переменной, которая была указана при вызове процедуры в качестве значения параметра. Отметим, что при указании ключевого слова OUTPUT значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Не разрешается использование любых выражений или констант, допустимое для обычных параметров. Ключевое слово VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR. Оно определяет, что выходным параметром будет результирующее множество.

Ключевое слово DEFAULT представляет собой значение, которое будет принимать соответствующий параметр по умолчанию. Таким образом, при вызове процедуры можно не указывать явно значение соответствующего параметра.

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

Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию. Ключевое слово ENCRYPTION предписывает серверу выполнить шифрование кода хранимой процедуры, что может обеспечить защиту от использования авторских алгоритмов, реализующих работу хранимой процедуры. Ключевое слово AS размещается в начале собственно тела хранимой процедуры. В теле процедуры могут применяться практически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры. Выход из хранимой процедуры можно осуществить посредством команды RETURN.

Удаление хранимой процедуры

DROP PROCEDURE { имя_процедуры} [ ,... n]

Выполнение хранимой процедуры

Для выполнения хранимой процедуры используется команда: [ [ EXEC [ UTE] имя_процедуры [ ;номер] [ [ @имя_параметра= ] { значение | @имя_переменной} [ OUTPUT ] | [ DEFAULT ] ] [ ,... n]

Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера.

Использование ключевого слова OUTPUT при вызове процедуры раз- решается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT.

Когда же при вызове процедуры для параметра указывается ключевое слово DEFAULT, то будет использовано значение по умолчанию. Естест- венно, указанное слово DEFAULT разрешается только для тех параметров, для которых определено значение по умолчанию.

Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры. Присвоить параметру значение по умолчанию, просто пропустив его при перечислении, нельзя. Если же тре- буется опустить параметры, для которых определено значение по умолча- нию, достаточно явного указания имен параметров при вызове хранимой процедуры. Более того, таким способом можно перечислять параметры и их значения в произвольном порядке.

Отметим, что при вызове процедуры указываются либо имена пара- метров со значениями, либо только значения без имени параметра. Их комбинирование не допускается.

Использование RETURN в хранимой процедуре

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

CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

Задание 1.

EXEC Count_Books

Проверьте результат.

Пример создания процедуры c входным параметром:

CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GO

Задание 2 . Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC Count_Books_Pages 100

Проверьте результат.

Пример создания процедуры c входными параметрами:

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Задание 3. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC Count_Books_Title 100 , "П%"

Проверьте результат.

Пример создания процедуры c входными параметрами и выходным параметром:

CREATE PROCEDURE Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Задание 4. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите с помощью набора команд:

Sql> Declare @q As int EXEC Count_Books_Itogo 100, "П%", @q output select @q

Проверьте результат.

Пример создания процедуры c входными параметрами и RETURN:

CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Пушкин А.С." RETURN 1 ELSE RETURN 2

Задание 5. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_status

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

CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase = Code_purchase* 2

Задание 6. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC update_proc

Пример процедуры с входным параметром для получения всей ин- формации о конкретном авторе:

CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Authors WHERE name_author= @k

Задание 7.

EXEC select_author "Пушкин А.С." или select_author @k= "Пушкин А.С." или EXEC select_author @k= "Пушкин А.С."

Пример создания процедуры с входным параметром и значением по умолчанию для увеличения значения ключевого поля в таблице Purchases в заданное количество раза (по умолчанию в 2 раза):

CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @p

Процедура не возвращает никаких данных.

Задание 8. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

EXEC update_proc 4 или EXEC update_proc @p = 4 или EXEC update_proc --будет использовано значение по умолчанию.

Пример создания процедуры с входным и выходным параметрами. Создать процедуру для определения количества заказов, совершенных за указанный период:

CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @d1 AND @d2 SET @c = ISNULL(@c, 0 )

Задание 9. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

DECLARE @c2 INT EXEC count_purchases ’01- jun- 2006 ’, ’01- jul- 2006 ’, @c2 OUTPUT SELECT @c2

Варианты заданий к лабораторной работе №4

Общие положения. В утилите SQL Server Management Studio создать новую страницу для кода (кнопка «Создать запрос»). Программно сделать активной созданную БД DB_Books с помощью оператора Use. Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL запросу, которые были выполнены во второй лабораторной работе. Причем код SQL запросов нужно изменить таким образом, чтобы в них можно было передавать значения полей, по которым осуществляется поиск.

Например, исходное задание и запрос в лабораторной работе №2:

/*Выбрать из справочника поставщиков (таблица Deliveries) названия ком- паний, телефоны и ИНН (поля Name_company, Phone и INN), у которых название компании (поле Name_company) „ОАО МИР“.

SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = "ОАО МИР"

*/ –В данной работе будет создана процедура:

CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = @comp

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

EXEC select_name_company "ОАО МИР"

Список заданий

В утилите SQL Server Management Studio создать новую программу. Программно сделать активной индивидуальную БД, созданную в лабораторной работе №1, с помощью оператора Use. Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL запросу, которые представлены в виде отдельных заданий по вариантам.

Вариант 1

1. Вывести список сотрудников, у которых есть хотя бы один ребенок.

2. Вывести список детей, которым выдали подарки в указанный период.

3. Вывести список родителей, у которых есть несовершеннолетние дети.

4. Вывести информацию о подарках со стоимостью больше указанного числа, отсортированных по дате.

Вариант 2

1. Вывести список приборов с указанным типом.

2. Вывести количество отремонтированных приборов и общую стоимость ремонтов у указанного мастера.

3. Вывести список владельцев приборов и количество их обращений, отсортированный по количеству обращений по убыванию.

4. Вывести информацию о мастерах с разрядом больше указанного числа или с датой приема на работу меньше указанной даты.

Вариант 3

2. Вывести список кодов продаж, по которым продано цветов на сумму больше указанного числа.

3. Вывести дату продажи, сумму, продавца и цветок по указанному коду продажи.

4. Вывести список цветов и сорт для цветов с высотой больше указанного числа или цветущий.

Вариант 4

1. Вывести список лекарств с указанным показанием к применению.

2. Вывести список дат поставок, по которым продано больше указанного числа одноименного лекарства.

3. Вывести дату поставки, сумму, ФИО руководителя от поставщика и название лекарства по коду поступления больше указанного числа.

Вариант 5

2. Вывести список списанного оборудования по указанной причине.

3. Вывести дату поступления, название оборудования, ФИО ответственного и дату списания для оборудования, списанного в указанный период.

4. Вывести список оборудования с указанным типом или с датой поступления больше определенного значения

Вариант 6

1. Вывести список блюд с весом больше указанного числа.

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

3. Вывести объем продукта, название блюда, название продукта с кодом блюда от указанного начального значения по определенному конечному значению.

4. Вывести порядок приготовления блюда и название блюда с количеством углеводов больше определенного значения или количеством калорий больше указанного значения.

Вариант 7

1. Вывести список сотрудников с указанной должностью.

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

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

Вариант 8

1. Вывести список сотрудников с указанной причиной увольнения.

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

Вариант 9

1. Вывести список сотрудников, бравших отпуск указанного типа.

2. Вывести список документов с датой регистрации в указанный период.

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

4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.

Вариант 10

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

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

4. Вывести список зарегистрированных документов с указанным типом документа или с кодом документа меньше определенного значения.

Вариант 11

1. Вывести список сотрудников, назначенных на указанную должность.

2. Вывести список документов с датой регистрации в указанный период.

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

4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.

Вариант 12

3. Вывести список лиц, бравших оборудование в прокат и количество их обращений, отсортированный по количеству обращений по убыванию.

Вариант 13

1. Вывести список оборудования с указанным типом. 2. Вывести список оборудования, которое списал определенный сотрудник.

3. Вывести количество списанного оборудования, сгруппированного по типам оборудования.

4. Вывести информацию о сотрудниках с датой приема на работу больше определенной даты.

Вариант 14

1. Вывести список цветков с указанным типом листа.

2. Вывести список кодов поступлений, по которым продано цветов на суммы больше определенного значения.

3. Вывести дату поступления, сумму, названия поставщика и цветов по определенному коду поставщика.

4. Вывести список цветов и сорт для цветов с высотой больше определенного числа или цветущий.

Вариант 15

1. Вывести список клиентов, заехавших в номера в указанный период.

2. Вывести общую сумму оплат за номера для каждого клиента.

3. Вывести дату заезда, тип номера, ФИО клиентов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных клиентов в номерах определенного типа.

Вариант 16

1. Вывести список оборудования с указанным типом.

2. Вывести список оборудования, которое брал в прокат определенный клиент.

3. Вывести список лиц, бравших оборудование в прокат и количество их обращений, отсортированных по количеству обращений по убыванию.

4. Вывести информацию о клиентах, отсортированных по адресам.

Вариант 17

1. Вывести список ценностей с закупочной стоимостью больше определенного значения или сроком гарантии больше указанного числа.

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

3. Вывести сумму стоимости ценностей с кодом в указанном диапазоне.

4. Вывести список материально ответственных лиц с датой приема на работу в указанном диапазоне.

Вариант 18

1. Вывести список ремонтных работ, выполненных определенным мастером.

2. Вывести список этапов работ, входящих в работы, в названии которых встречается указанное слово.

3. Вывести сумму стоимости этапов ремонтных работ для работ с кодом в указанном диапазоне.

4. Вывести список мастеров с датой приема на работу в указанном диапазоне.

Вариант 19

1. Вывести список лекарств с определенным показанием.

2. Вывести список номеров чеков, по которым продано больше определенного числа лекарств.

3. Вывести дату продажи, сумму, ФИО кассира и лекарство по чеку с указанным номером.

4. Вывести список лекарств и единицы измерения для лекарств с количеством в упаковке больше указанного числа или кодом лекарства меньше определенного значения.

Вариант 20

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

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

4. Вывести список зарегистрированных документов с указанным типом документа или с кодом документа в определенном диапазоне.

Последнее обновление: 14.08.2017

Нередко операция с данными представляет набор инструкций, которые необходимо выполнить в определенной последовательности. Например, при добавлении покупке товара необходимо внести данные в таблицу заказов. Однако перед этим надо проверить, а есть ли покупаемый товар в наличии. Возможно, при этом понадобится проверить еще ряд дополнительных условий. То есть фактически процесс покупки товара охватывает несколько действий, которые должны выполняться в определенной последовательности. И в этом случае более оптимально будет инкапсулировать все эти действия в один объект - хранимую процедуру (stored procedure).

То есть по сути хранимые процедуры представляет набор инструкций, которые выполняются как единое целое. Тем самым хранимые процедуры позволяют упростить комплексные операции и вынести их в единый объект. Изменится процесс покупки товара, соответственно достаточно будет изменить код процедуры. То есть процедура также упрощает управление кодом.

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

И еще один важный аспект - производительность. Хранимые процедуры обычно выполняются быстрее, чем обычные SQL-инструкции. Все потому что код процедур компилируется один раз при первом ее запуске, а затем сохраняется в скомпилированной форме.

Для создания хранимой процедуры применяется команда CREATE PROCEDURE или CREATE PROC .

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

Например, пусть в базе данных есть таблица, которая хранит данные о товарах:

CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

Создадим хранимую процедуру для извлечения данных из этой таблицы:

USE productsdb; GO CREATE PROCEDURE ProductSummary AS SELECT ProductName AS Product, Manufacturer, Price FROM Products

Поскольку команда CREATE PROCEDURE должна вызываться в отдельном пакете, то после команды USE, которая устанавливает текущую базу данных, используется команда GO для определения нового пакета.

После имени процедуры должно идти ключевое слово AS.

Для отделения тела процедуры от остальной части скрипта код процедуры нередко помещается в блок BEGIN...END:

USE productsdb; GO CREATE PROCEDURE ProductSummary AS BEGIN SELECT ProductName AS Product, Manufacturer, Price FROM Products END;

После добавления процедуры мы ее можем увидеть в узле базы данных в SQL Server Management Studio в подузле Programmability -> Stored Procedures :

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

Выполнение процедуры

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

EXEC ProductSummary

Удаление процедуры

Для удаления процедуры применяется команда DROP PROCEDURE :

DROP PROCEDURE ProductSummary

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

Введение

Многие считают, что они похожи на процедуры различных (соответственно, кроме MS SQL). Пожалуй, это действительно так. У них есть схожие параметры, они могут выдавать схожие значения. Более того, в ряде случаев они соприкасаются. Например, они сочетаются с базами данных DDL и DML, а также с функциями пользователя (кодовое название - UDF).

В действительности же хранимые процедуры SQL обладают широким спектром преимуществ, которые выделяют их среди подобных процессов. Безопасность, вариативность программирования, продуктивность - все это привлекает пользователей, работающих с базами данных, все больше и больше. Пик популярности процедур пришелся на 2005-2010 годы, когда вышла программа от "Майкрософт" под названием «SQL Server Management Studio». С ее помощью работать с базами данных стало гораздо проще, практичнее и удобнее. Из года в год такой набирал популярность в среде программистов. Сегодня же является абсолютно привычной программой, которая для пользователей, «общающихся» с базами данных, встала наравне с «Экселем».

При вызове процедуры она моментально обрабатывается самим сервером без лишних процессов и вмешательства пользователя. После этого можно осуществлять любые удаление, исполнение, изменение. За все это отвечает DDL-оператор, который в одиночку совершает сложнейшие действия по обработке объектов. Причем все это происходит очень быстро, а сервер фактически не нагружается. Такая скорость и производительность позволяют очень быстро передавать большие объемы информации от пользователя на сервер и наоборот.

Для реализации данной технологии работы с информацией существует несколько языков программирования. К ним можно отнести, например, PL/SQL от Oracle, PSQL в системах InterBase и Firebird, а также классический «майкрософтовский» Transact-SQL. Все они предназначены для создания и выполнения хранимых процедур, что позволяет в крупных обработчиках баз использовать собственные алгоритмы. Это нужно и для того, чтобы те, кто осуществляет управление такой информацией, могли защитить все объекты от несанкционированного доступа сторонних лиц и, соответственно, создания, изменения или удаления тех или иных данных.

Продуктивность

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

Безопасность

Такой тип обработки информации отличается от схожих процессов тем, что он гарантирует повышенную безопасность. Это обеспечивается за счет того, что доступ других пользователей к процедурам может быть исключен целиком и полностью. Это позволит администратору проводить операции с ними самостоятельно, не опасаясь за перехват информации или несанкционированный доступ к базе данных.

Передача данных

Связь между хранимой процедурой SQL и клиентским приложением заключается в использовании параметров и возвращаемых значениях. Последним не обязательно передавать данные в хранимую процедуру, однако эта информация (в основном по запросу пользователя) и перерабатывается для SQL. После того как хранимая процедура завершила свою работу, она отсылает пакеты данных обратно (но, опять же, по желанию) к вызвавшему его приложению, используя различные методы, с помощью которых может быть осуществлен как вызов хранимой процедуры SQL, так и возврат, например:

Передача данных с помощью параметра типа Output;

Передача данных с помощью оператора возврата;

Передача данных с помощью оператора выбора.

А теперь разберемся, как же выглядит этот процесс изнутри.

1. Создание EXEC-хранимой процедуры в SQL

Вы можете создать процедуру в MS SQL (Managment Studio). После того как создастся процедура, она будет перечислена в программируемый узел базы данных, в которой процедура создания выполняется оператором. Для выполнения хранимые процедуры SQL используют EXEC-процесс, который содержит имя самого объекта.

При создании процедуры ее название появляется первым, после чего производится один или несколько параметров, присвоенных ему. Параметры могут быть необязательными. После того как параметр(ы), то есть тело процедуры, будут написаны, нужно провести некоторые необходимые операции.

Дело в том, что тело может иметь локальные переменные, расположенные в ней, и эти переменные являются локальными также по отношению к процедурам. Другими словами, их можно рассматривать только внутри тела процедуры Microsoft SQL Server. Хранимые процедуры в таком случае считаются локальными.

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

Тело процедуры может иметь любой вид из например, такие как создание таблицы, вставки одного или нескольких строк таблицы, установление типа и характера базы данных и так далее. Тем не менее тело процедуры ограничивает выполнение некоторых операций в нем. Некоторые из важных ограничений перечислены ниже:

Тело не должно создавать какой-либо другой хранимой процедуры;

Тело не должно создать ложное представление об объекте;

Тело не должно создавать никаких триггеров.

2. Установка переменной в тело процедуры

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

Иногда можно заметить, что несколько переменных установлены в одной строке, и каждый переменный параметр отделяется запятой. Также обратите внимание, что переменная имеет префикс @. В теле процедуры вы можете установить переменную, куда вы хотите. К примеру, переменная @NAME1 может объявлена ​​ближе к концу тела процедуры. Для того чтобы присвоить значение объявленной переменной используется набор личных данных. В отличие от ситуации, когда объявлено более одной переменной в одной строке, в такой ситуации используется только один набор личных данных.

Часто пользователи задают вопрос: «Как назначить несколько значений в одном операторе в теле процедуры?» Что ж. Вопрос интересный, но сделать это гораздо проще, чем вы думаете. Ответ: с помощью таких пар, как «Select Var = значение». Вы можете использовать эти пары, разделяя их запятой.

В самых различных примерах люди показывают создание простой хранимой процедуры и выполнение ее. Однако процедура может принимать такие параметры, что вызывающий ее процесс будет иметь значения, близкие к нему (но не всегда). Если они совпадают, то внутри тела начинаются соответствующие процессы. Например, если создать процедуру, которая будет принимать город и регион от вызывающего абонента и возвращать данные о том, сколько авторов относятся к соответствующим городу и региону. Процедура будет запрашивать таблицы авторов базы данных, к примеру, Pubs, для выполнения этого подсчета авторов. Чтобы получить эти базы данных, к примеру, Google загружает сценарий SQL со страницы SQL2005.

В предыдущем примере процедура принимает два параметра, которые на английском языке условно будут называться @State и @City. Тип данных соответствует типу, определенному в приложении. Тело процедуры имеет внутренние переменные @TotalAuthors (всего авторов), и эта переменная используется для отображения их количества. Далее появляется раздел выбора запроса, который все подсчитывает. Наконец, подсчитанное значение выводится в окне вывода с помощью оператора печати.

Как в SQL выполнить хранимую процедуру

Есть два способа выполнения процедуры. Первый путь показывает, передавая параметры, как разделенный запятыми список выполняется после имени процедуры. Допустим, мы имеем два значения (как в предыдущем примере). Эти значения собираются с помощью переменных параметров процедуры @State и @City. В этом способе передачи параметров важен порядок. Такой метод называется порядковая передача аргументов. Во втором способе параметры уже непосредственно назначены, и в этом случае порядок не важен. Этот второй метод известен как передача именованных аргументов.

Процедура может несколько отклоняться от типичной. Все так же, как и в предыдущем примере, но только здесь параметры сдвигаются. То есть параметр @City хранится первым, а @State хранится рядом со значением по умолчанию. Параметр по умолчанию выделяется обычно отдельно. Хранимые процедуры SQL проходят как просто параметры. В этом случае, при условии, параметр «UT» заменяет значение по умолчанию «СА». Во втором исполнении проходит только одно значение аргумента для параметра @City, и параметр @State принимает значение по умолчанию «СА». Опытные программисты советуют, чтобы все переменные по умолчанию располагались ближе к концу списка параметров. В противном случае исполнение не представляется возможным, и тогда вы должны работать с передачей именованных аргументов, что дольше и сложнее.

4. Хранимые процедуры SQL Server: способы возврата

Существует три важных способа отправки данных в вызванной хранимой процедуре. Они перечислены ниже:

Возврат значения хранимой процедуры;

Выход параметра хранимых процедур;

Выбор одной из хранимых процедур.

4.1 Возврат значений хранимых процедур SQL

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

Теперь давайте посмотрим, как выполнить процедуру и вывести значение, возвращаемое ей. Выполнение процедуры требует установления переменной и печати, которая проводится после всего этого процесса. Обратите внимание, что вместо оператора печати вы можете использовать Select-оператор, например, Select @RetValue, а также OutputValue.

4.2 Выход параметра хранимых процедур SQL

Ответное значение может быть использовано для возврата одной переменной, что мы и видели в предыдущем примере. Использование параметра Output позволяет процедуре отправить одно или несколько значений переменных для вызывающей стороны. Выходной параметр обозначается как раз-таки этим ключевым словом «Output» при создании процедуры. Если параметр задан в качестве выходного параметра, то объект процедуры должен присвоить ему значение. Хранимые процедуры SQL, примеры которых можно увидеть ниже, в таком случае возвращаются с итоговой информацией.

В нашем примере будет два выходных имени: @TotalAuthors и @TotalNoContract. Они указываются в списке параметров. Эти переменные присваивают значения внутри тела процедуры. Когда мы используем выходные параметры, вызывающий абонент может видеть значение, установленное внутри тела процедуры.

Кроме того, в предыдущем сценарии две переменные объявляются, чтобы увидеть значения, которые установливают хранимые процедуры MS SQL Server в выходном параметре. Тогда процедура выполняется путем подачи нормального значения параметра «CA». Следующие параметры являются выходными и, следовательно, объявленные переменные передаются в установленном порядке. Обратите внимание, что при прохождении переменных выходное ключевое слово также задается здесь. После того, как процедура выполнена успешно, значения, возвращаемые с помощью выходных параметров, выводятся на окно сообщений.

4.3 Выбор одной из хранимых процедур SQL

Эта техника используется для возврата набора значений в виде таблицы данных (RecordSet) к вызывающей хранимой процедуре. В этом примере SQL хранимая процедура с параметрами @AuthID запрашивает таблицу «Авторы» путем фильтрации возвращаемых записей с помощью этого параметра @AuthId. Оператор Select решает, что должно быть возвращено вызывающему хранимой процедуры. При выполнении хранимой процедуры AuthId передается обратно. Такая процедура здесь всегда возвращает только одну запись или же вообще ни одной. Но хранимая процедура не имеет каких-либо ограничений на возвращение более одной записи. Нередко можно встретить примеры, в которых возвращение данных с использованием избранных параметров с участием вычисленных переменных происходит путем предоставления нескольких итоговых значений.

В заключение

Хранимая процедура является довольно серьезным программным модулем, возвращающим или передающим, а также устанавливающим необходимые переменные благодаря клиентскому приложению. Поскольку хранимая процедура выполняется на сервере сама, обмена данными в огромных объемах между сервером и клиентским приложением (для некоторых вычислений) можно избежать. Это позволяет снижать нагрузки на сервера SQL, что, конечно же, идет на руку их держателям. Одним из подвидов являются хранимые процедуры T SQL, однако их изучение необходимо тем, кто занимается созданием внушительных баз данных. Также существует большое, даже огромное количество нюансов, которые могут быть полезны при изучении хранимых процедур, однако это нужно больше для тех, кто планирует плотно заняться программированием, в том числе профессионально.