Программные объекты

Разбить на страницы

Показывать лекцию целиком

Вы научитесь:

создавать локальные временные таблицы;

создавать глобальные временные таблицы;

создавать локальные переменные;

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

использовать команду SET для установки значения переменной;

использовать команду SELECT для установки значения переменной.

Временные таблицы

Временные таблицы походят на обычные таблицы, но существуют только тогда, когда их используют. Они автоматически удаляются Microsoft SQL Server после того, как все пользователи закончили работу с ними.

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

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

Временные таблицы создаются такими же командами, с помощью которых Transact-SQL создает обычную таблицу – это CREATE или SELECT INTO. Различие состоит лишь в том, что имя временной таблицы начинается либо с #, либо с ##. Таким образом, первый оператор в представленном ниже листинге создает обычную таблицу, а второй – временную таблицу.

CREATE TABLE NormalTable (theKey INT PRIMARY KEY, theValue CHAR(20))
CREATE TABLE #TamporaryTable (theKey INT PRIMARY KEY, theValue CHAR(20))

Единственное обстоятельство, которое следует учитывать при создании временной таблицы, состоит в том, что она не может иметь ограничений внешнего ключа FOREIGN KEY. Все другие возможности оператора CREATE TABLE доступны – временные таблицы могут иметь проверочное ограничение CHECK, значение по умолчанию и любую необходимую вам структуру.

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

Область действия временной таблицы определяется ее именем. Временная таблица, имя которой содержится один значок решетки, например #MyTable, имеет локальную (local) область действия. Временная таблица, содержащая в имени два значка, например ##MyTable, имеет глобальную (global) область действия.

Локальные временные таблицы (иногда называемые личными временными таблицами) видимы только в создавшем их соединении. Никакие другие соединения (даже установленные этим же пользователем) не могут их видеть или иметь доступ к ним. Соединение, создавшее временную таблицу, может в любое время удалить ее, но если она после выхода пользователя из системы все еще существует, SQL Server завершает работу таблицы автоматически. Глобальные таблицы, создаваемые указанием в имени двойного символа решетки, доступны всем пользователям. В глобальной таблице не требуется назначения привилегий: любой пользователь получает привилегии автоматически.

Глобальные временные таблицы могут явно уничтожаться, или же SQL Server будет уничтожать их после того, как создавшее соединение закроется, и все текущее использование таблицы будет завершено. Например, если пользователь А создаст временную таблицу ##MyTable, пользователь В может выполнить команды через нее. Если пользователь А выйдет из системы, в то время как пользователь В использует таблицу ##MyTable, то любые исполняемые на данный момент команды SQL Server будут выполнены, но пользователь В не сможет больше исполнить каких-либо команд с помощью этой таблицы.

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

Временные таблицы создаются с помощью таких же команд, как и обычные таблицы: CREATE или SELECT INTO. Предоставляемая таблица является доступной для соединения (т. е., она не считается локальной таблицей, созданной другим соединением), и она может использоваться точно так же, как и обычная таблица.

Создайте локальную временную таблицу

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос) для открытия нового окна Query (Запрос).

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).

Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

Выберите сценарий CreateLocal и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).

Query Analyzer создаст временную таблицу.

В панели Object Browser выберите папку User Tables в базе данных tempdb.

Нажмите клавишу F5 для обновления содержимого окна Object Browser и откройте папку User Tables. Query Analyzer отобразит в списке таблицу dbo.#LocalTable.

Создайте глобальную временную таблицу

Не закрывая окно, содержащее сценарий CreateLocal, нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос) для открытия нового окна Query (Запрос).

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).

Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

Выберите сценарий CreateGlobal и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).

Query Analyzer создаст временную таблицу.

В панели Object Browser выберите папку User Tables в базе данных tempdb.

Нажмите клавишу F5 для обновления содержимого окна Object Browser. Query Analyzer отобразит в списке таблицу dbo.##GlobalTable.

Используйте локальную временную таблицу из текущего сеанса

Выберите окно Query (Запрос), содержащее сценарий CreateLocal.

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).

Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

