Объектно-ориентированное программирование и моделирование
База курса:
Цель курса:
Мы даем вам профессию и готовим к реальной работе
Орг-вопросы
3. Проверьте чтобы вы были записаны тут, допишите свои почтовые ящики (которые вы читаете):
2022.intervolga.ru/imit_2022
Выбрать лист группы, найти ФИО, проверить почту, получить номер темы ЛР
4. Для сдачи лабораторных работ заведите приватный репозиторий на github:
Добавьте antonov@intervolga.ru как collaborator к вашему репозиторию:
Внесите адрес репозитория напротив своей фамилии в таблицу
Если вы все сделали верно, в колонке “Приватный” автоматически загорится слово приватный
Правила ведения репозитория
Советы
ПОД ВОПРОСОМ!!!
Лекция 12: Запросы (часть 2)
Примеры использования языка запросов для получения данных из нескольких таблиц
Как использовать данные одного запроса внутри другого запроса
Иногда внутри одного запроса необходимо использовать данные другого запроса. Например, требуется ограничить выборку значений в условии отбора одного запроса данными другого запроса. Второй запрос по отношению к первому является вложенным, а первый запрос по отношению ко второму является основным или внешним. Причем уровней вложенности запросов друг в друга в общем случае может быть несколько.
Пример: Допустим, необходимо узнать, в каких расходных накладных и каким покупателям продавались товары, перечисленные в составе конкретной приходной накладной
Как использовать данные одного запроса внутри другого запроса
В условии отбора (Расход.Состав.Товар В ()) значение поля Товар из табличной части Состав расходной накладной проверяется на попадание в перечень возможных значений. Для получения этого набора значений используется вложенный запрос. Описание вложенного запроса начинается с предложения ВЫБРАТЬ и ничем не отличается от обычного запроса.
Обратите внимание, что текст вложенного запроса принято располагать со смещением относительно основного запроса для повышения наглядности и структурированности запроса в целом.
Как использовать данные одного запроса внутри другого запроса
В данном пример в запросе указано произвольное значение отбора, условие отбора не задается жестко в тексте запроса.
Для этого в условии отбора, в предложении ГДЕ языка запросов, используются параметры. В тексте запроса параметры обозначаются символом «&», после которого следует имя параметра (например, параметр &Документ).
В результате выполнения вложенного запроса будет получен перечень товаров из табличной части приходной накладной, выбранной в качестве значения параметра Документ
Как использовать данные одного запроса внутри другого запроса
В результате выполнения вложенного запроса будет получен перечень товаров из табличной части приходной накладной, выбранной в качестве значения параметра Докумен
При выполнении внешнего запроса сначала выполняется вложенный запрос, и результат его выполнения подставляется в условие отбора внешнего запроса.
Как использовать данные одного запроса внутри другого запроса
В результате выполнения основного запроса мы увидим список расходных накладных, в которых продавались товары, перечисленные в составе конкретной приходной накладной, указанной в параметре Документ.
Как использовать данные одного запроса внутри другого запроса
Таким же образом вложенные запросы могут использоваться в условии отбора, передаваемого в качестве параметра в виртуальную таблицу. В этом случае при формировании виртуальной таблицы на основе исходной (реальной) таблицы базы данных выборка значений из исходной таблицы будет ограничена набором значений, полученным в результате выполнения вложенного запроса.
Например, виртуальная таблица РегистрСведений.Цены.СрезПоследних возвращает срез последних записей регистра сведений Цены по каждому товару на определенную дату. Эта таблица имеет параметры Период и Условие, которые мы можем использовать в языке запросов.
В параметре Период мы можем задать дату, на которую должен быть выполнен срез последних записей регистра сведений. В параметре Условие мы можем задать условие отбора записей из исходной (реальной) таблицы регистра сведений при формировании виртуальной таблицы.
Как использовать данные одного запроса внутри другого запроса
Допустим, нам нужно увидеть последние установленные цены товаров на сегодняшнюю дату. Но не на все товары, а только на те, которые перечислены в составе конкретной расходной накладной.
Поскольку мы хотим увидеть самые последние цены на товары из регистра сведений, то параметр Период мы не указываем. В параметре Условие мы можем задать произвольное условие на языке запросов, использующее любые поля регистра сведений Цены.
В этом условии (Товар В ()) значение поля регистра сведений Товар проверяется на попадание в перечень возможных значений. Для получения этого набора значений используется вложенный запрос. Таким образом, при формировании виртуальной таблицы из исходной (реальной) таблицы регистра сведений Цены будут отбираться записи об изменении цен только тех товаров, которые перечислены в составе расходной накладной, выбранной в качестве значения параметра Документ.
В результате выполнения запроса мы увидим последние установленные цены на товары, перечисленные в составе конкретной расходной накладной
Как получить данные из разных таблиц
Часто в результате запроса требуется вывести данные из разных таблиц, связав их по значению некоторого поля, то есть какие-то поля вывести из одной таблицы, какие-то – из другой для одного и того же значения поля из этих таблиц. Например, можно вывести для одного и того же товара количество его поступлений и продаж.
В этом случае используются несколько источников данных, которые перечисляются после ключевого слова ИЗ. В качестве источников запроса могут выступать реальные и виртуальные таблицы, а также вложенные запросы, но мы пока для упрощения примера будем рассматривать ситуацию, когда в качестве источников запроса выступают две таблицы базы данных. Суть примера от этого не изменится.
Исходные таблицы запроса обычно связываются (соединяются) между собой по некоторому условию – условию связи. Поле, по которому производится связь, обычно имеет ссылочный тип. При соединении данных из исходных таблиц запроса для каждой записи из этих таблиц проверяется условие равенства значений ссылочных полей этого типа. Условие связи источников запроса задается в предложении ИЗ, после ключевого слова ПО. Например, ПО Товары.Ссылка = Цены.Товар.
Как получить данные из разных таблиц
Таблицы соединяются внутренним, левым внешним, правым внешним или полным внешним соединением. Ключевые слова, определяющие тип соединения (например, ЛЕВОЕ СОЕДИНЕНИЕ), располагаются между именами таблиц в предложении ИЗ.
Пример: вывести перечень товаров из справочника Товары и при этом показать для каждого товара последнюю установленную на него цену.
Решение: нужно соединить в запросе данные из таблиц Справочник.Товары и РегистрСведений.Цены.СрезПоследних.
Не для всех товаров из справочника Товары существует соответствующая им цена в срезе последних записей регистра сведений Цены
Внутреннее соединение
При внутреннем соединении таблиц в результат запроса попадут только те записи из таблиц-источников, которые удовлетворяют заданному условию связи (после ключевого слова ПО).
Пример: необходимо вывести из справочника Товары те товары, на которые установлена цена в регистре сведений Цены, и показать актуальную цену для каждого товара. Товары, на которые не установлена цена, выводить не нужно, а также не нужно выводить последние установленные цены из регистра сведений для товаров, которых не существует в справочнике товаров.
Решение: Нужно соединить в запросе данные из таблиц Справочник.Товары и РегистрСведений.Цены.СрезПоследних внутренним соединением. Из справочника Товары выведем поля Код, Наименование и Производитель, а из среза последних записей регистра сведений Цены – поле Цена
Внутреннее соединение
В результат запроса попали только те записи из обеих таблиц, для которых было выполнено условие связи (Товары.Ссылка = Цены.Товар), то есть для каждой записи из справочника Товары была найдена соответствующая запись из среза последних записей регистра сведений Цены, в которой значение ссылочного поля Товар было равно значению поля Ссылка. Поэтому в результате запроса содержатся 6 записей, а в исходной таблице справочника – 10, так как для четырех записей справочника не было найдено соответствий в регистре сведений.
В обратную сторону условие связи также должно выполняться.
Примечание: Ключевое слово ВНУТРЕННЕЕ в тексте запроса можно не указывать, но оно повышает наглядность и читаемость текста запроса.
При соединениях чаще всего применяется условие на равенство, но в языке запросов есть возможность использовать все операции сравнения и логические операции И, ИЛИ, НЕ.
Внутреннее соединение
Если в таблице будет найдено несколько записей, удовлетворяющих условию соединения, то в результат запроса будут включены все эти записи. Например, если в запросе использовать соединение таблицы справочника Товары и реальной таблицы регистра сведений Цены, то в результате мы увидим несколько установленных цен для одного и того же товара.
Левое внешнее соединение
Если связать таблицы регистров накопления левым соединением, то в результат запроса попадут записи из обеих таблиц, удовлетворяющие условию связи, и, кроме того, записи из первой таблицы, расположенной слева от ключевого слова СОЕДИНЕНИЕ, для которых не найдено соответствия во второй таблице.
Пример: Необходимо вывести все записи из справочника Товары и показать актуальную цену товаров, на которые установлена цена в регистре сведений Цены. При этом последние установленные цены из регистра сведений для товаров, которых не существует в справочнике товаров, выводить не нужно.
Решение: Нужно соединить в запросе данные из таблиц Справочник.Товары и РегистрСведений.Цены.СрезПоследних левым внешним соединением.
Примечание: Ключевое слово ВНЕШНЕЕ в тексте запроса можно не указывать, но оно повышает наглядность и читаемость текста запроса.
Левое внешнее соединение
В результат запроса попали все 10 записей из Товары независимо от того, была найдена соответствующая им запись из среза последних записей регистра сведений Цены или нет. Так произошло потому, что таблица Справочник.Товары расположена слева от слова СОЕДИНЕНИЕ.
Строки результата запроса, для которых не найдено соответствующих условию записей из второго источника, будут содержать значение NULL в полях, формируемых на основании записей из этого источника. Из второй таблицы в результат запроса попадут только записи, удовлетворяющие условию связи, то есть если в справочнике товаров не существует товара, для которого установлена цена в регистре сведений, то такая запись не попадет в результат запроса.�Внимание: значения NULL не являются нулем или пустой строкой. Значения данного типа обозначают неуказанные (отсутствующие) значения или значения, не имеющие смысла. В данном случае в поле Цена для товаров Обувь, Продукты, Детская обувь и Пинетки будет находиться значение NULL.
Правое внешнее соединение
При правом соединении таблиц ситуация зеркально противоположная. То есть из правой таблицы в результат запроса попадут все записи, а из левой – только те, которые удовлетворяют условию связи.
Задача: необходимо вывести из справочника Товары те товары, на которые установлена цена в регистре сведений Цены, и показать актуальную цену для каждого товара. При этом товары, на которые не установлена цена, выводить не нужно, а из регистра сведений нужно вывести срез всех последних записей.
Решение: Для этого нам нужно соединить в запросе данные из таблиц Справочник.Товары и РегистрСведений.Цены.СрезПоследних правым внешним соединением.
Правое внешнее соединение
В результат запроса попали все 6 записей из таблицы среза последних записей регистра сведений Цены, расположенной справа от слова СОЕДИНЕНИЕ. В нашем примере это не так очевидно: так, все товары, для которых установлены цены, присутствуют в справочнике Товары. Но в общем случае если в справочнике товаров не существует товара, для которого установлена цена в регистре сведений, то такая запись все равно попадет в результат запроса.
Строки результата запроса, для которых не найдено соответствующих условию записей из первого источника, будут содержать значение NULL в полях, формируемых на основании записей из этого источника.
Из первой таблицы в результат запроса попадут только записи, удовлетворяющие условию связи. То есть если в срезе последних записей регистра сведений Цены не найдена цена для какого-то товара, то такая запись из справочника Товары не попадет в результат запроса.
Правое и левое внешнее соединение
Правое соединение полностью аналогично левому, но при этом таблицы меняются местами в тексте запроса. Таким образом, если мы поменяем местами наши исходные таблицы и свяжем их правым соединением (первый вариант запроса), то мы получим результат, полностью аналогичный предыдущему примеру с левым соединением
При этом порядок следования полей таблиц в условии связи значения не имеет, важно лишь, какая таблица расположена слева, а какая – справа от слова СОЕДИНЕНИЕ.
Полное соединение
При полном соединении таблиц в результат запроса будут включены записи из обеих исходных таблиц, которые соответствуют указанному условию. Кроме того, в результат запроса будут включены также еще и те записи из обоих источников, для которых не найдено соответствий.
Задача: необходимо вывести все записи из справочника Товары и срез всех последних записей из регистра сведений Цены. Нужно показать актуальную цену для тех товаров, на которые она устанавливалась.
�Решение: Нужно соединить в запросе данные из таблиц Справочник.Товары и РегистрСведений.Цены.СрезПоследних полным внешним соединением
Полное соединение
В результат запроса попали все записи из исходных таблиц запроса. Но в нашем частном случае мы получили результат, полностью аналогичный примеру с левым соединением, так как все товары, для которых установлены цены в регистре сведений, присутствуют в справочнике Товары. Для тех товаров, на которые устанавливалась цена, она найдена в срезе последних записей регистра сведений и отражена в результате запроса.
Строки результата запроса, для которых не найдено соответствующих условию записей из какого-либо источника, будут содержать значение NULL в полях, формируемых на основании записей из этого источника.
Полное соединение
Следует иметь в виду, что при работе в клиент-серверном варианте, когда в качестве СУБД используется PostgreSQL, производительность выполнения запросов с конструкцией ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ значительно снижается. В особенности это касается случаев, когда в запросе встречаются две и более такие конструкции. Поэтому в общем случае не рекомендуется использовать полное внешнее соединение в запросах. И в тех случаях, где это возможно, рекомендуется переписать текст исходного запроса без использования этой конструкции.
Мы рассматривали различные виды соединения источников запроса на примере двух таблиц базы данных. В общем случае в запросе может содержаться не только одно соединение (двух источников), но и несколько соединений нескольких источников сразу. Об этом в следующем примере.
Кроме того, в соединении источников могут участвовать и вложенные запросы. Однако соединения с вложенными запросами крайне нежелательны, так как в большинстве случаев могут привести к неоптимальному выполнению запроса.
Как получить данные из разных таблиц, связанных несколькими соединениями
Задача: необходимо вывести перечень товаров из справочника Товары и при этом показать остаток каждого товара и количество его продаж.
Дано:
Регистр накопления ОстаткиТоваров, который накапливает информацию о приходе и расходе товаров. Виртуальная таблица Остатки этого регистра накопления позволяет получить информацию об остатках каждого товара.
Регистр накопления Продажи, который накапливает информацию о продажах товаров. Виртуальная таблица Обороты этого регистра накопления позволяет получить информацию о количестве и сумме оборотов (продаж) каждого товара.
Решение: нужно соединить в запросе данные из таблиц Справочник.Товары, РегистрНакопления.ОстаткиТоваров.Остатки и РегистрНакопления.Продажи.Обороты.
Регистры накопления ОстаткиТоваров и Продажи содержат поле Товар, ссылающееся на справочник Товары. По полю этого типа и будет производиться связь таблиц в запросе, то есть в условии связи для каждой записи из исходных таблиц будут сравниваться значения уникального поля Ссылка из справочника и ссылочного поля Товар из виртуальных таблиц Остатки или Обороты регистров накопления.
Данные из разных таблиц, связанных несколькими соединениями
Сначала свяжем данные всех трех таблиц левыми соединениями и выведем из справочника Товары поле Товар, из виртуальной таблицы Остатки регистра накопления ОстаткиТоваров (таблицы остатков) – поле КоличествоОстаток, из виртуальной таблицы Обороты регистра накопления Продажи (таблицы оборотов) – поле КоличествоОборот
Данные из разных таблиц, связанных несколькими соединениями
Видим, что в результат запроса попали все записи (7 товаров) из справочника Товары, как и должно быть при левом соединении. Для каждого товара были найдены соответствующие записи в таблицах остатков и оборотов товаров и подставлены в результат запроса.
Сначала выполняется левое соединение справочника Товары с виртуальной таблицей остатков. Результат этого соединения - в левой таблице. Для каждого товара из справочника ищется соответствующая запись в таблице остатков, и найденный остаток выводится для конкретного товара. Товары, у которых нет остатка (Сапоги, Масло, Пинетки), также попадают в результат соединения.
На следующем этапе результат этого первого соединения (левая таблица) связывается левым соединением с виртуальной таблицей оборотов. Для каждого товара из результата первого соединения ищется соответствующая запись в таблице оборотов, и найденное количество продаж выводится для конкретного товара. Товары, которые ни разу не продавались (Сметана, Пинетки), также попадают в результат запроса
Данные из разных таблиц, связанных несколькими соединениями
Теперь посмотрим, как изменится результат запроса при различных комбинациях типов соединений исходных таблиц запроса. Оставим без изменения тип первого (по порядку следования в запросе) соединения справочника Товары с виртуальной таблицей остатков. А тип второго соединения изменим на внутреннее соединение.
Данные из разных таблиц, связанных несколькими соединениями
Теперь свяжем внутренним соединением справочник Товары с виртуальной таблицей остатков. Тип второго соединения (по порядку следования в запросе) установим как левое соединение.
Данные из разных таблиц, связанных несколькими соединениями
Теперь свяжем все три исходные таблицы запроса внутренними соединениями
Данные из разных таблиц, связанных несколькими соединениями
Как видно из рассмотренных выше случаев, три исходные таблицы запроса Справочник.Товары, РегистрНакопления.ОстаткиТоваров.Остатки и РегистрНакопления.Продажи.Обороты последовательно связывались разными видами соединений. Порядок расположения синтаксических конструкций в предложении ИЗ был следующий: <Имя первой таблицы> <Тип соединения> <Имя второй таблицы> <Условие связи первой и второй таблицы> <Тип соединения> <Имя третьей таблицы> <Условие связи первой и третьей таблицы>.
Но можно расположить соединения не последовательно, а как бы вложить их друг в друга и изменить условие связи таблиц, тогда результат запроса будет другим. Например, свяжем левым соединением таблицы остатков и оборотов и затем результат этого соединения свяжем левым соединением с таблицей справочника товаров
В запроса второе (по порядку следования в запросе) соединение вложено в первое. Но на самом деле оно выполняется первым как соединение с наибольшим уровнем вложенности.
Данные из разных таблиц, связанных несколькими соединениями
При выполнении запроса сначала таблица остатков связывается с таблицей оборотов левым соединением. Результат этого соединения мы видим в левой таблице. Поскольку тип соединения – левый, в результат попадают все 4 товара, у которых есть остаток. При этом у одного из товаров (Сметана) нет продаж, а товары, которые проданы без остатка (Сапоги, Масло), в результат соединения не попадают.
На следующем этапе таблица справочника товаров связывается левым соединением с результатом первого соединения (левая таблица). Для каждого товара из справочника ищется соответствующая запись в результате первого соединения, и найденные остатки и обороты выводятся для конкретного товара. Товары, у которых нет остатка (Сапоги, Масло, Пинетки) из справочника Товары, также попадают в результат запроса
Как получить данные из таблицы, на которую ссылается поле другой таблицы
В языке запросов существует очень удобная возможность – обращаться не только к полям исходных таблиц запроса, перечисленным в предложении ИЗ, но и к полям таблицы, на которую ссылается поле исходной таблицы запроса.
Исходная таблица - документ Событие, который имеет ссылочное поле Клиент с типом ссылки на справочник Клиенты. Обращаясь к полю Клиент в документе, можно получить из справочника Клиенты любые данные о клиенте, на которого ссылается данное поле.
Данные из таблицы, на которую ссылается поле другой таблицы
Задача: нужно вывести список документов Событие и при этом для каждого клиента отобразить его адрес и телефон.
В списке выборки запроса перечислены поля Дата, ВидСобытия и Клиент исходной таблицы запроса – документа Событие, а также поля Адрес и Телефон таблицы справочника Клиенты, на которую ссылается поле документа Клиент. Имена полей таблицы справочника пишутся через точку, например, Клиент.Адрес.
Обращение к полям через точку называется операцией разыменования ссылочного поля. В цепочке разыменования может быть перечислено через точку несколько полей, например, Номенклатура.Поставщик.Страна. В этом примере поле Номенклатура исходной таблицы ссылается на другую таблицу (справочник Поставщики), имеющую, в свою очередь, поле Поставщик, ссылающееся на третью таблицу (справочник Страны), из которой запросом получается поле Страна
Данные из таблицы, на которую ссылается поле другой таблицы
Рассмотрим, что же на самом деле происходит при получении значений полей через точку в языке запросов. При разыменовании ссылочных полей платформа добавляет таблицу, на которую ссылается поле исходной таблицы запроса, в список источников запроса (в предложение ИЗ) и выполняет соединение этих таблиц по полям ссылочного типа.
Как получить данные из разных таблиц, не связывая, а дополняя их
При выборке данных с помощью запросов бывает необходимо дополнить данные, получившиеся в результате выполнения одного запроса, данными другого запроса. Например, можно дополнить информацию из приходных накладных со структурой (Контрагент, Товар, Количество, Цена) данными из расходных накладных с такой же структурой.
Для этого в языке запросов существует возможность объединения нескольких запросов. При этом записи, полученные с помощью каждого из объединяемых запросов, собираются в один результат запроса. При объединении каждый запрос получает данные независимо, то есть у каждого из запросов – свое описание выбираемых полей (ВЫБРАТЬ), источников запроса (ИЗ), условий отбора (ГДЕ), полей группировки (СГРУППИРОВАТЬ ПО). Затем данные, получаемые в результате каждого запроса, объединяются, и уже над этим объединением выполняются такие операции, как упорядочивание результатов (УПОРЯДОЧИТЬ ПО) и расчет итогов (ИТОГИ ПО).
Для объединения запросов используется предложение ОБЪЕДИНИТЬ, которое располагается в секции Объединение запросов текста запроса.
Получение данные из разных таблиц, не связывая, а дополняя их
Задача: нужно объединить вместе информацию из документов Заказ товара и информацию из документов Расходная накладная. При этом нужно вывести суммарное количество заказанных и проданных товаров по каждому клиенту. Также нужно получить общий итог по результату запроса в целом и отдельно по каждому клиенту.
Решение: нужно получить данные из таблицы Документ.ЗаказТовара, сгруппировать их по полям этой таблицы Клиент и Товар, затем получить данные из таблицы Документ.РасходнаяНакладная, сгруппировать их по полям этой таблицы Покупатель и Товар и объединить полученную информацию. Затем рассчитать общие итоги и итоги по полю Клиент для результата объединения запросов
Получение данные из разных таблиц, не связывая, а дополняя их
Объединяемые запросы должны иметь одинаковое количество полей в списке полей выборки. В случае, если поля выборки объединяемых запросов имеют разный тип, поля результата запроса будут иметь составной тип.
В общем случае могут объединяться результаты выполнения сразу нескольких запросов.
По умолчанию при объединении запросов полностью одинаковые строки в результате запроса заменяются одной. Если требуется, чтобы были оставлены разные строки, необходимо указать ключевое слово ВСЕ.
В общем случае при объединении в запросе результатов нескольких запросов следует использовать конструкцию ОБЪЕДИНИТЬ ВСЕ, а не ОБЪЕДИНИТЬ, т.к. во втором варианте при объединении запросов полностью одинаковые строки заменяются одной, на что затрачивается дополнительное время, даже тогда, когда одинаковых строк в запросах заведомо быть не может.
Временные таблицы и пакетные запросы
Бывают ситуации, когда в качестве источника запроса необходимо использовать не таблицы базы данных, а данные, полученные в результате выполнения другого запроса. Например, источником запроса могут служить данные приходных накладных, сгруппированные по товарам или отобранные по дате документа.
Запрос, служащий источником данных, является вложенным, а запрос, который эти данные использует, является основным или внешним.
Задача: нужно вывести данные обо всех товарах и их производителях с соблюдением иерархии справочника Товары. Для каждого товара из справочника мы хотим видеть дату его поступления и поставщика из приходных накладных.
Казалось бы, в этом нет ничего особенно сложного. Свяжем левым соединением таблицы справочника Товары и документа ПриходнаяНакладная по ссылкам товаров и выведем требуемые поля из обеих таблиц. При этом упорядочим результат запроса по наименованиям товаров в порядке иерархии справочника
Временные таблицы и пакетные запросы
На реальной базе, конечно, список товаров может быть очень большим.
Временные таблицы и пакетные запросы
Предположим, нужно получить тот же список, но с данными о поступлениях товаров только за месяц.
Для этого наложим условие на поле Дата приходной накладной так, чтобы выбирались данные из приходных накладных только за ноябрь.
Результат запроса – несколько не такой, как мы ожидали
«Пропала» иерархия справочника товаров, так как в результате запроса нет данных для построения иерархии. Так произошло потому, что при накладывании условия отбора на поле правой таблицы при левом соединении тип соединения становится внутренним.
Временные таблицы и пакетные запросы
В этом и в некоторых других случаях выходом из ситуации является использование вложенного запроса в качестве источника внешнего запроса. Поэтому предыдущий запрос можно переписать следующим образом
При выполнении основного запроса сначала выполняется вложенный запрос, в котором получаются данные о поступлении товаров за ноябрь. Затем справочник товаров связывается левым соединением с результатом вложенного запроса по ссылкам товаров, и в итоге мы получаем требуемый результат.
Временные таблицы и пакетные запросы
Текст запроса стал довольно громоздким и сложным для восприятия. С этой точки зрения лучше поместить результат вложенного запроса во временную таблицу и затем использовать ее как источник нашего основного запроса.
Временная таблица не существует в базе данных. Это просто некоторая область в памяти компьютера, которая создается на ограниченное время, как, например, это происходит при создании какой-либо переменной встроенного языка. Эта область создается и заполняется данными при выполнении запроса, содержащего ключевое слово ПОМЕСТИТЬ, после которого следует произвольное имя временной таблицы.
Заметим, что для быстрой и эффективной выборки данных из временной таблицы желательно проиндексировать эту таблицу по полям, которые будут затем участвовать в условии отбора или в условии соединения. Делается это с помощью предложения ИНДЕКСИРОВАТЬ ПО.
Временные таблицы и пакетные запросы
Временную таблицу можно использовать в качестве источника других запросов точно так же, как и таблицу базы данных. Но сделать это можно либо из встроенного языка с помощью менеджера временных таблиц , либо с помощью пакетного запроса.
Пакетный запрос содержит последовательность запросов, разделенных символом «;» (точка с запятой). При выполнении пакетного запроса входящие в него запросы выполняются друг за другом. При этом если при выполнении одного из запросов была создана временная таблица, то следующие за ним запросы при их выполнении могут использовать данные этой временной таблицы.
Необходимо учитывать, что временные таблицы будут существовать до окончания исполнения всего пакета запроса или до исполнения в пакете запроса, уничтожающего данную временную таблицу с помощью конструкции УНИЧТОЖИТЬ.
Разобьем запрос, содержащий вложенный запрос, на два запроса и объединим их в один пакетный запрос
Временные таблицы и пакетные запросы
В первом запросе мы помещаем данные о поступлении товаров за ноябрь во временную таблицу с именем ПоступлениеТоваров. Затем следует символ «;» (точка с запятой), который указывает на то, что это – пакетный запрос. В следующем запросе справочник товаров связывается левым соединением с временной таблицей по ссылкам товаров, и в итоге мы получаем результат, аналогичный результату при выполнении запроса, содержащего вложенный запрос
Временные таблицы и пакетные запросы
Использование временных таблиц вместо вложенных запросов почти всегда делает запрос более оптимальным. С точки зрения эффективности исполнения запросов крайне не рекомендуется использовать соединения с вложенными запросами, так как в этом случае СУБД может выбрать неоптимальный план запроса, что на больших объемах данных приводит к временным задержкам и другим неприятностям при выполнении таких запросов.
Временные таблицы и пакетные запросы
Как можно переписать запрос, выполняющий несколько соединений нескольких таблиц с использованием временной таблицы
Временные таблицы и пакетные запросы
Второй вариант, использующий временную таблицу, более понятен, по крайней мере полезен для изучения. Так как если выполнить сначала только первую часть запроса (помещающую данные во временную таблицу), а затем – весь запрос целиком, то можно поэтапно проследить, какие данные получаются в результате каждого соединения
Выполнение запросов из встроенного языка
Выполнение запросов из встроенного языка
Выполнение запроса во встроенном языке состоит из следующих этапов:
Затем либо:
Либо:
Выполнение запросов из встроенного языка
Для формирования и выполнения запроса, а также для получения и обработки его результатов во встроенном языке предназначены следующие программные объекты:
Важно помнить, что вся работа с запросами выполняется только на сервере.
Создание запроса
Прежде всего, во встроенном языке нужно создать объект Запрос. Затем, используя свойство Текст объекта Запрос, нужно поместить в него текст запроса, написанный на языке запросов. В тексте запроса описывается, какие данные, из каких таблиц нужно получить и как эти данные представить. Например, в следующем фрагменте кода создается программный объект Запрос, в тексте которого описывается, что нужно извлечь значения полей Наименование, Адрес и Телефон для всех записей справочника Клиенты
Передача параметров в запрос
Допустим, мы хотим добавить в запрос, получающий все записи из справочника Клиенты, параметризированное условие так, чтобы видеть только тех клиентов, в наименовании которых встречается определенная подстрока.
В консоли
Запрос, созданный конструктором запроса
Обратите внимание, что в процедуре на встроенном языке, в отличие от текста запроса в консоли запросов, каждую кавычку («"») в шаблоне строки нужно удваивать
Передача параметров в запрос
Если текст запроса содержит параметры, то перед выполнением запроса значения параметров должны быть переданы в запрос.
Это выполняется с помощью метода УстановитьПараметр() объекта Запрос
Первым параметром в метод УстановитьПараметр() передается строка с именем параметра запроса, а вторым параметром передается значение.
Получение выборки из результата запроса
После присвоения текста и установки параметров запрос запускается на выполнение с помощью метода Выполнить() объекта Запрос. Именно в этот момент и происходит чтение данных из базы данных. Прочитанные данные возвращаются в виде объекта РезультатЗапроса, содержащего выбранные данные из базы данных.
Результат выполнения запроса может не содержать строк. Проверку этого следует выполнять с помощью метода Пустой() объекта РезультатЗапроса.
Проверку на пустой результат следует выполнять до получения выборки из результата запроса методом Выбрать(), поскольку на получение выборки затрачивается дополнительное время.
Чтобы обработать данные, содержащиеся в объекте РезультатЗапроса, из него получается выборка с помощью метода Выбрать(), который возвращает новый объект ВыборкаИзРезультатаЗапроса, то есть коллекцию данных, предназначенную для обхода ее элементов
Обход выборки из результата запроса
Для обхода выборки из результата запроса нужно организовать цикл, в котором перебираются элементы коллекции данных, содержащихся в объекте ВыборкаИзРезультатаЗапроса. Для этого используется метод выборки Следующий(), который позволяет перейти к следующей записи результата запроса в соответствии с порядком обхода выборки
Этот метод вызывается в цикле Пока Выборка.Следующий() … Цикл до тех пор, пока не будет получено значение Ложь. При первом проходе цикла метод Следующий() позиционирует выборку на первую запись.
Если выборка из результата запроса получена, но ее метод Следующий() еще ни разу не вызывался (то есть выборка еще не спозиционирована), то значения полей выборки будут не определены, например, их нельзя будет посмотреть в отладчике.
Результат запроса может содержать записи, не имеющие иерархии (например, список документов) или, наоборот, записи, обладающие иерархией.
Обход выборки из результата запроса
Второй случай получения иерархических записей, когда в запросе рассчитываются итоги. Дело в том, что при расчете итогов образуется некая иерархическая структура, поскольку записи с одинаковым значением поля (полей), для которых рассчитываются итоги, в результате запроса собираются (группируются) вместе и достраиваются итоговой строкой. Эта итоговая строка является по отношению к ним родительской строкой, стоящей на более высоком уровне иерархии.
Поэтому существует несколько разных способов обхода выборки – в линейном порядке, в иерархическом порядке или по группировкам. Тип обхода выборки задается значением системного перечисления ОбходРезультатаЗапроса и передается в качестве параметра в метод Выбрать() объекта РезультатЗапроса.
При линейном обходе выборки будут последовательно получаться все записи из результата запроса, при иерархическом обходе получаются только записи результата запроса, находящиеся на одном уровне иерархии, при обходе по группировкам будут получены только родительские записи, являющие групповыми итогами.
Обход выборки из результата запроса
Пример: запрос, в котором выводятся наименования товаров и количество их поступления из состава приходных накладных и рассчитываются иерархические итоги по полю Товар.
Итоговые записи в таблице выделены курсивом, итоговые записи для иерархических уровней справочника выделены жирным шрифтом.
Обход выборки из результата запроса
Линейный (прямой) порядок обхода результата запроса
Для получения линейной выборки из результата запроса необходимо вызвать метод Выбрать() объекта РезультатЗапроса без параметров либо с параметром ОбходРезультатаЗапроса.Прямой
Иерархический порядок обхода результата запроса
При иерархическом обходе результата запроса обходятся только записи, находящиеся на одном уровне иерархии. Для получения иерархической выборки из результата запроса необходимо вызвать метод Выбрать() объекта РезультатЗапроса с параметром ОбходРезультатаЗапроса.ПоГруппировкамСИерархией.
Однако при первом вызове метода Выбрать() с этим типом обхода будут получены только записи, находящиеся на самом верхнем уровне иерархии
Для обхода записей всех уровней иерархии в цикле обхода первой выборки нужно для каждой записи выборки методом Выбрать() объекта ВыборкаИзРезультатаЗапроса получать вложенные иерархические выборки, которые будут содержать подчиненные записи текущей записи выборки.
В нашем примере в момент, когда первая выборка будет позиционирована на запись с номером 1, мы запросим у нее подчиненную иерархическую выборку. Таким образом, мы получим выборку, которая нам вернет записи с номерами 2, 5, 8. А когда выборка верхнего уровня будет позиционирована на запись с номером 11, полученная у нее иерархическая выборка вернет записи с номерами 12, 15, 18. Так реализуется иерархический обход результатов запроса.
Иерархический порядок обхода результата запроса
Пример: обход результата запроса, в котором получаются записи из состава приходных накладных и рассчитываются иерархические итоги по полю Товар. В процедуре выполнения запроса из результата запроса получается выборка с типом обхода ПоГруппировкамСИерархией, содержащая записи, находящиеся на самом верхнем уровне иерархии
Иерархический порядок обхода результата запроса
Затем вызывается процедура ВыдатьВсеВложения(), в которую передается полученная выборка. В этой процедуре иерархическая выборка обходится в цикле, и в окно сообщений выводятся данные этой выборки. После этого в теле цикла для текущей записи выборки вызывается метод Выбрать(), и из нее получается подчиненная выборка записей, находящихся на следующем уровне иерархии. При этом в случае получения в запросе иерархических итогов нужно анализировать тип записи выборки. Если тип записи – Итог по иерархии, то вторым параметром в метод Выбрать() нужно передавать имя группировки, для которой были рассчитаны иерархические итоги.
Затем рекурсивно вызывается процедура ВыдатьВсеВложения(), в которую передается полученная иерархическая выборка, содержащая подчиненные записи родительской записи выборки
Иерархический порядок обхода результата запроса
Результат иерархического обхода выборки
Кроме количества поступления товара и его наименования выводятся характеристики получаемые с помощью одноименных методов выборки (объекта ВыборкаИзРезультатаЗапроса):
Результат иерархического обхода выборки:
Обход по группировкам
Обход результата запроса по группировкам используется в случае получения в результате запроса итоговых данных (в запросе присутствует слово ИТОГИ ПО). В нашем примере это будут записи с номерами 1, 2, 5, 8, 11, 12, 15, 18.
Для получения выборки по группам из результата запроса необходимо вызвать метод Выбрать() объекта РезультатЗапроса с параметром ОбходРезультатаЗапроса.ПоГруппировкам.
При вызове метода Выбрать() с этим типом обхода будут получены только родительские записи, являющие групповыми итогами. Для обхода детальных записей в цикле обхода групповой выборки нужно для каждой записи выборки методом Выбрать() объекта ВыборкаИзРезультатаЗапроса линейным способом получать дочерние выборки, которые будут содержать подчиненные записи текущей записи выборки.
В процедуре выполнения запроса для получения групповых итогов из результата запроса получается выборка с типом обхода ПоГруппировкам. Эта выборка обходится в цикле, и в окно сообщений выводятся данные этой выборки.
Обход по группировкам
После этого в теле цикла для текущей записи второй выборки вызывается метод Выбрать() с прямым порядком обхода (т. к. нужно обойти детальные записи). Затем вызывается процедура ВыдатьДочерниеЗаписи(), в которую передается полученная выборка, содержащая подчиненные записи родительской записи выборки.
В процедуре ВыдатьДочерниеЗаписи() обходятся детальные записи для каждой группировки (итоговой строки), и в окно сообщений выводится суммарное количество поступлений каждого товара и его наименование, а также такие характеристики текущей записи выборки, как: Уровень, Тип записи и Группировка. Эти характеристики были рассмотрены в предыдущем разделе.
Если в запросе получается общий итог, то эта запись занимает место в корне иерархии с нулевым уровнем и типом записи Общий Итог, а все остальные записи как бы вложены в нее, и их уровень соответственно возрастает на единицу.
Обход выборки результата запроса, содержащего данные табличной части
Часто объекты базы данных, например документы, имеют основную таблицу, в которой содержатся реквизиты документа, и подчиненную таблицу, в которой содержатся данные табличной части документа. Эти данные можно получить, обращаясь отдельно к таблице с данными табличной части , а также их можно получить из основной таблицы в качестве вложенного результата запроса. Второй вариант рассмотрим ниже.
При выполнении запроса к основной таблице поле результата запроса, содержащее данные из табличной части (например, ЗаказТовара.Состав), будет иметь тип РезультатЗапроса, то есть содержать вложенный результат запроса, сформированный на основе табличной части.
Чтобы обработать данные вложенной таблицы, нужно в цикле обхода основной выборки из результата запроса для каждой записи выборки методом Выбрать() объекта ВыборкаИзРезультатаЗапроса получить вложенную выборку, которая будет содержать данные табличной части для текущей записи основной выборки.
Ниже приводится пример процедуры встроенного языка, в которой обрабатываются как реквизиты документа, так и данные его табличной части и выводятся в окно сообщений
Задавайте вопросы.
Антонов Виталий Михайлович,
руководитель направления 1С-проектов
ИА ИНТЕРВОЛГА
вертикальная оцифровка бизнеса