Табличные функции SQL

1. Табличные функции простые (встроенные)
2. CROSS APPLY и OUTTER APPLY
3. Сложные табличные функции (мульти-оператор)
4. суммирование

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

В этой статье я представлю конструкции и возможности табличных функций, доступных в SQL Server. Способ их создания и типичные применения. Я также опишу здесь, особенно связанные с ними, особые типы соединений - CROSS APPLY / OUTER APPLY.

Табличные функции простые (встроенные)

В SQL Server у нас есть две группы табличных функций. Первая из них - простые функции, возвращающие результат одного запроса . Они также называются параметризованными представлениями . Это, вероятно, лучшая аналогия, чтобы запомнить их назначение. Они работают очень похоже на обычные взгляды. Их преимуществом является возможность использования параметров, передаваемых в их интерьер.

В определении этого типа функции мы можем поставить только один запрос. Мы не можем использовать какую-либо сложную логику, процедуры или переменные в них - сложные функции (мульти-оператор). По этой причине структура очень упрощена, она не содержит блок BEGIN ... END. Целое включено в команду RETURN ().

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

СОЗДАЙТЕ ФУНКЦИЮ [dbo]. [CustOrders] (- начало определения такое же, как и для скалярных функций - мы можем указать от 0 до 1024 параметров @CustomerID nchar (5)) RETURNS TABLE - функция возвращает таблицу AS RETURN (SELECT o. CustomerID, o. OrderID, o. OrderDate, CAST (SUM (UnitPrice * Количество * (1 - скидка)) как smallmoney) как OrderValue FROM dbo. Заказы о внутреннем объединении dbo. [Детали заказа] из о. OrderID = из. OrderId ГДЕ вкл. CustomerID = @CustomerID GROUP BY o. CustomerID, o. OrderID, OrderDate, ShipCity) GO

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

SELECT * FROM dbo. CustOrders ('BOLID') AS TabZam ORDER BY OrderValue

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

SELECT c. CustomerID, c. CompanyName, c. ContactName, c. City, c. Country, TabZam. * ОТ ДБО. Клиенты C INNER JOIN. CustOrders ('BOLID') КАК TabZam ВКЛ. CustomerID = TabSam. CustomerID ORDER BY OrderValue

CROSS APPLY и OUTTER APPLY

При обсуждении табличных функций необходимо упомянуть особые типы суставов. В дополнение к стандартному INNER, OUTER JOIN, у нас есть два дополнительных, основанных на операторе APPLY . Они предназначены для табличных выражений, в частности функций. Они очень полезны и довольно просты в использовании.

Их применение лучше всего показано сразу на примере. В предыдущем задании мы объединили таблицу с функцией, которой мы явно передали параметр CustomerID = 'BOLID'.

Таким образом, были возвращены все заказы для конкретного клиента, а также информация об этом из таблицы dbo.Customers.

В ситуации, когда мы хотим запускать функцию много раз, то есть вызывать ее для всех клиентов из Испании, мы можем использовать для этого CROSS или OUTER APPLY.

CROSS APPLY - мы обычно используем для объединения набора (например, таблицы) с любой табличной функцией. Оператор APPLY позволяет передавать в качестве входного параметра, например, табличной функции, значение из набора слева от этого объединения. Звучит сложно? Одна картинка (пример) стоит более 1000 слов.

Давайте предположим, что мы хотим отобразить детали заказов и клиентов, как в предыдущем примере, но для всех, кто приезжает из Испании.

Их пять:

SELECT c. CustomerID, c. CompanyName, c. ContactName, c. Страна FROM dbo. Клиенты c ГДЕ Страна = 'Испания' ЗАКАЗАТЬ c. CustomerID

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

ВЫБЕРИТЕ c. CustomerID, c. CompanyName, c. ContactName, c. Страна, TabZam. * ОТ ДБО. Клиенты c CROSS APPLY dbo. CustOrders (c. CustomerID) TabZam ГДЕ Страна = 'Испания' ORDER BY c. CustomerID, OrderValue

Затем для каждой отдельной строки из таблицы dbo.Customers выполняется соединение типа CROSS JOIN. Следовательно, информация о данном клиенте связана с каждой записью, возвращаемой в данном вызове нашей функцией.

Обратите внимание, что в результатах поиска отсутствует CustomerID = 'FISSA'. CROSS APPLY - работает аналогично внутреннему соединению INNER JOIN . Клиент не размещал никаких заказов, функция dbo.CustOrders () не возвращала никаких записей и поэтому, как и в INNER JOIN, запись была удалена из набора результатов.

Чтобы получить результат также «несогласованные» записи из таблицы слева, вы должны использовать OUTPUT APPLY .

Он работает аналогично CROSS APPLY с той разницей, что он эквивалентен внешнему соединению. Таким образом, как и в LEFT OUTER JOIN , в конце обработки все записи из таблицы в левой части оператора будут добавлены в набор результатов.

Запрос с учетом всех клиентов будет выглядеть так:

ВЫБЕРИТЕ c. CustomerID, c. CompanyName, c. ContactName, c. Страна, TabZam. * ОТ ДБО. Клиенты c НАШИМ ПРИМЕНЕНИЕМ dbo. CustOrders (c. CustomerID) TabZam ГДЕ Страна = 'Испания' ORDER BY c. CustomerID, OrderValue

Как видите, использование и работа объединений на основе оператора APPLY проста и интуитивно понятна. Стоит добавить, что используя его, мы можем комбинировать не только функции, но и другие табличные выражения - например, подзапросы.

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

