Локальная переменная Transact-SQL представляет собой объект, содержащий некоторое значение определенного типа. Переменные обычно используются в пакетах и скриптах:
в качестве счетчика цикла;
для хранения значения, которое необходимо проверить инструкцией управления потоком;
для хранения значения, возвращенного функцией или хранимой процедурой.
Следующий скрипт создает небольшую тестовую таблицу из 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 AdventureWorks2008R2; 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 AdventureWorks2008R2; 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 AdventureWorks2008R2; GO DECLARE @EmpIDVariable int; SELECT @EmpIDVariable = MAX(EmployeeID) FROM HumanResources.Employee; GO
Если инструкция SELECT возвращает более одной строки, и переменная ссылается на нескалярное выражение, ей присваивается значение, которое возвращается для выражения в последней строке результирующего набора. Например, в следующем пакете переменной @EmpIDVariable присваивается значение идентификатора BusinessEntityID последней возвращенной строки, равное 1:
USE AdventureWorks2008R2; GO DECLARE @EmpIDVariable int; SELECT @EmpIDVariable = BusinessEntityID FROM HumanResources.Employee ORDER BY BusinessEntityID DESC; SELECT @EmpIDVariable; GO
Я хочу использовать одно и то же значение для разных запросов из разных БД
DECLARE @GLOBAL_VAR_1 INT = Value_1 DECLARE @GLOBAL_VAR_2 INT = Value_2 USE "DB_1" GO SELECT * FROM "TABLE" WHERE "COL_!" = @GLOBAL_VAR_1 AND "COL_2" = @GLOBAL_VAR_2 USE "DB_2" GO SELECT * FROM "TABLE" WHERE "COL_!" = @GLOBAL_VAR_2
но это дает ошибку.
Необходимо объявить скалярную переменную "@ GLOBAL_VAR_2".
Кто-нибудь может предложить какой-либо способ сделать это...?
11 ответовНевозможно объявить глобальную переменную в Transact-SQL. Однако, если все, что вы хотите, чтобы ваши переменные были доступны для пакетов одного скрипта, вы можете использовать инструмент SQLCMD или режим SQLCMD SSMS и определить этот инструмент /переменные, зависящие от режима, например:
:setvar myvar 10
А затем используйте их следующим образом:
$(myvar)
Чтобы использовать режим SSQ SQLCMD:
Вы не можете объявить глобальные переменные в SQLServer.
Если вы используете Management Studio, вы можете использовать режим SQLCMD, как указано @Lanorkin.
В противном случае вы можете использовать CONTEXT_INFO для хранения одной переменной, которая видна во время сеанса и соединения, но после этого она исчезнет.
Только по-настоящему глобальным будет создать глобальную временную таблицу (с именем ## yourTableName) и сохранить там свои переменные, но это также исчезнет, когда все соединения будут закрыты.
Вы можете попробовать глобальную таблицу:
Create table ##global_var (var1 int ,var2 int) USE "DB_1" GO SELECT * FROM "TABLE" WHERE "COL_!" = (select var1 from ##global_var) AND "COL_2" = @GLOBAL_VAR_2 USE "DB_2" GO SELECT * FROM "TABLE" WHERE "COL_!" = (select var2 from ##global_var)
В этом конкретном примере ошибка связана с GO после операторов использования. Операторы GO сбрасывают среду, поэтому пользовательских переменных не существует. Они должны быть объявлены снова. И ответ на вопрос о глобальных переменных - Нет, глобальных переменных не существует, по крайней мере версии сервера Sql, равные или до 2008 года. Я не могу гарантировать то же самое для более новых версий сервера SQL.
С уважением, HINI
Начиная с SQL Server 2016 , новый способ обмена информацией в сеансе представлен через SESSION_CONTEXT и sp_set_session_context .
Вы можете использовать их как альтернативу CONTEXT_INFO() , в которой сохраняется только двоичное значение, ограниченное 128 байтами. Кроме того, пользователь может переписать значение в любое время, и использовать его для проверки безопасности не очень хорошо.
Следующие проблемы решаются с помощью новых утилит. Вы можете хранить данные в более удобном для пользователя формате:
EXEC sp_set_session_context "language", "English"; SELECT SESSION_CONTEXT(N"language");
Кроме того, мы можем пометить его как read-only:
EXEC sp_set_session_context "user_id", 4, @read_only = 1;
Если вы попытаетесь изменить контекст сеанса read-only , вы получите что-то вроде этого:
Сообщение 15664, уровень 16, состояние 1, процедура sp_set_session_context, строка 10 Невозможно установить ключ "user_id" в контексте сеанса. Ключ был установлен как только для этой сессии.
Попробуйте использовать; вместо GO . У меня это работало на 2008 R2 версии
DECLARE @GLOBAL_VAR_1 INT = Value_1; DECLARE @GLOBAL_VAR_2 INT = Value_2; USE "DB_1"; SELECT * FROM "TABLE" WHERE "COL_!" = @GLOBAL_VAR_1 AND "COL_2" = @GLOBAL_VAR_2; USE "DB_2"; SELECT * FROM "TABLE" WHERE "COL_!" = @GLOBAL_VAR_2;
Подобный результат можно получить, создав скалярные функции, которые возвращают значения переменных. Конечно, вызовы функций могут быть дорогостоящими, если вы используете их в запросах, которые возвращают большое количество результатов, но если вы ограничиваете набор результатов, все должно быть в порядке. Здесь я использую базу данных, созданную только для хранения этих полустатических значений, но вы также можете создавать их для каждой базы данных. Как видите, здесь нет входных переменных, только функция с хорошо названным именем, которая возвращает статическое значение: если вы измените это значение в функции, оно мгновенно изменится везде, где она используется (при следующем вызове). >
USE GO CREATE FUNCTION dbo.global_GetStandardFonts () RETURNS NVARCHAR(255) AS BEGIN RETURN "font-family:"Calibri Light","sans-serif";" END GO -- Usage: SELECT "body{" + globalDatabase.dbo.global_GetStandardFonts() + "}..." -- Result: body{font-family:"Calibri Light","sans-serif";}...
Задание значений переменныхВ настоящее время в языке SQL предусмотрены два способа задания значения переменной - для этой цели можно использовать оператор SELECT или SET. С точки зрения выполняемых функций эти операторы действуют почти одинаково, не считая того, что оператор SELECT позволяет получить исходное присваиваемое значение из таблицы, указанной в операторе SELECT.
Оператор SET обычно используется для задания значений переменных в такой форме, какая более часто встречается в процедурных языках. В качестве типичных примеров применения этого оператора можно указать следующие:
SET @b = @a * 1.5
Обратите внимание на то, что во всех этих операторах непосредственно осуществляются операции присваивания, в которых используются либо явно заданные значения, либо другие переменные. С помощью оператора SET невозможно присвоить переменной значение, полученное с помощью запроса; запрос должен быть выполнен отдельно и только после этого полученный результат может быть присвоен с помощью оператора SET. Например, попытка выполнения такого оператора вызывает ошибку:
SET @c = COUNT(*) FROM City
а следующий оператор выполняется вполне успешно:
SET @c = (SELECT COUNT(*) FROM City)
Оператор SELECT обычно используется для присваивания значений переменным, если источником информации, которая должна быть сохранена в переменной, является запрос. Например, действия, осуществляемые в приведенном выше коде, гораздо чаще реализуются с помощью оператора SELECT:
SELECT @c = COUNT(*) FROM City
Обратите внимание на то, что данный код немного понятнее (в частности, он более лаконичен, хотя и выполняет те же действия).
Таким образом, можно, сформулировать следующее общепринятое соглашение по использованию того и другого оператора.
Оператор SET используется, если должна быть выполнена простая операция присваивания значения переменной, т.е. если присваиваемое значение уже задано явно в форме определенного значения или в виде какой-то другой переменной.
Оператор SELECT применяется, если присваивание значения переменной должно быть основано на запросе.
Использование переменных в запросах SQLОдним из полезных свойств языка T-SQL является то, что переменные могут использоваться в запросах без необходимости создания сложных динамических строк, встраивающих переменные в программный код. Динамический SQL продолжает свое существование, но одиночное значение можно изменить проще - с помощью переменной.
Везде, где в запросе может использоваться выражение, может использоваться и переменная. В следующем примере продемонстрировано использование переменной в предложении WHERE:
DECLARE @IdProd int;
SET @IdProd = 1;
SELECT
Прежде чем использовать переменную или константу в программе, ее почти всегда необходимо объявить. Все объявления должны размещаться в разделе объявлений программы PL/SQL . В PL/SQL объявления могут относиться к переменным, константам, TYPE (например, коллекциям или записям) и исключениям. В этой статье рассматриваются объявления переменных и констант .
Когда вы объявляете переменную, PL/SQL выделяет память для хранения ее значения и присваивает выделенной области памяти имя, по которому это значение можно извлекать и изменять. В объявлении также задается тип данных переменной; он используется для проверки присваиваемых ей значений. Базовый синтаксис объявления переменной или константы:
Имя тип_данных [ := | DEFAULT значение_по_умолчанию]; BEGIN FOR book_rec IN (SELECT * FROM book) LOOP process_book (book_rec); END LOOP; END;
Преимущества объявлений с привязкойВо всех объявлениях тип переменной (символьный, числовой, логический и т. д.) задается явно. В каждом объявлении непосредственно указывается тип данных и, как правило, ограничение, налагаемое назначение этого типа. Это распространенный подход к объявлению переменных, но в некоторых ситуациях он может вызвать проблемы.
- Синхронизация со столбцами базы данных. Переменная PL/SQL часто «представляет» информацию из таблицы базы данных. Если явно объявить переменную, а затем изменить структуру таблицы, это может привести к нарушению работы программы.
- Нормализация локальных переменных. Допустим, переменная PL/SQL хранит вычисляемые значения, которые используются в разных местах приложения. К каким последствиям может привести повторение (жесткое кодирование) одних и тех же типов данных и ограничений во всех объявлениях?
Переменные T-SQL создаются с помощью команды DECLARE, имеющей следующий синтаксис:
DECLARE@Имя_Переменной Тип_Данных [,
@Имя_Переменной Тип_Данных, …]
Все имена локальных переменных должны начинаться символом @. Например, для объявления локальной переменной UStr, которая хранит до 16 символовUnicode, можно использовать следующую инструкцию:
DECLARE @UStr varchar(16)
Используемые для переменных типы данных в точности совпадают с существующими в таблицах. В одной команде DECLAREчерез запятую может быть перечислено несколько переменных. В частности в следующем примере создаются две целочисленные переменныеaиb:
Область определения переменных (т.е. срок их жизни) распространяется только на текущий пакет. По умолчанию только что созданные переменные содержат пустые значения NULLи до включения в выражения должны быть инициализированы.
Задание значений переменных
В настоящее время в языке SQLпредусмотрены два способа задания значения переменной - для этой цели можно использовать операторSELECTилиSET. С точки зрения выполняемых функций эти операторы действуют почти одинаково, не считая того, что операторSELECTпозволяет получить исходное присваиваемое значение из таблицы, указанной в оператореSELECT.
Оператор SET обычно используется для задания значений переменных в такой форме, какая более часто встречается в процедурных языках. В качестве типичных примеров применения этого оператора можно указать следующие:
SET @b = @a * 1.5
Обратите внимание на то, что во всех этих операторах непосредственно осуществляются операции присваивания, в которых используются либо явно заданные значения, либо другие переменные. С помощью оператора SET невозможно присвоить переменной значение, полученное с помощью запроса; запрос должен быть выполнен отдельно и только после этого полученный результат может быть присвоен с помощью оператора SET. Например, попытка выполнения такого оператора вызывает ошибку:
SET @c = COUNT(*) FROM City
а следующий оператор выполняется вполне успешно:
SET @c = (SELECT COUNT(*) FROM City)
Оператор SELECT обычно используется для присваивания значений переменным, если источником информации, которая должна быть сохранена в переменной, является запрос. Например, действия, осуществляемые в приведенном выше коде, гораздо чаще реализуются с помощью оператора SELECT:
SELECT @c = COUNT(*) FROM City
Обратите внимание на то, что данный код немного понятнее (в частности, он более лаконичен, хотя и выполняет те же действия).
Таким образом, можно, сформулировать следующее общепринятое соглашение по использованию того и другого оператора.
Оператор SET используется, если должна быть выполнена простая операция присваивания значения переменной, т.е. если присваиваемое значение уже задано явно в форме определенного значения или в виде какой-то другой переменной.
Оператор SELECT применяется, если присваивание значения переменной должно быть основано на запросе.
Использование переменных в запросах SQL
Одним из полезных свойств языка T-SQL является то, что переменные могут использоваться в запросах без необходимости создания сложных динамических строк, встраивающих переменные в программный код. Динамический SQL продолжает свое существование, но одиночное значение можно изменить проще - с помощью переменной.
Везде, где в запросе может использоваться выражение, может использоваться и переменная. В следующем примере продемонстрировано использование переменной в предложении WHERE:
DECLARE @IdProd int;
SET @IdProd = 1;
SELECT
WHERE IdProd = @IdProd;
Глобальные системные переменные
В SQLServerимеется более тридцати глобальных переменных, не имеющих параметров, которые определяются и поддерживаются системой. Все глобальные переменные имеют префикс в виде двух символов @. Вы можете извлечь значение любой из них с помощью простого запросаSELECT, как в следующем примере:
SELECT @@CONNECTIONS
Здесь используется глобальная переменная @@CONNECTIONSдля извлечения количества подключений кSQLServerсо времени запуска программы.
Среди наиболее часто применяемых системных переменных можно отметить следующие:
@@ERROR- Содержит номер ошибки, возникшей при выполнении последнего оператораT-SQLв текущем соединении. Если ошибка не обнаружена, содержит 0. Значение этой системной переменной переустанавливается после выполнения каждого очередного оператора. Если требуется сохранить содержащееся в ней значение, то это значение следует переносить в локальную переменную сразу же после выполнения оператора, для которого должен быть сохранен код ошибки.
@@IDENTITY- Содержит последнее идентификационное значение, вставленное в базу данных в результате выполнения последнего оператораINSERT. Если в последнем оператореINSERTне произошла выработка идентификационного значения, системная переменная @@IDENTITYсодержитNULL. Это утверждение остается справедливым, даже если отсутствие идентификационного значения было вызвано аварийным завершением при выполнении оператора. А если с помощью одного оператора осуществляется несколько операций вставки, этой системной переменной присваивается только последнее идентификационное значение.
@@ROWCOUNT- Одна из наиболее широко используемых системных переменных. Возвращает информацию о количестве строк, затронутых последним оператором. Обычно применяется для контроля ошибок, отличных от тех, которые относятся к категории ошибок этапа прогона программы. Например, если в программе обнаруживается, что после вызова на выполнение оператораDELETEс конструкциейWHEREколичество затронутых строк равно нулю, то можно сделать вывод, что произошло нечто непредвиденное. После этого сообщение об ошибке может быть активизировано вручную.
Следует отметить, что с версии SQLServer2000 глобальные переменные принято называть функциями. Название глобальные сбивало пользователей с толку, позволяя думать, что область действия таких переменных шире, чем у локальных. Глобальным переменным часто ошибочно приписывалась возможность хранить информацию, независимо от того, включена она в пакет либо нет, что, естественно, не соответствовало действительности.