Функции Временные таблицы локальные CREATE TABLE Test

Функции

Временные таблицы локальные CREATE TABLE #Test. Table ( id INT PRIMARY KEY ) Таблица будет существовать только во время выполнения одной сессии, и работать с ней сможете только вы. БД tempdb

Временные таблицы глобальные CREATE TABLE ##Test. Table ( id INT PRIMARY KEY ) Таблица будет видна всем. Уничтожается после закрытия создавшей ее сессии /окончания работы с ней другими пользователями

Табличные переменные • DECLARE @table_var table(id int); • Автоматически очищаются в конце функции, хранимой процедуры или пакета, где они были определены • Табличная переменная не участвует в транзакции. • Не подходят для хранения значительных объёмов данных (>100 строк).

Передача имени таблицы DECLARE @SQL varchar(8000), @table_name varchar(20)='dbo. Employees' SET @SQL = 'SELECT * FROM ' + @table_name exec(@SQL)

Типы данных, определяемые пользователем user defined type CREATE TYPE mytype FROM varchar(11) NOT NULL ;

Табличный тип • В БД объявляется user defined type в виде таблицы с нужным типом данных. Его можно передавать в хранимую процедуру как параметр. В хранимой процедуре этот тип будет виден как t-sql таблица, к которой можно делать запросы. • CREATE TYPE newlist AS TABLE(id int NULL)

Таблица в качестве параметра для процедуры CREATE procedure [dbo]. [Pass. Table. Param] @data newlist readonly as begin declare @table(id int) insert into @table(id) select id from @data end exec [dbo]. [Pass. Table. Param] (select salary from Employees)

Предложение OVER • Определяет секционирование и упорядочение набора строк до применения соответствующей оконной функции. То есть предложение OVER определяет окно или определяемый пользователем набор строк внутри результирующего набора запроса. • OVER ( [ <PARTITION BY clause> ] [ <ORDER BY clause> ] )

Сумма нарастающим итогом SELECT * , SUM(salary) OVER (ORDER BY empid) AS Running_Sum from Employees

SELECT *, SUM(salary) OVER (ORDER BY empid) AS Running_Sum , SUM(salary) OVER (partition by mgrid ORDER BY empid) AS Running_Sum_Dept , SUM(salary) OVER (partition by mgrid) AS Total_Dept from Employees

ROW_NUMBER() SELECT S. *, ROW_NUMBER() OVER (ORDER BY S. emp. Name) AS Row. Num FROM Employees S

ROW_NUMBER() + PARTITION SELECT S. *, ROW_NUMBER() OVER (PARTITION BY S. mgrid ORDER BY S. emp. Name) AS Local. Row. Num FROM Employees S

RANK ( ) / DENSE_RANK ( ) • Rank - возвращает ранг каждой строки в секции результирующего набора. Ранг строки вычисляется как единица плюс количество рангов, находящихся до этой строки. (1, 1, 1, 4) • Dense_rank - возвращает ранг строк в секции результирующего набора без промежутков в ранжировании. Ранг строки равен количеству различных значений рангов, предшествующих строке, увеличенному на единицу. (1, 1, 2)

NTILE ( N ) • Распределяет строки упорядоченной секции в заданное количество групп. SELECT S. * , NTILE(3) OVER (ORDER BY S. salary) AS Gr FROM Employees S

SELECT S. * , ROW_NUMBER() OVER (PARTITION BY S. mgrid ORDER BY S. emp. Name) AS Local. Row. Num , RANK() OVER (ORDER BY S. salary) AS Rank , COUNT(*) OVER (PARTITION BY S. mgrid ) AS Amount FROM Employees S

CTE • Обобщенные табличные выражения (CTE) можно представить себе как временные результирующие наборы, определенные в области выполнения единичных инструкций SELECT, INSERT, UPDATE, DELETE или CREATE VIEW.

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

Структура CTE WITH expression_name ( column_name [, . . . n] ) AS ( CTE_query_definition ) Инструкция для обращения к ОТВ: SELECT <column_list> FROM expression_name;

CTE WITH gr_chess AS (SELECT type_fig, COUNT(*) AS Amount FROM Chessman GROUP BY type_fig) SELECT type_fig FROM gr_chess WHERE Amont= (SELECT MAX(Amount) FROM gr_chess)

