Определяемые пользователем встроенные функции
Определяемые пользователем встроенные функции представляют собой подмножество определяемых пользователем функций, возвращающих значения типа данных table. Встроенные функции могут быть использованы для замены параметризованных представлений. В то время как представления ограничены одной инструкцией SELECT, определяемые пользователем встроенные функции могут содержать более сложную логику фильтрации записей, основанную на параметрах функции.
CREATE FUNCTION function_name
( [ { @parameter_name [ AS ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH [ ENCRYPTION ] [ SCHEMABINDING ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
function_name
Имя пользовательской функции. Скобки после имени функции обязательны даже при отсутствии параметров
@parameter_name
Параметр пользовательской функции. Может быть объявлен один или несколько параметров.
Для функций допускается не более 1 024 параметров. При выполнении функции значение каждого из объявленных параметров должно быть указано пользователем, если для этого параметра не определено значение по умолчанию.
Имя параметра всегда должно начинаться со знака @. Параметры локальны в пределах функции, то есть в разных функциях могут быть использованы одинаковые имена параметров. Аргументы могут использоваться только вместо констант. Они не могут использоваться вместо имен таблиц, имен столбцов или имен других объектов базы данных.
Parameter_data_type
Тип данных параметра. Для параметров функций Transact-SQL допустимы любые типы данных за исключением типа данных timestamp, нескалярные типы cursor и table также не могут быть указаны в качестве типов данных параметров.
[ = default ]
Значение параметра по умолчанию. Если определено значение dшefault, функция выполняется даже в том случае, если для данного параметра значение не указано.
Если параметр функции имеет значение по умолчанию, то для него должно быть указано ключевое слово DEFAULT для получения функцией значения по умолчанию. Применение ключевого слова DEFAULT следует отличать от использования аргументов со значениями по умолчанию в хранимых процедурах, когда не указанный аргумент неявно принимает значение по умолчанию.
TABLE
Указывает, что возвращаемым значением функции, возвращающей табличное значение, является таблица. Функциям, возвращающим табличное значение, могут передаваться только константы и локальные переменные.
Во встроенных функциях, возвращающих табличное значение, возвращаемое значение TABLE определяется при использовании единственной инструкции SELECT. Встроенные функции не имеют соответствующих возвращаемых переменных.
ENCRYPTION
Указывает, что компонент Database Engine преобразует исходный текст инструкции CREATE FUNCTION в скрытый формат. Пользователи, не имеющие доступа к системным таблицам или файлам баз данных, не смогут получить скрытый текст.
SCHEMABINDING
Указывает, что функция привязана к объектам базы данных, которые содержат ссылки на нее. Если аргумент SCHEMABINDING указан, нельзя изменить базовые объекты таким способом, который может повлиять на определение функции. Сначала нужно изменить или удалить само определение функции, чтобы удалить зависимости от объекта, который требуется изменить.
Привязка функции к объектам, на которые она ссылается, удаляется только в следующих случаях:
· При удалении функции.
· При изменении функции инструкцией ALTER, если не указан параметр SCHEMABINDING.
Select_stmt
Единичная инструкция SELECT, определяющая возвращаемое значение встроенной функции, возвращающей табличное значение.
Пример:
В следующем примере создается функция ufnGetEmployeeInfo. Функция состоит из единственного оператора SELECT, и по сути является параметризованным представлением.
CREATE FUNCTION ufnGetEmployeeInfo (@LoginName nvarchar(50))
RETURNS TABLE
AS
RETURN
(
SELECT u.FirstName, e.JobTitle
FROM Employee AS e INNER JOIN User AS u
ON e.UserID = u.UserID
WHERE u.LoginName = @LoginName;
);
SELECT *
FROM ufnGetEmployeeInfo ('admin');
Встроенные функции также могут быть использованы для улучшения работы индексированных представлений. Индексированное представление не может использовать параметры в условиях поиска предложения WHERE для настройки сохраненного результирующего набора в соответствии с нуждами конкретных пользователей. Тем не менее, можно задать индексированное представление, в котором будет храниться полный набор данных, соответствующих представлению, а затем создать встроенную функцию, в которой содержатся параметризованные условия поиска, позволяющие пользователям настраивать результаты. Если определение представления является сложным, то работа по составлению результирующего набора в основном сводится к таким операциям, как построение статистических выражений и соединение таблиц, что приводит к созданию кластеризованного индекса представления. При последующем создании встроенной функции, обращающейся к индексированному представлению, в ней могут быть применены пользовательские параметризованные фильтры, что позволит возвращать конкретные строки данных результирующего набора индексированного представления.
Лекция № 18
Концепция транзакций
Концепция транзакции была разработана для тех случаев, в которых результирующее состояние базы данных зависит от общей успешности выполнения ряда операций. Это может происходить из-за того, что последовательные операции могут приводить к изменению результатов предыдущих операций. В таких случаях, если какая либо операция завершается ошибкой, результирующее состояние может быть неопределенным.
Чтобы разрешить эту проблему, в транзакциях группируются серии операций способом, при котором обеспечивается определенность окончательного результата. Все операции должны завершиться успешно и затем быть выполнены (записаны в базу данных), в противном случае вся транзакция дает сбой. Отмена транзакции называется откатом. Откат дает возможность восстановить базу данных после изменений и возвратить эту базу данных в то состояние, которое было перед выполнением транзакции.
Например, в автоматизированной банковской транзакции, чтобы правильно выполнить перевод денег со счета A на счет B, кредитованию счета B должно предшествовать списание денег со счета А, в противном случае транзакция должна завершиться ошибкой.
Транзакция имеет следующий набор свойств ACID:
· Атомарность (Atomicity) Транзакция является наименьшей неделимой единицей работы и выполняется только один раз, независимо от того вся ли работа выполнена либо она не выполнена вообще.
· Целостность (Consistency) Транзакция сохраняет целостность данных, преобразуя одно согласованное состояние данных в другое согласованное состояние.
· Изоляция (Isolation) Транзакция является единицей изоляции и каждая из них возникает отдельно и независимо от параллельных транзакций.
· Надежность (Durability) Транзакция является единицей восстановления. Если транзакция проходит успешно, то внесенные ей изменения сохраняются даже в случае сбоя или завершения работы системы. Если транзакция дает сбой, система остается в том состоянии, которое было до выполнения транзакции.
Рассмотрим перечисленные свойства на примере банковской транзакции:
· Атомарность. Данное свойство гарантирует, что списание денег со счета А и кредитование счета В либо произойдут вместе, либо не произойдет ни одного из них.
· Целостность. Гарантирует, что целостность данных после завершения транзакции не будет нарушена. Например, если при пополнении счета В произойдет ошибка, списания средств со счета А не произойдет.
· Изоляция. Обеспечивает возможность параллельного выполнения независимой транзакции по переводу средств со счета А на счет С, не нарушая при этом целостность данных, то есть счета В и С будут увеличены на указанные для них значения, а счет А уменьшен на сумму этих значений. При этом, если средств недостаточно для операции перевода на счет В и достаточно для перевода на счет С, транзакция по кредитованию счета С завершится успешно, а для счета В транзакция завершится с ошибкой и будет отменена.
· Надежность. Гарантирует, что если во время выполнения транзакции после списания средств со счета А и до внесения соответствующей суммы на счет В произойдет сбой системы, то после восстановления средства со счета А списаны не будут. Если же сбой произойдет после успешной фиксации транзакции, счет А будет уменьшен, а счет В пополнен на указанную сумму.