Что такое процедуры в sql

Хранимые процедуры (компонент Database Engine)

Хранимая процедура в SQL Server представляет собой группу из одного или нескольких операторов Transact-SQL или ссылку на метод среды CLR Microsoft .NET Framework. Процедуры аналогичны конструкциям в других языках программирования, поскольку обеспечивают следующее:

• обрабатывают входные параметры и возвращают вызывающей программе значения в виде выходных параметров;
• содержат программные инструкции, которые выполняют операции в базе данных, включая вызов других процедур;
• возвращают значение состояния вызывающей программе, таким образом передавая сведения об успешном или неуспешном завершении (и причины последнего).

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

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

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

Большая безопасность
Многие пользователи и клиентские программы могут выполнять операции с базовыми объектами базы данных посредством процедур, даже если у них нет прямых разрешений на доступ к базовым объектам. Процедура проверяет, какие из процессов и действий могут выполняться, и защищает базовые объекты базы данных. Это устраняет необходимость предоставлять разрешения на уровне индивидуальных объектов и упрощает формирование уровней безопасности.

Предложение EXECUTE AS может быть указано в инструкции CREATE PROCEDURE, чтобы разрешить олицетворение других пользователей или разрешить пользователям или приложениям выполнять определенные действия баз данных без необходимости иметь прямые разрешения на базовые объекты и команды. Например, для некоторых действий, таких как TRUNCATE TABLE, предоставить разрешения нельзя. Чтобы выполнить инструкцию TRUNCATE TABLE, у пользователя должны быть разрешения ALTER на нужную таблицу. Предоставление разрешений ALTER не всегда подходит, так как фактические разрешения пользователя выходят за пределы возможности усечения таблицы. Заключив инструкцию TRUNCATE TABLE в модуль и указав, что этот модуль должен выполняться от имени пользователя, у которого есть разрешения на изменение таблицы, можно предоставить разрешение на усечение таблицы пользователю с разрешением EXECUTE для этого модуля.

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

Использование параметров в процедурах помогает предотвратить атаки типа «инъекция SQL». Так как входные данные параметра обрабатываются как литеральное значение, а не как исполняемый код, злоумышленнику сложнее вставить команду в инструкции Transact-SQL внутри процедуры и нарушить безопасность.

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

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

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

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

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

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

Пользовательские процедуры
Пользовательские процедуры могут быть созданы в пользовательской базе данных или любых системных базах данных, за исключением базы данных Resource . Процедура может быть разработана в Transact-SQL или в качестве ссылки на метод Microsoft платформа .NET Framework CLR.

