Переменные в sql запросе: MS SQL Server и T-SQL

база данных — Как в обычном SQL запросе, используя только SELECT, объявлять переменную для использования ее в условиях выборки?


Вопрос задан


Изменён
1 год 6 месяцев назад


Просмотрен
403 раза

Пример запроса и использование переменой ниже. Чтобы каждый раз менять нужное число не во всех блоках, а только в начале скрипта в виде переменой, для дальнейшего запуска через местный SQL менеджер (RunScript см. ниже).

/*переменая a = 1234*/
SELECT BLOCK1.cl1, BLOCK2.cl2 
FROM 
(SELECT a, b FROM table1
WHERE TIN = /*переменая*/) BLOCK1
LEFT JOIN 
(SELECT a, b FROM table2
WHERE TIN = /*переменая*/) BLOCK2
 ON BLOCK1.KEY=BLOCK2.KEY

PS На предприятии используют некий RunScript, чисто написаное мини-оконное приложение для сотрудников, где можно использовать только SELECT и выгружать готовую таблицу в Excel для дальнейших манипуляций с данными.

  • sql
  • база-данных
  • oracle
  • переменные
  • скрипт






3

Если нужна действительно переменная, а не константное значение литерала 123, то надо добавить переменную подстановки. Как добавить, зависит от языка, на котором реализован RunScript.

Например, так командой VARIABLE, доступной во многих инструментах:

variable a number
exec a = 123
select block1.cl1, block2.cl2 
from (
    select 123 tin, 'key1' key, 1 cl1, 2 cl2 from dual
    ) block1
left join (
    select 123 tin, 'key1' key, 3 cl1, 4 cl2 from dual
    ) block2 on block1.key=block2.key and block1.tin = 123
       CL1        CL2
---------- ----------
         1          4

WITH
temp_a AS
    (SELECT 1234 AS a FROM DUAL),
BLOCK1 AS 
    (SELECT a, b, TIN, cl1 FROM table1), 
BLOCK2 AS 
    (SSELECT a, b, TIN, cl2 FROM table2)
SELECT
    BLOCK1.cl1, BLOCK2. cl2 
FROM
BLOCK1
LEFT JOIN BLOCK2
       ON BLOCK1.KEY=BLOCK2.KEY,
 temp_a
WHERE
  BLOCK1.TIN=temp_a.a AND   
  BLOCK2.TIN=temp_a.a







Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации


Почта

Необходима, но никому не показывается





Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки


2.

20. Переменные — Transact-SQL В подлиннике : Персональный сайт Михаила Флёнова

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

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

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

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

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

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


