Create Function

dbo.summ(@data DATETIME)

RETURNS int

AS

BEGIN

DECLARE @c int

SELECT @c = SUM (Количество)

FROM Поставка a INNER JOIN Заказ b ON a.Номер_заказа = b.Номер_заказа

WHERE дата_заказа = @data

RETURN (@c)

END

Вызов функции можно осуществить следующим образом

DECLARE @kol int

SET @kol=dbo.samm ('02.11.05')

/*просмотр результата*/

SELECT @kol

Функции Inline

Создание и изменение функции этого типа выполняется с помощью команды:

{CREATE | ALTER } FUNCTION [владелец.]имя_функции

([ { @имя_параметра скаляр_тип_данных

[=default]})

RETURNS TABLE

[ WITH {ENCRYPTION | SCHEMABINDING}

AS

RETURN [(] SELECT_оператор [)]

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

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

Возвращаемое функцией значение типа TABLE может быть использовано непосредственно в запросе, т.е. в разделе FROM.

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

CREATE FUNCTION dbo.itog()

RETURNS TABLE

AS

RETURN (SELECT TOP 2 Наименование

FROM Изделие a INNER JOIN Поставка b

ON а.Код_модели=b.Код_модели

ORDER BY Количество DESC)

Вызов функции можно осуществить следующим образом

SELECT Наименование FROM dbo.itog()

Функции Multi-statement

Создание и изменение функций типа Multi-statement выполняется с помощью следующей команды:

{CREATE | ALTER }FUNCTION [владелец.]имя_функции

([ { @имя_параметра скаляр_тип_данных

[=default]}])

RETURNS @имя_параметра TABLE

<определение_таблицы>

[WITH {ENCRYPTION | SCHEMABINDING}

AS

BEGIN

<тело_функции>

RETURN

END

Использование большей части параметров рассматривалось при описании предыдущих функций.

Набор возвращаемых данных должен формироваться с помощью команд INSERT, выполняемых в теле функции. Кроме того, в теле функции допускается использование различных конструкций языка SQL, которые могут контролировать значения, размещаемые в выходном наборе строк. При работе с командой INSERT требуется явно указать имя того объекта, куда необходимо вставить строки (переменная типа TABLE).

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

Например. Создать функцию (типа multi-statement), которая для некоторого сотрудника выводит список всех его подчиненных (подчиненных как непосредственно ему, так и опосредствованно через других сотрудников).

Список сотрудников с указанием каждого руководителя представлен в таблице emp_mgr со следующей структурой (у директора организации начальника нет – null):

emp (сотрудник)	mgr (руководитель)
a	null
b	a
c	a
d	a
e	f
f	b
g	b
i	c
k	d

CREATE FUNCTION fn_findReports(@id_emp CHAR(2))

RETURNS @report TABLE(empid CHAR(2) PRIMARY KEY, mgrid CHAR(2))

AS

BEGIN

DECLARE @r INT

DECLARE @t TABLE(empid CHAR(2) PRIMARY KEY, mgrid CHAR(2),

pr INT DEFAULT 0)

INSERT INTO @t SELECT emp,mgr,0 FROM emp_mgr WHERE emp=@id_emp

SET @r=@@ ROWCOUNT /*функция возвращает количество строк*/

WHILE @r>0

BEGIN

UPDATE @t SET pr=1 WHERE pr=0

INSERT INTO @t SELECT e.emp, e.mgr,0 FROM emp_mgr e, @t t

WHERE e.mgr=t.empid AND t.pr=1

SET @r=@@ ROWCOUNT /*функция возвращает количество строк*/

UPDATE @t SET pr=2 WHERE pr=1

END

INSERT INTO @report SELECT empid, mgrid FROM @t

RETURN

END

Определим список подчиненных сотрудника ‘b’:

SELECT * FROM fn_findReports('b')

Оператор возвратит следующие значения:

emp	mgr
b	a
e	f
f	b
g	b