Временные процедуры
Временные процедуры — это один из видов пользовательских процедур. Временные процедуры схожи с постоянными процедурами, за исключением того, что они хранятся в базе данных tempdb. Существует два вида временных процедур: локальные и глобальные. Они отличаются друг от друга именами, видимостью и доступностью. Имена локальных временных процедур начинаются с одного знака диеза (#); они видны только текущему соединению пользователя и удаляются, когда закрывается соединение. Имена глобальных временных процедур начинаются с двух знаков диеза (##); они видны любому пользователю и удаляются после окончания последнего сеанса, использующего процедуру.

Системные функции
Системные процедуры включены в SQL Server. Физически они хранятся во внутренней скрытой базе данных Resource . Логически они отображаются в схеме sys каждой системной и пользовательской базы данных. В дополнение к этому, база данных msdb также содержит системные хранимые процедуры в схеме dbo . Эти процедуры используются для планирования предупреждений и заданий. Поскольку названия системных процедур начинаются с префикса sp_ , этот префикс не рекомендуется использовать при создании пользовательских процедур. Полный список системных процедур см. в разделе Системные хранимые процедуры (Transact-SQL).

SQL Server поддерживает системные процедуры, предоставляющие интерфейс от SQL Server до внешних программ для различных действий по обслуживанию. Эти расширенные процедуры имеют префикс xp_. Полный список расширенных процедур см. в разделе Общие расширенные хранимые процедуры (Transact-SQL).

Расширенные пользовательские процедуры
Расширенные процедуры позволяют создавать внешние подпрограммы на языке программирования, таком как C. Эти процедуры представляют собой библиотеки DLL, которые экземпляр SQL Server может динамически загружать и запускать.

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

Связанные задачи

Описание задачи	Раздел
Описывает создание хранимой процедуры.	Создание хранимой процедуры
Описывает изменение хранимой процедуры.	Изменение хранимой процедуры
Описывает удаление хранимой процедуры.	Удаление хранимой процедуры
Описывает выполнение хранимой процедуры.	Выполнение хранимой процедуры
Описывает предоставление разрешений на хранимую процедуру.	Предоставление разрешений на хранимую процедуру
Описывает возврат данных из хранимой процедуры в приложение.	Возврат данных из хранимой процедуры
Описывает перекомпиляцию хранимой процедуры.	Перекомпиляция хранимой процедуры
Описывает переименование хранимой процедуры.	Изменение имени хранимой процедуры
Описывает просмотр определения хранимой процедуры.	Просмотр определения хранимой процедуры
Описывает просмотр зависимостей хранимой процедуры.	Просмотр зависимостей хранимой процедуры
Описывается использование параметров в хранимой процедуре.	Параметры

SQL-Ex blog

Хранимые процедуры SQL: входные и выходные параметры, типы, обработка ошибок и кое-что еще

Добавил Sergey Moiseenko on Суббота, 25 февраля. 2023

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

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

Что необходимо для выполнения примеров

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

Что такое хранимая процедура SQL?

В простейшем виде хранимая процедура — это не более чем набор операторов Transact-SQL (T-SQL), который хранится в базе данных и может выполняться при вызове хранимой процедуры по имени. Имя присваивается хранимой процедуре при ее создании. Для краткости хранимую процедуру часто называют хп (SP).

Особенности хранимых процедур

• Хранимые процедуры могут принимать входные параметры.
• Хранимые процедуры могут возвращать выходные параметры.
• Хранимые процедуры содержат программные операторы.
• Хранимые процедуры могут возвращать значение состояния, показывающее успешность или сбой (и почему он произошел).
• Нет ограничения на размер хранимой процедуры
• Имеется четыре типа хранимых процедур:
  1. Определяемые пользователем
  2. Временные
  3. Системные
  4. Расширенные определяемые пользователем

Определения четырех типов хранимых процедур

Определяемая пользователем хранимая процедура

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

Временная хранимая процедура

Временная хранимая процедура — это вид пользовательской хранимой процедуры, которая очень похожа на временные таблицы. Временные хранимые процедуры хранятся в базе данных «tempdb», видной на вкладке «System Databases». Эти временные хранимые процедуры могут использоваться как локальные или глобальные.

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

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

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

Системные хранимые процедуры

Вы обнаружите, что довольно часто используете системные хранимые процедуры. Полезно познакомиться с ними, чтобы не изобретать колесо. Эти хранимые процедуры включены по умолчанию в установку SQL Server и всегда будут начинаться с префикса «sys.sp_». Вам могут пригодиться самые распространенные системные хранимые процедуры, включая sys.sp_addUser, sys.sp_addLogin, sys.sp_addRole, sys.sp_change_users_login и т.д.

1. Databases (базы данных)
2. System Databases (системные базы данных)
3. Tempdb
4. Programmability (программирование)
5. Stored Procedures (хранимые процедуры)
6. System Stored Procedures (системные хранимые процедуры)

Расширенные хранимые процедуры пользователя

Расширенные хранимые процедуры пользователя используются для создания внешних процедур на языках типа C, C#, VB и т.д. Они реализуются как DLL, которые SQL Server может загрузить и выполнять динамически.

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

Зачем использовать хранимые процедуры

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

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

Мы можем предоставить пользователям разрешение на использование (вызов) хранимой процедуры или же запретить пользователям ее вызывать. Замечание: пользователь не должен иметь разрешение или доступ к таблице, чтобы вызвать хранимую процедуру, которая будет менять данные в этой таблице. Это позволит пользователям добавлять данные в таблицу для создания записей данных, не имея к ним доступа на просмотр, удаление или изменение любых данных в таблице, помимо тех, на которые вы дали им разрешения.

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

Создание простой хранимой процедуры

Теперь, когда мы знаем немного больше о преимуществах хранимых процедур, давайте создадим базовый тестовый пример для работы. В коде примера, приведенного ниже, мы создаем хранимую процедуру с именем uspGetEmployees. Я уверен, что вы можете догадаться, что подразумевает часть «Get Employees», но что такое «usp» в имени? Вы можете именовать хранимые процедуры как вам нравится, но отраслевым стандартом является использование соглашения, которое мы здесь применили. «usp» — это сокращение от «User Stored Procedure» (пользовательская хранимая процедура).

USE [AdventureWorks2019] 
GO 
CREATE PROCEDURE uspGetEmployees 
AS 
SELECT 
BusinessEntityID 
, FirstName 
, LastName 
FROM Person.Person; 
GO

Видно, что блок кода выше очень похож на создание представления (VIEW) в SQL Server. Вы просто предваряете оператор SELECT текстом «CREATE PROCEDURE «.

Замечание. При создании хранимой процедуры вы можете сократить слово «PROCEDURE» до «PROC»; любой вариант будет работать.

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

При выполнении хранимой процедуры вам просто нужно напечатать команду EXECUTE (или EXEC) с последующим именем хранимой процедуры, как в примере ниже.

EXEC uspGetEmployees;

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

Изменение/модификация хранимой процедуры

Вы можете использовать команду «ALTER PROC» или «ALTER PROCEDURE» для изменения функциональности хранимой процедуры. Скажем, мы хотим также получать отчество из таблицы «Person.Person» в базе данных AdventureWorks. Вместо создания новой хранимой процедуры мы можем модифицировать имеющуюся, как в примере ниже.

ALTER PROC uspGetEmployees 
AS 
SELECT 
BusinessEntityID 
, FirstName 
, MiddleName 
, LastName 
FROM Person.Person; 
GO

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

EXEC uspGetEmployees;

Видно, что единственным отличием в результатах этого множества и предыдущего является наличие отчества.

ОК, довольно просто. Давайте продолжим. Теперь мы добавим предложение WHERE и будем использовать предикат IS NOT NULL для фильтрации сотрудников, у которых присутствует отчество (не стоит NULL).

ALTER PROC uspGetEmployees 
AS 
SELECT 
BusinessEntityID 
, FirstName 
, MiddleName 
, LastName 
FROM Person.Person 
WHERE MiddleName IS NOT NULL; 
GO

Давайте опять выполним эту процедуру и посмотрим на результаты.

EXEC uspGetEmployees;

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

Удаление хранимой процедуры в SQL подобно удалению таблицы, представления и т.д. Вы просто вызываете команду «DROP PROCEDURE» или «DROP PROC» с именем процедуры.

DROP PROC uspGetEmployees;

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

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

Что такое входной параметр? В простейшем виде это переменная. Когда мы добавляем переменную к хранимой процедуре, мы считаем ее входным параметром, а не переменной, хотя это одно и то же. Просто считайте это соглашением о наименовании, чтобы отделить одно от другого.

Когда мы добавляем входной параметр в хранимую процедуру, она помещается после команды «CREATE PROC» и выше команды «AS». И, как и для любой переменной, имя параметра должно начинаться с символа @ и последующим типом данных. В следующем примере мы изменяем нашу хранимую процедуру, добавляя переменную и присваивая ей значение по умолчанию.

ALTER PROCEDURE uspGetEmployees 
@lastname VARCHAR(40) = 'abel' 
AS 
SELECT 
BusinessEntityID 
, FirstName 
, LastName 
FROM Person.Person 
WHERE LastName = @lastname; 
GO

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

EXEC uspGetEmployees;

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

EXEC uspGetEmployees 'Akers';

Что если вы не уверены, как пишется имя? Хорошо, тогда опять изменим нашу хранимую процедуру. Теперь мы заменим равенство » sql»>ALTER PROCEDURE uspGetEmployees
@lastname VARCHAR(40) = ‘abel’
AS
SELECT
BusinessEntityID
, FirstName
, LastName
FROM Person.Person
WHERE LastName LIKE @lastname;
GO

Это позволит нам использовать подстановочные знаки в параметре и вернуть, например, каждого, чья фамилия начинается с «Ab».

EXEC uspGetEmployees 'Ab%';

Хранимые процедуры с несколькими входными параметрами

Добавление нескольких параметров следует тем же принципам, что и команда с единственным параметром, только они должны разделяться запятой. В примере ниже мы добавляем параметр «FirstName» и ссылку на него в предложении WHERE. Мы начинаем с удаления значений по умолчанию, которые присваивались параметрам в предыдущих примерах. Значения параметров будут передаваться пользователем при вызове хранимой процедуры.

ALTER PROCEDURE uspGetEmployees 
@lastname VARCHAR(40) 
, @firstname VARCHAR(40) 
AS 
SELECT 
BusinessEntityID 
, FirstName 
, LastName 
FROM Person.Person 
WHERE LastName LIKE @lastname AND FirstName LIKE @firstname; 
GO

Теперь при выполнении хранимой процедуры нам нужно включать в вызов значения обоих параметров. В этом примере мы вернем все строки, у которых фамилия начинается с «Ab», а имя — с «K».

EXEC uspGetEmployees 'Ab%', 'K%';

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

EXEC uspGetEmployees @FirstName = 'Kim', @LastName = 'Ab%';

Обычно вы не будете этого делать. Но если вы не уверены в порядке перечисления параметров, вы можете использовать этот метод, чтобы гарантировать получение желаемых результатов. В противном случае, если вы поменяете порядок параметров при вызове хранимой процедуры без указания имен параметров, запрос не вернет правильный результат. Поскольку сначала будет выполняться поиск по фамилии, а затем по имени — в порядке нашего примера, то запрос ничего не вернет, т.к. нет сотрудника в таблице с фамилией Kim и именем Abercrombie.

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

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

1) CREATE PROC myRowCount 
2) @lname VARCHAR(40), 
3) @numrows INT = 0 OUTPUT 
4) AS 
5) SELECT LastName 
6) FROM Person.Person 
7) WHERE LastName LIKE @lname 
 SET @numrows = @@ROWCOUNT;

