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

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

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

Источник

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

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Параллельное хранилище данных

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

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

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

Следующий скрипт создает небольшую тестовую таблицу из 26 строк. Переменная используется в скрипте в качестве:

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

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

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

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

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

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

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

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

Переменные имеют локальную область видимости и доступны только внутри пакета или процедуры, где они объявлены. В следующем примере вложенная область видимости, созданная для выполнения процедуры sp_executesql, не имеет доступа к переменной, объявленной в более высокой области видимости, и возвращает ошибку:

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

При объявлении переменной присваивается значение NULL. Чтобы изменить значение переменной, применяется инструкция SET. Этот способ присвоения значений переменным является предпочтительным. Кроме того, переменной можно присвоить значение, указав ее в списке выбора инструкции SELECT.

Чтобы присвоить значение переменной при помощи инструкции SET, необходимо указать ее имя и присваиваемое значение. Этот способ присвоения значений переменным является предпочтительным. Например, следующий пакет объявляет две переменные, присваивает им значения и использует их в предложении WHERE инструкции SELECT :

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

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

Если инструкция SELECT возвращает более одной строки и переменная ссылается на нескалярное выражение, ей присваивается значение, которое возвращается для выражения в последней строке результирующего набора. Например, в следующем пакете переменной @EmpIDVariable присваивается значение идентификатора BusinessEntityID последней возвращенной строки, равное 1:

Источник

Русские Блоги

Операторы DECLARE, SET, PRINT T-SQL

1. ЗАЯВИТЬ заявление

Оператор DECLARE — это оператор объявления данных, основная функция которого — объявление локальных переменных, функций, хранимых процедур или курсорных переменных и т. Д .;

Основной формат синтаксиса:

DECLARE < < @local_variable [AS] data_type >| [= value] > [. n]

@local_variable — это имя переменной, которое должно начинаться с @, data_type — это тип данных переменной, а value — значение объявленной переменной (необязательно)

Пример: объявлены и назначены две локальные переменные: имя пользователя и пароль.

Результаты приведены ниже:

2. Заявление SET

Оператор SET — это оператор присваивания данных, который может присваивать значения локальным переменным, а также может использоваться для установки параметров обработки системы в SQL Server при выполнении команд SQL;

Основной формат синтаксиса:

УСТАНОВИТЬ вариант

Первый оператор SET @local_variable — это имя локальной переменной, value — это значение локальной переменной, а expression — это выражение

Второй оператор SET указывает назначение параметров при выполнении команд SQL.

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

Результаты приведены ниже:

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

Тот же эффект, что и назначение SET

3. Распечатать заявление

Оператор PRINT — это оператор вывода данных, который может возвращать клиенту определенную пользователем информацию, а также может отображать строковые значения локальных и глобальных переменных;

Основной формат синтаксиса:

PRINT msg_str | @local_varible | string_expr

msg_str: строковая константа или строковая константа Unicode, переменная символьного типа данных @local_varible, строковое выражение string_expr

Источник

PRINT (Transact-SQL)

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Параллельное хранилище данных

Возвращает клиенту пользовательское сообщение.

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

msg_str
Символьная строка или строковая константа Юникода. Дополнительные сведения см. в статье Константы (Transact-SQL).

@ local_variable
Переменная любого допустимого символьного типа данных. Аргумент @ local_variable должен иметь тип char, nchar, varchar или nvarchar либо должен неявно преобразовываться в эти типы данных.

string_expr
Выражение, возвращающее строку. Может содержать объединенные буквенные значения, функции и переменные. Дополнительные сведения см. в разделе Выражения (Transact-SQL).

Комментарии

Строка сообщения может иметь длину до 8 000 символов для строки, отличной от Юникода, и 4 000 символов для строки в Юникоде. Более длинные строки усекаются. Типы данных varchar(max) и nvarchar(max) усекаются до типов данных, которые не длиннее varchar(8000) и nvarchar(4000).

Для возвращения сообщений можно также использовать функцию RAISERROR. Преимущества функции RAISERROR перед функцией PRINT:

функция RAISERROR поддерживает вставку аргументов в строку сообщения об ошибке с помощью механизма, основанного на функции printf из стандартной библиотеки языка С;

в дополнение к текстовому сообщению, функция RAISERROR может в сообщении указать уникальный номер ошибки, степень ее серьезности и код состояния;

функция RAISERROR может быть использована для возвращения пользовательских сообщений, созданных с помощью системной хранимой процедуры sp_addmessage.

Примеры

A. Печать, выполняемая по условию (IF EXISTS)

В следующем примере инструкция PRINT применяется для возвращения сообщения по условию.

Б. Построение и отображение строки

В следующем примере результаты функции GETDATE преобразуются в тип данных nvarchar и объединяются с текстовой строкой для последующего возврата функцией PRINT .

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

В. Печать, выполняемая по условию

В следующем примере инструкция PRINT применяется для возвращения сообщения по условию.

Источник

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 для миллиона строк.

Источник