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

Переменные и управляющие конструкции

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

Например, определим переменную name, которая будет иметь тип NVARCHAR:

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

С помощью выражения SET можно присвоить переменной некоторое значение:

Так как @name предоставляет тип NVARCHAR, то есть строку, то этой переменной соответственно и присваивается строка. А переменной @age присваивается число, так как она представляет тип INT.

Выражение PRINT возвращает сообщение клиенту. Например:

И с его помощью мы можем вывести значение переменной:

При выполнении скрипта внизу SQL Server Management Studio отобразится значение переменных:

7) Переменная SQL Server

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

В этом уроке вы узнаете:

Типы переменных: локальные, глобальные

MS SQL имеет два типа переменных:

1. Локальная переменная
2. Глобальная переменная.

Однако пользователь может создать только локальную переменную.

Ниже на рисунке показаны два типа переменных, доступных на сервере MS SQL.

Локальная переменная:

• Пользователь объявляет локальную переменную.
• По умолчанию локальная переменная начинается с @.
• Каждая область локальной переменной имеет ограничение на текущий пакет или процедуру в пределах любого данного сеанса.

Глобальная переменная:

• Система поддерживает глобальную переменную . Пользователь не может их объявить.
• Глобальная переменная начинается с @@
• Он хранит информацию о сеансе .

Как ОБЪЯВИТЬ переменную

• Перед использованием любой переменной в пакете или процедуре, вам необходимо объявить переменную.
• Команда DECLARE используется для переменной DECLARE, которая выступает в качестве заполнителя для ячейки памяти.
• Только после того, как объявление сделано, переменная может использоваться в последующей части пакета или процедуры.

Синтаксис TSQL:

Правила:

• Инициализация – необязательная вещь при объявлении.
• По умолчанию, DECLARE инициализирует переменную в NULL.
• Использование ключевого слова «AS» необязательно.
• Чтобы объявить более одной локальной переменной, используйте запятую после определения первой локальной переменной, а затем определите имя следующей локальной переменной и тип данных.

Примеры объявления переменной:

Запрос: с «КАК»

Запрос: без ‘AS’

Запрос: ОБЪЯВИТЬ две переменные

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

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

1. При объявлении переменной используется ключевое слово DECLARE.
2. Использование SET
3. Использование SELECT

Давайте посмотрим на все три способа в деталях:

При объявлении переменной используется ключевое слово DECLARE.

Синтаксис T-SQL:

Здесь после типа данных мы можем использовать ‘=’, за которым следует присвоить значение

Запрос:

Использование SET

Иногда мы хотим разделить объявление и инициализацию. SET может использоваться для назначения значений переменной, после объявления переменной. Ниже приведены различные способы назначения значений с помощью SET:

Пример : присвоение значения переменной с помощью SET

Синтаксис:

Запрос:

Пример : присвоить значение нескольким переменным с помощью SET.

Синтаксис:

Правило: одно ключевое слово SET может использоваться для присвоения значения только одной переменной .

Запрос:

Пример : присвоение значения переменной с помощью скалярного подзапроса с помощью SET

Синтаксис:

Правила:

• Заключите запрос в скобки.
• Запрос должен быть скалярным запросом. Скалярный запрос – это запрос с результатами в виде одной строки и одного столбца. В противном случае запрос выдаст ошибку.
• Если запрос возвращает ноль строк, то для переменной устанавливается значение EMPTY, т.е. NULL.

Предположение: предположим, что у нас есть таблица «Guru99» с двумя столбцами, как показано ниже:

Мы будем использовать таблицу «Guru99» в дальнейших уроках

Пример 1: Когда подзапрос возвращает одну строку в результате.

Пример 2: когда подзапрос возвращает нулевую строку в результате

В данном конкретном случае значением переменной является ПУСТО, то есть ПУСТО (NULL).

ИСПОЛЬЗОВАНИЕ ВЫБРАТЬ

Так же, как SET, мы также можем использовать SELECT для присвоения значений переменным, после объявления переменной с помощью DECLARE. Ниже приведены различные способы присвоения значения с помощью SELECT:

Пример : присвоение значения переменной с помощью SELECT

Синтаксис:

Запрос:

Пример : присвоение значения нескольким переменным с помощью SELECT

