Основы языка Transact-SQL. Переменные. Встроенные функции. Скалярные функции, страница 5

Входной параметр - тип товара . Функция возвращает значение целого типа, полученное из оператора SELECT при суммировании количества товара (по столбцу Количество) из таблицы Товары. Условие отбора записей для суммирования – совпадение типа товара (столбец Тип_товара таблицы Товары) со значением входного параметра функции. Пример обращения к функции - определим количество товара с типом Шины: DECLARE @kol INT SET @kol = user1.Goods('Шины') SELECT @kol

64

8.2.2. Внедренные табличные функции (функции Inline)   Внедренная табличная функция не имеет в своем теле блока BEGIN…END – вместо этого возвращается результирующий набор данных инструкции SELECT в виде таблицы с заданным именем. Общий синтаксис внедренной табличной функции: 

65

CREATE FUNCTION [имя_схемы.] имя_функции ( [ { @имя_параметра тип_данных_параметра [ = значение_параметра_по_умолчанию ] } [, …n] ] ) RETURNS TABLE [ WITH <function_option> [, …n] ] [ AS ] RETURN [ ( ] инструкция_SELECT [ ) ]

66

Основная часть параметров соответствует параметрам скалярных функций. Особенность функции Inline: - структура возвращаемого значения с типом данных table не указывается явно. Вместо этого для возвращаемого табличного значения автоматически используется результат запроса SELECT (единственная команда функции Inline). Код этого запроса задается параметром инструкция_SELECT и может быть дополнительно помещен в круглые скобки.

67

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

68

Пример 1. Рассмотрим создание и использование функции Inline с именем FunctionB, которая возвращает список фамилий и имен студентов определенного факультета (факультет задается в качестве аргумента функции) из таблицы Студенты:

69

(функция содержит одну инструкцию SELECT после ключевого слова RETURN). Пример вызова этой функции:

70

Пример 2. Функция, которая возвращает таблицу товаров и рассчитывает для каждой строки произведение количества и цены товара (общую стоимость товара). CREATE FUNCTION GetPrice() RETURNS TABLE [ AS ] RETURN (SELECT Дата, Название_товара, Цена, Количество, Цена*Количество AS Стоимость FROM Товары)

71

Для примера создана функция без параметров, в этом случае после имени функции задаются пустые круглые скобки. Для функции данного типа после RETURN нужно в скобках указать запрос, результат которого и будет возвращаться функцией. У всех полей запроса должны быть имена (при отсутствии имен, например, для вычисляемых выражений, они задаются с помощью слова AS).

72

Созданная функция может вызываться с помощью оператора SELECT, например: SELECT * FROM GetPrice() или SELECT * FROM GetPrice() WHERE Количество=1 (в последнем случае вывод результатов работы функции, ограничивается предложением WHERE).

73

Пример 3. Создание и применение функции табличного типа для определения двух наименований товара с наибольшим остатком. CREATE FUNCTION user1.itog() RETURNS TABLE AS RETURN (SELECT TOP 2 Товар.Название FROM Товар INNER JOIN Склад ON Товар.КодТовара=Склад.КодТовара ORDER BY Склад.Остаток DESC)

74

Использовать функцию для получения двух наименований товара с наибольшим остатком можно следующим образом: SELECT Название FROM user1.itog()

75

8.2.3. Табличные функции с множеством инструкций (функции Multi-statement) Данные функции, как и функции Inline, возвращают значение табличного типа. Но в отличие от функций Inline, при создании функции Multi-statement необходимо явно задать структуру возвращаемого значения. Эта структура указывается сразу после ключевого слова TABLE и, таким образом, является частью определения возвращаемого типа данных. При этом используется блок операторов BEGIN – END.

76

Общий синтаксис функции Multistatement: CREATE FUNCTION [имя_схемы.] имя_функции ( [ { @имя_параметра [ AS ] [имя_схемы.] тип_данных_параметра [ = значение_параметра_по_умолчанию ] } [, …n] ] ) RETURNS @возвращаемая_переменная TABLE <определение_табличного_типа_ данных> [ WITH <function_option> [, …n] ] [ AS ] BEGIN тело_функции RETURN END

77