Внешняя обработка "Консоль запросов" для управляемого приложения
Механизм запросов - это один из способов доступа к данным, которые поддерживает платформа 1С:Предприятие. Используя этот механизм, разработчик может читать и обрабатывать данные, хранящиеся в информационной базе.
ВАЖНО! Изменение данных с помощью запросов невозможно. Это объясняется тем, что запросы специально предназначены для быстрого получения и обработки некоторой выборки из больших массивов данных, которые могут храниться в базе данных.
Табличный способ доступа к данным
Запросы реализуют табличный способ доступа к данным, которые хранятся в базе данных. Это означает, что все данные представляются в виде совокупности связанных между собой таблиц, к которым можно обращаться как по отдельности, так и к нескольким таблицам во взаимосвязи.
Такой способ работы с данными позволяет получать сложные выборки данных, сгруппированные и отсортированные определенным образом. Для этих выборок могут быть:
- рассчитаны общие и промежуточные итоги,
- наложены ограничения на количество или состав записей и пр.
Для повышения скорости выполнения запроса необходимо:
- создать индексы в условиях запроса, полях соединения, агрегации и сортировки;
- задавать параметры большинства виртуальных таблиц;
- фильтрацию виртуальных таблиц необходимо производить с использованием параметров отбора.
Виртуальные таблицы в базе данных физически не существуют, они генерируются на уровне платформы для повышения быстродействия и удобства доступа к данным.
При формировании запросов 1С нельзя:
- соединять таблицы с подзапросами;
- соединять обычные таблицы с виртуальными;
- использовать логическое «ИЛИ» в условиях;
- использовать подзапросы в условиях соединения;
- получать данные через точку от полей составного типа без ключевого слова «
Выразить» (пример).
Состав запроса 1С
Запрос формируется и выполняется разработчиком из встроенного языка. Общая схема написания запросов включает код:
- объявление (инициализация) запроса, текст которого может включать:
- описание запроса (обязательная секция);
- объединение запросов;
- упорядочивание результатов;
- автоупорядочивание;
- описание итогов;
- получение результата запроса;
- формирование выборки из результата запроса;
- обход выборки и получение нужных данных.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
// Инициализация запроса Запрос = Новый Запрос; Запрос.Текст = "ВЫБРАТЬ | ТоварыИУслуги.Номенклатура, | ТоварыИУслуги.Количество, | ТоварыИУслуги.Цена | ИЗ Документ.РасходнаяНакладная.ТоварыИУслуги КАК ТоварыИУслуги"; // Получение результата запроса РезультатЗапроса = Запрос.Выполнить(); // Получение выборки из результата запроса ВыборкаЗапроса = РезультатЗапроса.Выбрать(); // Обработка выборки данных Пока ВыборкаЗапроса.Следующий() Цикл Сообщение = Новый СообщениеПользователю; Сообщение.Текст = ВыборкаЗапроса.Наименование; Сообщение.Сообщить(); КонецЦикла; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
// Секция описания запроса ВЫБРАТЬ // перечень полей (* - выбрать все) * ИЗ Документ.РасходнаяНакладная.ТоварыИУслуги; // Или ВЫБРАТЬ // перечень полей ТоварыИУслуги.Номенклатура, ТоварыИУслуги.Цена // после "ИЗ" перечисление источников данных ( реальных и виртуальных таблиц, через запятую) // после "КАК" - задание псевдонима для конкретной таблицы ИЗ Документ.РасходнаяНакладная.ТоварыИУслуги КАК ТоварыИУслуги; |
ВЫБРАТЬ ПЕРВЫЕ <количество>- задает предельное количество строк в результате запроса (если нужно с конца - поменять упорядочиваниеУБЫВилиВОЗР);ВЫБРАТЬ РАЗЛИЧНЫЕ- исключение дублей в результате запроса (если в списке выборки указано несколько полей, то в результат запроса отбираются записи, содержащие неповторяющиеся комбинации значений сразу по нескольким полям);
|
1 2 3 4 |
ВЫБРАТЬ Количество(*) КАК Всего, Количество(РАЗЛИЧНЫЕ ТоварыИУслуги.Номенклатура) ИЗ Документ.РасходнаяНакладная.ТоварыИУслуги КАК ТоварыИУслуги |
Количество всевозможных значений, отличных от NULL, для некоторого поля в таблице:
КОЛИЧЕСТВО(<Имя поля>)
Для определения числа различных записей:
Неверно (выведет общее число записей):
ГДЕ- условия отбора данных из таблицы, причем поле конструкции ГДЕ может не входить в список выборки, например:пример12345678ВЫБРАТЬТоварыИУслуги.Номенклатура,ТоварыИУслуги.ЦенаИЗ Документ.РасходнаяНакладная.ТоварыИУслуги КАК ТоварыИУслугиГДЕТоварыИУслуги.Количество<100 ИТоварыИУслуги.Количество>50 ИЛИТоварыИУслуги.Количество МЕЖДУ 0 И 3[свернуть]
|
1 2 3 4 5 6 7 8 9 10 11 12 |
// Секция описания запроса ВЫБРАТЬ // после "КАК" - задание псевдонимов ТоварыИУслуги.Номенклатура КАК Номенклатура, ТоварыИУслуги.Цена КАК Цена ИЗ Документ.РасходнаяНакладная.ТоварыИУслуги КАК ТоварыИУслуги // Секция упорядочивания результатов запроса (для примитивных типов данных) УПОРЯДОЧИТЬ ПО // порядок возрастания "УБЫВ" или "ВОЗР" Цена, Номенклатура УБЫВ |
|
1 2 3 4 5 6 7 8 9 |
// Секция описания запроса ВЫБРАТЬ * ИЗ Документ.РасходнаяНакладная.ТоварыИУслуги КАК ТоварыИУслуги // Секция упорядочивания результатов запроса (пропущена) // Секция автоупорядочивания (упорядочивание ссылочных полей в наиболее ожидаемом порядке) АВТОУПОРЯДОЧИВАНИЕ |
Примечание: в общем случае, чтобы расположить записи результата запроса в наиболее ожидаемом (естественном) порядке, рекомендуется упорядочить записи таблицы непосредственно по ссылочному полю, а затем использовать конструкцию АВТОУПОРЯДОЧИВАНИЕ. В остальных случаях использовать автоупорядочивание записей результата запроса не рекомендуется.
Язык запросов
Для того чтобы разработчик имел возможность использовать запросы для реализации собственных алгоритмов, в платформе реализован язык запросов.
Этот язык основан на SQL, но при этом содержит значительное количество расширений, ориентированных на отражение специфики финансово-экономических задач и на максимальное сокращение усилий по разработке прикладных решений.
Наиболее существенные возможности, реализуемые языком запросов 1С:
Если поля какой-либо таблицы имеют ссылочный тип (хранят ссылки на объекты другой таблицы), разработчик может в тексте запроса ссылаться на них через ".", при этом количество уровней вложенности таких ссылок система не ограничивает.
Система поддерживает обращения к вложенным табличным частям и как к отдельным таблицам, и как к целым полям одной таблицы.
Например, при обращении к документу РасходнаяНакладная (содержащему табличную часть ТоварыИУслуги), мы можем считать табличную часть как отдельную таблицу:
Но также мы можем считать заголовочную запись документа, в которой значением поля Товары будут все записи вложенной таблицы, подчиненные этому объекту (документу):
Для выбора наиболее правильного ("естественного") порядка вывода информации на экран или в отчет разработчику в большинстве случаев достаточно задать режим автоматического упорядочивания.
Итоги и подитоги формируются с учетом группировки и иерархии, обход уровней может выполняться в произвольном порядке с подведением подитогов, обеспечивается корректное построение итогов по временным измерениям.
Виртуальные таблицы, предоставляемые системой, позволяют получить практически готовые данные для большинства прикладных решений без необходимости составления сложных запросов.
Например, такая виртуальная таблица может предоставить данные по остаткам товаров в разрезе периодов на какой-то момент времени. При этом виртуальные таблицы максимально используют хранимую информацию, например, ранее рассчитанные итоги и т.д.
В языке запросов поддерживаются стандартные для SQL операции, такие, как объединение (Union), соединение (Join) и т.д.
Язык запросов позволяет использовать в запросах временные таблицы. С их помощью можно:
- повысить производительность запросов;
- в некоторых случаях снизить количество блокировок;
- сделать текст запроса более легким для восприятия.
Время "жизни" временной таблицы:
- в течение времени исполнения запроса, либо
- в течение времени жизни менеджера временных таблиц (исполнения процедуры в случае программной обработки менеджером).
В обоих случаях временная таблиц может быть уничтожена и ранее этого, с помощью (соответственно):
- конструкции языка запросов, либо
- метода МВТ.
Если требуется в рамках одного запроса определить две временные таблицы с одинаковым именем, одну из них нужно уничтожить вручную, поскольку в рамках одного запроса две временные таблицы с одинаковым именем существовать не могут.
Для более удобной работы с временными таблицами в языке запросов поддерживается работа с пакетными запросами - таким образом, создание временной таблицы и ее использование помещаются в один запрос. Пакетный запрос представляет собой последовательность запросов, разделенных символом ";". Запросы исполняются один за другим. Результатом выполнения пакетного запроса в зависимости от используемого метода будет являться либо результат, возвращаемый последним запросом пакета, либо массив результатов всех запросов пакета в той последовательности, в которой следуют запросы в пакете.
В общем случае, обращение запроса к конкретным данным информационной базы происходит через параметры. Однако, предопределенные в конфигураторе данные могут быть прописаны в запросе напрямую. Это распространяется на:
- справочники;
- планы видов характеристик (ПВХ);
- планы видов расчетов (ПВР);
- точки маршрута бизнес-процессов;
- планы счетов.
- пустые ссылки;
- перечисления и системные перечисления из перечня ВидДвиженияНакопления, ВидСчета, ВидДвиженияБухгалтерии.
Практическое использование языка запросов
Поля исходных таблиц могут иметь составной тип. Как правило, для выполнения конкретного запроса в данных условиях не нужны все возможные типы данной ссылки. В этом случае, следует ограничить количество возможных типов при помощи функции ВЫРАЗИТЬ, которая приводит значения поля к одному из примитивных типов, или к ссылочному типу данных (в последнем случае в синтаксисе <Имя таблицы> указывает на соответствующую таблицу информационной базы).
Если данный запрос является универсальным и используется в нескольких разных ситуациях (где типы ссылки могут быть разными), то можно формировать запрос динамически, подставляя в функцию ВЫРАЗИТЬ тот тип, который необходим при данных условиях.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Запрос.Текст = "ВЫБРАТЬ | ВЫБОР | КОГДА Продажи.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг | ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.РеализацияТоваровУслуг).Номер | КОГДА Продажи.Регистратор ССЫЛКА Документ.ЗаказПокупателя | ТОГДА ВЫРАЗИТЬ(Продажи.Регистратор КАК Документ.ЗаказПокупателя).Номер | КОНЕЦ КАК Номер, | Продажи.Контрагент, | Продажи.Количество, | Продажи.Стоимость |ИЗ | РегистрНакопления.Продажи КАК Продажи |ГДЕ | Продажи.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг | ИЛИ Продажи.Регистратор ССЫЛКА Документ.ЗаказыПокупателя"; |
Функция ЗНАЧЕНИЕ() предназначена для получения ссылки на предопределенные значения справочников, перечислений, планов видов характеристик, планов счетов, планов видов расчетов, точек маршрутов бизнес процессов.
При этом имя вида объекта указывается в запросе в единственном числе (в коде модулей - во множественном).
Ссылка на справочник:
|
1 |
ВЫБРАТЬ ЗНАЧЕНИЕ(Справочник.ВидыНоменклатуры.Услуга) |
Проверка на пустое значение справочника:
|
1 2 3 4 5 |
ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты КАК Контрагенты ГДЕ Контрагенты.ОсновнойДоговорКонтрагента = ЗНАЧЕНИЕ(Справочник.ДоговорыКонтрагентов.ПустаяСсылка) |
Получение значения перечисления:
|
1 2 3 4 5 6 7 |
ВЫБРАТЬ Ссылка.Владелец КАК Контрагент ИЗ Справочник.ДоговорыКонтрагентов КАК Договор ГДЕ Договор.ВидДоговора = ЗНАЧЕНИЕ(Перечисление.ВидыДоговоровКонтрагентов.СПокупателем) ИЛИ Договор.ВидДоговора = ЗНАЧЕНИЕ (Перечисление.ВидыДоговоровКонтрагентов.ПустаяСсылка) |
Получение значения плана видов характеристик:
|
1 |
ВЫБРАТЬ ЗНАЧЕНИЕ(ПланВидовХарактеристик.НастройкиПользователей.ПустаяСсылка) |
Получение счета в плане счетов:
|
1 |
ВЫБРАТЬ ЗНАЧЕНИЕ (ПланСчетов.Хозрасчетный.Товары) КАК СчетТоваров |
Получение системных перечислений:
|
1 2 3 4 5 6 7 8 |
ВЫБРАТЬ ЗНАЧЕНИЕ(ВидДвиженияНакопления.Расход) КАК Расход, ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход) КАК Приход, ЗНАЧЕНИЕ(ВидДвиженияБухгалтерии.Дебет) КАК Дебет, ЗНАЧЕНИЕ(ВидДвиженияБухгалтерии.Кредит) КАК Кредит, ЗНАЧЕНИЕ(ВидСчета.Активный) КАК Активный, ЗНАЧЕНИЕ(ВидСчета.Пассивный) КАК Пассивный, ЗНАЧЕНИЕ(ВидСчета.АктивноПассивный) КАК АктивноПассивный |
Аналог функции ЗначениеЗаполнено() в запросе:
В явном виде функции проверки на заполнение значения нет, но возможна проверка на несовпадение, например:
|
1 |
ГДЕ НЕ ОсновнойДоговорКонтрагента = ЗНАЧЕНИЕ(Справочник.ДоговорыКонтрагента.ПустаяСсылка) |
Проверка на отсутствие в списке:
|
1 |
ГДЕ НЕ ОсновнойДоговорКонтрагента В (ЗНАЧЕНИЕ(Справочник.ДоговорыКонтрагента.ПустаяСсылка), НЕОПРЕДЕЛЕНО, NULL,"") |
Т. е. если значение не пустое или не входит в список пустых, его можно считать заполненным.
Недопустима передача в ЗНАЧЕНИЕ параметра синтаксисом
ЗНАЧЕНИЕ(&Параметр).
https://its.1c.ru/db/metod8dev/content/2659/hdoc
|
1 2 3 4 5 6 7 8 9 |
ВЫБРАТЬ Номенклатура.Код, Номенклатура.Наименование КАК Наименование, Номенклатура.ЗакупочнаяЦена ИЗ Справочник.Номенклатура КАК Номенклатура ГДЕ Номенклатура.Ссылка В ИЕРАРХИИ(&Группа) |
Будут получены все записи справочника Номенклатура, находящиеся в группе &Группа, включая:
- ее саму;
- ее подчиненные группы;
- элементы, принадлежащие подчиненным группам.
|
1 2 3 4 5 6 7 8 9 |
ВЫБРАТЬ Номенклатура.Код, Номенклатура.Наименование КАК Наименование, Номенклатура.ЗакупочнаяЦена ИЗ Справочник.Номенклатура КАК Номенклатура ГДЕ Номенклатура.Родитель = &Группа |
Запрос выберет группы и элементы, находящиеся в подчинении группы &Группа.
|
1 2 3 4 5 6 |
ВЫБРАТЬ ПЕРВЫЕ 1 Номенклатура.Ссылка ИЗ Справочник.Номенклатура КАК Номенклатура ГДЕ Номенклатура.Родитель = &Родитель |
Ссылка элемента, для которого необходимо проверить наличие дочерних элементов, передается в параметр запроса "Родитель". После выполнения такого запроса необходимо проверить результат на пустоту. Если результат не пустой, то подчиненные записи есть:
|
1 2 3 4 5 |
Если Запрос.Выполнить().Пустой() Тогда Сообщить("Зписей нет"); Иначе Сообщить("Записи есть"); КонецЕсли; |
В языке запросов не предусмотрено специальных средств для получения всех родителей элемента. Для выполнения задачи можно воспользоваться иерархическими итогами, однако получение иерархических итогов оптимизировано для построения итогов большого количества записей, и не вполне эффективно для получения родителей одного элемента. Для более эффективного получения всех родительских записей элемента, рекомендуется перебирать в цикле его родителей небольшими порциями:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
ТекущийЭлементНоменклатуры = ЭлементНоменклатура; Запрос = Новый Запрос("ВЫБРАТЬ | Номенклатура.Родитель, | Номенклатура.Родитель.Родитель, | Номенклатура.Родитель.Родитель.Родитель, | Номенклатура.Родитель.Родитель.Родитель.Родитель, | Номенклатура.Родитель.Родитель.Родитель.Родитель.Родитель |ИЗ | Справочник.Номенклатура КАК Номенклатура | |ГДЕ | Номенклатура.Ссылка = &ТекущийЭлементНоменклатуры"; Пока Истина Цикл Запрос.УстановитьПараметр("ТекущийЭлементНоменклатуры", ТекущийЭлементНоменклатуры); Результат = Запрос.Выполнить(); Если Результат.Пустой() Тогда Прервать; КонецЕсли; Выборка = Результат.Выбрать(); Выборка.Следующий(); Для НомерКолонки = 0 По Результат.Колонки.Количество() - 1 Цикл ТекущийЭлементНоменклатуры = Выборка[НомерКолонки]; Если ТекущийЭлементНоменклатуры = Справочники.Номенклатура.ПустаяСсылка() Тогда Прервать; Иначе Сообщить(ТекущийЭлементНоменклатуры); КонецЕсли; КонецЦикла; Если ТекущийЭлементНоменклатуры = Справочники.Номенклатура.ПустаяСсылка() Тогда Прервать; КонецЕсли; КонецЦикла; |
В данном примере в окно служебных сообщений выводятся все родители для ссылки, записанной в переменную ЭлементНоменклатура. В цикле выбирается по 5 родителей ссылки.
Если число уровней в справочнике ограничено и невелико, то возможно получение всех родителей одним запросом без цикла.
|
1 2 3 4 5 6 7 8 |
ВЫБРАТЬ Номенклатура.Код, Номенклатура.Наименование КАК Наименование, Номенклатура.ЗакупочнаяЦена ИЗ Справочник.Номенклатура КАК Номенклатура УПОРЯДОЧИТЬ ПО Наименование ИЕРАРХИЯ |
Данный запрос выбирает все записи из справочника и производит упорядочивание по иерархии. Результат будет упорядочен по наименованию, с учетом иерархии.
Для того чтобы группы справочника размещались выше элементов необходимо в данном запросе заменить предложение УПОРЯДОЧИТЬ ПО на следующее:
|
1 2 3 |
УПОРЯДОЧИТЬ ПО Номенклатура.ЭтоГруппа ИЕРАРХИЯ, Наименование |
Результат по-прежнему будет упорядочен по иерархии, однако группы будут располагаться выше элементов.
Возможна также замена предложения УПОРЯДОЧИТЬ ПО на предложение АВТОУПОРЯДОЧИВАНИЕ. В этом случае результат будет упорядочен в соответствии с настройками справочника, т.е. если в справочнике указано, что группы должны располагаться выше элементов, то они будут расположены выше.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
ВЫБРАТЬ Номенклатура.Код, Номенклатура.Наименование КАК Наименование, Номенклатура.ЗакупочнаяЦена ИЗ Справочник.Номенклатура КАК Номенклатура ГДЕ (Номенклатура.ЭтоГруппа = ЛОЖЬ) УПОРЯДОЧИТЬ ПО Наименование ИТОГИ ПО Номенклатура.Ссылка ТОЛЬКО ИЕРАРХИЯ |
Для получения итогов по иерархии в запросе необходимо в предложении ИТОГИ ПО указать ключевое слово ИЕРАРХИЯ после указания поля, по которому будет рассчитываться итоги.
Пример отчета "Обороты номенклатуры" с получением итогов по иерархии:
ВЫБРАТЬ
|
1 2 3 4 5 6 7 |
УчетНоменклатурыОбороты.Номенклатура КАК Номенклатура, УчетНоменклатурыОбороты.Номенклатура.Представление, УчетНоменклатурыОбороты.КоличествоОборот КАК КоличествоОборот ИЗ РегистрНакопления.УчетНоменклатуры.Обороты КАК УчетНоменклатурыОбороты ИТОГИ СУММА(КоличествоОборот) ПО Номенклатура ИЕРАРХИЯ |
В результате данного запроса будут рассчитаны итоги не только для каждой номенклатуры, но и для групп, к которым принадлежит та или иная номенклатура.
В случае, когда не нужны итоги по элементам, а нужны итоги только по группам, нам необходимо использовать в итогах конструкцию ТОЛЬКО ИЕРАРХИЯ:
|
1 2 3 4 5 6 7 8 |
ВЫБРАТЬ УчетНоменклатурыОбороты.Номенклатура КАК Номенклатура, УчетНоменклатурыОбороты.Номенклатура.Представление, УчетНоменклатурыОбороты.КоличествоОборот КАК КоличествоОборот ИЗ РегистрНакопления.УчетНоменклатуры.Обороты КАК УчетНоменклатурыОбороты ИТОГИ СУММА(КоличествоОборот) ПО Номенклатура ТОЛЬКО ИЕРАРХИЯ |
В результате данного запроса будут итоговые записи только для групп номенклатуры.
Оператор ССЫЛКА позволяет проверить, является ли значение выражения, указанного справа от него, ссылкой на таблицу, указанную слева (если "да" – результатом оператора будет ИСТИНА, иначе – ЛОЖЬ).
см. также: ВЫРАЗИТЬ() (приведение значения к какому-либо типу)
|
1 2 3 4 5 |
ВЫБРАТЬ Справочник.Номенклатура.Наименование, Справочник.Номенклатура.ЕдиницаИзмерения ГДЕ Справочник.Номенклатура.ЕдиницаИзмерения ССЫЛКА Справочник.ЕдиницыИзмерения |
Оператор ПОДОБНО позволяет сравнить значение выражения (типа СТРОКА), указанного слева от него, со строкой шаблона, указанной справа (если значение выражения удовлетворяет шаблону – ИСТИНА, иначе – ЛОЖЬ).
Служебные символы в строке шаблона:
- % (процент): последовательность, содержащая любое количество произвольных символов;
- _ (подчеркивание): один произвольный символ;
- […] (в квадратных скобках один или несколько символов): любой одиночный символ из перечисленных внутри квадратных скобок (в т.ч. диапазоны, например a-z, означающие произвольный символ, входящий в диапазон, включая концы диапазона);
- [^…] (в квадратных скобках значок отрицания, за которым следует один или несколько символов): любой одиночный символ, кроме тех, которые перечислены следом за значком отрицания.
Любой другой символ означает сам себя и не несет никакой дополнительной нагрузки.
Если в качестве самого себя необходимо записать один из перечисленных символов, то ему должен предшествовать <Спецсимвол>. Сам <Спецсимвол> (любой подходящий символ) определяется в этом же операторе после ключевого слова СПЕЦСИМВОЛ.
Например, шаблон “%АБВ[0-9][абвг]\_абв%” СПЕЦСИМВОЛ “\” означает подстроку, состоящую из последовательности символов:
- перед этой последовательностью может располагаться произвольный набор символов;
- буквы А;
- буквы Б;
- буквы В;
- одной цифры;
- одной из букв а, б, в или г;
- символа подчеркивания;
- буквы а;
- буквы б;
- буквы в.