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

DECLARE ©Employee AS int

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

INSERT INTO ©Reports

SELECT EmployeelD, ManagerlD

FROM HumanResources.Employee

WHERE EmployeelD = ©EmployeelD /* Теперь производится выборка образца для рекурсивных вызовов. Для этого, ** по-видимому, было бы лучше применить курсор, но речь об этом пойдет в одной ** из следующих глав... */

SELECT ©Employee = MIN(EmployeelD) FROM HumanResources.Employee WHERE ManagerlD = ©EmployeelD

/* Следуюш[ие операции также, по-видимому, было бы лучше выполнить с помощью

** курсора, но на данный момент эта тема еще не пройдена, поэтому соответствующие

** действия просто моделируются. Обратите внимание на то, что вызов функции

** является рекурсивным!

WHILE ©Employee IS NOT NULL BEGIN

INSERT INTO ©Reports SELECT *

FROM fnGetReports(©Employee)

SELECT ©Employee = MIN(EmployeelD) FROM HumanResources.Employee WHERE EmployeelD > ©Employee AND ManagerlD = ©EmployeelD

RETURN

END GO

В определении функции, приведенном выше, предусмотрено получение лишь минимальной информации о служащем и его руководителе, поскольку, если бы потребовалось получить дополнительную информацию, можно было бы просто снова выполнить соединение с таблицей Employee. Кроме того, автор позволил себе немного расширить рамки толкования требований, предъявленных в условиях задачи, поэтому включР1я в результаты и данные о самом указанном руководителе. Это было сделано в основном для упрощения реализации рекурсивного алгоритма, а также для предоставления своего рода исходного результата для результирующего набора. В качестве иллюстрации рассмотрим пример формируемьгх результатов- служащий Terri Duffy имеет идентификатор служащего EmployeelD, равный 12, поэтому непосредственно укажем данный идентификатор в вызове функции: SELECT * FROM fnGetReports(12)

В результате выполнения этого запроса будет получена не только ранее выявленная информация об одном служащем, подчиняющемся служащему Terri Duffy, но также информация о тех, кто подчиняется служащему Roberto Tamburello (который подчиняется служащему Terri Duffy) и о самом служащем Terri Duffy (напомним, что было решено включать в качестве исходной точки информацию о служащем, с которого начинается анализ дерева подчиненности): EmployeelD ManagerlD

12 3 4 9

265 267 270

158 158 158 3

263 263 3 3

(14 row(s) affected)

Теперь мы можем перейти к осуществлению завершающего шага и применить операцию соединения к полученным и исходным данным. Для этого воспользуемся почти таким же запросом, с помощью которого была предпринята первая попытка выяснить, кто является подчиненным служащего Terri Duffy: DECLARE ©EmployeelD int

SELECT ©EmployeelD = EmployeelD FROM HumanResources.Employee e JOIN Person.Contact с

ON e.ContactID = e.ContactID WHERE LastName = Duffy AND FirstName = Terri

SELECT e.EmployeelD, ec.LastName, ec.FirstName, mc.LastName AS ReportsTo

FROM HumanResources.Employee AS e

JOIN dbo.fnGetReports(©EmployeelD) AS r

ON e.EmployeelD = r.EmployeelD JOIN HumanResources.Employee AS m

ON m.EmployeelD = r.ManagerlD JOIN Person.Contact AS ec

ON e.ContactID = ec.ContactID JOIN Person.Contact AS mc

ON m.ContactID = mc.ContactID

В результате будет получена информация обо всех 14 служащих, которые прямо или косвенно подчиняются служащему Terri Duf f у: EmployeelD LastName FirstName ReportsTo

12 3 4 9

Duffy

Tamburello

Walters

Erickson

Goldberg

Miller

Margheim

Terri

Roberto

Gail

Jossef

Dylan

Diane

S?nchez

Duffy

Tamburello

Tamburello

Tamburello

Tamburello

Miller

(14 row(s) affected)

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

Требования по обеспечению детерминированного выполнения функций

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

По указанному признаку пользовательские функции подразделяются на две категории - детерминированные и недетерминированные. Детерминированное поведение функции зависит скорее не от того, каковы ее параметры, а от действий, выпол-няемьгх в самой функции. Если функция возвращает одно и то же значение после каждого вызова с одним и тем же набором допустимых параметров, то она называется детерминированной. Примером детерминированной встроенной функции может служить SUM (). Сумма чисел 3, 5 и 10 всегда равна 18, а функция SUM () при любом ее вызове с указанными параметрами возвращает указанное значение. С другой стороны, значение функции GETDATE () является недетерминированным, поскольку никто не гарантирует получение одного и того же значения при каждом ее вызове.

Функция рассматривается как детерминированная, если она соответствует четырем описанным ниже критериям.

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

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

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