Рассматривается ситуация, когда хранимые процедуры могут ухудшить производительность запросов.
При компиляции хранимых процедур в MS SQL Server 2000 хранимые процедуры помещаются в процедурный кэш, что может способствовать увеличению производительности при их выполнении за счет исключения необходимости в синтаксическом разборе, оптимизации и компиляции кода хранимых процедур.
С другой стороны в хранении откомпилированного кода хранимой процедуры кроются подводные камни, которые могут иметь обратный эффект.
Дело в том, что при компиляции хранимой процедуры компилируется план выполнения тех операторов, которые составляют код процедуры, соответственно, если откомпилированная хранимая процедура кэшируется, то кэшируется и ее план выполнения, а следовательно, хранимая процедура не будет оптимизироваться под конкретную ситуацию и параметры запросов.
Проведет небольшой эксперимент, чтобы продемонстрировать это.
ШАГ 1
. Создание БД.
Для эксперимента создадим отдельную базу данных.
CREATE DATABASE test_sp_perf
ON (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
LOG ON (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
ШАГ 2.
Создание таблицы.
CREATE TABLE sp_perf_test(column1 int, column2 char(5000))
ШАГ 3. Заполнение таблицы тестовыми строками. В таблицу намеренно добавляются дублирующиеся строки. 10000 строк с номерами от 1 до 10000, и 10000 строк с номерами 50000.
DECLARE @i int
SET @i=1
WHILE(@i<10000)
BEGIN
INSERT INTO sp_perf_test(column1, column2) VALUES(@i,"Test string #"+CAST(@i as char(8)))
INSERT INTO sp_perf_test(column1, column2) VALUES(50000,"Test string #"+CAST(@i as char(8)))
SET @i= @i+1
END
SELECT COUNT(*) FROM sp_perf_test
GO
ШАГ 4. Создание некластерного индекса. Поскольку план выполнения кэшируется вместе с процедурой, индекс будет при всех вызовах использоваться одинаково.
CREATE NONCLUSTERED INDEX CL_perf_test ON sp_perf_test(column1)
GO
ШАГ 5. Создание хранимой процедуры. Процедура просто выполняет оператор SELECT с условием.
CREATE PROC proc1 (@param int)
AS
SELECT column1, column2 FROM sp_perf_test WHERE column1=@param
GO
ШАГ 6. Запуск хранимой процедуры. При запуске ранимой процедуры специально используется селективный параметр. В результате выполнения процедуры получаем 1 строку. План выполнения показывает на использование некластерного индекса, т.к. запрос селективный и это наилучший способ извлечь строку. Процедура, оптимизированная на выборку одной строки, сохраняется в процедурном кэше.
EXEC proc1 1234
GO
ШАГ 7. Запуск хранимой процедуры с неселективным параметром. В качестве параметра используется значение 50000. Строк с таким значением первого столбца около 10000, соответственно, пользоваться некластерным индексом и операцией bookmark lookup неэффективно, но поскольку откомпилированный код с планом выполнения храниться в процедурном кэше, именно он и будет использоваться. План выполнения показывает это, а так же то, что для 9999 строк выполнялась операция bookmark lookup.
EXEC proc1 50000
GO
ШАГ 8. Выполнение выборки строк с первым полем равным 50000. При выполнении отдельного запроса выполняется оптимизация и компиляция запроса с конкретным значением первого столбца. В результате оптимизатор запросов определяет, что поле много раз дублируется и принимает решение использовать операцию table scan, что в данном случае намного эффективнее, чем использование некластерного индекса.
SELECT column1, column2 FROM sp_perf_test WHERE column1=50000
GO
Таким образом, можно заключить, что использование хранимых процедур не всегда может повысить производительность запросов. Следует крайне внимательно подходить к тем хранимым процедурам, которые работают с результатами с переменным количеством строк и использующие различные планы выполнения.
Можете использовать скрипт для повторения эксперимента на свеем MS SQL сервере.
Включай в свои процедуры строку - SET NOCOUNT ON:
С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.
Transact-SQL
Используй имя схемы с именем объекта:
Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.
Transact-SQL
SELECT * FROM dbo.MyTable --Вот так делать хорошо -- Вместо SELECT * FROM MyTable --А так делать плохо --Вызов процедуры EXEC dbo.MyProc --Опять же хорошо --Вместо EXEC MyProc --Плохо!
Не используй префикс «sp_» в имени своих хранимых процедур:
Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.
Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *):
Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже.
При работе с SQL Server пользователи могут создавать собственные процедуры, реализующие те или иные действия. Хранимые процедуры являются полноценными объектами базы данных, а потому каждая из них хранится в конкретной базе данных. Непосредственный вызов хранимой процедуры возможен, только если он осуществляется в контексте той базы данных, где находится процедура.
Типы хранимых процедур
В SQL Server имеется несколько типов хранимых процедур.
Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.
Триггеры
Триггеры являются одной из разновидностей хранимых процедур. Их исполнение происходит при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML). Триггеры используются для проверки целостности данных, а также для отката транзакций.
Триггер – это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных. Применение триггеров большей частью весьма удобно для пользователей базы данных. И все же их использование часто связано с дополнительными затратами ресурсов на операции ввода/вывода. В том случае, когда тех же результатов (с гораздо меньшими непроизводительными затратами ресурсов) можно добиться с помощью хранимых процедур или прикладных программ, применение триггеров нецелесообразно.
Триггеры – особый инструмент SQL-сервера, используемый для поддержания целостности данных в базе данных. С помощью ограничений целостности, правил и значений по умолчанию не всегда можно добиться нужного уровня функциональности. Часто требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность. Кроме того, иногда необходимо отслеживать изменения значений таблицы, чтобы нужным образом изменить связанные данные. Триггеры можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответствии с правилами, стандартными значениями и т.д.
Триггер представляет собой специальный тип хранимых процедур, запускаемых сервером автоматически при попытке изменения данных в таблицах, с которыми триггеры связаны. Каждый Триггер привязывается к конкретной таблице. Все производимые им модификации данных рассматриваются как одна транзакция. В случае обнаружения ошибки или нарушения целостности данных происходит откат этой транзакции. Тем самым внесение изменений запрещается. Отменяются также все изменения, уже сделанные триггером.
Создает триггер только владелец базы данных. Это ограничение позволяет избежать случайного изменения структуры таблиц, способов связи с ними других объектов и т.п.
Триггер представляет собой весьма полезное и в то же время опасное средство. Так, при неправильной логике его работы можно легко уничтожить целую базу данных, поэтому триггеры необходимо очень тщательно отлаживать.
В отличие от обычной подпрограммы, триггер выполняется неявно в каждом случае возникновения триггерного события, к тому же он не имеет аргументов. Приведение его в действие иногда называют запуском триггера. С помощью триггеров достигаются следующие цели:
проверка корректности введенных данных и выполнение сложных ограничений целостности данных, которые трудно, если вообще возможно, поддерживать с помощью ограничений целостности, установленных для таблицы;
выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновлении таблицы, реализованном определенным образом;
накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;
поддержка репликации.
Основной формат команды CREATE TRIGGER показан ниже:
<Определение_триггера>::=
CREATE TRIGGER имя_триггера
BEFORE | AFTER <триггерное_событие>
ON <имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Триггерные события состоят из вставки, удаления и обновления строк в таблице. В последнем случае для триггерного события можно указать конкретные имена столбцов таблицы. Время запуска триггера определяется с помощью ключевых слов BEFORE (Триггер запускается до выполнения связанных с ним событий) или AFTER (после их выполнения).
Выполняемые триггером действия задаются для каждой строки (FOR EACH ROW), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT).
Неправильно написанные триггеры могут привести к серьезным проблемам, таким, например, как появление "мертвых" блокировок. Триггеры способны длительное время блокировать множество ресурсов, поэтому следует обратить особое внимание на сведение к минимуму конфликтов доступа.
Триггер может быть создан только в текущей базе данных, но допускается обращение внутри триггера к другим базам данных, в том числе и расположенным на удаленном сервере.
Хранимая процедура - это специальный тип пакета инструкций Transact-SQL, созданный, используя язык SQL и процедурные расширения. Основное различие между пакетом и хранимой процедурой состоит в том, что последняя сохраняется в виде объекта базы данных. Иными словами, хранимые процедуры сохраняются на стороне сервера, чтобы улучшить производительность и постоянство выполнения повторяемых задач.
Компонент Database Engine поддерживает хранимые процедуры и системные процедуры. Хранимые процедуры создаются таким же образом, как и все другие объекты баз данных, т.е. при помощи языка DDL. Системные процедуры предоставляются компонентом Database Engine и могут применяться для доступа к информации в системном каталоге и ее модификации.
При создании хранимой процедуры можно определить необязательный список параметров. Таким образом, процедура будет принимать соответствующие аргументы при каждом ее вызове. Хранимые процедуры могут возвращать значение, содержащее определенную пользователем информацию или, в случае ошибки, соответствующее сообщение об ошибке.
Хранимая процедура предварительно компилируется перед тем, как она сохраняется в виде объекта в базе данных. Предварительно компилированная форма процедуры сохраняется в базе данных и используется при каждом ее вызове. Это свойство хранимых процедур предоставляет важную выгоду, заключающуюся в устранении (почти во всех случаях) повторных компиляций процедуры и получении соответствующего улучшения производительности. Это свойство хранимых процедур также оказывает положительный эффект на объем данных, участвующих в обмене между системой баз данных и приложениями. В частности, для вызова хранимой процедуры объемом в несколько тысяч байтов может потребоваться меньше, чем 50 байт. Когда множественные пользователи выполняют повторяющиеся задачи с применением хранимых процедур, накопительный эффект такой экономии может быть довольно значительным.
Хранимые процедуры можно также использовать для следующих целей:
для создания журнала логов о действиях с таблицами баз данных.
Использование хранимых процедур предоставляет возможность управления безопасностью на уровне, значительно превышающем уровень безопасности, предоставляемый использованием инструкций GRANT и REVOKE, с помощью которых пользователям предоставляются разные привилегии доступа. Это возможно вследствие того, что авторизация на выполнение хранимой процедуры не зависит от авторизации на модифицирование объектов, содержащихся в данной хранимой процедуре, как это описано в следующем разделе.
Хранимые процедуры, которые создают логи операций записи и/или чтения таблиц, предоставляют дополнительную возможность обеспечения безопасности базы данных. Используя такие процедуры, администратор базы данных может отслеживать модификации, вносимые в базу данных пользователями или прикладными программами.
Создание и исполнение хранимых процедур
Хранимые процедуры создаются посредством инструкции CREATE PROCEDURE , которая имеет следующий синтаксис:
CREATE PROC proc_name [({@param1} type1 [ VARYING] [= default1] )] {, …} AS batch | EXTERNAL NAME method_name Соглашения по синтаксису
Параметр 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 =] {proc_name | @proc_name_var} {[[@parameter1 =] value | [@parameter1=] @variable ] | DEFAULT}.. Соглашения по синтаксису
За исключением параметра 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:
Процедура ModifyEmpId в примере иллюстрирует использование хранимых процедур, как часть процесса обеспечения ссылочной целостности (в данном случае между таблицами Employee и Works_on). Подобную хранимую процедуру можно использовать внутри определения триггера, который собственно и обеспечивает ссылочную целостность.
В примере ниже показано использование в хранимой процедуре предложения OUTPUT:
Данную хранимую процедуру можно запустить на выполнение посредством следующих инструкций:
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:
Процедура EmployeesInDept - это простая процедура, которая отображает табельные номера и фамилии всех сотрудников, работающих в определенном отделе. Номер отдела является параметром процедуры, и его нужно указать при ее вызове. Выполнение этой процедуры выводит таблицу с двумя столбцами, заголовки которых совпадают с наименованиями соответствующих столбцов таблицы базы данных, т.е. Id и LastName. Чтобы изменить заголовки столбцов результата (а также их тип данных), в SQL Server 2012 применяется новое предложение WITH RESULTS SETS. Применение этого предложения показано в примере ниже:
USE SampleDb; EXEC EmployeesInDept "d1" WITH RESULT SETS (( 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 требуется выполнить следующую последовательность шагов в указанном порядке:
Создать хранимую процедуру на языке C# или Visual Basic, а затем скомпилировать ее, используя соответствующий компилятор.
Используя инструкцию CREATE ASSEMBLY , создать соответствующий выполняемый файл.
Выполнить процедуру, используя инструкцию 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 { 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, определяемые пользователем функции и триггеры. Эта инструкция имеет следующий синтаксис:
CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM {dll_file} Соглашения по синтаксису
В параметре 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.