1. Мы создаем новую хранимую процедуру с именем «myRowCount».
2. Добавляем входной параметр с именем «@lname» и типом данных «VARCHAR(40)».
3. Теперь давайте добавим выходной параметр с именем «@numrows», т.к. мы хотим подсчитать число строк, возвращаемых хранимой процедурой. Тип данных переменной — INT, значение по умолчанию — 0, и нам необходимо указать, что это выходной параметр, с помощью ключевого слова OUTPUT. Это даст знать SQL, что эта переменная будет использоваться для вывода данных.
4. Ключевое слово «AS» говорит создать хранимую процедуру, содержащую последующий код.
5. Наш основной оператор SELECT.
6. Предложение FROM указывает таблицы, откуда будут извлекаться данные.
7. Предложение WHERE устанавливает фильтры для оператора SELECT.
8. На этой строке мы устанавливаем (присваиваем) для «@numrows» значение «@@ROWCOUNT». Когда вы видите двойной символ @, это признак системной переменной, которых имеется несколько. Познакомиться с системными переменными можно в документации. Поскольку @@ROWCOUNT получает значение при каждом выполнении запроса, мы захватим число, сгенерированное при выполнении нашей хранимой процедуры, и загрузим его в выходной параметр @numrows.

1) DECLARE @retrows INT 
2) EXEC myRowCount 'B%', @numrows = @retrows OUTPUT 
3) SELECT @retrows AS 'Rows';

