Формирование отчетов

На рис. 9.4 показан отчет Затраты по объектам за первый квартал 1998 года. В этот отчет включены данные из запроса Затраты за период. Отчет получен с помощью мастера отчетов. В окне базы данных надо выбрать тип объекта – Отчеты и нажать кнопку Создать отчет с помощью мастера, далее выбрать запрос Затраты за период – в качестве источника данных. После этого, делая выбор в предлагаемых диалогах, остается уточнить перечень полей, включаемых в отчет, вариант группировки и сортировки записей, вариант вывода итоговых строк и ориентацию страниц. Отчет в сочетании с запросом позволяет отобрать из базы данных требуемую информацию, сгруппировать итоговые записи (на рис. 9.4 группировка выполнена по объектам) и автоматически получить итоговые строки. Отчет можно напечатать, выбрав пункт меню Сервис/Связи с Office/Публикация в Microsoft Office Word. Наконец, в отчет нетрудно внести редакционные изменения, но не в режиме создания с помощью мастера отчетов, а в режиме конструктора. Так в нашем примере слово Sum, которое процедура Мастер отчетов вставляет в итоговые строки, заменено на слово Сумма. Вообще в режиме конструктора можно изменять и таблицы, и формы, и запросы, и отчеты.

Завершая краткое рассмотрение СУБД Access, подчеркнем важную роль запросов. Именно с помощью запросов в любой СУБД можно отбирать данные по многим критериям, сортировать записи и, вообще, выполнять многие операции манипулирования данными. В основе этих операций – инструкции языка структурированных запросов (SQL). Когда мы создаем запрос, используя через процедуру Конструктор его визуальное конструирование, Access формирует текст этого запроса на языке SQL. Этот текст можно увидеть и при желании изменить в момент просмотра запроса. Для просмотра запроса мы нажимаем в окне базы данных кнопку Запросы и двойным щелчком мыши открываем запрос. Эти действия приводят к появлению на экране сначала предложений по вводу значений параметров, а затем виртуальной таблицы, являющейся результатом выполнения запроса. Если теперь в меню приложения Access выбрать пункт Вид/Режим SQL, то мы увидим наш запрос не в виде таблицы отобранных данных, а в виде SQL-инструкций. Эти инструкции можно изменить и с помощью пункта меню Запрос/Запуск или кнопки Запуск на панели инструментов (красный восклицательный знак) выполнить запрос и получить новую таблицу с отобранными данными.

Рис. 9.4. Типичный вид отчета, получаемого с помощью СУБД Access

Завершая краткое рассмотрение СУБД Access, подчеркнем важную роль запросов. Именно с помощью запросов в любой СУБД можно отбирать данные по многим критериям, сортировать записи и, вообще, выполнять многие операции манипулирования данными. В основе этих операций – инструкции языка структурированных запросов (SQL). Когда мы создаем запрос, используя через процедуру Конструктор его визуальное конструирование, Access формирует текст этого запроса на языке SQL. Этот текст можно увидеть и при желании изменить в момент просмотра запроса. Для просмотра запроса мы нажимаем в окне базы данных кнопку Запросы и двойным щелчком мыши открываем запрос. Эти действия приводят к появлению на экране сначала предложений по вводу значений параметров, а затем виртуальной таблицы, являющейся результатом выполнения запроса. Если теперь в меню приложения Access выбрать пункт Вид/Режим SQL, то мы увидим наш запрос не в виде таблицы отобранных данных, а в виде SQL-инструкций. Эти инструкции можно изменить и с помощью пункта меню Запрос/Запуск или кнопки Запуск на панели инструментов (красный восклицательный знак) выполнить запрос и получить новую таблицу с отобранными данными.

Основные инструкции (команды) языка SQL мы считаем полезным рассмотреть, т.к. представление о языке SQL позволяет лучше понять возможности манипулирования данными, имеющиеся в современных СУБД. В русских переводах компьютерной литературы инструкции SQL и других языков программирования часто называют операторами. В англоязычных изданиях применяются термины statement или instruction.

9.4. Основные возможности языка структурированных запросов (SQL)