Синтаксис:

Правила: В отличие от SET, SELECT может использоваться для присвоения значения нескольким переменным, разделенным запятой .

Пример : присвоение значения переменной с помощью подзапроса с помощью SELECT

Синтаксис:

Правила:

• Заключите запрос в круглые скобки.
• Запрос должен быть скалярным запросом. Скалярный запрос – это запрос с результатом в виде одной строки и одного столбца. В противном случае запрос выдаст ошибку.
• Если запрос возвращает ноль строк, то переменная имеет значение EMPTY, то есть NULL.
• Пересмотрите нашу таблицу ‘Guru99’

Пример 1: Когда подзапрос возвращает одну строку в результате.

Пример 2: когда подзапрос возвращает нулевую строку в результате

В данном конкретном случае переменная имеет значение ПУСТО, то есть NULL.

Пример 3. Присвойте значение переменной с помощью регулярного оператора SELECT.

Синтаксис:

Правила:

• В отличие от SET, если запрос приводит к нескольким строкам, тогда значение переменной устанавливается равным значению последней строки.
• Если запрос возвращает ноль строк, то для переменной устанавливается значение EMPTY, т.е. NULL.

Запрос 1: запрос возвращает одну строку.

Запрос 2: запрос возвращает несколько строк.

В этом особом случае значение переменной устанавливается равным значению последней строки .

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

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

Для замены аналитических функций часто используют запросы с самосоединением, сложные подзапросы и прочее. Большинство таких решений оказываются неэффективными с точки зрения производительности.

Также в MySQL нет рекурсии. Однако с некоторой частью задач, которые обычно решаются аналитическими функциями или рекурсией, можно справиться и средствами MySQL.

Одним из этих средств является уникальный, нехарактерный для прочих СУБД механизм работы с переменными внутри запроса SQL. Мы можем объявить переменную внутри запроса, менять ей значение и подставлять в SELECT для вывода. Причем порядок обработки строк в запросе и, как следствие, порядок присвоения значений переменным можно задать в пользовательской сортировке!

Предупреждение. В статье подразумевается, что обработка выражений в предложении SELECT осуществляется слева направо, однако официального подтверждения такого порядка обработки в документации MySQL нет. Это необходимо иметь в виду при смене версии сервера. Для гарантии последовательности вычисления можно использовать фиктивный оператор CASE или IF.

Аналог рекурсии

Рассмотрим простой пример, который генерирует последовательность Фибоначчи (в последовательности Фибоначчи каждый член равен сумме двух предыдущих, а первые 2 равны единице):

Данный запрос генерирует 18 чисел Фибоначчи, не считая первых двух:

Разберём теперь как оно работает.

В строчках 5) 6) генерируется 9 записей. Тут ничего необычного.

В строчке 7) мы объявляем две переменные @I, @J и присваиваем им 1.

В строке 3) происходит следующее: сначала переменной @I присваивается сумма двух переменных. Затем то же самое присваиваем переменной @J, причем с учетом того, что значение @I уже поменялось.

Другими словами, вычисления в SELECT выполняются слева направо – см. также замечание в начале статьи.

Причем изменение переменных осуществляется в каждой из наших 9 записей, т.е. при обработке каждой новой строки в переменных @I и @J будут содержаться значения, вычисленные при обработке предыдущей строки.

Чтобы решить эту же задачу средствами других СУБД, нам пришлось бы писать рекурсивный запрос!

Примечание:
Переменные нужно объявлять в отдельном подзапросе (строка 7), если бы мы объявили переменную в предложении SELECT, она, скорее всего, вычислилась бы только 1 раз (хотя конкретное поведение будет зависеть от версии сервера). Тип переменной определяется значением, которым она инициализирована. Этот тип может динамически меняться. Если переменной присвоить NULL, её типом будет BLOB.

Порядок обработки строк в SELECT, как было сказано выше, зависит от пользовательской сортировки. Простой пример нумерации строк в заданном порядке:

Аналоги аналитических функций

Переменные также можно использовать для замены аналитических функций. Далее несколько примеров. Для простоты будем считать, что все поля NOT NULL, а сортировка и партиционирование (PARTITION BY) происходят по одному полю. Использование NULL значений и более сложных сортировок сделает примеры более громоздкими, но суть не поменяет.