1. Здесь мы объявляем переменную, чтобы принять значение из выходного параметра процедуры.
   В этом примере мы называем новую переменную «@retrows», сокращение от «return rows». Мы должны назначить ей тип данных, который соответствует выходной переменной, которую мы создали в хранимой процедуре.
2. Здесь мы выполняем нашу хранимую процедуру с помощью команды «EXEC myRowCount», и нам нужно предоставить значение входному параметру. В данном примере мы хотим вернуть все строки, для которых фамилия начинается с «B». Далее на той же строке мы хотим получить значение в нашу переменную «@numrows», которую мы создали в хранимой процедуре, и передать это значение в нашу новую переменную, которую мы описали в операторе DECLARE выше. Опять же нам нужно добавить ключевое слово OUTPUT, чтобы напомнить компилятору об этой переменной.
3. Теперь мы создаем последний оператор SELECT, в котором получаем значение (число строк) из переменной «@retrows» и именуем результирующий набор «Rows».

Обработка ошибок в хранимых процедурах

Здесь мы опять изменим хранимую процедуру uspGetEmployees. С помощью функции @@ROWCOUNT, о которой говорилось в предыдущем разделе, мы будем возвращать сообщение «No Records Found», если ничего не будет возвращено. Это упрощенный, но эффективный взгляд на обработку ошибок в хранимых процедурах.

Здесь мы выполним хранимую процедуру дважды. Сначала вернем все строки, фамилии которых начинаются с «Ak»; потом выполним хранимую процедуру, которая вернет сообщение об ошибке. Но первое, что нужно сделать, — это изменить процедуру.

ALTER PROCEDURE uspGetEmployees 
@lastname VARCHAR(40) 
AS 
SELECT 
BusinessEntityID 
, FirstName 
, LastName 
FROM Person.Person 
WHERE LastName LIKE @lastname 
IF @@ROWCOUNT < 1
BEGIN 
RAISERROR('No Records Found', 10,1) 
END; 
GO

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

EXEC uspGetEmployees 'Ak%';

Теперь выполним ту же процедуру с именем, которого нет в таблице Person.Person.

EXEC uspGetEmployees 'zz%';

Несмотря на то, что это был примитивный пример, он даст вам направление работы в тестовом и рабочем окружении. Экспериментируйте и вносите изменения. Делайте обработку ошибок интуитивно понятной.

Безопасность хранимых процедур

• Абстракция: Пользователи могут выполнять сложные запросы, не зная структуры лежащих в основе таблиц или других объектов базы данных.
• Шифрование: Мы можете зашифровать код в хранимой процедуре после ее создания.
• Предотвращение SQL-инъекции: Все передаваемые в хранимую процедуру параметры могут быть проверены до передачи их в таблицу или другой объект базы данных.
• Согласованное манипулирование данными: Хранимая процедура выполняет всякий раз один и тот же код в одном и том же порядке.
• Контроль выполнения: Вы можете установить разрешения на хранимую процедуру, позволяя только определенным пользователям ли группам выполнять ее.
• Обработка ошибок: Хранимые процедуры обеспечивают возможность использовать последовательную и эффективную обработку ошибок и создание отчетов.

Ссылки по теме:

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

SQL — Урок 15. Хранимые процедуры. Часть 1.

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

CREATE PROCEDURE имя_процедуры (параметры) begin операторы end

Параметры это те данные, которые мы будем передавать процедуре при ее вызове, а операторы — это собственно запросы. Давайте напишем свою первую процедуру и убедимся в ее удобстве. В уроке 10, когда мы добавляли новые записи в БД shop, мы использовали стандартный запрос на добавление вида:

