5 Запросы Введение Запросы в V 8 предназначены

5. Запросы Введение. Запросы в V 8 предназначены для выборки информации из базы данных (т. е. предназначены только для чтения данных). Примеры чтения данных прямым обращением к БД и запросом: А) Выборка = Справочники. Сотрудники. Выбрать(); Пока Выборка. Следующий() Цикл Если Выборка. Оклад > 10000 Тогда Сообщить(Выборка. Наименование + " имеет оклад " + Выборка. Оклад); Конец. Если; Конец. Цикла; Б) Запрос = Новый Запрос(" |ВЫБРАТЬ * ИЗ Справочник. Сотрудники |ГДЕ Оклад > 10000"); Выборка = Запрос. Выполнить(). Выбрать(); Пока Выборка. Следующий() Цикл Сообщить(Выборка. Наименование + " имеет оклад " + Выборка. Оклад); Конец. Цикла;

Замечания. Замечание 1. Вариант с запросом обычно выполняется быстрее, так как в клиент-серверном варианте работы запрос выполняется на сервере и не требуется передавать по сети весь справочник, который может быть очень большим. В клиент-серверном варианте работы V 8 запросы будут транслироваться в SQL для выполнения в среде MS SQL Server. Сервер БД предпримет необходимые действия для оптимизации запроса. Замечание 2. При использовании запроса, результат целиком помещается в память, тогда как выборка, сформированная средствами встроенного языка, загружает информацию порциями и позволяет перебрать большие списки объектов, не требуя значительного объема памяти.

5. 1. Общая схема выполнения запроса: 1. Создание объекта типа Запрос с текстом запроса на специальном языке запросов. 2. Установка параметров запроса с помощью метода Установить. Параметр. 3. Выполнение запроса, получение результата. 4. Получение выборки из результата запроса или выгрузка результата в таблицу значений / дерево значений. Также есть возможность использовать результат запроса как источник данных для сводной таблицы. 5. Обработка выборки или таблицы значений (например, перебор строк) и выполнение действий, для которых был нужен запрос, например, вывод области при формировании отчета

пример // 1. создание объекта Запрос = Новый Запрос(" |ВЫБРАТЬ Код, Наименование |ИЗ Справочник. Номенклатура"); // 2. запрос без параметров // 3. выполнение запроса и получение результата Результат. Запроса = 3 апрос. Выполнить (); // 4. получение выборки из результата запроса Выборка = Результат. Запроса. Выбрать(); // 5. обход записей в выборке Пока Выборка. Следующий() Цикл //обращение к полям Сообщить(Выборка. Наименование); Конец. Цикла;

5. 2. Конструктор запросов В V 8 существует Конструктор запросов, который рекомендуется для быстрого создания запросов. – изучить самостоятельно Вызов КЗ происходит из любого программного модуля с помощью главного меню Конфигуратора

5. 3. Язык запросов В общем случае текст запроса строится по следующей схеме: ВЫБРАТЬ <Список полей | *> [ИЗ <Список таблиц-источников>] [ГДЕ <Список условий>] [УПОРЯДОЧИТЬ ПО <Список полей > ] | [АВТОУПОРЯДОЧИВАНИЕ] [СГРУППИРОВАТЬ ПО <Список полей>] [ИТОГИ [<агрегатные функции>] ПО <Список полей> [ОБЩИЕ]]

5. 3. 1. Предложение ВЫБРАТЬ / SELECT ВЫБРАТЬ [РАЗЛИЧНЫЕ] <Список полей выборки | *> [ПЕРВЫЕ <Количество>] В качестве источника данных для запроса можно использовать справочники, документы, журналы документов, регистры и другие таблицы-источники (см. дальше). Примеры: ВЫБРАТЬ Наименование, Цена ИЗ Справочник. Товары ВЫБРАТЬ * ИЗ Справочник. Сотрудники ВЫБРАТЬ Номер, Дата, Представление ИЗ Документ. Расходная. Накладная ВЫБРАТЬ * ИЗ Регистр. Накопления. Продажи ( или без ключевого слова ИЗ) ВЫБРАТЬ Справочник. Товары. Наименование, Справочник. Товары. Цена ВЫБРАТЬ Справочник. Сотрудники. *

