Как присвоить значение переменной в sql

Для создания сложных запросов, выполняемых в несколько этапов, в SQL предусмотрены переменные.

В переменных могут сохраняться результаты вычисления встроенных функций и констант.

Для использования переменной, ее надо предварительно « объявить » , выполнив команду DECLARE языка SQL. Команда DECLARE имеет следующий формат: DECLARE , где — это название объявляемой переменной, а — тип данных, хранимых в ней. Названия переменных начинаются с символа « @ » , после которого могут использоваться буквы латинского алфавита, цифры и знаки « @ » , « $ » , « % » , « & » , « ~ » , « _ » .

Поддерживаются следующие основные типы данных:

Geometry — пространственный объект;
BigInt — целочисленное значение в диапазоне от -2 63 -1 до 2 63 (от — 9,223,372,036,854,775,808 по +9,223,372,036,854,775,807 );
Int — целочисленное значение в диапазоне от -2 31 ( -2,147,483,648 ) to 2 31 -1 ( 2,147,483,647 );
SmallInt — целочисленное значение в диапазоне от -2 15 ( -32,768 ) to 2 15 -1 ( 32,767 );
Double , Float , Real — число с плавающей точкой;
char , varchar — строковое значение.

Переменным можно присваивать только значения соответствующего типа данных. Для задания значения переменной используется команда SET языка SQL, имеющая формат SET = , где — выражение составленное из числовых и строковых функций и констант, соединенных знаками сложения, вычитания, умножения и деления, и сгруппированных с помощью скобок.

Использование переменных

DECLARE @i Int; SET @i = 2+2; SELECT @i*3

В примере объявляется переменная @g типа Int . Для переменной задается значение — результат вычисления выражения 2+2 , после чего значение переменной умноженной на 3 выводится в область ответа с помощью ключевого слова SELECT .

Присвоение значений переменных для выделенных пулов SQL в Azure Synapse Analytics

В этой статье вы найдете важные советы по присвоению значений переменных T-SQL в выделенном пуле SQL.

Задание переменных с помощью DECLARE

Переменные в выделенном пуле SQL задаются с помощью операторов DECLARE или SET . Инициализация переменных с помощью DECLARE — один из наиболее гибких способов задать значение переменной в пуле SQL.

DECLARE @v int = 0 ;

С помощью DECLARE можно задать одновременно несколько переменных. SELECT или UPDATE нельзя использовать для выполнения следующих задач:

DECLARE @v INT = (SELECT TOP 1 c_customer_sk FROM Customer where c_last_name = 'Smith') , @v1 INT = (SELECT TOP 1 c_customer_sk FROM Customer where c_last_name = 'Jones') ;

Нельзя инициализировать и использовать переменную в одном и том же операторе DECLARE. Чтобы проиллюстрировать это, ниже приведен недопустимый пример, так как @p1 инициализируется и используется в одной и той же инструкции DECLARE. Следующий пример показывает возникающую ошибку:

DECLARE @p1 int = 0 , @p2 int = (SELECT COUNT (*) FROM sys.types where is_user_defined = @p1 ) ;

Задание значений с помощью SET

SET — это очень распространенный метод задания одной переменной.

Ниже перечислены допустимые способы задания значения с помощью инструкции SET:

SET @v = (Select max(database_id) from sys.databases); SET @v = 1; SET @v = @v+1; SET @v +=1;

С помощью SET можно одновременно задать только одну переменную. Тем не менее допускаются составные операторы.

Ограничения

Нельзя использовать UPDATE, чтобы присвоить значение переменной.

Дальнейшие действия

Дополнительные советы по разработке приведены в обзоре разработки.

2.20. Переменные

Язык запросов Transact-SQL отличается не только мощной поддержкой транзакций, но и поддержкой переменных, благодаря которым, становиться возможным создание более сложных запросов и даже целых программ. Переменные есть и в других вариантах языка SQL (в том числе и PL/SQL), поэтому это не индивидуальное преимущество именно MS SQL Server.

Переменные – элементы языка с присвоенными значениями. Если честно, то такое определение мне не очень нравиться, потому что не отражает суть переменных и абсолютно не понятно, что это и для чего нужно. Чтобы понять, что такое переменная, нужно вспомнить, как данные хранятся в памяти.