INSERT INTO customers (name, email) VALUE (‘Иванов Сергей’, ‘sergo@mail.ru’);

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

CREATE PROCEDURE ins_cust(n CHAR(50), e CHAR(50)) begin insert into customers (name, email) value (n, e); end

Обратите внимание, как задаются параметры: необходимо дать имя параметру и указать его тип, а в теле процедуры мы уже используем имена параметров. Один нюанс. Как вы помните, точка с запятой означает конец запроса и отправляет его на выполнение, что в данном случае неприемлемо. Поэтому, прежде, чем написать процедуру необходимо переопределить разделитель с ; на «//», чтобы запрос не отправлялся раньше времени. Делается это с помощью оператора DELIMITER // :

Таким образом, мы указали СУБД, что выполнять команды теперь следует после //. Следует помнить, что переопределение разделителя осуществляется только на один сеанс работы, т.е. при следующем сеансе работы с MySql разделитель снова станет точкой с запятой и при необходимости его придется снова переопределять. Теперь можно разместить процедуру:

CREATE PROCEDURE ins_cust(n CHAR(50), e CHAR(50)) begin insert into customers (name, email) value (n, e); end //

Итак, процедура создана. Теперь, когда нам понадобится ввести нового покупателя нам достаточно ее вызвать, указав необходимые параметры. Для вызова хранимой процедуры используется оператор CALL , после которого указывается имя процедуры и ее параметры. Давайте добавим нового покупателя в нашу таблицу Покупатели (customers):

call ins_cust(‘Сычов Валерий’, ‘valera@gmail.ru’)//

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

Появился, процедура работает, и будет работать всегда, пока мы ее не удалим с помощью оператора DROP PROCEDURE название_процедуры .

Как было сказано в начале урока, процедуры позволяют объединить последовательность запросов. Давайте посмотрим, как это делается. Помните в уроке 11 мы хотели узнать, на какую сумму нам привез товар поставщик «Дом печати»? Для этого нам пришлось использовать вложенные запросы, объединения, вычисляемые столбцы и представления. А если мы захотим узнать, на какую сумму нам привез товар другой поставщик? Придется составлять новые запросы, объединения и т.д. Проще один раз написать хранимую процедуру для этого действия.

Казалось бы, проще всего взять уже написанные в уроке 11 представление и запрос к нему, объединить в хранимую процедуру и сделать идентификатор поставщика (id_vendor) входным параметром, вот так:

CREATE PROCEDURE sum_vendor(i INT) begin CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=i); SELECT SUM(summa) FROM report_vendor; end //

Но так процедура работать не будет. Все дело в том, что в представлениях не могут использоваться параметры . Поэтому нам придется несколько изменить последовательность запросов. Сначала мы создадим представление, которое будет выводить идентификатор поставщика (id_vendor), идентификатор продукта (id_product), количество (quantity), цену (price) и сумму (summa) из трех таблиц Поставки (incoming), Журнал поставок (magazine_incoming), Цены (prices):

CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming;

А потом создадим запрос, который просуммирует суммы поставок интересующего нас поставщика, например, с id_vendor=2:

SELECT SUM(summa) FROM report_vendor WHERE id_vendor=2;

Вот теперь мы можем объединить два этих запроса в хранимую процедуру, где входным параметром будет идентификатор поставщика (id_vendor), который будет подставляться во второй запрос, но не в представление:

CREATE PROCEDURE sum_vendor(i INT) begin CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

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

call sum_vendor(1)// call sum_vendor(2)// call sum_vendor(3)//

Как видите, процедура срабатывает один раз, а затем выдает ошибку, говоря нам, что представление report_vendor уже имеется в БД. Так происходит потому, что при обращении к процедуре в первый раз, она создает представление. При обращении во второй раз, она снова пытается создать представление, но оно уже есть, поэтому и появляется ошибка. Чтобы избежать этого возможно два варианта.

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

DROP PROCEDURE sum_vendor// DROP VIEW report_vendor// CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming// CREATE PROCEDURE sum_vendor(i INT) begin SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

call sum_vendor(1)// call sum_vendor(2)// call sum_vendor(3)//

Второй вариант — прямо в процедуре дописать команду, которая будет удалять представление, если оно существует:

CREATE PROCEDURE sum_vendor(i INT) begin DROP VIEW IF EXISTS report_vendor; CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

Перед использованием этого варианта не забудьте удалить процедуру sum_vendor, а затем проверить работу:

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

Онлайн-курс. Освойте востребованную профессию с зарплатой от 70 000 руб в месяц!

Теперь нажмите кнопку, что бы не забыть адрес и вернуться к нам снова.

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

— это специальный тип пакета инструкций Transact-SQL, созданный, используя язык SQL и процедурные расширения. Основное различие между пакетом и хранимой процедурой состоит в том, что последняя сохраняется в виде объекта базы данных. Иными словами, хранимые процедуры сохраняются на стороне сервера, чтобы улучшить производительность и постоянство выполнения повторяемых задач.