5. 3. 2. Псевдонимы полей (КАК/ AS) Пример: ВЫБРАТЬ Наименование КАК Товар, Цена, Ед. Изм КАК Единица. Измерения ИЗ Справочник. Товары УПОРЯДОЧИТЬ ПО Товар Результат запроса будет следующий: Замечание. Ключевое слово КАК может быть опущено. ВЫБРАТЬ Наименование Товар, Цена, Ед. Изм Единица. Измерения ИЗ Справочник. Товары УПОРЯДОЧИТЬ ПО Товар

5. 3. 3. Ключевое слово РАЗЛИЧНЫЕ/DISTINCT - позволяет оставить в результате запроса только отличающиеся строки ВЫБРАТЬ Контрагент ИЗ Документ. Расходная. Накладная ВЫБРАТЬ РАЗЛИЧНЫЕ Контрагент ИЗ Документ. Расходная. Накладная

5. 3. 4. Ключевое слово ПЕРВЫЕ / ТОР -позволяет ограничить выборку несколькими первыми записями. Часто это ключевое слово применяется в комбинации с сортировкой (см. предложение УПОРЯДОЧИТЬ ПО). ВЫБРАТЬ ПЕРВЫЕ 3 Наименование, Цена ИЗ Справочник. Номенклатура УПОРЯДОЧИТЬ ПО Цена УБЫВ

5. 3. 5. Поля из вложенных таблиц Поле в списке выборки может ссылаться на вложенную таблицу источника данных, например, на табличную часть справочника, документа. Список полей, выбираемых из вложенной таблицы, описывается по следующему образцу: //требуются все поля из вложенной таблицы ВЫБРАТЬ Документ. Расходная. Накладная. Состав. * //требуется только одно поле из табличной части ВЫБРАТЬ Документ. Расходная. Накладная. Состав. Сумма //требуется несколько полей из табличной части ВЫБРАТЬ Документ. Расходная. Накладная. Состав. (Количество, Сумма) //для полей вложенной таблицы можно указать псевдонимы ВЫБРАТЬ Документ. Расходная. Накладная. Состав. (Количество КАК Кол, Сумма КАК Сум)

5. 3. 6. Предложение ИЗ / FROM - позволяет указать таблицы-источники для запроса и задать порядок их соединения, если таблиц несколько. Вот простые примеры запросов с предложением ИЗ: ВЫБРАТЬ * ИЗ Справочник. Товары ВЫБРАТЬ * ИЗ Документ. Расходная. Накладная

5. 3. 7. Таблицы-источники данных Если у справочника есть табличные части, то к ним можно обращаться по имени. При этом в результате запроса появляется вложенная таблица. Поле «Представление» виртуальное, т. е. не хранится в базе данных, а генерируется «на лету» .

(продолжение) Примеры: ВЫБРАТЬ Код, Наименование, Цена, Родитель ИЗ Справочник. Номенклатура ВЫБРАТЬ *, Представление ИЗ Справочник. Номенклатура ВЫБРАТЬ Наименование, Это. Группа , Пометка. Удаления ИЗ Справочник. Номенклатура ГДЕ Это. Группа = ИСТИНА И Пометка. Удаления = ЛОЖЬ Структуры таблиц – источников данных - самостоятельно

5. 3. 8. Псевдонимы источников КАК/AS ВЫБРАТЬ Спр. Наименование, Спр. Цена, Спр. Страна ИЗ Справочник. Номенклатура КАК Спр

5. 3. 9. Запросы к табличным частям В качестве таблиц - источников можно указывать табличные части объектов, например, справочников и документов( это не то же самое, что получать табличную часть как поле запроса, содержащее вложенную таблицу, как раньше). Например, ВЫБРАТЬ Товар, Цена, Количество, Сумма И 3 Документ. Расходная. Накладная. Состав Если запрос делается к табличной части справочника или документа, то обращение к реквизитам шапки документа или обычным реквизитам справочника производится через поле Ссылка, например: ВЫБРАТЬ Ссылка. Дата, Ссылка. Номер, Номенклатура, Цена, Количество, Сумма ИЗ Документ. Расходная. Накладная. Состав

продолжение

5. 3. 10. Вложенные запросы в списке источников В V 8 можно указать в качестве источника другой запрос. Для вложенного запроса, как для обычной таблицы-источника, можно указать псевдоним. Пример: ВЫБРАТЬ Товары. Номенклатура КАК Товар, Товары. Номенклатура. Закупочная. Цена КАК Цена ИЗ ( ВЫБРАТЬ Номенклатура ИЗ Документ. Расходная. Накладная. Состав ) КАК Товары

продолжение