Сразу заметим, что в этой небольшой книге мы не ставим задачу дать описание SQL, а всего лишь приведем обзор основных команд и на нескольких примерах покажем возможности SQL-запросов. Что касается точного описания инструкций SQL, то это описание не совсем совпадает для различных СУБД. Наиболее известные серверы реляционных баз данных (Oracle, MS SQL Server, Cybase, Informix и некоторые другие) поддерживают ту или иную версию или уровень стандарта ANSI / ISO SQL (ANSI – Американский национальный институт стандартов, ISO – Международная организация стандартизации), но для повышения эффективности каждый коммерческий SQL-сервер применяет некоторые расширения стандарта. Приведенные ниже примеры проверены в рамках СУБД Access. Кстати, справочная система Access достаточно полно разъясняет инструкции SQL. Ясное и полное описание технологии клиент/сервер, включающее также классификацию и описание команд SQL, применительно к СУБД Oracle 7 приведено в [14].

Приступая к рассмотрению SQL-запросов, надо также пояснить понятие транзакции. Транзакция базы данных – это единица работы, состоящая из одной или нескольких инструкций (команд) SQL. Транзакцию нельзя разбить на отдельные запросы. Например, перечисление денег предполагает именно две неотделимые друг от друга операции: уменьшение суммы на одном счете в базе данных и равнозначное увеличение на другом счете. Поэтому SQL-серверы рассматривают поступающие команды SQL как временные шаги, пока не поступит команда COMMIT, отмечающая конец текущей транзакции.

Можно выделить несколько классов команд SQL: определения данных, манипулирования данными, управления транзакциями, управления данными (доступом к ним). Последние два класса инструкций применяются в запросах к SQL-серверам, а первые два класса – и в запросах к локальным СУБД.

Команды языка определения данных (DDL – Data Definition Language) – это команды SQL, которые можно использовать для создания (CREATE), изменения (ALTER) и удаления (DROP) различных объектов базы данных (таблиц, процедур и др.). В качестве примера рассмотрим команду, с помощью которой можно создать таблицу Объекты [ КодОб, ИмяОб]:

CREATE TABLE Объекты (КодОб LONG CONSTRAINT obj PRIMARY KEY, ИмяОб TEXT(40))

В этой инструкции CREATE TABLE указывается имя создаваемой таблицы (Объекты), и затем в скобках, через запятую описываются поля записей. Поле КодОб имеет тип LONG (см. раздел 3), а после слова CONSTRAINT (ограничение) указывается имя индекса (obj) для этого поля и параметр PRIMARY KEY – ведь поле КодОб должно быть первичным ключом. Описание второго поля таблицы включает его имя (ИмяОб), тип (TEXT) и длину в байтах (40). Пример показывает, что при хорошем знании синтаксиса языка SQL структуру любой таблицы базы данных можно создать не только с помощью имеющихся в Access (но не в каждой СУБД) средств визуального конструирования, но и с помощью команды SQL.

К числу команд языка манипулирования данными (DML) относятся инструкции SELECT (отбор данных), INSERT (вставка записей в таблицу), UPDATE (обновление записей), DELETE (удаление записей). Приведем пример инструкции SELECT:

SELECT Затраты.[Код затр], Объекты.Объект, Затраты.Дата, Затраты.Стоимость

FROM Затраты, Организации, Работы, Объекты

WHERE Объекты.[Код об] = Затраты.[Код об]

AND Затраты.Дата >= #7/01/2009# AND Затраты.Дата < #1/1/2010#

ORDER BY Затраты.[Код затр];

В этом примере создается виртуальная таблица, состав полей которой определен во фразе SELECT, до фразы FROM: поле Код затр из таблицы Затраты, поле Объект из таблицы Объекты и т.д. Имя таблицы и имя поля указываются через точку. Имя поля Код затр заключено в квадратные скобки, т.к. содержит пробел. В некоторых СУБД для ограничения имен с пробелами применяются апострофы. Поэтому удобнее при конструировании таблиц вместо пробелов в именах полей использовать подчерк.

Во фразе FROM указываются имена исходных таблиц.