DECLARE {@local_variable datatype} [,..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. Давайте рассмотрим его построчно:

  1. SELECT @vFirstName = vcName – после оператора SELECT, указываем, что необходимо значение поля «vcName» присвоить переменной @vFirstName;

  2. FROM tbPeoples – данные выбираются из таблицы tbPeoples;

  3. 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, то рекомендую использовать только локальные переменные, чтобы случайно не спровоцировать конфликта имен или значений. Так как глобальные переменные доступны всем, то их может увидеть любой пользователь, подключенный в данный момент к серверу.

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

  1. @@SERVERNAME – содержит имя экземпляра MS SQL Server;

  2. @@VERSION – информация о версии SQL Server;

  3. @@ROWCOUNT – содержит количество строк, обработанных последней выполненной командой;

  4. @@SPID – идентификатор текущего процесса;

  5. @@TRANCOUNT – уровень вложенности транзакций;

  6. @@NESTLEVEL – уровень вложенности при выполнении хранимых процедур и триггеров;

  7. @@OPTIONS – текущие настройки серверных параметров;

  8. @@ERROR – содержит код ошибки последней выполненной команды;

  9. @@IDENTITY – последнее значение счетчика, которое было использовано после добавления строки.

  10. @@CURSOR_ROWS – возвращает количество записей, полученных последней командой открытия курсора.

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

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


SELECT @@SERVERNAME

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

Предыдущая глава

2.19. Транзакции

Следующая глава

2.21. Конвертирование типов

Назад к содержимому «Transact-SQL В подлиннике»

Переменные SQL: основы и использование

В этой статье мы изучим понятия и детали использования переменной SQL. В SQL Server локальные переменные используются для хранения данных во время выполнения пакета. Локальные переменные могут быть созданы для различных типов данных, а также им могут быть присвоены значения. Кроме того, присвоенные значения переменных могут быть изменены в течение периода выполнения.
Жизненный цикл переменной начинается с момента ее объявления и должен заканчиваться в конце пакета. На
с другой стороны, если переменная используется в хранимой процедуре, область действия переменной ограничена
текущая хранимая процедура. В следующих разделах мы подкрепим эту теоретическую информацию различными примерами.

Примечание: В примерах этой статьи используется образец базы данных AdventureWorks.

Объявление переменной SQL

Следующий синтаксис определяет, как объявить переменную:

DECLARE { @LOCAL_VARIABLE data_type [= значение] }

Теперь давайте интерпретируем приведенный выше синтаксис.

Во-первых, если мы хотим использовать переменную в SQL Server, мы должны объявить ее. Оператор DECLARE используется для объявления
переменная в SQL Server. На втором шаге мы должны указать имя переменной. Имена локальных переменных должны начинаться со знака @, поскольку это правило необходимо с точки зрения синтаксиса. Наконец, мы определили тип данных переменной. Аргумент значения, указанный в синтаксисе, является необязательным параметром, помогающим присвоить переменной начальное значение во время объявления. С другой стороны, мы можем присвоить или заменить значение переменной на следующих шагах пакета. Если мы не присваиваем переменной начальное значение, она инициализируется как NULL.

В следующем примере будет объявлена ​​переменная с именем @VarValue и типом данных varchar. В
в то же время мы назначим начальное значение «Спасите нашу планету»:

DECLARE @TestVariable AS VARCHAR(100)=’Save Our Planet’

PRINT @TestVariable

Присвоение значения переменной SQL

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

DECLARE @TestVariable AS VARCHAR(100)

SET @TestVariable = ‘One Planet One Life’

PRINT @TestVariable

В следующем примере мы будем использовать оператор SELECT, чтобы присвоить значение переменной:

DECLARE @TestVariable AS VARCHAR(100)

SELECT @TestVariable = ‘Спасите природу’

ПЕЧАТЬ @TestVariable

Кроме того, оператор SELECT может использоваться для присвоения значения переменной из таблицы, представления или со скалярным значением.
функции. Теперь мы рассмотрим эту концепцию использования на следующем примере:

DECLARE @PurchaseName AS NVARCHAR(50)

SELECT @PurchaseName = [Имя]

ОТ [Закупки]. [Поставщик]

ГДЕ BusinessEntityID = 1492

PRINT @PurchaseName

Как видно, значение @PurchaseName было присвоено из таблицы Vendor.

Теперь мы присвоим значение переменной из скалярной функции:

DECLARE @StockVal AS INT

SELECT @StockVal=dbo.ufnGetStock(1)

SELECT @StockVal AS [VariableVal]

Несколько переменных SQL

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

1

2

3

4

5

6

ОБЪЯВИТЬ @Variable1 КАК VARCHAR(100)

ОБЪЯВИТЬ @Variable2 КАК УНИКАЛЬНЫЙ ИДЕНТИФИКАТОР

SET @Variable1 = ‘Экономить воду, экономить жизнь’

SET @Variable2= ‘6D8446DE-68DA-4169-A2C5-4C0995C00CC1’

PRINT @Variable1

PRINT @Variable2

5

Этот путь утомителен и неудобен. Однако у нас есть более эффективный способ объявить несколько переменных в одном
утверждение. Мы можем использовать оператор DECLARE в следующей форме, чтобы мы могли присваивать значения этим переменным в
один оператор SELECT:

Decable @variable1 as varchar (100), @variable2 as uliousidentifier

select @variable1 = ‘Сохранить срок службы водой’, @variable2 = ‘6d84446de-68DA-4169-a2c5-4c0995c00cc1’

Print @variable1

. @Переменная2

Кроме того, мы можем использовать оператор SELECT для присвоения значений из таблиц нескольким переменным:

1

2

3

4

5

6

7

Объявление @VaracCountNumber AS nvarchar (15)

, @variablename as nvarchar (50)

Select @varaccountnumber = accountnumber, @variablename = имя

из [покупка]. [Vendor]

, где BusinessItid = 1492 92 92 92 9292

29292

2 9000 3

9000 3

9000 3

9000 3

9000 2 9000 2 9000 2

. PRINT @VarAccountNumber

PRINT @VariableName

Полезные советы о переменных SQL

Совет 1: Как мы упоминали ранее, срок действия локальной переменной истекает в конце пакета. Сейчас мы
проанализирует следующий пример этой проблемы:

DECLARE @TestVariable AS VARCHAR(100)

SET @TestVariable = ‘Think Green’

GO

PRINT @TestVariable

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

1

2

3

4

5

6

7

8

ЕСЛИ OBJECT_ID(‘tempdb..#TempTbl’) НЕ NULL DROP TABLE #TempTbl

DECLARE @TestVariable AS VARCHAR(100)

SET @TestVariable = ‘Hello World’

#

SELECT @TestVariable AS

GO

ОБЪЯВИТЬ @TestVariable AS VARCHAR(100)

SELECT @TestVariable = VarVal FROM #TempTbl

ПЕЧАТЬ @TestVariable

Совет 2: Предположим, мы присвоили значение из таблицы переменной и результирующий набор SELECT
Оператор возвращает более одной строки. Основная проблема на этом этапе будет заключаться в том, какое значение строки присваивается переменной. В этом случае присвоенное значение переменной будет последней строкой результирующего набора. в
В следующем примере последней строке результирующего набора будет присвоена переменная:

1

2

3

4

5

6

7

8

9

SELECT AccountNumber

Из [Закупки]. [Поставщик]

Заказ по бизнесу. заказать по BusinessEntityID

ВЫБЕРИТЕ @VarAccountNumber AS VarValue

Совет 3: Если объявленные переменные типы данных и типы данных с присвоенным значением не совпадают, SQL Server
выполняет неявное преобразование в процессе присвоения значения, если это возможно. Тип данных с более низким приоритетом
преобразованы SQL Server в тип данных с более высоким приоритетом, но эта операция может привести к потере данных. Для
В следующем примере мы присвоим переменной значение с плавающей запятой, но этот тип данных переменной объявлен как
целое число:

DECLARE @FloatVar AS FLOAT = 12312.1232

DECLARE @IntVar AS INT

SET @IntVar=@FloatVar

PRINT @IntVar

Заключение

В этой статье мы рассмотрели концепцию переменных SQL с разных точек зрения, а также узнали, как определить переменную и как присвоить ей значение (значения).

  • Автор
  • Recent Posts

Esat Erkec

Esat Erkec — специалист по SQL Server, начавший свою карьеру более 8 лет назад в качестве разработчика программного обеспечения. Он является сертифицированным экспертом по решениям Microsoft для SQL Server.

Большая часть его карьеры была посвящена администрированию и разработке баз данных SQL Server. Его текущие интересы связаны с администрированием баз данных и бизнес-аналитикой. Вы можете найти его в LinkedIn.

Просмотреть все сообщения Эсата Эркеца

Последние сообщения Эсата Эркеца (посмотреть все)

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

Вы можете использовать объекты переменных в запросе SQL, а также строковые значения.
Чтобы использовать переменные в запросе SQL, запрос должен быть записан в виде формулы,
начинающиеся со знака равенства (=) и содержащие строки, соединенные знаком
амперсанд (&).

Вы можете использовать переменные вместо имени таблицы или комбинировать их с
Предложение WHERE вместо значений фильтра.

Использование переменной для имени таблицы

Вместо указания явного имени таблицы можно создать
переменная для хранения имени таблицы и запуска оператора select для переменной:

 ="SELECT * FROM "&GlobalVariables.TableName.Text

Это эквивалентно:

 ВЫБЕРИТЕ * ИЗ [имя_таблицы]

Использование переменной для значения фильтра

Вы можете создать переменную для хранения имени продукта:

 ="ВЫБЕРИТЕ * ИЗ ПРОДУКТОВ, ГДЕ ProductName = '"&
GlobalVariables. ProductName.Text&"’"

Это будет эквивалентно выбору из таблицы продуктов:

 SELECT * FROM Products WHERE ProductName= 'Виджет'

Если переменная возвращает строковое значение, оно должно быть заключено в одинарный
кавычки (‘). Если переменная возвращает числовое значение, это не так.
необходимый. Например:

 ="ВЫБЕРИТЕ * ИЗ ПРОДУКТОВ, ГДЕ ProductID="&
GlobalVariables.ProductID.Text

Синтаксис базы данных

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

  • Управление типами баз данных

  • Создание псевдонима базы данных.

Если вы используете переменные в запросах SQL, требуемый синтаксис зависит от
тип базы данных, для которой написан запрос. Например, это
оператор написан на стандартном SQL:

=" ВЫБЕРИТЕ Id, AccCde FROM LdgA, где AccCde нравится
'"&GlobalVariables.