Функции – ограничения (1) • Определяемые пользователем функции не могут выполнять действия, изменяющие состояние базы данных. • Определяемые пользователем функции не могут возвращать несколько результирующих наборов. Используйте хранимую процедуру, если нужно возвращать несколько результирующих наборов. • Обработка ошибок в функциях, определяемых пользователем, ограниченна. UDF не поддерживает инструкции TRY…CATCH, @ERROR и RAISERROR.

Функции – ограничения (2) • Определяемые пользователем функции не могут вызывать хранимую процедуру. • Определяемые пользователем функции не могут использовать динамический SQL и временные таблицы. • Определяемые пользователем функции могут быть вложенными, то есть из одной функции может быть вызвана другая. Вложенность определяемых пользователем функций не может превышать 32 уровней.

Функции – разрешения • Инструкции присваивания. • Инструкции DECLARE, объявляющие локальные переменные и локальные курсоры. • Инструкции SELECT, которые содержат списки выбора с выражениями, присваивающими значения локальным переменным. • Операции над локальными курсорами, которые объявляются, открываются, закрываются и освобождаются в теле функции. Допустимы только те инструкции FETCH, которые предложением INTO присваивают значения локальным переменным. Инструкции FETCH, возвращающие данные клиенту, недопустимы. • Инструкции INSERT, UPDATE и DELETE, которые изменяют локальные табличные переменные.

Функции, возвращающие значение CREATE FUNCTION function_name ( [@parameter scalar_parameter_data_type [ = default ] [ , . . . n ] ] ) RETURNS scalar_return_data_type [ AS ] BEGIN function_body RETURN scalar_expression END

Значение CREATE FUNCTION f 2 (@num int) RETURNS INT AS BEGIN RETURN ( select count(id) from chess where id>@num) END;

CREATE FUNCTION test. F(@n 1 int, @n 2 int) RETURNS int AS BEGIN Return (@n 1*@n 2) END Select test. F(5, 8)

Функции, возвращающие таблицу • Inline - возвращает результат единичного оператора SELECT • Multistatement - возвращающие таблицу из нескольких инструкций

Функции Inline CREATE FUNCTION function_name ( [@parameter_data_type [ = default ] [ READONLY ] [ , . . . n ] ] ) RETURNS TABLE [ AS ] RETURN ( select_stmt )

Пример inline-функции CREATE FUNCTION dbo. ufn_Sales. By. Store (@storeid int) RETURNS TABLE AS RETURN ( SELECT P. Product. ID, P. Name, SUM(SD. Line. Total) AS 'Total' FROM Production. Product AS P JOIN Sales. Order. Detail AS SD ON SD. Product. ID = P. Product. ID JOIN Sales. Order. Header AS SH ON SH. Sales. Order. ID = SD. Sales. Order. ID JOIN Sales. Customer AS C ON SH. Customer. ID = C. Customer. ID WHERE C. Store. ID = @storeid GROUP BY P. Product. ID, P. Name ); SELECT * FROM dbo. ufn_Sales. By. Store(15)

Функции, возвращающие таблицу из нескольких инструкций CREATE FUNCTION function_name ( [@parameter_name scalar_parameter_data_type [ = default ] [ , . . . n ] ] ) RETURNS @return_variable TABLE <table_type_definition> [ AS ] BEGIN function_body RETURN END

Функции-таблицы: пример CREATE FUNCTION Example. Func ( @id INT = 0 ) RETURNS @table TABLE (salary INT) AS BEGIN INSERT @table (SELECT num+@id from Table 1) RETURN END select * from Example. Func(2)

Вызов функций • Функции, возвращающие значение: SET @salary = Example. Func(@id, @name) SELECT Example. Func(@id, @name) • Функции, возвращающие таблицу: SELECT * FROM Example. Func(@id, @name)

Таблица в качестве параметра для функции create function [dbo]. [Pass. Table. Param 22] (@data mylist readonly) returns int As begin declare @cc int; select @cc=count(*) from @data ; return @cc; end DECLARE @table_var mylist; insert @table_var select salary from Employees; select dbo. Pass. Table. Param 22(@table_var)

Оператор APPLY • позволяет вызывать возвращающую табличное значение функцию для каждой строки, возвращаемой внешним табличным выражением запроса. SELECT Tl. *, Tr. * FROM Table AS Tl CROSS APPLY function(Tl. field 1) AS Tr;

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