Во фразе WHERE задаются условия отбора записей. Так, из таблицы Затраты отбираются только те записи, в которых дата больше (позже) или равна 01.07.2009 и меньше (раньше) 01.01.2010 (даты в текстах запросов задаются в формате #мм/дд/гг#). Для того, чтобы к записи из таблицы Затраты “привязать” через код объекта соответствующие записи из таблицы Объекты, во фразе WHERE заданы еще условия равенства этих кодов. Все условия заданы через И (AND), поскольку в этом примере требуется выполнение и первого, и второго, и третьего условий. В некоторых запросах условия задаются через ИЛИ (OR) – тогда для отбора записи требуется выполнение или первого, или второго условия.

Во фразе ORDER BY указано поле, по которому надо упорядочить (отсортировать) записи в виртуальной таблице результатов. Эта таблица не хранится в базе данных, а создается только в оперативном порядке, например, когда запрос является источником данных для формирования отчета.

На рис. 9.6 показан результат запроса, содержащего рассмотренную инструкцию SELECT.

Рис. 9.6. Результат запроса, содержащего инструкцию SELECT

Приведем также примеры инструкции DELETE:

DELETE * FROM Объекты - удаление всех записей из таблицы Объекты,

DELETE * FROM Организации WHERE Город IN (‘Париж’, ’Ницца’, ’Чикаго’) - удаление из таблицы Организации записей, соответствующих тем организациям, которые расположены в городах, входящих в указанное множество. Если в таблице таких записей не окажется, то ничего удалено не будет.

К числу команд управления транзакциями относятся следующие:

COMMIT - сохранить (зафиксировать) внесенные текущей транзакцией изменения,

ROLLBACK – отменить изменения, вносимые текущей транзакцией,

SAVEPOINT – назначить промежуточную точку сохранения.

Промежуточные точки позволяют разбить выполняемую транзакцией работу на фрагменты:

INSERT …

DELETE …

SAVEPOINT one

UPDATE …

DELETE …

SAVEPOINT two

INSERT …

UPDATE …

Используя точки сохранения можно отменить одни части транзакции и оставить другие:

ROLLBACK TO SAVEPOINT two

Эта команда отменит действие всех SQL-инструкций после точки сохранения two.

Команды языка управления данными (DCL) используются для управления доступом пользователей к базе данных. Эти команды, как и инструкции управления транзакциями применяются при работе с SQL-серверами. Например, в СУБД Oracle 7 [14] введено понятие ролей. Каждая роль имеет имя и определенный набор полномочий: право создавать или удалять таблицы, изменять определенные таблицы и т.п. Если пользователь знает пароль некоторой роли, например, роли с именем role7, то он может установить эту роль в текущем сеансе и тем самым получить полномочия этой роли:

SET ROLE role7 IDENTIFIED BY x77y - устанавливается роль role7 с паролем x77y.

Кроме обширного набора команд языка SQL, серверы реляционных баз данных предоставляют средства для создания процедур и триггеров базы данных (триггер - особый тип процедур – см. далее). Так, Oracle 7 предлагает для SQL процедурные языковые расширения, называемые PL/SQL. Сам SQL – непроцедурный язык: мы не видим деталей обработки, конкретных процедур, в которые сервер транслирует наш запрос. Отсюда и простота использования SQL. Процедурные языки, такие как Си, Паскаль, Бейсик, требуют детальной записи действий. Поэтому их использовать сложнее, но они обладают большей гибкостью и более широкими возможностями. PL/SQL сочетает возможности SQL с возможностями процедурного языка. Как и в программе на процедурных языках (раздел 10), в процедуре на PL/SQL можно описать переменные и константы, использовать инструкции проверки условий, организовывать циклы и т.д. В виде процедур и их пакетов можно запрограммировать сложные алгоритмы обработки данных, что иногда невозможно или неэффективно сделать с помощью одних только команд SQL. Особым видом процедур являются триггеры базы данных. Триггер – это процедура, связываемая с таблицей. Когда дается команда SQL, соответствующая условиям триггера, СУБД запускает триггер на выполнение. Поэтому триггеры используют для того, чтобы СУБД реагировала на различные ситуации, например, на ввод в таблицу определенных данных. Таким образом, в рамках технологии «клиент/сервер» на сервере хранятся не только данные, но и многочисленные процедуры их обработки.

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

Контрольные вопросы к главе 9

Что такое база данных, предметная область, структурирование данных;

структуры данных – таблица, запись, поле;

назначение ключа, индекса;

что такое реляционная база данных;

понятие информационно-логической модели предметной области;

назначение СУБД;

нормализация отношений, что такое первая, вторая и третья нормальные формы;

обеспечение целостности данных в базе данных, понятия ссылочной целостности и целостности домена;

схема обобщенной технологии работы с СУБД;

работа с СУБД Access – создание новой базы данных, создание структуры таблиц, ввод данных в таблицы;

создание и применение форм, запросов, отчетов;

что такое SQL, классы команд SQL;

команды языка определения данных;

команды языка манипулирования данными;

команды управления транзакциями;

команды языка управления данными (доступом к данным);

что такое процедуры и триггеры базы данных.