Выберите сценарий UseLocal и нажмите кнопку Open (Открыть). Query Analyzer откроет сценарий.

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).

Query Analyzer выполнит оператор

Используйте глобальную временную таблицу из текущего сеанса

Выберите окно Query (Запрос), содержащее сценарий CreateGlobal.

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).

Query Analyzer отобразит диалоговое окно Open Query File (Открытие файл сценария).

Выберите сценарий UseGlobal и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).

Query Analyzer выполнит оператор

Используйте локальную временную таблицу из другого сеанса

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).

Query Analyzer откроет новое окно Query (Запрос).

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).

Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

Выберите сценарий UseLocal и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).

Поскольку локальная временная таблица не доступна в новом сеансе запроса, анализатор запросов Query Analyzer отобразит сообщение об ошибке.

Используйте глобальную временную таблицу из другого сеанса

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).

Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

Выберите сценарий UseGlobal и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).

Поскольку глобальная временная таблица доступна в новом сеансе запроса, анализатор запросов Query Analyzer отобразит результат.

Закройте два открытых окна запроса Query, но одно оставьте открытым для сохранения соединения с сервером. Если будет предложено сохранить изменения, нажмите No (Нет).

Переменные

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

Понятие о переменных

Переменные обозначаются префиксом @; например @MyVariable. Как и временные таблицы, переменные имеют две области действия: локальную и глобальную. Глобальные переменные обозначаются двойным символом @: @@VERSION.

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

Локальные переменные

Локальные переменные создаются с помощью оператора DECLARE, который имеет следующий синтаксис:

DECLARE @локальная_переменная тип_данных

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

DECLARE @var1 int, @var2 int

Большинство типов данных являются скалярными (scalar); то есть они содержат одно значение, такое как число или строка. Возможность объявления перемененных с табличным типом данных, является новшеством в SQL Server 2000. Синтаксис для создания переменной табличного типа показан ниже:

DECLARE @локкальная_переменная Table ({определение_таблицы})

В этом примере, определение_таблицы идентично обычному определению CREATE TABLE, за исключением того, что разрешается использование лишь следующих ограничений: PRIMARY KEY, UNIQUE KEY, NULL и CHECK.

Совет. Другим полезным типом данных для переменных является sql_variant. Переменные типа варианты могут содержать любой тип данных. Это позволяет вам назначать различные типы данных одной локальной переменной в ходе выполнения процедуры.

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

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

SET @myCharVariable = 'Hello, World'

использовать команду SELECT с указанием константы или переменной:

SELECT @myCharVariable = 'Hello, World'

использовать команду SELECT с указанием другого оператора SELECT:

SELECT @myCharVariable = MAX (OilName) FROM Oils

использовать команду INSERT INTO с указанием переменной табличного типа:

INSERT INTO @myTableVariable
   SELECT * FROM Oils

Обратите внимание, что в третьем случае ( SELECT с другим SELECT ) оператор присвоения (=) замещает второе ключевое слово в SELECT; оно второй раз не повторяется. Последний пример демонстрирует синтаксис INSERT INTO...SELECT команды INSERT INTO. Вы также можете использовать синтаксис INSERT INTO...VALUES:

INSERT INTO @myTableVariable
   VALUES ('The Value')

Глобальные переменные

Глобальные переменные обозначаются двойным символом @ (@@VERSION) и предоставляются программой SQL Server. Они не могут создаваться пользователем. Большинство глобальных переменных предоставляет информацию о текущем статусе SQL Server. Они все представлены в панели Object Browser в папке Common Functions.

Переменные конфигурирования

Наиболее часто используемые переменные конфигурирования приведены в таблице 25.1. Они предоставляют информацию о текущих установках различных свойств и параметров SQL Server.

Переменные конфигурирования
Переменная	Значение
@@CONNECTIONS	Число соединений или попыток соединения с момента последнего запуска сервера.
@@DATEFIRST	Возвращает число, обозначающее день недели (Monday=1, Sunday=7).
@@DBTS	Последнее значение колонки с отметкой времени, вставленной в базу данных.
@@LANGID	Идентификатор локального языка, использующегося в данный момент.
@@LANGUAGE	Название текущего языка.
@@OPTIONS	Возвращает значение текущей опции SET.
@@SERVERNAME	Имя локального сервера.
@@VERSION	Дата, версия и тип процессора текущей инсталляции.

