Sql где хранятся процедуры

Просмотр определения хранимой процедуры

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

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

Разрешения

Системная хранимая процедура: sp_helptext
Необходимо быть членом роли public. Определения системных объектов видимы для всех. Определения пользовательских объектов видимы владельцу объекта и получателям любого из следующих разрешений: ALTER, CONTROL, TAKE OWNERSHIP и VIEW DEFINITION.

Системная функция: OBJECT_DEFINITION()
Определения системных объектов видимы для всех. Определения пользовательских объектов видимы владельцу объекта и получателям любого из следующих разрешений: ALTER, CONTROL, TAKE OWNERSHIP и VIEW DEFINITION. Эти разрешения неявно предоставляются членам предопределенных ролей базы данных db_owner, db_ddladminи db_securityadmin .

Представление каталога объектов: sys.sql_modules
Видимость метаданных в представлениях каталогов ограничивается защищаемыми объектами, которыми пользователь владеет или на которые ему были предоставлены разрешения. Дополнительные сведения см. в разделе Metadata Visibility Configuration.

Azure Synapse Analytics не поддерживает системную хранимую процедуру sp_helptext . Вместо нее используйте представление каталога объектов sys.sql_modules . Примеры приведены далее в этой статье.

Просмотр определения хранимой процедуры

Можно использовать один из следующих способов:

• Среда SQL Server Management Studio
• Transact-SQL

Использование среды SQL Server Management Studio

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

1. В обозреватель объектов подключитесь к экземпляру ядро СУБД, а затем разверните этот экземпляр.
2. Последовательно разверните узел Базы данных, базу данных, которой принадлежит процедура, и узел Программирование.
3. Разверните раздел Хранимые процедуры, щелкните процедуру правой кнопкой мыши, нажмите Создать скрипт для хранимой процедуры, а затем выберите один из следующих пунктов: Используя CREATE, Используя ALTER или Используя DROP и CREATE.
4. Выберите New Query Editor Window (Окно редактирования нового запроса). При этом отобразится определение процедуры.

Использование Transact-SQL

Просмотр определения процедуры в редакторе запросов

Системная хранимая процедура: sp_helptext

1. В обозреватель объектов подключитесь к экземпляру ядро СУБД.
2. На панели инструментов нажмите Создать запрос.
3. В окне запроса введите следующую инструкцию, которая использует системную хранимую процедуру sp_helptext . Измените имя базы данных и имя хранимой процедуры для ссылки на нужную базу данных и хранимую процедуру.

USE AdventureWorks2022; GO EXEC sp_helptext N'AdventureWorks2022.dbo.uspLogError'; 

Системная функция: OBJECT_DEFINITION()

1. В обозреватель объектов подключитесь к экземпляру ядро СУБД.
2. На панели инструментов нажмите Создать запрос.
3. В окне запроса введите следующие инструкции, которые используют системную функцию OBJECT_DEFINITION . Измените имя базы данных и имя хранимой процедуры для ссылки на нужную базу данных и хранимую процедуру.

USE AdventureWorks2022; GO SELECT OBJECT_DEFINITION (OBJECT_ID(N'AdventureWorks2022.dbo.uspLogError')); 

Представление каталога объектов: sys.sql_modules

1. В обозреватель объектов подключитесь к экземпляру ядро СУБД.
2. На панели инструментов нажмите Создать запрос.
3. В окне запроса введите следующие инструкции, которые используют представление каталогов sys.sql_modules . Измените имя базы данных и имя хранимой процедуры для ссылки на нужную базу данных и хранимую процедуру.

USE AdventureWorks2022; GO SELECT [definition] FROM sys.sql_modules WHERE object_id = (OBJECT_ID(N'dbo.uspLogError')); 

См. также

• Создание хранимой процедуры
• Изменение хранимой процедуры
• Удаление хранимой процедуры
• Изменение имени хранимой процедуры
• OBJECT_DEFINITION (Transact-SQL)
• sys.sql_modules (Transact-SQL)
• sp_helptext (Transact-SQL)
• OBJECT_ID (Transact-SQL)

Хранимые процедуры (ядро СУБД)

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

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

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

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

Снижение сетевого трафика между клиентами и сервером

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

Повышенная безопасность.

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

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

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

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

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

Повторное использование кода

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

Более легкое обслуживание

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

Улучшение производительности

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

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

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

Пользовательский

Определяемую пользователем процедуру можно создать в определяемой пользователем базе данных или во всех системных базах данных, кроме Resource базы данных. Процедура может быть разработана в Transact-SQL или в качестве ссылки на метод microsoft платформа .NET Framework common runtime language (CLR).

Временные процедуры

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

Системные

Системные процедуры включены в ядро СУБД. Они физически хранятся в внутренней, скрытой Resource базе данных и логически отображаются в sys схеме каждой системной и определяемой пользователем базы данных. Кроме того, msdb база данных также содержит системные хранимые процедуры в dbo схеме, которая используется для планирования оповещений и заданий. Так как системные процедуры начинаются с префикса sp_ , рекомендуется не использовать этот префикс при именовании определяемых пользователем процедур. Полный список системных процедур см. в разделе «Системные хранимые процедуры» (Transact-SQL).

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

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

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

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

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

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

Связанный контент

• Хранимые процедуры CLR
• Отложенное разрешение имен

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

— это специальный тип пакета инструкций 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.

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

Нередко операция с данными представляет набор инструкций, которые необходимо выполнить в определенной последовательности. Например, при добавлении данных покупки товара необходимо внести данные в таблицу заказов. Однако перед этим надо проверить, а есть ли покупаемый товар в наличии. Возможно, при этом понадобится проверить еще ряд дополнительных условий. То есть фактически процесс покупки товара охватывает несколько действий, которые должны выполняться в определенной последовательности. И в этом случае более оптимально будет инкапсулировать все эти действия в один объект — хранимую процедуру (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