ВЫБЕРИТЕ c. CustomerID, c. CompanyName, c. ContactName, c. Страна, TabZam. * ОТ ДБО. Клиенты c OUTER APPLY (- вместо функций, табличное выражение SELECT o. CustomerID, o. OrderID, o. OrderDate, CAST (SUM (UnitPrice * Количество * (1 - скидка)) как smallmoney) как OrderValue FROM dbo. присоединиться к dbo. [Детали заказа] от него o. OrderID = от. OrderId WHERE o. CustomerID = c. CustomerID GROUP BY o. CustomerID, o. OrderID, OrderDate, ShipCity) TabZam WHERE Страна = 'Испания' ORDER BY c. CustomerID , OrderValue

Сложные табличные функции (мульти-оператор)

В ситуациях, когда нам нужна большая сложность программирования, мы можем использовать функции Multi-Statement. Построение такой функции немного сложнее.

Помимо параметров, которые мы определяем так же, как и в других типах, определение набора результатов является обязательным. После ключевого слова RETURNS мы присваиваем таблице возвращаемое имя и по очереди указываем все ее атрибуты (столбцы). Как и при создании обычной таблицы, столбцы должны быть описаны с конкретными типами данных. Здесь мы можем назначить значения по умолчанию, IDENTITY или первичный ключ.

В рамках обработки такой функции мы должны явно (INSERT INTO) вставлять элементы в эту таблицу результатов.

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

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

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

СОЗДАЙТЕ ФУНКЦИЮ [dbo]. [SplitString] (@InputStr varchar (8000), @Delimiter char (1) = ',') - теперь определение набора результатов RETURNS @temptable TABLE (SingleStr varchar (8000)) AS BEGIN DECLARE @ index int DECLARE @tempStr varchar ( 8000) SET @ index = 1 ЕСЛИ LEN (@InputStr) <1 ИЛИ @InputStr ОБЫЧНО ВОЗВРАЩАЕТСЯ В ТЕЧЕНИЕ @ index! = 0 НАЧАТЬ SET @ index = CHARINDEX (@ Delimiter, @ InputStr) IF @ index! = 0 SET @tempStr = слева (@ InputStr, @index - 1) ELSE SET @tempStr = @InputStr IF (LEN (@tempStr)> 0) - здесь мы записываем в таблицу результатов INSERT INTO @temptable (SingleStr) ЗНАЧЕНИЯ (@tempStr) SET @InputStr = right (@InputStr, LEN (@InputStr) - @ index) IF LEN (@InputStr) = 0 BREAK END RETURN END

Интересными элементами этой конструкции являются явное вставление элементов в таблицу результатов (используя INSERT INTO). Результатом будут только те элементы, которые будут добавлены таким образом. Наконец, с ключевым словом RETURN мы остановим функцию.

Использование - так же, как для простых табличных функций. Для начала приведем примеры с двумя разными разделителями (по умолчанию используется запятая):

- разделитель в виде пробелов SELECT * FROM dbo. SplitString ('Ala имеет кошку', '') - с разделителем по умолчанию (запятая) SELECT * от dbo. SplitString ('12345,65489,23546', ПО УМОЛЧАНИЮ)

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

ИСПОЛЬЗОВАТЬ Northwind GO WITH Ord as (ВЫБРАТЬ CustomerId, OrderId, ROW_NUMBER () OVER (Partition BY CustomerId order by OrderId DESC) КАК RN FROM.dbo. Orders) ВЫБРАТЬ o1. CustomerID, STUFF ((SELECT ',' + Cast (o2. OrderID as varchar) ОТ Ord o2, ГДЕ o2. CustomerID = o1. CustomerID и o2. RN <= 5 ДЛЯ ПУТИ XML ('')), 1, 1, ' ') как Last5OrderNo в #tempdata ОТ Ord o1 ГДЕ o1. RN <= 5 GROUP BY o1. CustomerID SELECT * из #tempData SELECT * из #tempData t CROSS APPLY dbo. [SplitString] (Last5OrderNo, ',')

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

Во всяком случае, вы можете сравнить себя: 

СОЗДАЙТЕ ФУНКЦИЮ [dbo]. [SplitStringCTE] (@InputStr varchar (8000), @Delimiter char (1) = ',') ВОЗВРАЩАЕТ ТАБЛИЦУ КАК ВОЗВРАТ (С помощью SplitString AS (ВЫБРАТЬ СЛУЧАЙ, КОГДА CHARINDEX (@Delimiter, @InputStr) = 0 THEN @InputStr ELSE LEFT ( @InputStr, CHARINDEX (@Delimiter, @InputStr) - 1) КОНЕЦ РАЗДЕЛЕН, СЛУЧАЙ, КОГДА CHARINDEX (@Delimiter, @InputStr) = 0 ПОСЛЕ НУЛЬ ПРАВО (@InputStr, LEN (@InputStr) - CHARINDEX (@DelStIn @) )) КОНЕЦ ТОДО СОЮЗ ВСЕ ВЫБИРАЕТ СЛУЧАЙ, КОГДА CHARINDEX (@Delimiter, TODO) = 0 ТОГДА ТОДО ИЛИ ВЛЕВО (TODO, CHARINDEX (@Delimiter, TODO) - 1) КОНЕЦ РАЗДЕЛЕН, СЛУЧАЙ КОГДА CHARINDEX (@Delimiter, TODO) = NULL ELSE RIGHT (TODO, LEN (TODO) - CHARINDEX (@Delimiter, TODO)) КОНЕЦ ТОДО ИЗ SplitString, ГДЕ ТОДО НЕ НУЛЕВОЙ) ВЫБРАТЬ SPLITED ИЗ SplitString)

суммирование

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

Похожие