Переменные и временные таблицы

SQL Server позволяет использовать два вида переменных — локальные и глобальные. Имя локальной переменной начинается с одного символа глобальной — с двух «@@». Глобальные переменные позволяют только прочитать значение. Локальные переменные можно создавать, присваивать им значения и читать их. Набор предоставляемых глобальных переменных достаточно обширен, в табл. 10.1 приведен перечень наиболее часто используемых [20, 23, 24].

Таблица 10.1

Часто используемые глобальные переменные

Ниже приведен текст запроса, позволяющего получить версию SQL Server и заданный для текущей сессии язык. В результатах запроса столбцы буду называться «Version» и «Lang», соотвественно:

SELECT 00VERSION as [Version], 00LANGUAGE as [Lang].

Для объявления локальных переменных в Т-SQL используется оператор DECLARE, его формат приведен ниже:

DECLARE.

{ [01ocal_var [AS] data_type | [ = value ] }.

I { 0cursor_var CURSOR }.

} [,…n].

I {0table_var [AS]).

При определении обычной переменной должны указываться ее имя, тип, опционально может задаваться значение. Ключевое слово «AS» между именем переменной и типом данных ставить не обязательно. У переменной может быть любой встроенный или пользовательский тип данных, поддерживаемый SQL Server, за исключением text, ntext, image [20]. Ниже приведен пример объявления целочисленной переменной @i и инициализации ее значением 1:

DECLARE 0i int =1.

В одном операторе DECLARE можно объявить несколько перемененных, разделяя их определения запятыми:

DECLARE (c)Group nvarchar (50), 0Sales money.

С помощью DECLARE может создаваться и табличная переменная. В этом случае после ее названия (и необязательного ключевого слова «AS») должно идти слово «TABLE» и описание таблицы. Табличную переменную нужно создавать в отдельном операторе DECLARE:

DECLARE (c)МуТаЫ TABLE (.

ID int NOT NULL,.

AUTHOR varchar (30),.

TITLE varchar (50)).

Вопрос, связанный с объявлением курсоров, будет рассмотрен в параграфе 10.6.

Присвоить значение локальной переменной можно с помощью операторов SET или SELECT. Упрощенный формат оператора SET, используемый чаще всего, представлен ниже:

SET @local_var = expression.

Здесь @local_var — имя локальной переменной; expression — выражение, описывающее присваиваемое значение. Например, в следующем фрагменте кода объявляется переменная @MyVar и ей присваивается значение «Пробный текст» :

DECLARE 0MyVar nchar (20);

SET @MyVar = 'Пробный текст';

В SQL Server, начиная с версии 2008, наряду с обычным оператором присваивания может использоваться составной. В табл. 10.2 приведены варианты его записи.

Таблица 10.2.

Составной оператор присваивания

Например, SET 0 j = @ j + 2 будет аналогично выражению SET 0 j +=2. Переменной можно присвоить и значение, полученное в результате выполнения запроса. Ниже приведен подобный пример:

DECLARE 0rows int;

SET 0rows = (SELECT COUNT (*) FROM Bookl).

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

DECLARE 0st varchar (50);

SET 0st = (SELECT Author, Title FROM Bookl).

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

SELECT (01ocal_var { = I += I -= I *= I /= I %= I S= I ^= I |= } expression) [,…n] [;].

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

DECLARE 0MyVarl nchar (20);

DECLARE 0i int =1;

SELECT gMyVarl = 'Пробный текст', @i+=l;

SELECT gMyVarl AS [Text], 0i AS [Number].

Необходимо отметить, что SQL Server не допускает присваивания и выборки значений в одном операторе SELECT. Поэтому выполнение приведенного ниже фрагмента кода приведет к сообщению об ошибке:

SELECT gMyVarl, gi+=l.

Чтобы избегать путаницы, рекомендуется присваивать значения с помощью оператора SET.

Говоря про область действия локальных переменных, необходимо ввести понятие пакета заданий. Для SQL Server пакет заданий или просто пакет (англ. batch) — это набор команд, отправляемых серверу БД клиентским приложением, которые подвергаются синтаксическому анализу и выполняются как единый блок. При работе из среды SQL Server Management Studio для указания границ пакетов используется инструкция до. Переменные локальны по отношению к пакету, в котором они определены [16]. Попытка использовать переменную, определенную в другом пакете, приведет к ошибке.

В примере ниже первый оператор print сработает корректно и выведет в сообщении значение переменной gst. Второй оператор print, стоящий после ключевого слова до, закончится с ошибкой, так как в новом пакете переменная 0st не определена:

DECLARE 0st varchar (50);

SET 0st='test';

/* этот оператор отработает нормально */.

print gst до.

— здесь будет ошибка, т.к. 0st уже не существует.

print gst.

Этот же пример иллюстрирует принятые в SQL Server правила оформления комментариев. Последовательность " -" означает, что далее до конца строки идет комментарий. Комментарий на несколько строк начинается с «/*» и заканчивается «*/» .

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

DECLARE gMyTab TABLE (.

Id int primary key,.

Author varchar (30),.

Title varchar (50));

INSERT INTO gMyTab (Id, Author, Title).

SELECT DISTINCT [Id],[Author],[Title].

FROM dbo.Bookl.

WHERE [Year]>2000;

Select * FROM SMyTab.

В литературе отмечается, что с точки зрения производительности табличные переменные лучше применять при работе с небольшими объемами данных (несколько строк), а если данных много, рекомендуется использовать временные таблицы |16|.

SQL Server позволяет создавать локальные и глобальные временные таблицы. Все виды временных таблиц создаются в БД для временных объектов tempdb.

Локальная временная таблица видна только в создавшем ее сеансе, на уровне ее создания и на всех внутренних уровнях стека вызовов (внутренние процедуры, функции и т. д.). Признак локальной временной таблицы — префикс «#» в имени. SQL Server автоматически уничтожает временную таблицу, когда уровень ее создания в стеке вызовов выходит за пределы области видимости.

Глобальная временная таблица видна во всех сеансах, а не только в создавшем ее. Такие таблицы уничтожаются, когда отключается сеанс, в рамках которого она создавалась, и на таблицу нет активных ссылок. Имя глобальной временной таблицы начинается с «##» .

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

use MyTest.

go.

CREATE TABLE IMyTab (.

Id int primary key.

Author varchar (30),.

Title varchar (50));

INSERT INTO #MyTab (Id, Author, Title).

SELECT DISTINCT [Id], [Author],[Title].

FROM dbo.Bookl.

WHERE [Year]>2000;

SELECT * FROM #MyTab.

go.

• —этот запрос тоже выполнится без ошибки
• —т.к. сеанс один, хотя пакеты заданий и разные

SELECT * FROM #MyTab.

go.

-удаляем таблицу.

DROP TABLE #МуTab.

Хочется отметить, что несмотря на явное указание на использование БД MyTest, временная таблица #МуТаb создается именно в БД tempdb (рис. 10.1). Кроме того, границы пакета команд не влияют на область видимости временных таблиц, и второй оператор SELECT, находящийся после инструкции go, отработает так же, как и первый.

Оператором DROP TABLE временная таблица явным образом удаляется, дожидаться ее автоматического удаления не обязательно.

Рис. 10.1. Временная таблица в базе tempdb.