Временные таблицы и пакетные запросы

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

Запрос, служащий источником данных, является вложенным, а запрос, который эти данные использует, является основным, или внешним.

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

Казалось бы, в этом нет ничего особенно сложного. Свяжите левым соединением таблицы справочника Номенклатура и документа ПриходнаяНакладная по ссылкам товаров и выведите требуемые поля из обеих таблиц. При этом упорядочьте результат запроса по наименованиям товаров в порядке иерархии справочника (листинг 11.33).

Листинг **11.**33. Вывод всех товаров в порядке иерархии справочника «Номенклатура» с данными об их поступлении

ВЫБРАТЬ

Номенклатура.Наименование КАК Наименование,

ПриходнаяНакладнаяМатериалы.Ссылка.Дата КАК Дата,

ПриходнаяНакладнаяМатериалы.Ссылка.Номер КАК Номер

ИЗ

Справочник.Номенклатура КАК Номенклатура

ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПриходнаяНакладная.Материалы КАК ПриходнаяНакладнаяМатериалы

ПО Номенклатура.Ссылка = ПриходнаяНакладнаяМатериалы.Материал

 

УПОРЯДОЧИТЬ ПО

Номенклатура.Ссылка ИЕРАРХИЯ

АВТОУПОРЯДОЧИВАНИЕ

Результат выполнения запроса представлен на рис. 11.32.

Рис. 11.32. Вывод всех товаров в порядке иерархии справочника «Номенклатура» с данными об их поступлении

На реальной базе, конечно, список товаров может быть очень большим. Предположим, вы хотите получить тот же список, но с данными о поступлениях товаров только из приходной накладной № 1.

Для этого наложите условие на поле Номер приходной накладной (листинг 11.34).

**Листинг 1****1.**34. Вывод всех товаров в порядке иерархии справочника «Номенклатура» с данными об их поступлении из приходной накладной № 1

ВЫБРАТЬ

Номенклатура.Наименование КАК Наименование,

ПриходнаяНакладнаяМатериалы.Ссылка.Дата КАК Дата,

ПриходнаяНакладнаяМатериалы.Ссылка.Номер КАК Номер

ИЗ

Справочник.Номенклатура КАК Номенклатура

ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПриходнаяНакладная.Материалы КАК ПриходнаяНакладнаяМатериалы

ПО (Номенклатура.Ссылка = ПриходнаяНакладнаяМатериалы.Материал)

ГДЕ

ПриходнаяНакладнаяМатериалы.Ссылка.Номер = "000000001"

 

УПОРЯДОЧИТЬ ПО

Номенклатура.Ссылка ИЕРАРХИЯ

АВТОУПОРЯДОЧИВАНИЕ

Вы видите, что результат запроса несколько не такой, как вы ожидали (рис. 11.33).

Рис. 11.33. Вывод товаров в порядке иерархии справочника «Номенклатура» с данными об их поступлении из приходной накладной № 1

В результате выполнения запроса отражена только та номенклатура, которая присутствует в приходной накладной № 1, и отсутствует иерархия справочника Номенклатура, так как в результате запроса нет данных для построения иерархии. Так произошло потому, что при накладывании условия отбора на поле правой таблицы (ПриходнаяНакладнаяМатериалы) при левом соединении тип соединения становится внутренним.

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

Листинг **11.**35. Вывод всех товаров в порядке иерархии справочника «Номенклатура» с данными об их поступлении из приходной накладной № 1

ВЫБРАТЬ

Номенклатура.Наименование КАК Наименование,

НакладнаяМатериалы.Дата КАК Дата,

НакладнаяМатериалы.Номер КАК Номер

ИЗ

Справочник.Номенклатура КАК Номенклатура

ЛЕВОЕ СОЕДИНЕНИЕ

(ВЫБРАТЬ

ПриходнаяНакладнаяМатериалы.Материал КАК Материал,

ПриходнаяНакладнаяМатериалы.Ссылка.Дата КАК Дата,

ПриходнаяНакладнаяМатериалы.Ссылка.Номер КАК Номер

ИЗ

Документ.ПриходнаяНакладная.Материалы КАК ПриходнаяНакладнаяМатериалы

ГДЕ

ПриходнаяНакладнаяМатериалы.Ссылка.Номер = "000000001") КАК НакладнаяМатериалы

ПО Номенклатура.Ссылка = НакладнаяМатериалы.Материал

 

УПОРЯДОЧИТЬ ПО

Номенклатура.Ссылка ИЕРАРХИЯ

АВТОУПОРЯДОЧИВАНИЕ

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

Рис. 11.34. Вывод всех товаров в порядке иерархии справочника «Номенклатура» с данными об их поступлении из приходной накладной № 1