Компонент Database Engine поддерживает хранимые процедуры и системные процедуры. Хранимые процедуры создаются таким же образом, как и все другие объекты баз данных, т.е. при помощи языка DDL. Системные процедуры предоставляются компонентом Database Engine и могут применяться для доступа к информации в системном каталоге и ее модификации.

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

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

Хранимые процедуры можно также использовать для следующих целей:

• управления авторизацией доступа;
• для создания журнала логов о действиях с таблицами баз данных.

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

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

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

Хранимые процедуры создаются посредством инструкции CREATE PROCEDURE, которая имеет следующий синтаксис:

Параметр schema_name определяет имя схемы, которая назначается владельцем созданной хранимой процедуры. Параметр proc_name определяет имя хранимой процедуры. Параметр @param1 является параметром процедуры (формальным аргументом), чей тип данных определяется параметром type1. Параметры процедуры являются локальными в пределах процедуры, подобно тому, как локальные переменные являются локальными в пределах пакета. Параметры процедуры — это значения, которые передаются вызывающим объектом процедуре для использования в ней. Параметр default1 определяет значение по умолчанию для соответствующего параметра процедуры. (Значением по умолчанию также может быть NULL.)

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

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

Предложение EXECUTE AS определяет контекст безопасности, в котором должна исполняться хранимая процедура после ее вызова. Задавая этот контекст, с помощью Database Engine можно управлять выбором учетных записей пользователей для проверки полномочий доступа к объектам, на которые ссылается данная хранимая процедура.

По умолчанию использовать инструкцию CREATE PROCEDURE могут только члены предопределенной роли сервера sysadmin и предопределенной роли базы данных db_owner или db_ddladmin. Но члены этих ролей могут присваивать это право другим пользователям с помощью инструкции GRANT CREATE PROCEDURE.

В примере ниже показано создание простой хранимой процедуры для работы с таблицей Project:

USE SampleDb; GO CREATE PROCEDURE IncreaseBudget (@percent INT=5) AS UPDATE Project SET Budget = Budget + Budget * @percent/100;

Как говорилось ранее, для разделения двух пакетов используется инструкция GO. Инструкцию CREATE PROCEDURE нельзя объединять с другими инструкциями Transact-SQL в одном пакете. Хранимая процедура IncreaseBudget увеличивает бюджеты для всех проектов на определенное число процентов, определяемое посредством параметра @percent. В процедуре также определяется значение числа процентов по умолчанию (5), которое применяется, если во время выполнения процедуры этот аргумент отсутствует.

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

В отличие от основных хранимых процедур, которые всегда сохраняются в текущей базе данных, возможно создание временных хранимых процедур, которые всегда помещаются во временную системную базу данных tempdb. Одним из поводов для создания временных хранимых процедур может быть желание избежать повторяющегося исполнения определенной группы инструкций при соединении с базой данных. Можно создавать локальные или глобальные временные процедуры. Для этого имя локальной процедуры задается с одинарным символом # (#proc_name), а имя глобальной процедуры — с двойным (##proc_name).

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

Жизненный цикл хранимой процедуры состоит из двух этапов: ее создания и ее выполнения. Каждая процедура создается один раз, а выполняется многократно. Хранимая процедура выполняется посредством инструкции EXECUTE пользователем, который является владельцем процедуры или обладает правом EXECUTE для доступа к этой процедуре. Инструкция EXECUTE имеет следующий синтаксис:

За исключением параметра return_status, все параметры инструкции EXECUTE имеют такое же логическое значение, как и одноименные параметры инструкции CREATE PROCEDURE. Параметр return_status определяет целочисленную переменную, в которой сохраняется состояние возврата процедуры. Значение параметру можно присвоить, используя или константу (value), или локальную переменную (@variable). Порядок значений именованных параметров не важен, но значения неименованных параметров должны предоставляться в том порядке, в каком они определены в инструкции CREATE PROCEDURE.

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

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

USE SampleDb; EXECUTE IncreaseBudget 10;

Инструкция EXECUTE в этом примере выполняет хранимую процедуру IncreaseBudget, которая увеличивает бюджет всех проектов на 10%.

В примере ниже показано создание хранимой процедуры для обработки данных в таблицах Employee и Works_on:

USE SampleDb; GO CREATE PROCEDURE ModifyEmpId (@oldId INTEGER, @newId INTEGER) AS UPDATE Employee SET WHERE UPDATE Works_on SET EmpId = @newId WHERE EmpId = @oldId;

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

В примере ниже показано использование в хранимой процедуре предложения OUTPUT:

USE SampleDb; GO CREATE PROCEDURE DeleteEmployee @empId INT, @counter INT OUTPUT AS SELECT @counter = COUNT(*) FROM Works_on WHERE EmpId = @empId DELETE FROM Employee WHERE DELETE FROM Works_on WHERE EmpId = @empId;