Статистические переменные

Статистические переменные предоставляют информацию о процессах, выполненных SQL Server с момента последнего запуска. Основные переменные показаны в таблице 25.2.

Статические переменные
Переменная	Значение
@@CPU_BUSY	Время, потраченное процессором на работу с момента последнего запуска сервера.
@@IDLE	Время, бездействия SQL Server с момента последнего запуска сервера.
@@IO_BUSY	Время, которое потратил SQL Server на выполнение операций ввода и вывода с момента последнего запуска сервера.
@@TOTAL_ERRORS	Число ошибок чтения/записи диска с момента последнего запуска сервера.
@@TOTAL_READ	Число выполненных сервером операций чтения с диска с момента последнего запуска сервера.
@@TOTAL_WRITE	Число выполненных сервером операций записи на диск с момента последнего запуска сервера.

Системные переменные

Системные переменные показаны в таблице 25.3. Они предоставляют информацию о последних операциях с таблицами, выполненных сервером.

Системные переменные
Переменная	Значение
@@IDENTITY	Последнее значение идентификационной колонки, вставленной в базу данных.
@@ROWCOUNT	Количество строк, подвергшихся воздействию последнего оператора.

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

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

DECLARE @theOil char(20)
SET @theOil = 'Basil'

-- Эта команда будет выполнена
SELECT OilName, Description
FROM Oils
WHERE OilName = @theOil
Однако оба следующих оператора SELECT не будут выполнены:
DECLARE @theCommand char(10), @theField char(10)
SET @theCommand = 'SELECT'
SET @theField = 'OilName'

-- Эта команда не будет выполнена
@theCommand * FROM Oils

-- Как и эта
SELECT @theField from Oils

Объявите локальную переменную

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).

Query Analyzer откроет новое окно Query (Запрос).

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).

Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

Выберите сценарий CreateLocalVariable и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).

Query Analyzer выполнит сценарий и отобразит результат.

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

В окне запроса выберите панель редактирования Editor Pane или откройте новое окно Query (Запрос), если вы закрыли окно, использовавшееся в предыдущем упражнении.

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).

Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

Выберите сценарий SelectVariable и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).

Query Analyzer выполнит сценарий и отобразит результат.

Используйте табличные переменные

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).

Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

Выберите сценарий UseTableVariable и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).

Query Analyzer выполнит сценарий и отобразит результат.

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

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).

Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).

Выберите сценарий GlobalVariable и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).

В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).

Query Analyzer выполнит сценарий и отобразит результат.

Краткое содержание

Чтобы...	Синтаксис оператора SQL
Создать локальную временную таблицу	`CREATE TABLE @имя_таблицы (определение_таблицы)`
Создать глобальную временную таблицу	`CREATE TABLE @@имя_таблицы (определение_таблицы)`
Объявить скалярную локальную переменную	`DECLARE @имя_переменной`
Объявить локальную переменную табличного типа	`DECLARE @имя_переменной TABLE(определение_таблицы)`
Установить значение скалярной переменной команды	`SET @имя_переменной = использование SET константа_или_выражение`
Установить значение скалярной переменной, используя команду SELECT и константу	`SELECT @имя_переменной = константа_или_выражение`
Установить значение переменной, используя команду SELECT	`SELECT @имя_переменной = неявное выражение_столбца FROM имя_таблицы`
Добавить строки к переменным табличного типа	`INSERT INTO @имя_переменной опертор_выборки`

Страницы:

Вы научитесь:

создавать локальные временные таблицы;

создавать глобальные временные таблицы;

создавать локальные переменные;

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

использовать команду SET для установки значения переменной;

использовать команду SELECT для установки значения переменной.

Временные таблицы

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

CREATE TABLE NormalTable (theKey INT PRIMARY KEY, theValue CHAR(20))
CREATE TABLE #TamporaryTable (theKey INT PRIMARY KEY, theValue CHAR(20))