Однако вы видите, что текст запроса (см. листинг 11.35) стал довольно громоздким и сложным для восприятия. С этой точки зрения лучше поместить результат вложенного запроса во временную таблицу и затем использовать ее как источник вашего основного запроса.

Временная таблица не существует в базе данных. Это просто некоторая область в памяти компьютера, которая создается на ограниченное время, как, например, это происходит при создании какой-либо переменной встроенного языка. Эта область создается и заполняется данными при выполнении запроса, содержащего ключевое слово ПОМЕСТИТЬ, после которого следует произвольное имя временной таблицы.

Возьмите только текст вложенного запроса и дополните его предложением ПОМЕСТИТЬ, которое располагается сразу после списка полей выборки (листинг 11.36).

Листинг **11.**36. Помещение данных о поступлениях товаров во временную таблицу

ВЫБРАТЬ

ПриходнаяНакладнаяМатериалы.Материал КАК Материал,

ПриходнаяНакладнаяМатериалы.Ссылка.Дата КАК Дата,

ПриходнаяНакладнаяМатериалы.Ссылка.Номер КАК Номер

ПОМЕСТИТЬ НакладнаяМатериалы

ИЗ

Документ.ПриходнаяНакладная.Материалы КАК ПриходнаяНакладнаяМатериалы

ГДЕ

ПриходнаяНакладнаяМатериалы.Ссылка.Номер = "000000001"

Примечание

Заметьте, что для быстрой и эффективной выборки данных из временной таблицы желательно проиндексировать эту таблицу по полям, которые будут затем участвовать в условии отбора или в условии соединения. Делается это с помощью предложения ИНДЕКСИРОВАТЬ ПО.

Чтобы увидеть данные, помещенные во временную таблицу, в консоли запросов нажмите Выполнить запрос с временными таблицами рядом с кнопкой Выполнить (рис. 11.35).

Рис. 11.35. Данные временной таблицы «НакладнаяМатериалы»

Итак, в результате выполнения запроса (листинг 11.36) вы поместили данные о поступлениях товаров во временную таблицу с именем НакладнаяМатериалы.

Теперь эту таблицу можно использовать в качестве источника других запросов точно так же, как и таблицу базы данных. Но сделать это можно либо из встроенного языка с помощью менеджера временных таблиц (этот вариант вы рассмотрите позже, в разделе «Использование временных таблиц с помощью встроенного языка»), либо с помощью пакетного запроса. Этот вариант вы рассмотрите сейчас.

Пакетный запрос содержит последовательность запросов, разделенных символом «;» (точка с запятой). При выполнении пакетного запроса входящие в него запросы выполняются друг за другом. При этом если при выполнении одного из запросов была создана временная таблица, то следующие за ним запросы при их выполнении могут использовать данные этой временной таблицы.

Таким образом, после запроса из листинга 11.36 поставьте точку с запятой и допишите выборку из справочника Номенклатура с левым соединением с временной таблицей НакладнаяМатериалы (листинг 11.37).

Листинг **11.**37. Вывод номенклатуры в порядке иерархии с данными о ее поступлении

ВЫБРАТЬ

ПриходнаяНакладнаяМатериалы.Материал КАК Материал,

ПриходнаяНакладнаяМатериалы.Ссылка.Дата КАК Дата,

ПриходнаяНакладнаяМатериалы.Ссылка.Номер КАК Номер

ПОМЕСТИТЬ НакладнаяМатериалы

ИЗ

Документ.ПриходнаяНакладная.Материалы КАК ПриходнаяНакладнаяМатериалы

ГДЕ

ПриходнаяНакладнаяМатериалы.Ссылка.Номер = "000000001"

;

ВЫБРАТЬ

Номенклатура.Наименование КАК Наименование,

НакладнаяМатериалы.Дата КАК Дата,

НакладнаяМатериалы.Номер КАК Номер

ИЗ

Справочник.Номенклатура КАК Номенклатура

ЛЕВОЕ СОЕДИНЕНИЕ НакладнаяМатериалы КАК НакладнаяМатериалы

ПО Номенклатура.Ссылка = НакладнаяМатериалы.Материал

 

УПОРЯДОЧИТЬ ПО

Номенклатура.Ссылка ИЕРАРХИЯ

АВТОУПОРЯДОЧИВАНИЕ

В первом запросе вы помещаете данные о поступлении товаров во временную таблицу с именем НакладнаяМатериалы. Затем следует символ «;» (точка с запятой), который указывает на то, что это – пакетный запрос. В следующем запросе справочник товаров связывается левым соединением с временной таблицей по ссылкам номенклатуры, и в итоге вы получаете результат, аналогичный результату при выполнении запроса, содержащего вложенный запрос (рис. 11.34).

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

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