Функции Виды функций Встроенные Определенные

Функции

Виды функций • Встроенные. • Определенные пользователем.

Категории встроенных функций Function Описание Функции, возвращающие наборы строк. Возвращают объект, который можно использовать так же, как табличные ссылки в SQL-инструкции (после FROM). Агрегатные функции Обрабатывают коллекцию значений и возвращают одно результирующее значение. Ранжирующие функции Возвращают ранжирующее значение для каждой строки в секции. Скалярная функция Обрабатывают и возвращают одиночное значение. Скалярные функции можно применять везде, где выражение допустимо.

Скалярные функции Категория функции Описание Функции конфигурации Возвращают сведения о текущей конфигурации. Функции преобразования Поддержка приведения и преобразования типов данных. Функции работы с курсорами Возвращают сведения о курсорах. Функции и типы данных даты и времени Выполняют операции над исходными значениями даты и времени, возвращают строковые и числовые значения, а также значения даты и времени. Логические функции Выполнение логических операций. Математические функции Выполняют вычисления, основанные на числовых значениях, переданных функции в виде аргументов, и возвращают числовые значения. Функции метаданных Возвращают сведения о базах данных и объектах баз данных. Функции безопасности Возвращают данные о пользователях и ролях. Строковые функции Выполняют операции со строковым (char или varchar) исходным значением и возвращают строковое или числовое значение. Системные функции Выполняют операции над значениями, объектами и параметрами экземпляра SQL Server и возвращают сведения о них. Системные статистические функции Возвращают статистические сведения о системе. Функции обработки текста и изображений Выполняют операции над текстовыми или графическими исходными значениями или столбцами и возвращают сведения о значении.

Вызов функции • • SELECT getdate(); SELECT DATEADD (day , 25 , getdate()) SELECT sqrt(25) SELECT dist=abs(field 1 -field 2) FROM T

Для чего нужны функции UDF • Для реализации логики приложения на стороне базы данных • Позволяют использовать повторно написанный код, реализующий бизнеслогику • Облегчают поддержку

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

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

Функции – разрешения (1) • Определяемые пользователем функции могут быть вложенными, то есть из одной функции может быть вызвана другая. Вложенность определяемых пользователем функций не может превышать 32 уровней. • UDF могут быть вызваны через Select • Скалярные функции могут быть использованы после SELECT, WHERE, HAVING • Табличные функции могут быть использованы после FROM, JOIN, CROSS APPLY.

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

Виды функций Скалярные Scalar Табличные Inline Multistatement

Функции, возвращающие значение (скалярные) 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 ] [ , . . . n ] ] WITH ENCRYPTION |SCHEMABINDING ) RETURNS TABLE [ AS ] RETURN ( select_stmt )

WITH ENCRYPTION • Этот параметр в системном каталоге кодирует информацию, содержащую текст инструкции CREATE FUNCTION. Таким образом, предотвращается несанкционированный просмотр текста, который был использован для создания функции. Данная опция позволяет повысить безопасность системы баз данных.

WITH SCHEMABINDING • привязывает UDF к объектам базы данных, к которым эта функция обращается. После этого любая попытка модифицировать объект базы данных, к которому обращается функция, претерпевает неудачу. • Функция может быть связанной со схемой, только если следующие ограничения истины: • все вложенные функции, вызываемые из данной, также связаны со схемой с помощью опции SCHEMABINDING; • объекты, на которые ссылается функция, должны использовать имя из двух частей именования: dbo. objectname;

Пример 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 );

Вызов inline-функции SELECT ufn_Sales. By. Store(15) SELECT * FROM dbo. ufn_Sales. By. Store(15)

Функции, возвращающие таблицу из нескольких инструкций CREATE FUNCTION function_name ( [@parameter data_type [ = default ] [ , . . . n ] ] ) RETURNS @return_variable TABLE [ 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 = dbo. Example. Func(@id, @name) SELECT dbo. Example. Func(@id, @name) • Функции, возвращающие таблицу: SELECT * FROM dbo. Example. Func(@id, @name)

Параметры функции • @parameter_name • Допускается не более 2 100 параметров. • При выполнении функции значение каждого из объявленных параметров должно быть указано пользователем, если для них не определены значения по умолчанию. • Параметры являются локальными в пределах функции, в разных функциях могут быть использованы одинаковые имена параметров. • Параметры могут использоваться только вместо констант. Они не могут использоваться вместо имен таблиц, имен столбцов или имен других объектов базы данных.

Таблица в качестве параметра для функции CREATE TYPE mylist as TABLE (id int, name char(5)) 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 @t_var mylist; insert @t_var select salary, e_name from Emps; select dbo. Pass. Table. Param 22(@t_var)

Изменение функций • ALTER FUNCTION f 1…

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

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