SET @local_variable (Transact-SQL)

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)

Устанавливает указанную локальную переменную, предварительно созданную с помощью инструкции DECLARE @local_variable, в указанное значение.

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис для SQL Server, базы данных SQL Azure или управляемого экземпляра SQL Azure:

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 ] ] } ]   
      }   
    }  
}   

Синтаксис для Azure Synapse Analytics и Parallel Data Warehouse:

SET @local_variable { = | += | -= | *= | /= | %= | &= | ^= | |= } expression  

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

@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.

{ += | -= | *= | /= | %= | &= | ^= | |= }
Составной оператор присваивания:

+= Сложение и присваивание

-= Вычитание и присваивание

*= Умножение и присваивание

/= Деление и присваивание

%= Остаток от деления и присваивание

&= Выполнение побитовой операции AND и присваивание

^= Выполнение побитовой операции XOR и присваивание

|= Выполнение побитовой операции OR и присваивание

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 или запрашивается курсор KEYSET Transact-SQL, это автоматически будет курсор 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 в зависимости от заданного по умолчанию параметра локального курсора базы данных.

Переменные курсора всегда локальные, даже если ссылаются на глобальный курсор. Если переменная курсора ссылается на глобальный курсор, курсор имеет ссылки как на глобальный, так и на локальный курсоры. Дополнительные сведения см. в Примере D, Использование SET с глобальным курсором.

Дополнительные сведения см. в разделе DECLARE CURSOR (Transact-SQL).

Вы можете использовать составной оператор присваивания во всех случаях, когда выполняется присваивание с выражением справа от оператора, включающим переменные, а также инструкцию SET в инструкциях UPDATE, SELECT и RECEIVE.

Не используйте переменную в инструкции SELECT для сцепления значений (то есть для вычисления статистических значений). Это связано с тем, что все выражения в списке SELECT (включая назначения) могут быть выполнены несколько раз для каждой строки вывода. Дополнительные сведения см. в этой статье базы знаний.

Разрешения

Требуется членство в роли public. Все пользователи могут использовать SET @local_variable.

Примеры

В большинстве примеров используется образец базы данных AdventureWorks sample database.

A. Отображение значения переменной, инициализированной при помощи инструкции SET

Следующий пример создает переменную @myvar, записывает строковое значение в переменную и печатает значение переменной @myvar.

DECLARE @myvar CHAR(20);  
SET @myvar = 'This is a test';  
SELECT @myvar;  
GO  

Б. Использование локальной переменной, значение которой назначено при помощи инструкции SET в инструкции SELECT

В следующем примере создается локальная переменная с именем @state. Она используется в инструкции SELECT для поиска имен и фамилий всех работников, которые живут в штате Oregon.

USE AdventureWorks2019;  
GO  
DECLARE @state CHAR(25);  
SET @state = N'Oregon';  
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City  
FROM HumanResources.vEmployee  
WHERE StateProvinceName = @state;
GO 

В. Использование составного оператора присваивания для локальной переменной

Следующие два примера позволяют получить один и тот же результат. В каждом примере создается локальная переменная с именем @NewBalance, которая умножается на 10, и отображается новое значение локальной переменной в инструкции SELECT. Во втором примере используется составной оператор присваивания.

/* Example one */  
DECLARE  @NewBalance  INT ;  
SET  @NewBalance  =  10;  
SET  @NewBalance  =  @NewBalance  *  10;  
SELECT  @NewBalance;
GO
  
/* Example Two */  
DECLARE @NewBalance INT = 10;  
SET @NewBalance *= 10;  
SELECT @NewBalance;
GO

Г. Использование инструкции 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;   
GO
--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 AdventureWorks2019.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;
GO

Е. Присваивание значения из запроса

Следующий пример использует запрос для присваивания значения переменной.

USE AdventureWorks2019;  
GO  
DECLARE @rows INT;  
SET @rows = (SELECT COUNT(*) FROM Sales.Customer);  
SELECT @rows;
GO

Ж. Присваивание значения переменной определяемого пользователем типа путем изменения свойства типа

В следующем примера устанавливается значение для определяемого пользователем типа Point путем изменения значения свойства типа X.

DECLARE @p Point;  
SET @p.X = @p.X + 1.1;  
SELECT @p;  
GO  

Узнайте подробнее о создании Point, на который ссылается UDT в этом примере и в следующих примерах в разделе Создание определяемых пользователем типов.

З. Присваивание значения переменной определяемого пользователем типа путем вызова метода типа

Следующий пример устанавливает значение определяемого пользователем типа 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);  

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

К. Отображение значения переменной, инициализированной при помощи инструкции SET

Следующий пример создает переменную @myvar, записывает строковое значение в переменную и печатает значение переменной @myvar.

DECLARE @myvar CHAR(20);  
SET @myvar = 'This is a test';  
SELECT TOP 1 @myvar FROM sys.databases;

Л. Использование локальной переменной, значение которой назначено при помощи инструкции SET в инструкции SELECT

Следующий пример создает локальную переменную с именем @dept и использует ее в инструкции SELECT для нахождения имен и фамилий всех работников, которые живут в штате Marketing.

-- Uses AdventureWorks 
  
DECLARE @dept CHAR(25);  
SET @dept = N'Marketing';  
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name  
FROM DimEmployee   
WHERE DepartmentName = @dept;  

М. Использование составного оператора присваивания для локальной переменной

Следующие два примера позволяют получить один и тот же результат. Они создают локальную переменную с именем @NewBalance, умножают ее на 10 и отображают новое значение локальной переменной в инструкции SELECT. Во втором примере используется составной оператор присваивания.

/* Example one */  
DECLARE  @NewBalance INT;  
SET  @NewBalance  =  10;  
SET  @NewBalance  =  @NewBalance  *  10;  
SELECT TOP 1 @NewBalance FROM sys.tables;  
  
/* Example Two */  
DECLARE @NewBalance INT = 10;  
SET @NewBalance *= 10;  
SELECT TOP 1 @NewBalance FROM sys.tables;  

Н. Присваивание значения из запроса

Следующий пример использует запрос для присваивания значения переменной.

-- Uses AdventureWorks 
  
DECLARE @rows INT;  
SET @rows = (SELECT COUNT(*) FROM dbo.DimCustomer);  
SELECT TOP 1 @rows FROM sys.tables;  

Следующие шаги

Дополнительные сведения о связанных понятиях см. в следующих статьях:

• Составные операторы (Transact-SQL)
• DECLARE @local_variable (Transact-SQL)
• EXECUTE (Transact-SQL)
• SELECT (Transact-SQL)
• Инструкции SET (Transact-SQL)