Для примеров создадим таблицу TestTable:

где
group_id – идентификатор группы (аналог окна аналитической функции);
order_id – уникальное поле, по которому будет производиться сортировка;
value – некоторое числовое значение.

Заполним нашу таблицу тестовыми данными:

Примеры замены некоторых аналитических функций.

1) ROW_NUMBER() OVER(ORDER BY order_id)

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10

2) ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY order_id)

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1

3) SUM(value) OVER(PARTITION BY group_id ORDER BY order_id)

group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1

4) LAG(value) OVER(PARTITION BY group_id ORDER BY order_id)

group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL

Для LEAD всё то же самое, только нужно сменить сортировку на ORDER BY group_id, order_id DESC

Для функций COUNT, MIN, MAX всё несколько сложнее, поскольку, пока мы не проанализируем все строчки в группе(окне), мы не сможем узнать значение функции. MS SQL, например, для этих целей «спулит» окно (временно помещает строки окна в скрытую буферную таблицу для повторного к ним обращения), в MySQL такой возможности нет. Но мы можем для каждого окна вычислить значение функции в последней строке при заданной сортировке (т.е. после анализа всего окна), а затем, отсортировав строки в окне в обратном порядке, проставить вычисленное значение по всему окну.

Таким образом, нам понадобится две сортировки. Чтобы итоговая сортировка осталась той же, что и в примерах выше, отсортируем сначала по полям group_id ASC, order_id DESC, затем по полям group_id ASC, order_id ASC.

5) COUNT(*) OVER(PARTITION BY group_id)

В первой сортировке мы просто нумеруем записи. Во второй всем строкам окна присваиваем максимальный номер, который и будет соответствовать количеству строк в окне.

group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1

Функции MAX и MIN вычисляются по аналогии. Приведу только пример для MAX:

6) MAX(value) OVER(PARTITION BY group_id)

group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

7) COUNT(DISTINCT value) OVER(PARTITION BY group_id)

Интересная вещь, которая отсутствует в MS SQL Server, но её можно вычислить с подзапросом, взяв MAX от RANK. Так же поступим и здесь. В первой сортировке вычислим RANK() OVER(PARTITION BY group_id ORDER BY value DESC), затем во второй сортировке проставим максимальное значение всем строкам в каждом окне:

group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

Производительность

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

1) Классический способ с самомоединением

Что на 10000 записей в таблице TestTable выдаёт:

Duration / Fetch
16.084 sec / 0.016 sec

2) С использованием переменных:

Duration / Fetch
0.016 sec / 0.015 sec

Результат говорит сам за себя. Однако надо понимать, что вычисленные с помощью переменных значения не оптимально использовать в условиях фильтрации. Сортировка и вычисление будут происходить для ВСЕХ строк, несмотря на то, что в итоге нам нужна только малая их часть.

Рассмотрим более подробно на примере такой задачи:

Вывести по 2 первые строки из таблицы TestTable для каждого значения group_id, отсортированных по order_id.

Вот как эта задача решалась бы в СУБД с поддержкой аналитических функций:

Однако оптимизатор MySQL ничего не знает о том, по каким правилам мы вычисляем поле RowNum. Ему придётся пронумеровать ВСЕ строки, и только потом отобрать нужные.

Теперь представьте, что у нас 1 миллион записей и 20 уникальных значений group_id. Т.е. чтобы выбрать 40 строк, MySQL будет вычислять значение RowNum для миллиона строк! Красивого решения этой задачи одним запросом в MySQL нет. Но можно сначала получить список уникальных значений group_id, например, так:

Затем средствами любого другого языка программирования сгенерировать запрос вида:

20 лёгких запросов отработают намного быстрее, чем вычисление RowNum для миллиона строк.

Declare Variables SQL Server

В SQL Server (Transact-SQL) переменная позволяет программисту временно хранить данные во время выполнения кода.

Синтаксис

Синтаксис объявления переменных в SQL Server с помощью оператора DECLARE:

Параметры или аргументы

variable_name — имя для назначения переменной.
datatype — тип данных для назначения переменной.
initial_value — необязательный. Это значение, которое первоначально было присвоено переменной при ее объявлении.