В Microsoft SQL Server 2000 появился новый тип table, используемый в языке Transact-SQL. Тип table подобен временным таблицам. Тип table можно использовать с целью сохранения результирующего набора для последующей обработки. Тип table можно использовать только для определения локальных переменных данного типа и возврата значения из функции пользователя. Основное использование типа table – временное хранилище для набора строк, которые возвращаются как результирующий набор таб-личнозначной функции.
Область применения
Подобно другим локальным переменным, переменная table имеет вполне определенную область применения, - это функция, хранимая процедура или пакет, в котором эта пе-ременная объявлена. В пределах этой области переменная table может использоваться так, как будто это обычная таблица. Переменная table может находиться там, где ожидается присутствие таблицы или табличного выражения в операторах SELECT, INSERT, UPDATE и DELETE. Однако вы не можете использоваться переменную table в следующих случаях:
INSERT INTO табличная_переменная EXEC хранимая_процедура
SELECT список_select INTO табличная_переменная
Объявление
При объявлении переменной переменная table инициализируется как пустая таблица. Если вы выполняете оператор SELECT сразу после объявления табличной переменной, то увидите пустой набор строк. Не допускается присвоение одной табличной переменной другой.
Индексы
Другой важной особенностью типа table является то, что не допускается создание индексов на табличных переменных за исключением индексов, которые создаются ограничениями PRIMARY KEY и UNIQUE. При доступе к табличным переменным оптимизатор использует индексы, созданными ограничениями PRIMARY KEY и UNIQUE на табличной переменной.
Следующий вывод демонстрирует Clustered Index Seek (поиск в кластерном индексе):
Set statistics profile on
Go
Declare @Mytable TABLE (lname varchar (30) Primary Key, fname varchar (30))
Insert into @Mytable
Select distinct au_lname, au_fname from authors where au_lname like 'B%'
Select * from @Mytable where lname = 'Bennet'
Go
Транзакции
На содержимое табличных переменных не оказывают влияния откаты транзакций. Табличная переменная не считается частью хранимой базы данных, и изменения, вносимые в табличную переменную, не журнализируются.
ФУНКЦИИ ПОЛЬЗОВАТЕЛЯ
Функции в языках программирования разбивают большие программные задачи на более мелкие. Любой код, который должен выполнить логику, помещенную в функцию, может вызвать эту функцию, а не повторять всю логику этой функции.
Функция пользователя (UDF) представляет собой модуль Transact-SQL, который принимает один или более входных аргументов и подсчитывает возвращаемое значение. Например, функция может вычислять расстояние между двумя точками, формировать список сотрудников, работающих под управлением заданного менеджера.
UDF подобны хранимым процедурам. Т.е. UDF может содержать как операторы управления ходом выполнения программы, так и операторы DML.
Отличие UDF от хранимых процедур состоит в следующем:
- UDF не поддерживает выходных параметров.
- UDF не может использоваться для получения результатов “FOR XML”. Результат “FOR XML” может быть в хранимой процедуре.
- Ошибки Transact-SQL, которые вызывают отмену оператора с последующим переходом к следующему оператору в модуле (триггере или хранимой процедуре), внутри функции обрабатываются иначе. В функциях ошибки Transact-SQL прерывают выполнение функции. Это, в свою очередь, приводит к отмене оператора, который вызвал функцию.
Однако UDF представляет зачастую лучший выбор для приложений SQL, поскольку вы можете вызвать UDF в операторе SQL.
Определение функции пользователя
Прежде чем определить UDF, вы должны определить характеристики UDF, такие как имя UDF, схема (квалификатор), число и типы данных входных параметров, типы возвращаемых значений. Затем вы выполняете оператор CREATE FUNCTION для создания UDF. После определения функции, если вы обнаружите, что некоторые из этих характеристик не подходят для функции, вы можете использовать оператор ALTER FUNCTION для изменения ее определения. Однако Вы не можете использовать оператор ALTER FUNCTION для преобразования скалярнозначной функции в табличнозначную и наоборот. Аналогично, вы не можете использовать ALTER FUNCTION для преобразования однострочной (inline) функции в многооператорную и наоборот. Вы можете удалить UDF при помощи оператора DROP FUNCTION.
Вы должны иметь привилегию CREATE FUNCTION для создания, изменения и удаления UDF. Пользователи, которые не являются владельцем, должны получить соответствующие привилегии на функцию, прежде чем они смогут использовать функцию в операторах Transact-SQL. Чтобы создать или изменить таблицы со ссылками на UDF в ограничении CHECK, предложении DEFAULT или определении вычисляемого столбца, вы должны также иметь привилегию REFERENCES на эти функции.
Типы операторов, которые могут использоваться в функции:
- Операторы DECLARE, которые вы можете использовать для определения переменных и курсоров, локальных относительно функции.
- Присвоение значений объектам, локальным относительно функции, например, с помощью опции SET для присвоения значений скалярным и табличным локальным переменным.
- Операции с локальными курсорами: объявление, открытие, закрытие и освобождение памяти. Операторы FETCH, которые возвращают данные клиенту, не допускаются. Допускаются только такие операторы FETCH, которые присваивают значения локальным переменным в предложении INTO.
- Операторы управления (например, IF..ELSE, WHILE, RETURN, GOTO, BREAK и CONTINUE). Операторы SELECT, которые содержат список выбора с выражениями, которые присваивают значения локальным переменным функции.
- Операторы UPDATE, INSERT и DELETE, которые модифицируют локальные табличные переменные функции. • Операторы EXECUTE, которые вызывают расширенные хранимые процедуры.
Следующие правила действуют для функций, вызывающих расширенные хранимые процедуры:
- Расширенные хранимые процедуры не могут возвращать результирующие наборы клиенту. Если хранимая процедура возвращает результирующий набор, выполнение вызывающей функции прерывается.
- Расширенная хранимая процедура может повторно подключиться к SQL Server; однако эта процедура должна выполняться в той же транзакции, что и вызвавшая ее функция.
Функции, которые вызывают расширенные хранимые процедуры, помечаются как недетерминированные.
- Вызываемые расширенные хранимые процедуры выполняются в контексте учетной записи Microsoft Windows® security, под которой запускается SQL Server. Владелец хранимой процедуры должен иметь это в виду, предоставляя привилегии EXECUTE пользователям.
Детерминизм функций
Все функции являются либо детерминированными, либо недетерминированными. Функция является детерминированной, если она всегда возвращает одно и то же значение для одних и тех же значений входных аргументов.
Например, встроенная функция DATEADD является детерминированной, поскольку она всегда возвращает один и тот же результат для любого данного набора значений трех своих параметров. Функция GETDATE не является детерминированной, поскольку она всегда вызывается с одним и тем же аргументом, но возвращает все время разные значения при каждом выполнении.
После создания функции SQL Server проводит анализ того, является ли она детерминированной или нет. На основе этого анализа SQL Server помечает функцию соответствующим образом.
Чтобы функция была детерминированной, необходимо выполнение следующих условий:
- Она не должна осуществлять доступ ни к каким таблицам, кроме тех, которые определены локально.
- Она не вызывает никакой недетерминированной встроенной функции.
- Она не вызывает никакой недетерминированной UDF. • Она привязана к схеме.
- Она не вызывает никакой расширенной хранимой процедуры (xp - extended stored procedure).
Функции, связанные со схемой
Если для функции определено WITH SCHEMABINDING, функция оказывается схемно-привязанной к объектам базы данных (таблицам, но не табличным переменным, представлениям и другим UDF), на которые функция имеет ссылки.
Функция может быть привязана к схеме, если выполняются следующие условия:
- UDF и представления, на которые имеются ссылки в функции, являются также привязанными к схеме.
- Объекты, на которые имеются ссылки в функции, находятся все в той же базе данных, что и сама функция. Ссылки на все такие объекты должны использовать имена, состоящие из двух частей или одной.
- Пользователь, выполняющий оператор CREATE FUNCTION имеет привилегию REFERENCES на все объекты БД (таблицы, представления и UDFs), на которые функция содержит ссылки.
faq(c)