5. 3. 11. Конструкция СОЕДИНЕНИЕ. . . ПО/JOIN. . . ON Конструкция позволяет обращаться в одном запросе к нескольким таблицам. Используется для организации внутренних и внешних соединений таблиц.

Внутренние соединения Пример: ВЫБРАТЬ Док. Номенклатура, Спр. Закупочная. Цена КАК Цена, Спр. Родитель КАК Группа ИЗ Документ. Расходная. Накладная. Состав КАК Док СОЕДИНЕНИЕ Справочник. Номенклатура КАК Спр ПО Док. Номенклатура = Спр. Ссылка

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

Левое внешнее соединение Конструкция ЛЕВОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ означает , что в результат запроса будут включены все записи из первого источника; они будут соединены с записями из второго источника при выполнении заданного условия. Строки результата запроса, для которых не найдено соответствующих условию записей из второго источника, будут содержать значение NULL в полях, формируемых на основании записей из этого источника. ВЫБРАТЬ Спр. Наименование, Per. Курс ИЗ Справочник. Валюты КАК Спр ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ Регистр. Сведений. Курсы. Валют. Срез. Последних КАК Peг ПО Спр. Ссылка = Pег. Валюта

Правое внешнее соединение полностью аналогично левому, за исключением того, что таблицы поменялись местами. ВЫБРАТЬ Спр. Наименование, Peг. Курс ИЗ Регистр. Сведений. Курсы. Валют. Срез. Последних КАК Peг ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ Справочник. Валюты КАК Спр ПО Спр. Ссылка = Peг. Валюта

Полное внешнее соединение Конструкция ПОЛНОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ означает , в результат запроса будут включены все записи из обоих источников; они будут соединены друг с другом при выполнении указанного условия. Строки результата запроса, для которых не найдено соответствующих условию записей из какого-либо источника, будут содержать NULL в полях, формируемых на основании записей из этого источника.

5. 3. 12. Предложение ГДЕ / WHERE позволяет задать условие отбора данных из исходных таблицисточников запроса ВЫБРАТЬ Наименование, Закупочная. Цена КАК Цена ИЗ Справочник. Номенклатура ГДЕ Закупочная. Цена >= 1300

5. 3. 13. Логические операторы И, ИЛИ, НЕ ВЫБРАТЬ Наименование, Закупочная. Цена КАК Цена, Страна. Происхождения КАК Страна ИЗ Справочник. Номенклатура ГДЕ Страна. Происхождения = "Россия" И Закупочная. Цена < 1000 Приоритеты: НЕ, И, ИЛИ

5. 3. 14. Параметры в языке запросов Параметры – это внешние по отношению к запросу переменные, значения которых используются в запросе. Для указания параметра в запросе используется знак &. Пример //создание объекта Запрос с текстом запроса Запрос = Новый Запрос; Запрос. Текст="Выбрать Наименование, Цена |ИЗ Справочник. Номенклатура |ГДЕ Цена >= &Мин. Цена"; //передача параметров в запрос Запрос. Установить. Параметр("Мин. Цена", 1000); //выполнение запроса с установленными параметрами Результат = Запрос. Выполнить();

5. 3. 15. Ключевое слово МЕЖДУ/BETWEEN используется для задания интервалов. ВЫБРАТЬ Наименование, Закупочная. Цена КАК Цена ИЗ Справочник. Номенклатура ГДЕ Закупочная. Цена МЕЖДУ &Мин. Цена И &Макс. Цена

5. 3. 16. Проверка вхождения значения в список (В/IN) Варианты использования ключевого слова В рассмотрим на примерах: 1. //выбираем товары из заданного списка значений ВЫБРАТЬ Наименование, Закупочная. Цена ИЗ Справочник. Номенклатура В ГДЕ Ссылка (&Список. Выбранных. Товаров) 2. //выбираем товары, принадлежащие определенной группе //независимо от уровня, на котором они находятся ВЫБРАТЬ Наименование, Закупочная. Цена ИЗ Справочник. Номенклатура ГДЕ Ссылка В ИЕРАРХИИ (&Выбранная. Группа)

5. 3. 17. Проверка ссылочного значения (ССЫЛКА/REF) Оператор ССЫЛКА позволяет проверить, является ли значение выражения ссылкой на таблицу, указанную справа от него. Этот оператор полезен для полей, имеющих составной тип данных. ВЫБРАТЬ Наименование, Единица. Измерения ИЗ Справочник. Номенклатура ГДЕ Единица. Измерения ССЫЛКА Справочник. Единицы. Измерения