Когда вы заносите в компьютер какое-то число или строку, то в оперативной памяти для хранения этих данных выделяется участок памяти. Эта память имеет числовой адрес, а как известно, числа запоминать намного сложнее. Лично я могу помнить не более 10 номеров телефонов, а все остальное записываю в блокнот. Понятные слова запоминаются лучше и с ними проще работать. Чтобы обратиться к данным в памяти, необходимо указать адрес, по которому находятся нужное нам число или строка.

Чтобы не мучаться с числами, придумали переменные, которые, по своей сути являются именами участков в памяти, где находятся данные.

Вы объявляете локальные переменные в операторе DECLARE и после этого назначаете ему индивидуальное значение с помощью операторов SET или SELECT. Используйте оператор SET, когда желаемое значение известно, а оператор SELECT, когда вам нужно получить желаемое значение из таблицы. После того, как вы назначили переменное значение, вы можете использовать его в операторах, группах и процедурах, в которых они объявлены. Группы — это набор операторов Transact-SQL, которые отправляются вместе и исполняются как группа (несколько последовательно выполняемых команд). Мы не раз уже использовали группы, и вы их можете увидеть в листингах 2.9, 2.10 и так далее.

Имя локальной переменной начинается с одного символом @ в начале имени. Общий вид объявления переменной:

DECLARE [. n]

Пример установки переменной значения:

SET @имя = значение

После ключевого слова указывается локальная переменная (@имя). Далее идет знак равенства и значение, которое должно быть присвоено переменной.

Следующий пример объявляет две переменные и использует их:

Листинг 2.13. Пример использования переменных

-- Объявляем две переменные @vLastName и @vFirstName, -- которые имеют тип varchar размером в 50 символов DECLARE @vLastName varchar(50), @vFirstName varchar(50) -- Устанавливаем переменную фамилии @vLastName SET @vLastName='%СИДОРОВ' -- Выбираем имя работника из таблицы и записываем его в -- переменную @vFirstName SELECT @vFirstName = vcName FROM tbPeoples WHERE vcFamil LIKE @vLastName -- Печатаем результат PRINT 'Имя Cидорова '+@vFirstName

В результате на экране мы должны увидеть сообщение:

Имя Cидорова ИВАН

Теперь посмотрим, что происходит в сценарии. Сначала мы объявляем две переменные. Это можно сделать двумя способами. Первый – после ключевого слова DECLARE перечислить через запятую имена переменных и их тип, как показано в листинге 2.13. Напоминаю, что имя переменной должно начинаться с @.

Второй способ – каждую переменную описать в отдельной секции DECLARE:

DECLARE @vLastName varchar(50) DECLARE @vFirstName varchar(50)

Теперь переменной @vLastName, которая должна хранить фамилию работника, присваиваем значение ‘%СИДОРОВ’. Вначале фамилии стоит знак %, потому что в конце раздела 2.17 мы прибавили в начало каждой фамилии буквы mr. Если вы выполняли этот запрос, то обязательно необходимо добавить знак процента или переменной присвоить значение ‘mr.СИДОРОВ’.

Теперь выполняем запрос SELECT. Давайте рассмотрим его построчно:

SELECT @vFirstName = vcName – после оператора SELECT, указываем, что необходимо значение поля «vcName» присвоить переменной @vFirstName;
FROM tbPeoples – данные выбираются из таблицы tbPeoples;
WHERE vcFamil LIKE @vLastName – в условии поиска мы указываем, что поле «vcFamil» должно соответствовать содержимому шаблона в переменной @vLastName.

Новой для нас является и последняя строка, в которой происходит вывод на экран сообщения с помощью оператора PRINT. После оператора указывается сообщение, которое необходимо вывести. Сообщение может быть указано через пробел или в скобках. Следующий пример показывает, как использовать сообщение в скобках:

print('Команда недопустима')

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

DECLARE @Transname varchar(50) BEGIN TRANSACTION @Transname INSERT INTO tbPeoples(vcFamil, vcName) VALUES('ВАСИЛЬКОВ', 'ИВАН') COMMIT TRANSACTION @Transname