Данную хранимую процедуру можно запустить на выполнение посредством следующих инструкций:

DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT; PRINT N'Удалено сотрудников: ' + convert(nvarchar(30), @quantityDeleteEmployee);

Эта процедура подсчитывает количество проектов, над которыми занят сотрудник с табельным номером @empId, и присваивает полученное значение параметру ©counter. После удаления всех строк для данного табельного номера из таблиц Employee и Works_on вычисленное значение присваивается переменной @quantityDeleteEmployee.

Значение параметра возвращается вызывающей процедуре только в том случае, если указана опция OUTPUT. В примере выше процедура DeleteEmployee передает вызывающей процедуре параметр @counter, следовательно, хранимая процедура возвращает значение системе. Поэтому параметр @counter необходимо указывать как в опции OUTPUT при объявлении процедуры, так и в инструкции EXECUTE при ее вызове.

Предложение WITH RESULTS SETS инструкции EXECUTE

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

Следующие два примера помогут объяснить это предложение. Первый пример является вводным примером, который показывает, как может выглядеть результат, когда опущено предложение WITH RESULTS SETS:

USE SampleDb; GO CREATE PROCEDURE EmployeesInDept (@dept CHAR(4)) AS SELECT Id, LastName FROM Employee WHERE DepartamentNumber IN ( SELECT @dept FROM Department GROUP BY Number);

Процедура EmployeesInDept — это простая процедура, которая отображает табельные номера и фамилии всех сотрудников, работающих в определенном отделе. Номер отдела является параметром процедуры, и его нужно указать при ее вызове. Выполнение этой процедуры выводит таблицу с двумя столбцами, заголовки которых совпадают с наименованиями соответствующих столбцов таблицы базы данных, т.е. Id и LastName. Чтобы изменить заголовки столбцов результата (а также их тип данных), в SQL Server 2012 применяется новое предложение WITH RESULTS SETS. Применение этого предложения показано в примере ниже:

USE SampleDb; EXEC EmployeesInDept 'd1' WITH RESULT SETS (([Id] INT NOT NULL, [Фамилия] CHAR(20) NOT NULL));

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

Как можно видеть, запуск хранимой процедуры с использованием предложения WITH RESULT SETS в инструкции EXECUTE позволяет изменить наименования и тип данных столбцов результирующего набора, выдаваемого данной процедурой. Таким образом, эта новая функциональность предоставляет большую гибкость в исполнении хранимых процедур и помещении их результатов в новую таблицу.

Изменение структуры хранимых процедур

Компонент Database Engine также поддерживает инструкцию ALTER PROCEDURE для модификации структуры хранимых процедур. Инструкция ALTER PROCEDURE обычно применяется для изменения инструкций Transact-SQL внутри процедуры. Все параметры инструкции ALTER PROCEDURE имеют такое же значение, как и одноименные параметры инструкции CREATE PROCEDURE. Основной целью использования этой инструкции является избежание переопределения существующих прав хранимой процедуры.

Компонент Database Engine поддерживает тип данных CURSOR. Этот тип данных используется для объявления курсоров в хранимых процедурах. — это конструкция программирования, применяемая для хранения результатов запроса (обычно набора строк) и для предоставления пользователям возможности отображать этот результат построчно.

Для удаления одной или группы хранимых процедур используется инструкция DROP PROCEDURE. Удалить хранимую процедуру может только ее владелец или члены предопределенных ролей db_owner и sysadmin.

Хранимые процедуры и среда CLR

SQL Server поддерживает общеязыковую среду выполнения CLR (Common Language Runtime), которая позволяет разрабатывать различные объекты баз данных (хранимые процедуры, определяемые пользователем функции, триггеры, определяемые пользователем статистические функции и пользовательские типы данных), применяя языки C# и Visual Basic. Среда CLR также позволяет выполнять эти объекты, используя систему общей среды выполнения.

Среда CLR разрешается и запрещается посредством опции clr_enabled системной процедуры sp_configure, которая запускается на выполнение инструкцией RECONFIGURE. В примере ниже показано, как можно с помощью системной процедуры sp_configure разрешить использование среды CLR:

USE SampleDb; EXEC sp_configure 'clr_enabled',1 RECONFIGURE

Для создания, компилирования и сохранения процедуры с помощью среды CLR требуется выполнить следующую последовательность шагов в указанном порядке:

1. Создать хранимую процедуру на языке C# или Visual Basic, а затем скомпилировать ее, используя соответствующий компилятор.
2. Используя инструкцию CREATE ASSEMBLY, создать соответствующий выполняемый файл.
3. Сохранить процедуру в виде объекта сервера, используя инструкцию CREATE PROCEDURE.
4. Выполнить процедуру, используя инструкцию EXECUTE.

На рисунке ниже показана графическая схема ранее изложенных шагов. Далее приводится более подробное описание этого процесса.