5. 3. 18. Проверка пустых значений (ЕСТЬ NULL / IS NULL) Оператор ЕСТЬ NULL позволяет проверить значение заданного выражения на NULL (NULL-значения - это неуказанные, отсутствующие или неизвестные значения. ) ВЫБРАТЬ Наименование, Страна. Происхождения КАК Страна ИЗ Справочник, Номенклатура ГДЕ Страна. Происхождения ЕСТЬ NULL Обратите внимание, что в результате запроса присутствуют только группы справочника, так как для них поле Страна имеет NULLзначение (вообще не указывается). В результат запроса не попали товары, у которых строковое поле Страна. Происхождения имеет значение «» (пустая строка). Ни ноль, ни пустая строка, ни пробел не являются NULL-значением.

продолжение Если стоит задача выбрать элементы с неуказанным реквизитом, имеющим ссылочный тип, тогда следует поступать по-другому. Например, требуется выбрать все товары с незаполненным полем Основной. Поставщик, которое является ссылкой на справочник Контрагенты. Запрос = Новый Запрос(" | ВЫБРАТЬ Наименование, Основной. Поставщик | ИЗ Справочник. Номенклатура | ГДЕ Основной. Поставщик = &Пустой. Контрагент "); Запрос. Установить. Параметр("Пустой. Контрагент", Справочники. Контрагенты. Пустая. Ссылка()); Результат = Запрос. Вылолнить();

5. 3. 19. Сравнение строк (ПОДОБНО / LIKE) Ключевое слово ПОДОБНО позволяет сравнить значение строкового выражения, указанного слева от него, со строкой шаблона, указанной справа ВЫБРАТЬ Наименование ИЗ Справочник. Контрагенты ГДЕ Наименование ПОДОБНО "М%” «%» обозначает любую последовательность символов. Кроме этого, есть и другие служебные символы для задания выражения шаблона (самостоятельно)

5. 3. 20. Предложение УПОРЯДОЧИТЬ ПО / ORDER BY Используется для сортировки результата запроса. Пример 1 ВЫБРАТЬ Код, Наименование ИЗ Справочник. Номенклатура УПОРЯДОЧИТЬ ПО Наименование ВОЗР Пример 2 ВЫБРАТЬ ПЕРВЫЕ 5 Код, Наименование, Закупочная. Цена КАК Цена И 3 Справочник. Номенклатура УПОРЯДОЧИТЬ ПО Цена УБЫВ

5. 3. 21. Упорядочивание по иерархии Используется для иерархических справочников. Например: ВЫБРАТЬ Ссылка КАК Товар ИЗ Справочник. Номенклатура УПОРЯДОЧИТЬ ПО Наименование ИЕРАРХИЯ

5. 3. 22. Упорядочивание во вложенных таблицах ВЫБРАТЬ Накл. Состав. Ссылка. Номер, Номенклатура, Количество ИЗ Документ. Расходная. Накладная. Состав КАК Накл. Состав УПОРЯДОЧИТЬ ПО Накл. Состав. Ссылка. Номер, Накл. Состав. Номенклатура. Наименование

5. 3. 23. Автоупорядочивание /AUTOORDER позволяет включить режим автоматического формирования полей для упорядочивания результата запроса. Замечание. Если в запросе отсутствуют предложения УПОРЯДОЧИТЬ ПО, ИТОГИ и СГРУППИРОВАТЬ ПО, результат будет упорядочен по полям сортировки по умолчанию для таблиц, из которых выбираются данные, в порядке их появления в запросе. Прочие взаимодействия данных ключевых полей с словом Автоупорядочивание – самостоятельно.

5. 3. 24. Агрегатные функции в запросе Для получения сводной информации в запросе используются агрегатные функции, группировки и предложения ИМЕЮЩИЕ (HAVING). 1. В языке запросов V 8 существуют следующие агрегатные функции: • СУММА (SUM). Вычисляет сумму всех значений, содержащихся в столбце. • МАКСИМУМ (МАХ). Находит наибольшее значение в столбце. • МИНИМУМ (MIN). Находит наименьшее значение в столбце. • СРЕДНЕЕ (AVG). Вычисляет среднее арифметическое значение по столбцу. • КОЛИЧЕСТВО (COUNT). Подсчитывает количество значений, содержащихся в столбце. Если в качестве параметра данной функции передать звездочку ( «*» ), то функция подсчитает количество строк в таблице результата запроса.