Переменные (Transact-SQL)
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в Microsoft Fabric
Локальная переменная Transact-SQL представляет собой объект, содержащий одно значение определенного типа. Переменные обычно используются в пакетах и скриптах:
- В качестве счетчика можно подсчитать количество выполнения цикла или управлять числом выполнения цикла.
- для хранения значения, которое необходимо проверить инструкцией управления потоком;
- для хранения значения, возвращенного функцией или хранимой процедурой.
Замечания
Имена некоторых системных функций Transact-SQL начинаются с двух символов @ (@@). Хотя в более ранних версиях SQL Server функции называются глобальными переменными, @@ @@ функции не являются переменными, и они не имеют того же поведения, что и переменные. Функции @@ являются системными функциями, а их синтаксис использует правила для функций.
В представлении нельзя использовать переменные.
Откат транзакции не влияет на изменения переменных.
Объявление переменной Transact-SQL
Инструкция DECLARE инициализирует переменную Transact-SQL следующими значениями:
Назначение имени. Первым символом имени должен быть одиночный символ
@.Назначение длины и типа данных, определяемого системой или пользователем. Для числовых переменных задаются также точность и масштаб. Для переменных типа XML может быть назначена необязательная коллекция схем.
Задание значения
NULL.
Например, следующая DECLARE инструкция создает локальную переменную @mycounter с типом данных int . По умолчанию значение этой переменной равно NULL.
DECLARE @MyCounter INT;
Инструкция DECLARE позволяет объявить несколько переменных одинакового или разного типов через запятую.
Например, следующая DECLARE инструкция создает три локальные переменные с именем @LastName, @FirstName а @StateProvinceзатем инициализирует каждую из NULLследующих переменных:
DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @StateProvince NCHAR(2);
В другом примере следующая DECLARE инструкция создает логическую переменную @IsActive, которая объявляется как бит с значением 0 (false):
DECLARE @IsActive BIT = 0;
Область переменной
Областью видимости переменной называют диапазон инструкций Transact-SQL, которые могут к ней обращаться. Область переменной длится с точки, объявленной до конца пакета или хранимой процедуры, в которой она объявлена. Например, следующий скрипт создает синтаксическую ошибку, так как переменная объявляется в одном пакете (разделенном GO ключевым словом) и ссылается на другое:
USE AdventureWorks2022;
GO
DECLARE @MyVariable INT;
SET @MyVariable = 1;
GO
SELECT BusinessEntityID,
NationalIDNumber,
JobTitle
FROM HumanResources.Employee
WHERE BusinessEntityID = @MyVariable;
Переменные имеют локальную область и отображаются только в пакете или процедуре, в которой они определены. В следующем примере вложенная область, созданная для выполнения sp_executesql , не имеет доступа к переменной, объявленной в более высокой области, и возвращает и ошибку.
DECLARE @MyVariable INT;
SET @MyVariable = 1;
EXECUTE sp_executesql N'SELECT @MyVariable'; -- this produces an error
Установка значения в переменной Transact-SQL
При первом объявлении переменной его NULLзначение имеет значение . Чтобы назначить значение переменной, используйте инструкцию SET . Этот способ присвоения значений переменным является предпочтительным. Переменная также может иметь значение, назначаемое путем ссылки в списке выбора инструкции SELECT .
Чтобы присвоить значение переменной при помощи инструкции SET, необходимо указать ее имя и присваиваемое значение. Этот способ присвоения значений переменным является предпочтительным. Например, следующий пакет объявляет две переменные, присваивает им значения и использует их в предложении WHERE инструкции SELECT:
USE AdventureWorks2022;
GO
-- Declare two variables.
DECLARE @FirstNameVariable NVARCHAR(50),
@PostalCodeVariable NVARCHAR(15);
-- Set their values.
SET @FirstNameVariable = N'Amy';
SET @PostalCodeVariable = N'BA5 3HX';
-- Use them in the WHERE clause of a SELECT statement.
SELECT LastName,
FirstName,
JobTitle,
City,
StateProvinceName,
CountryRegionName
FROM HumanResources.vEmployee
WHERE FirstName = @FirstNameVariable
OR PostalCode = @PostalCodeVariable;
GO
Переменной можно присвоить значение, указав ее в списке выбора. Если переменная ссылается в списке выбора, она должна быть назначена скалярное значение или SELECT инструкция должна возвращать только одну строку. Например:
USE AdventureWorks2022;
GO
DECLARE @EmpIDVariable INT;
SELECT @EmpIDVariable = MAX(EmployeeID)
FROM HumanResources.Employee;
GO
Предупреждение
Если в одной SELECT инструкции существует несколько предложений назначения, SQL Server не гарантирует порядок оценки выражений. Эффекты видны только при наличии ссылок между назначениями.
Если инструкция возвращает несколько строк и переменная ссылается на нескаларовое SELECT выражение, переменная задается значением, возвращаемым для выражения в последней строке результирующий набор. Например, в следующем пакете @EmpIDVariable задано BusinessEntityID значение последней строки, возвращаемой 1:
USE AdventureWorks2022;
GO
DECLARE @EmpIDVariable INT;
SELECT @EmpIDVariable = BusinessEntityID
FROM HumanResources.Employee
ORDER BY BusinessEntityID DESC;
SELECT @EmpIDVariable;
GO
Примеры
Следующий скрипт создает небольшую тестовую таблицу из 26 строк. Переменная используется в скрипте в качестве:
- счетчика цикла для управления количеством вставляемых строк;
- значения, вставляемого в столбец целочисленного типа;
- аргумента функции, формирующей строку, которая вставляется в столбец символьного типа:
-- Create the table.
CREATE TABLE TestTable (cola INT, colb CHAR(3));
GO
SET NOCOUNT ON;
GO
-- Declare the variable to be used.
DECLARE @MyCounter INT;
-- Initialize the variable.
SET @MyCounter = 0;
-- Test the variable to see if the loop is finished.
WHILE (@MyCounter < 26)
BEGIN;
-- Insert a row into the table.
INSERT INTO TestTable
VALUES
-- Use the variable to provide the integer value
-- for cola. Also use it to generate a unique letter
-- for each row. Use the ASCII function to get the
-- integer value of 'a'. Add @MyCounter. Use CHAR to
-- convert the sum back to the character @MyCounter
-- characters after 'a'.
(
@MyCounter,
CHAR((@MyCounter + ASCII('a')))
);
-- Increment the variable to count this iteration
-- of the loop.
SET @MyCounter = @MyCounter + 1;
END;
GO
SET NOCOUNT OFF;
GO
-- View the data.
SELECT cola, colb FROM TestTable;
GO
DROP TABLE TestTable;
GO