В Microsoft SQL Server есть особый тип данных TABLE, на основе которого мы можем создавать табличные переменные , для того чтобы использовать их в своих инструкциях и процедурах, и сегодня мы с Вами рассмотрим эти переменные, узнаем, как они объявляются и какие у этих переменных особенности.
Описание табличных переменных MS SQL Server
Табличные переменные – это переменные с особым типом данных TABLE, которые используются для временного хранения результирующего набора данных в виде строк таблицы. Появились они еще в 2005 версии SQL сервера. Использовать такие переменные можно и в хранимых процедурах, и в функциях, и в триггерах, и в обычных SQL пакетах. Создаются табличные переменные так же, как и обычные переменные, путем их объявления инструкцией DECLARE.
Переменные такого типа предназначены в качестве альтернативы временным таблицам. Если говорить о том, что лучше использовать табличные переменные или временные таблицы, то однозначного ответа нет, у табличных переменных есть и плюсы, и минусы. Например, лично мне нравиться использовать табличные переменные, потому что их удобно создавать (т.е. объявлять ) и не нужно думать об их удалении или очищение в конце инструкции, так как они автоматически очищаются (как и обычные переменные ). Но при этом табличные переменные лучше использовать только тогда, когда Вы собираетесь хранить в них небольшой объём данных, в противном случае рекомендуется использовать временные таблицы.
Преимущества табличных переменных в Microsoft SQL Server
- Табличные переменные ведут себя как локальные переменные. Они имеют точно определенную область применения;
- Табличные переменные автоматически очищаются в конце инструкции, где они были определены;
- При использовании табличных переменных в хранимых процедурах повторные компиляции происходят реже, чем при использовании временных таблиц;
- Транзакции с использованием переменных TABLE продолжаются только во время процесса обновления соответствующей переменной. За счет этого табличные переменные реже подвергаются блокировке и требуют меньше ресурсов для ведения журналов регистрации.
Недостатки табличных переменных в MS SQL Server
- Запросы, которые изменяют переменные TABLE, не создают параллельных планов выполнения запроса;
- Переменные TABLE не имеют статистики распределения и не запускают повторных компиляций, поэтому рекомендуется использовать их для небольшого количества строк;
- Табличные переменные нельзя изменить после их создания;
- Табличные переменные нельзя создавать путем инструкции SELECT INTO;
- Переменные TABLE не изменяются в случае откатов транзакций, так как имеют ограниченную область действия и не являются частью постоянных баз данных.
Примеры использования табличных переменных в Microsoft SQL Server
Сейчас давайте перейдем к практике, и для начала хотелось бы отметить, что в качестве сервера у меня выступает Microsoft SQL Server 2016 Express , другими словами все запросы ниже запускались на данной версии СУБД.
Сначала давайте создадим тестовую таблицу и заполним ее тестовыми данными, для того чтобы посмотреть, как можно использовать табличные переменные вместе с обычными таблицами.
CREATE TABLE TestTable(ProductId INT IDENTITY(1,1) NOT NULL, ProductName VARCHAR(50) NULL CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ProductId ASC)) GO INSERT INTO TestTable (ProductName) VALUES ("Компьютер"), ("Монитор"), ("Принтер") GO SELECT * FROM TestTable
С помощью инструкции CREATE TABLE я создал таблицу TestTable, затем для добавления данных в таблицу я использовал инструкцию INSERT совместно с конструктором табличных значений VALUES , затем с помощью SELECT сделал выборку из только что созданной таблицы.
Объявление табличной переменной и ее использование
В данном примере мы объявим табличную переменную, добавим в нее данных, и сделаем выборку из двух таблиц (табличной переменной и обычной таблицы ) с объединением.
Объявление табличной переменной DECLARE @TableVar TABLE(ProductId INT NOT NULL, Price MONEY NULL); --Добавление данных в табличную переменную INSERT INTO @TableVar (ProductId, Price) VALUES (1, 500), (2, 300), (3, 200) --Использование табличной переменной с объединением данных SELECT TTable.ProductId, TTable.ProductName, TVar.Price FROM @TableVar TVar LEFT JOIN TestTable TTable ON TVar.ProductId = TTable.ProductId
Создание табличной переменной с первичным ключом, ограничением UNIQUE и с некластеризованным индексом
В данном примере показано, как можно создавать первичный ключ, ограничения UNIQUE и некластеризованные индексы для табличных переменных. Возможность создания некластеризованного индекса появилась, начиная с версии Microsoft SQL Server 2014.
Объявление табличной переменной DECLARE @TableVar TABLE(ProductId INT NOT NULL PRIMARY KEY, --Первичный ключ ProductName VARCHAR(50) NOT NULL, Price MONEY NOT NULL, UNIQUE (ProductName, Price), --Ограничение INDEX IX_TableVar NONCLUSTERED (Price) --Некластеризованный индекс); --Добавление данных в табличную переменную INSERT INTO @TableVar (ProductId, ProductName, Price) VALUES (1, "Компьютер", 500), (2, "Монитор", 300), (3, "Принтер", 200); --Выборка данных SELECT ProductName FROM @TableVar WHERE Price > 200
На этом мой рассказ о табличных переменных закончен, если Вы хотите детально изучить язык T-SQL, то рекомендую почитать мою книгу «Путь программиста T-SQL », надеюсь, материал был Вам полезен, пока!
Наверное, одним из первых вопросов, возникающих у начинающих программистов на T-SQL, это вопрос "А как получить выборку из таблицы, имя которой занесено в переменную?"
Т.к. в T-SQL нет возможности использовать в некоторых частях запроса значения переменных, то единственным доступным решением является использование динамического запроса. Идея очень проста: в специально определнной переменной "собирается" строка запроса, которая должна выполняться. Далее эта строка запускается на выполнение. Запуск можно осуществить двумя способами
- с помощью команды EXECUTE
- с помощью системной процедуры sp_executesql.
Выглядит это приблизительно так
I.Особенности динамического запроса
1. Динамический запрос ВСЕГДА выполняется В ТОМ-ЖЕ КОННЕКТЕ и КАК ОТДЕЛЬНЫЙ ПАКЕТ(batch). Другими словами при использовании такого запроса,
- вы ни имеете доступа к локальным переменным, объявленым до вызова динамического запроса (однако возможен доступ к cозданным ранее временным таблицам)
- локальные временые таблицы и переменные, созданные во время выполнения команды exec, будут недоступны в вызывающей процедуре, т.к. будут удалены по окончании пакета, в котором выполняется exec.
2. Динамический запрос ВСЕГДА выполняется с ПРАВАМИ ПОЛЬЗОВАТЕЛЯ, ВЫЗВАВШЕГО ПРОЦЕДУРУ, а не с правами владельца процедуры. Другими словами, если владельцем процедуры Procedure1 является User1, который имеет права к таблице Table1, то для пользователя User2 мало назначить права на выполнение процедуры Procedure1, если обращение в ней к таблице Table1 идет через динамический запрос. Придется давать ему соответствующие права и непосредственно для Table1.
3. Компиляция запроса происходят непосредственно перед его вызовом. Т.е. обо всех синтаксических ошибках вы узнаете только в этот момент.
II.Особенности использования команда exec
1. Команда exec поддерживает к качестве аргумента конкатенацю строк и/или переменных. НО не поддерживатеся конкатенация результатов выполнения функций, т.е. конструкции вида
exec
("SELECT * FROM "
+ LEFT
(@TableName, 10
))
запрещены к использованию.
2. В команде нет входных/выходных параметров.
III.Особенности использования процедуры sp_executesql
1. Процедура НЕ поддерживает в качестве параметров конкатенацию строк и/или переменных.
2. Текст запроса должен быть либо переменной типа NVARCHAR/NCHAR, либо такого же типа стринговой константой.
3. Имеется возможность передачи параметров в выполняемый скрипт и получение выходных значений
Последнее явно в документации не описано, поэтому вот несколько примеров
В данном примере в динамический запрос передаются 4 переменные, три из которых являюся выходными
declare @var1 int , @var2 varchar (100 ), @var3 varchar (100 ), @var4 int declare @mysql nvarchar (4000 ) set @mysql = "set @var1 = @var1 + @var4; set @var2 = " "CCCC" "; set @var3 = @var3 + " "dddd" "" set @var1 = 0 set @var2 = "BBBB" set @var3 = "AAAA" set @var4 = 10 select @var1, @var2, @var3 exec sp_executesql @mysql, N"@var1 int out, @var2 varchar(100) out, @var3 varchar(100) out, @var4 int" , @var1 = @var1 out , @var2 = @var2 out , @var3 = @var3 out , @var4 = @var4 select @var1, @var2, @var3
В данном примере в динамическом запросе открывается курсор, который доступен в вызывающей процедуре через выходную переменную
USE pubs declare @cur cursor exec sp_executesql N"set @curvar= cursor local for select top 10 au_id, au_lname, au_fname from authors open @curvar" , N"@curvar cursor output " , @curvar=@cur output FETCH NEXT FROM @cur WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM @cur END
Резюме(более IMHO, чем обязательные требования)
Динамический запрос очень полезная и иногда просто незаменимая вещь, НО способы его реализации и конкретно через вызов в отдельном пакете с правами пользователя, вызвавшего процедуру, принижают его практическое МАССОВОЕ применение.
Последнее обновление: 14.08.2017
Через переменные мы можем передавать данные в запросы. И также мы можем получать данные, которые являются результатом запросов, в переменные. Например, при выборке из таблиц с помощью команды SELECT мы можем извлекать данные в переменную с помощью следующего синтаксиса:
SELECT @переменная_1 = спецификация_столбца_1, @переменная_2 = спецификация_столбца_2, ...................................... @переменная_N = спецификация_столбца_N
Кроме того, в выражении SET значение, присваиваемое переменной, также может быть результатом команды SELECT.
Например, пусть у нас будут следующие таблицы:
CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL); CREATE TABLE Customers (Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(30) NOT NULL); CREATE TABLE Orders (Id INT IDENTITY PRIMARY KEY, ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE, CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE, CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL);
Используем переменные при извлечении данных:
DECLARE @maxPrice MONEY, @minPrice MONEY, @dif MONEY, @count INT SET @count = (SELECT SUM(ProductCount) FROM Orders); SELECT @minPrice=MIN(Price), @maxPrice = MAX(Price) FROM Products SET @dif = @maxPrice - @minPrice; PRINT "Всего продано: " + STR(@count, 5) + " товарa(ов)"; PRINT "Разница между максимальной и минимальной ценой: " + STR(@dif)
В данном случае переменная @count будет содержать сумму всех значений из столбца ProductCount таблицы Orders, то есть общее количество проданных товаров.
Переменные @min и @max хранят соответственно минимальное и максимальное значения столбца Price из таблицы Products, а переменная @dif - разницу между этими значениями. И подобно простым значениям, переменные также могут участвовать в операциях.
Другой пример:
DECLARE @sum MONEY, @id INT, @prodid INT, @name NVARCHAR(20); SET @id=2; SELECT @sum = SUM(Orders.Price*Orders.ProductCount), @name=Products.ProductName, @prodid = Products.Id FROM Orders INNER JOIN Products ON ProductId = Products.Id GROUP BY Products.ProductName, Products.Id HAVING Products.Id=@id PRINT "Товар " + @name + " продан на сумму " + STR(@sum)
Здесь извлекаемые данные из двух таблиц Products и Orders группируются по столбцам Id и ProductName из таблицы Products. Затем данные фильтруются по столбцу Id из Products. А извлеченные данные попадают в переменные @sum, @name, @prodid.
Инструкция SET присваивает значение переменной времени выполнения. Эти переменные могут быть системными переменными, специфичными для платформы, или пользовательскими переменными.
Ключевые слова
переменная
Обозначает системную или пользовательскую переменную.
значение
Обозначает строковое или числовое значение, соответствующее системной или пользовательской переменной.
Общие правила
Значения переменных устанавливаются на время сеанса. Значения, присвоенные переменной, должны соответствовать типу данных этой переменной. Например, вы не можете присвоить строковое значение переменной, которая объявлена с числовым типом данных. Команда, с помощью которой переменная создается, на разных платформах разная. Например, в DB2, Oracle и SQL Server используется инструкция DECLARE, в которой объявляется имя и тип переменной, однако на других платформах могут использоваться другие способы создания переменных.
Значение, которое присваивается переменной, не обязательно должно быть константой. Это может быть динамически генерируемое значение, создаваемое на основе подзапроса. Например, мы можем присвоить переменной emp_id_var максимальное значение идентификатора сотрудника (emp_id)|.
DECLARE emp_icLvar CHAR(5) SET empty var=(SELECT MAX(emp_id) FROM employees WHERE type="F")
В этом примере type F обозначает, что сотрудник работает полный день (full-time) и состоит на окладе.
Инструкция SET очень легко переносится с платформы на платформу. Только в Oracle используется несколько другая схема присвоения значения переменной. В следующем примере мы объявим в SQL Server переменную с именем emp_id_var и присвоим ей значение.
DECLARE emp_id var CHAR(5) SET emp_id_var="67888";
А теперь мы выполним ту же самую операцию для сервера Oracle.
DECLARE emp_id_var CHAR(5); emp_id_var:= "67888";
DB2
Платформа DB2 поддерживает базовую форму инструкции SET для присвоения значений локальным переменным, выходным параметрам или особым регистрам. В одной инструкции SET можно присваивать сразу несколько значений. Также эта инструкция позволяет присваивать значения столбцам базовой таблицы в триггере. Нельзя присвоить значения в одной инструкции двум типам переменных.
SET переменная={значение | NULL | DEFAULT} [, …]
Синтаксические элементы в DB2 следующие.
переменная
Указывается целевая переменная. Переменные SQL должны быть объявлены до использования. Переменная также может обозначать столбец базовой таблицы триггера.
значение
Указывается значение переменной в соответствии с ее типом данных. При присвоении значений столбцам в триггере вы также можете использовать ссылки на корреляционные имена OLD и NEW. За дополнительной информацией об этом обращайтесь к подразделу, посвященному DB2, раздела «Инструкция CREATE/ ALTER TRIGGER)).
Столбцу и переменной, которые могут принимать значения NULL, присваивается значение NULL.
Столбцам, которые создавались с указанием предложения WITH DEFAULT или IDENTITY, присваивается значение по умолчанию. Это предложение также присваивает пустое значение (NULL) тем столбцам, которые принимают пустые значения и при этом не определены с предложениями DEFAULTтя IDENTITY. Присвоим значение одной переменной.
SET new var.order_qty=125;
Если вы присваиваете значения в одной инструкции сразу нескольким переменным, то количество переменных слева от знака равенства должно в точности соответствовать количеству значений справа. Присвоим значения нескольким переменным.
SET new_var. order_qty=125, new_.va г. discount =
При использовании такого варианта, как SET переменная=SELECT значения результирующего набора инструкции SELECT должны в точности соответствовать переменным по количеству, положению и типу данных. Если инструкция SELECT не возвращает никаких значений, то переменным присваиваются значения NULL. Также для присвоения значений нескольким переменным в одной инструкции вы можете использовать инструкцию SELECT…INTO.
MySQL
Ключевое слово SET имеет в MySQL несколько способов использования. Во-первых, SET - это тип данных MySQL, в котором может быть несколько значений, разделенных запятыми. (За информацией об этой области применения обращайтесь к главе 2, раздел «Типы данных MySQL».) Кроме того, инструкция SET может присваивать значения пользовательской переменной. Здесь описывается именно этот способ применения. Синтаксис следующий:
SET переменная - значение […]
При присвоении в одной инструкции значений нескольким переменным эти значения отделяются друг от друга запятыми.
SET new_var.order_.aty - 125. new_var. discount - 4;
Кроме того, MySQL позволяет использовать инструкцию SELECT для присвоения значений переменным точно так же, как это описывается в разделе, посвященном стандарту ANSI. Однако у метода с использованием инструкции SELECT есть несколько слабых мест. Главная проблема состоит в том, что значения не присваиваются и инструкции SELECT немедленно. Таким образом, в следующем примере:
SELECT (_>new_var - row_id) AS а, (@new_var + 3) AS b FROM Lable_name;
переменная @new_var не получит нового значения row id + 3. Сохранится то значение, которое она имела в начале инструкции. Поэтому хорошей практикой является присвоение переменной только одного значения за раз.
Oracle
Предложение SET как метод присваивания значения переменным в Oracle не поддерживается. Вместо этого пользовательским переменным значения присваиваются при помощи оператора присваивания:=. Базовый синтаксис следующий.
PostgreSQL
В PostgreSQL команда SET используется для присваивания значения переменной во время выполнения.
SET переменная{ТО | -} {значение | DEFAULT}
Переменной во время выполнения можно присвоить строковое постоянное значение. При использовании ключевого слова DEFAULT переменной времени выполнения присваивается значение по умолчанию. Платформа PostgreSQL 7.2 поддерживает следующие переменные.
CLIENT ENCODING NAMES
Устанавливается мультибайтовая кодировка для клиентских систем PostgreSQL, скомпонованных с мультибайтовой поддержкой.
DATESTYLE
Устанавливается стиль, используемый для отображения даты и времени. Поддерживаются следующие стили.
Дата и время отображаются в формате ГПТ-ММ-ДД ЧЧ:ММ:СС (заданный по умолчанию стиль ISO 8601).
Дата и время отображаются в стиле Oracle/Ingres, а не в том стиле, который предписывается стандартом ANSI SQL.
PostgreSQL
Дата и время отображаются в длинном формате PostgreSQL, но не длиннее, чем задано по умолчанию.
Дата и время отображаются в виде ДД.ММ.ГГГГ. Вы можете уточнять стили SQL и Postgresql, используя ключевые слова European, US и NonEuropean, которые придают датам форматы дд/мм/гггг, мм/дд/гггг и мм/дд/гггг соответственно. Например: SETDATESTYLE=SQL, European.
Устанавливается начальное значение для внутреннего генератора случайных чисел. Значение может представлять собой любое число с плавающей точкой в диапазоне от 0 до 1, умноженное на 231-1. Это значение также можно установить при помощи функции PostgreSQL setseed. Например:
SELECT setseed(value); SEVER ENCODING;
Устанавливается мультибайтовая кодировка для серверов, скомпонованных с мульти-байтовой поддержкой.
Ниже приводится пример установки формата даты и времени стиля Oracle и European.
SET DATESTYLE ТО sql, European;
SOL Server
Платформа SQL Server поддерживает присваивание значений переменным при помощи инструкции SET, если эти переменные были ранее созданы при помощи инструкции DECLARE, а также присваивание значений переменным курсоров. (Также SQL Server использует инструкцию SET для других целей, например для включения и отключения флагов сеанса командами типа SETNOCOUNT ON.) Синтаксис, специфичный для данной платформы, приводится ниже.
Данная инструкция не поддерживает ключевое слово DEFAULT, но во всех прочих отношениях поддерживает синтаксис ANSI. Значение имя_сервера должно ссылаться на соединение, указанное в предыдущей инструкции CONNECT, либо в форме константы, либо в форме переменной.
Я хочу использовать одно и то же значение для разных запросов из разных БД
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 "
..." -- Result: ...