Глобальные переменные начинаются с двух символов @@. Когда вы создаете собственные программы на языке Transact-SQL, то рекомендую использовать только локальные переменные, чтобы случайно не спровоцировать конфликта имен или значений. Так как глобальные переменные доступны всем, то их может увидеть любой пользователь, подключенный в данный момент к серверу.

Читать:

Сколько стоит новый kia optima

И все же, глобальные переменные нужны. Существует несколько глобальных переменных, через которые можно определить полезную информацию о сервере или о времени выполнения. Давайте бегло рассмотрим некоторые из них:

@@SERVERNAME – содержит имя экземпляра MS SQL Server;
@@VERSION – информация о версии SQL Server;
@@ROWCOUNT – содержит количество строк, обработанных последней выполненной командой;
@@SPID – идентификатор текущего процесса;
@@TRANCOUNT – уровень вложенности транзакций;
@@NESTLEVEL – уровень вложенности при выполнении хранимых процедур и триггеров;
@@OPTIONS – текущие настройки серверных параметров;
@@ERROR – содержит код ошибки последней выполненной команды;
@@IDENTITY – последнее значение счетчика, которое было использовано после добавления строки.
@@CURSOR_ROWS – возвращает количество записей, полученных последней командой открытия курсора.

В следующих главах мы еще будем возвращаться к глобальным переменным и некоторые из них рассмотрим более подробно. Рассматривать все переменные сейчас не имеет смысла, потому что у нас еще не достаточно информации о языке Transact-SQL и базе данных MS SQL Server. А сейчас ограничимся только общими понятиями и простым примером.

В качестве примера, давайте посмотрим, как определить имя установленного экземпляра MS SQL Server. Для этого необходимо выполнить следующую команду:

SELECT @@SERVERNAME

В моем случае я увидел в качестве результата строку с единственным безымянным полем, в котором находился текст: CYD\FLENOV.

Переменные (Transact-SQL)

Локальная переменная Transact-SQL представляет собой объект, содержащий одно значение определенного типа. Переменные обычно используются в пакетах и скриптах:

в качестве счетчика цикла;
для хранения значения, которое необходимо проверить инструкцией управления потоком;
для хранения значения, возвращенного функцией или хранимой процедурой.

Имена некоторых системных функций Transact-SQL начинаются с двух символов @ (@@). Хотя в предыдущих версиях сервера SQL Server @@функции называются глобальными переменными, @@функции не являются переменными и используются иначе. @@functions являются системными функциями, а их синтаксис использует правила для функций.
В представлении нельзя использовать переменные.
Откат транзакции не влияет на изменения переменных.

Следующий скрипт создает небольшую тестовую таблицу из 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

Объявление переменных в языке Transact-SQL

Инструкция DECLARE инициализирует переменную Transact-SQL следующим образом:

Назначение имени. Имя должно иметь один @ в качестве первого символа.
Назначение длины и типа данных, определяемого системой или пользователем. Для числовых переменных задаются также точность и масштаб. Для переменных типа XML может быть дополнительно задана коллекция схем.
Присваивает созданной переменной значение NULL.

Например, следующая инструкция DECLARE создает локальную переменную @mycounter типа int.

DECLARE @MyCounter INT;

Инструкция DECLARE позволяет объявить несколько переменных одинакового или разного типов через запятую.

Например, следующая инструкция DECLARE создает три локальные переменные с именем @LastName, @FirstName и @StateProvince, присваивая каждой из них значение NULL:

DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @StateProvince NCHAR(2);

Областью видимости переменной называют диапазон инструкций Transact-SQL, которые могут к ней обращаться. Областью видимости переменной являются все инструкции между ее объявлением и концом пакета или хранимой процедуры, где она объявлена. Например, следующий скрипт содержит синтаксическую ошибку, поскольку переменная объявлена в одном пакете, а используется в другом:

USE AdventureWorks2022; GO DECLARE @MyVariable INT; SET @MyVariable = 1; -- Terminate the batch by using the GO keyword. GO -- @MyVariable has gone out of scope and no longer exists. -- This SELECT statement generates a syntax error because it is -- no longer legal to reference @MyVariable. 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