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

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

DECLARE ©OutVal int

EXEC spCLRExample @OutVal OUTPUT

SELECT ©OutVal

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

NEWTON 52

(1 row{s) affected)

12345

(1 row{s) affected)

Первым из них является результирующий набор, отправленный клиенту с помощью метода SqlContext. Pipe. При вызове на выполнение объекта команды результаты передаются по каналу, благодаря чему их получает клиент. Второй результирующий набор представляет итоги выборки значения переменной ©OutVal с помощью оператора SELECT.

Безусловно, рассматриваемый пример является довольно упрощенным, но вполне позволяет оценить открывающиеся возможности. В частности, при условии применения параметра EXTERNAL ACCESS соединение могло бы быть установлено с любым источником данных. Мы получили бы возможность осуществлять доступ к файлам и даже обращаться к Web-службам. Можно было бы ввести в действие библиотеки со сложными функциями, позволяющими выполнять такие действия, как обработка данных с помощью регулярных выражений (разумеется, не забывая следить за тем, как это скажется на производительности).

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

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

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

CREATE FUNCTION [<schema name>.]<function name>

( [ <@parameter name> [AS] [<schema name>.]<scalar data type> [ = <default value>] [ n ] ] )

RETURNS {<scalar type>TABLE [(<Table Definition>)]} [ WITH [ENCRYPTION][SCHEMABINDING]

[ RETURNS NULL ON NULL INPUT CALLED ON NULL INPUT ] [EXECUTE AS { CALLERSELFOWNER<user name>} ]

[AS] { EXTERNAL NAME <external method> BEGIN

[<function statements>]

{RETURN <type as defined in RETURNS сlause>RETURN (<SELECT statement>)} END }[;]

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

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

Формирование проекта SQL Server начинается со следующего простого шаблона:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

[Microsoft.SqlServer.Server.SqlFunction] public static SqlString ExampleUDF()

В этом месте должен находиться пользовательский код return new SqlString( Hello );

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

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

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

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

Такое решение можно осуществить путем внесения относительно небольших изменений в шаблон функции, предоставленный нам СУБД SQL Server. Прежде всего необходимо исключить объявления нескольких библиотек, поскольку фактически нам не придется работать с данными SQL Server, которые к ним относятся, а затем ввести два объявления требуемых библиотек. В конечном итоге останутся только следующие три объявления: using System;

using System.Text.RegularExpressions; using Microsoft.SqlServer.Server;

С этого момента мы можем приступить к реализации функции, внеся в нее лишь несколько изменений:

[SqlFunction(IsDeterministic = true, IsPrecise = true)] public static bool RegExIsMatch(string pattern, string matchString)

Regex reg = new Regex(pattern.TrimEnd(null)); return reg.Match(matchString.TrimEnd(null)).Success;

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

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

Приступим к дальнейшей работе и откомпилируем подготовленную функцию, после чего сможем приступить к выгрузке сборки: USE AdventureWorks

CREATE ASSEMBLY ExampleUDF

FROM <solution path>\ExampleUDF\bin\Debug\ExampleUDF.dll После этого создадим ссылку на функцию:

CREATE FUNCTION fCLRExample (

©Pattern nvarchar(max), ©MatchString nvarchar(max)

RETURNS BIT

AS EXTERNAL NAME ExampleUDF.UserDefinedFunctions.RegExIsMatch