Сначала создайте требуемую программу в какой-либо среде разработки, например Visual Studio. Скомпилируйте готовую программу в объектный код, используя компилятор C# или Visual Basic. Этот код сохраняется в файле динамической библиотеки (.dll), который служит источником для инструкции CREATE ASSEMBLY, создающей промежуточный выполняемый код. Далее выполните инструкцию CREATE PROCEDURE, чтобы сохранить выполняемый код в виде объекта базы данных. Наконец, запустите процедуру на выполнение, используя уже знакомую нам инструкцию EXECUTE.

В примере ниже показан исходный код хранимой процедуры на языке C#:

using System.Data.SqlClient; using Microsoft.SqlServer.Server; public partial class StoredProcedures < [SqlProcedure] public static int CountEmployees() < int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) as 'Количество сотрудников' " + "from Employee"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; >>

В этой процедуре реализуется запрос для подсчета числа строк в таблице Employee. В директивах using в начале программы указываются пространства имен, требуемые для ее выполнения. Применение этих директив позволяет указывать в исходном коде имена классов без явного указания соответствующих пространств имен. Далее определяется класс StoredProcedures, для которого применяется атрибут SqlProcedure, который информирует компилятор о том, что этот класс является хранимой процедурой. Внутри кода класса определяется метод CountEmployees(). Соединение с системой баз данных устанавливается посредством экземпляра класса SqlConnection. Чтобы открыть соединение, применяется метод Open() этого экземпляра. А метод CreateCommand() позволяет обращаться к экземпляру класса SqlCommnd, которому передается нужная SQL-команда.

В следующем фрагменте кода:

cmd.CommandText = "select count(*) as 'Количество сотрудников' " + "from Employee";

используется инструкция SELECT для подсчета количества строк в таблице Employee и отображения результата. Текст команды указывается, присваивая свойству CommandText переменной cmd экземпляр, возвращаемый методом CreateCommand(). Далее вызывается метод ExecuteScalar() экземпляра SqlCommand. Этот метод возвращает скалярное значение, которое преобразовывается в целочисленный тип данных int и присваивается переменной rows.

Теперь вы можете скомпилировать этот код, используя среду Visual Studio. Я добавил этот класс в проект с именем CLRStoredProcedures, поэтому Visual Studio скомпилирует одноименную сборку с расширением *.dll. В примере ниже показан следующий шаг в создании хранимой процедуры: создание выполняемого кода. Прежде чем выполнять код в этом примере, необходимо узнать расположение скомпилированного dll-файла (обычно находится в папке Debug проекта).

USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM 'D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll' WITH PERMISSION_SET = SAFE

Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, для которого можно создавать хранимые процедуры среды CLR, определяемые пользователем функции и триггеры. Эта инструкция имеет следующий синтаксис:

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

Предложение WITH PERMISSION_SET является очень важным предложением инструкции CREATE ASSEMBLY и всегда должно указываться. В нем определяется набор прав доступа, предоставляемых коду сборки. Набор прав SAFE является наиболее ограничивающим. Код сборки, имеющий эти права, не может обращаться к внешним системным ресурсам, таким как файлы. Набор прав EXTERNAL_ACCESS позволяет коду сборки обращаться к определенным внешним системным ресурсам, а набор прав UNSAFE предоставляет неограниченный доступ к ресурсам, как внутри, так и вне системы базы данных.

Чтобы сохранить информацию о коде сборке, пользователь должен иметь возможность выполнить инструкцию CREATE ASSEMBLY. Владельцем сборки является пользователь (или роль), исполняющий эту инструкцию. Владельцем сборки можно сделать другого пользователя, используя предложение AUTHORIZATION инструкции CREATE SCHEMA.

Компонент Database Engine также поддерживает инструкции ALTER ASSEMBLY и DROP ASSEMBLY. Инструкция ALTER ASSEMBLY используется для обновления сборки до последней версии. Эта инструкция также добавляет или удаляет файлы, связанные с соответствующей сборкой. Инструкция DROP ASSEMBLY удаляет указанную сборку и все связанные с ней файлы из текущей базы данных.

В примере ниже показано создание хранимой процедуры на основе управляемого кода, реализованного ранее:

USE SampleDb; GO CREATE PROCEDURE CountEmployees AS EXTERNAL NAME CLRStoredProcedures.StoredProcedures.CountEmployees

Инструкция CREATE PROCEDURE в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME. Этот параметр указывает, что код создается средой CLR. Имя в этом предложении состоит из трех частей:

assembly_name.class_name.method_name

• assembly_name — указывает имя сборки;
• class_name — указывает имя общего класса;
• method_name — необязательная часть, указывает имя метода, который задается внутри класса.

Выполнение процедуры CountEmployees показано в примере ниже:

USE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Вернет 7

Инструкция PRINT возвращает текущее количество строк в таблице Employee.