SET @local_variable (Transact-SQL)
Устанавливает указанную локальную переменную, предварительно созданную с помощью инструкции DECLARE @@local_variable, в указанное значение.
.gif "Значок ссылки на раздел")
Синтаксические обозначения в Transact-SQL
Синтаксис
SET
{ @local_variable
[ . { property_name | field_name } ] = { expression | udt_name { . | :: } method_name }
}
|
{ @SQLCLR_local_variable.mutator_method
}
|
{ @local_variable
{+= | -= | *= | /= | %= | &= | ^= | |= } expression
}
|
{ @cursor_variable =
{ @cursor_variable | cursor_name
| { CURSOR [ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
}
}
}
Аргументы
@ local_variable
Имя переменной любого типа кроме cursor, text, ntext, image или table. Имена переменных должны начинаться с одного символа @. Имена переменных должны соответствовать правилам для идентификаторов.property_name
Свойство определяемого пользователем типа.field_name
Общее поле определяемого пользователем типа.udt_name
Имя определяемого пользователем типа среды CLR.{ . | :: }
Указывает метод пользовательского типа среды CLR. Для метода экземпляра (не статического) используйте точку (.). Для статического метода используйте два двоеточия (::). Для обращения к методу, свойству или полю определяемого пользователем типа среды CLR необходимо разрешение EXECUTE для этого типа.method_name ( argument [ ,... n ] )
Метод определяемого пользователем типа, который принимает один или несколько аргументов для изменения состояния экземпляра типа. Статические методы должны быть общими.@ SQLCLR_local_variable
Переменная, тип которой находится в сборке. Дополнительные сведения см. в разделе Основные понятия о программировании интеграции со средой CLR.mutator_method
Метод из сборки, который может менять состояние объекта. К этому методу будет применяться свойство SQLMethodAttribute.IsMutator.{ += | -= | *= | /= | %= | &= | ^= | |= }
Составной оператор присваивания:+= Сложить и присвоить
-= Вычесть и присвоить
*= Умножить и присвоить
/= Разделить и присвоить
%= Получить остаток от деления и присвоить
&= Выполнить битовое И и присвоить
^= Выполнить битовое исключающее ИЛИ и присвоить
|= Выполнить битовое ИЛИ и присвоить
expression
Любое допустимое выражение.cursor_variable
Имя переменной курсора. Если переменная целевого курсора ранее ссылалась на другой курсор, эта ссылка будет удалена.cursor_name
Имя курсора, объявленного при помощи инструкции DECLARE CURSOR.CURSOR
Указывает, что инструкция SET содержит декларацию курсора.SCROLL
Указывает, что курсор поддерживает все аргументы выборки: FIRST, LAST, NEXT, PRIOR, RELATIVE и ABSOLUTE. Аргумент SCROLL не может быть задан, если задан аргумент FAST_FORWARD.FORWARD_ONLY
Указывает, что курсор поддерживает только параметр FETCH NEXT. Курсор может быть извлечен в одном направлении с первой до последней строки. Если аргумент FORWARD_ONLY задан без ключевых слов STATIC, KEYSET или DYNAMIC, создается курсор типа DYNAMIC. Если не указаны ни аргумент FORWARD_ONLY, ни аргумент SCROLL, по умолчанию используется аргумент FORWARD_ONLY, если нет ключевых слов STATIC, KEYSET или DYNAMIC. Для курсоров STATIC, KEYSET и DYNAMIC аргумент SCROLL добавляется по умолчанию.STATIC
Определяет курсор, который создает временную копию данных для использования курсором. Все запросы к курсору обращаются к указанной временной таблице в базе данных tempdb, поэтому изменения базовых таблиц не влияют на данные, возвращаемые выборками для данного курсора, а сам курсор не позволяет вносить изменения.KEYSET
Указывает, что членство или порядок строк в курсоре неизменны при его открытии. Набор ключей, который уникально определяет строки, встраивается в таблицу keysetв tempdb. Изменения на неключевые значения в базовых таблицах, либо созданных владельцем курсора, либо зафиксированных другими пользователям, видны при прокрутке владельцем курсора содержимого курсора. Вставки, выполненные другими пользователями, не будут видны, и невозможно выполнить вставку через серверный курсор Transact-SQL.Если какая-то строка удаляется, при попытке выбрать эту строку функция @@FETCH_STATUS возвращает значение -2. Обновления значений ключа из-за границ курсора аналогично удалению старой строки с последующей вставкой новой строки. Строка с новыми значениями невидима, и при попытке извлечь строку со старыми значениями функция @@FETCH_STATUS возвращает значение -2. Обновления видимы сразу, если они сделаны через курсор с помощью предложения WHERE CURRENT OF.
DYNAMIC
Определяет курсор, который отражает все изменения данных в строках в его результирующем наборе по мере того, как владелец курсора осуществляет прокрутку по курсору. Значения данных, порядок и членство строк в каждой выборке могут меняться. Параметры абсолютной и относительной выборки не поддерживаются динамическими курсорами.FAST_FORWARD
Задает курсор типа FORWARD_ONLY, READ_ONLY с включенной оптимизацией. Аргумент FAST_FORWARD не может быть задан, если задан аргумент SCROLL.READ_ONLY
Предотвращает внесение изменений через этот курсор. Предложение WHERE CURRENT OF не может иметь ссылку на курсор в инструкции UPDATE или DELETE. Этот параметр имеет преимущество над установленной по умолчанию возможностью обновления курсора.SCROLL LOCKS
Указывает, что позиционированные обновления или удаления, осуществляемые с помощью курсора, гарантированно будут выполнены успешно. SQL Server блокирует строки по мере их считывания в курсор, чтобы обеспечить доступность этих строк для последующих изменений. Аргумент SCROLL_LOCKS не может быть задан, если задан аргумент FAST_FORWARD.OPTIMISTIC
Указывает, что позиционированные обновления или удаления, осуществляемые с помощью курсора, не будут выполнены, если с момента считывания в курсор строка была обновлена. SQL Server не блокирует строки по мере их считывания в курсор. Вместо этого, чтобы определить, изменялась ли строка после считывания в курсор, выполняется сравнение значений столбца timestamp (или контрольных сумм, если в таблице нет столбца timestamp). Если строка была изменена, то попытки позиционированного обновления или удаления будут безрезультатными. Аргумент OPTIMISTIC не может быть задан, если задан аргумент FAST_FORWARD.TYPE_WARNING
Указывает, что клиенту будет отправлено предупреждение, если курсор неявно будет преобразован из одного запрашиваемого типа в другой.FOR select_statement
Стандартная инструкция SELECT, которая определяет результирующий набор курсора. Ключевые слова FOR BROWSE и INTO недопустимы в аргументе select_statement , входящем в объявление курсора.Если используются ключевые слова DISTINCT, UNION, GROUP BY или HAVING или в аргумент select_list включено статистическое выражение, будет создан курсор STATIC.
Если каждая из базовых таблиц не имеет уникального индекса и курсора ISO SCROLL или запрашивается курсор Transact-SQL KEYSET, он автоматически будет курсором STATIC.
Если аргумент select_statement содержит предложение ORDER BY, в котором столбцы не являются уникальными идентификаторами строк, курсор DYNAMIC будет преобразован в курсор KEYSET или в курсор STATIC, если курсор KEYSET не может быть открыт. Тоже самое происходит с курсором, определенным при помощи синтаксиса ISO, но без ключевого слова STATIC.
READ ONLY
Предотвращает внесение изменений через этот курсор. Предложение WHERE CURRENT OF не может иметь ссылку на курсор в инструкции UPDATE или DELETE. Этот параметр имеет преимущество над установленной по умолчанию возможностью обновления курсора. Это ключевое слово отличается от READ_ONLY тем, что между READ и ONLY вместо подчеркивания употребляется пробел.UPDATE [OF column_name[ ,... n ] ]
Определяет обновляемые столбцы в курсоре. Если указано предложение OF column_name [,...n], для изменений будут доступны только перечисленные столбцы. Если список не предоставляется, все столбцы могут быть обновлены, если только курсор не был определен как READ_ONLY.
Замечания
После объявления переменной она получает значение NULL. Используйте инструкцию SET, чтобы назначить значение, отличное от NULL, объявленной переменной. Инструкция SET, которая назначает значение переменной, возвращает одно значение. Если инициализируется несколько переменных, следует использовать отдельные инструкции SET для каждой локальной переменной.
Переменные могут быть использованы только в выражениях, но не вместо имен объектов или ключевых слов. Для создания динамических инструкций Transact-SQL используйте инструкцию EXECUTE.
Синтаксические правила для SET **@**cursor_variable не включают ключевые слова LOCAL и GLOBAL. Когда используется синтаксис SET **@**cursor_variable = CURSOR..., курсор создается как GLOBAL или LOCAL, в зависимости от значения параметра default to local cursor базы данных.
Переменные курсора всегда локальные, даже если ссылаются на глобальный курсор. Если переменная курсора ссылается на глобальный курсор, курсор имеет ссылки как на глобальный, так и на локальный курсоры. Дополнительные сведения см. в примере В.
Дополнительные сведения см. в разделе DECLARE CURSOR (Transact-SQL).
Составной оператор присваивания может использоваться везде, где выполняется присваивание с выражением справа от оператора, включающим переменные, или имеется инструкция SET в инструкциях UPDATE, SELECT и RECEIVE.
Не используйте переменную в инструкции SELECT для объединения значений (для вычисления агрегированных значений). Это может привести к получению непредвиденных результатов запроса. Это возможно, поскольку не гарантируется точный порядок выполнения выражений в списке SELECT (включая присваивания) для каждой выводимой строки. Дополнительные сведения см. в этой статье базы знаний.
Разрешения
Необходимо членство в роли public. Все пользователи могут использовать инструкцию SET **@**local_variable.
Примеры
А.Отображение значения переменной, инициализированной при помощи инструкции SET
Следующий пример создает переменную @myvar, записывает строковое значение в переменную и печатает значение переменной @myvar .
DECLARE @myvar char(20);
SET @myvar = 'This is a test';
SELECT @myvar;
GO
Б.Использование локальной переменной, значение которой назначено при помощи инструкции SET в инструкции SELECT
Следующий пример создает локальную переменную с именем @state и использует ее в инструкции SELECT для нахождения имен и фамилий всех работников, которые живут в штате Oregon.
USE AdventureWorks2012;
GO
DECLARE @state char(25);
SET @state = N'Oregon';
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City
FROM HumanResources.vEmployee
WHERE StateProvinceName = @state;
В.Использование составного оператора присваивания для локальной переменной
Следующие два примера позволяют получить один и тот же результат. Они создают локальную переменную с именем @NewBalance, умножают ее на 10 и отображают новое значение локальной переменной в инструкции SELECT. Во втором примере используется составной оператор присваивания.
/* Example one */
DECLARE @NewBalance int ;
SET @NewBalance = 10;
SET @NewBalance = @NewBalance * 10;
SELECT @NewBalance;
/* Example Two */
DECLARE @NewBalance int = 10;
SET @NewBalance *= 10;
SELECT @NewBalance;
Г.Использование инструкции SET с глобальным курсором
Следующий пример создает локальную переменную и устанавливает переменную курсора в значение имени глобального курсора.
DECLARE my_cursor CURSOR GLOBAL
FOR SELECT * FROM Purchasing.ShipMethod
DECLARE @my_variable CURSOR ;
SET @my_variable = my_cursor ;
--There is a GLOBAL cursor declared(my_cursor) and a LOCAL variable
--(@my_variable) set to the my_cursor cursor.
DEALLOCATE my_cursor;
--There is now only a LOCAL variable reference
--(@my_variable) to the my_cursor cursor.
Д.Определение курсора при помощи инструкции SET
Следующий пример использует инструкцию SET для определения курсора.
DECLARE @CursorVar CURSOR;
SET @CursorVar = CURSOR SCROLL DYNAMIC
FOR
SELECT LastName, FirstName
FROM AdventureWorks2012.HumanResources.vEmployee
WHERE LastName like 'B%';
OPEN @CursorVar;
FETCH NEXT FROM @CursorVar;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @CursorVar
END;
CLOSE @CursorVar;
DEALLOCATE @CursorVar;
Е.Присваивание значения из запроса
Следующий пример использует запрос для присваивания значения переменной.
USE AdventureWorks2012;
GO
DECLARE @rows int;
SET @rows = (SELECT COUNT(*) FROM Sales.Customer);
SELECT @rows;
Ж.Присваивание значения переменной определяемого пользователем типа путем изменения свойства типа
Следующий пример устанавливает значение для определяемого пользователем типа Point путем изменения значения свойства типа X.
DECLARE @p Point;
SET @p.X = @p.X + 1.1;
SELECT @p;
GO
З.Присваивание значения переменной определяемого пользователем типа путем вызова метода типа
Следующий пример устанавливает значение определяемого пользователем типа point путем вызова метода SetXY типа.
DECLARE @p Point;
SET @p=point.SetXY(23.5, 23.5);
И.Создание переменной для типа CLR и вызов метода мутатора
В следующем примере создается переменная для типа Point, а затем выполняется метод мутатора в Point.
CREATE ASSEMBLY mytest from 'c:\test.dll' WITH PERMISSION_SET = SAFE
CREATE TYPE Point EXTERNAL NAME mytest.Point
GO
DECLARE @p Point = CONVERT(Point, '')
SET @p.SetXY(22, 23);