Multi-statement функции

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

CREATE FUNCTION [ имя_схемы. ] имя _функции

([ { @имя_параметра [ AS ][ имя_схемы. ] тип_данных

[ = default ] [ READONLY ] } [,...n ] ])

RETURNS @имя_параметра TABLE определение_таблицы

[ WITH {ENCRYPTION | SCHEMABINDING | EXECUTE AS … },[…n] ]

[ AS ]

BEGIN

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

RETURN

END

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

В отличие от табличных функций, при создании функций Multi-statement необходимо явно задать структуру возвращаемого значения. Она указывается непосредственно после ключевого слова TABLE и, таким образом, является частью определения возвращаемого типа данных. Синтаксис конструкции определение_таблицы полностью соответствует одноименным структурам, используемым при создании обычных таблиц с помощью команды CREATE TABLE.

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

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

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

Пример multi-statement функции

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

CREATE FUNCTION sales2 (@k int =30)

RETURNS @my_table TABLE

([номер счета] VARCHAR(50),баланс MONEY)

AS

BEGIN

INSERT INTO @my_table

SELECT * FROM R1

WHERE [номер счета] IN (SELECT* FROM Test.dbo.sales3(@k))

RETURN

END

GO

-- обращение к функции

SELECT * FROM Test.dbo.sales2()

Удаление любой функции осуществляется командой:

DROP FUNCTION {[ владелец.] имя_функции } [,...n]