Запросы
В предыдущих главах, посвященных работе с СУБД, мы рассмотрели базовые аспекты работы с данными с использованием средств VCL. Вместе с тем, при работе с СУБД очень часто возникает необходимость в выполнении более сложных операций. Например, это может быть сложная выборка данных по таким условиям, которые проблематично или вовсе невозможно определить при помощи фильтра - например, из нескольких таблиц одновременно. Для таких целей используют специальный язык, который "понимают" СУБД - язык SQL, а для создания обращений к СУБД на этом языке в VCL имеется специальный компонент - Query.
Язык SQL
Все современные клиент-серверные СУБД имеют одну общую черту - работа с ними строится на языке SQL - Structured Query Language (структурированный язык запросов). Кроме того, чтобы не лишать разработчиков БД на Delphi возможности работать при помощи SQL с обычными файловыми СУБД типа dBase и Paradox, BDE предоставляет возможность прозрачного использования SQL и для них, используя свои внутренние механизмы.
Стандарт языка SQL определяется ANSI (Американским национальным институтом стандартов). Вместе с тем, каждый производитель СУБД считает своим долгом дополнить язык собственными расширениями которые, как они считают, будут весьма полезны. Иногда они несколько нарушают стандарт языка, хотя хорошие идеи имеют тенденцию развиваться и вскоре становиться стандартами де-факто сами по себе в силу полезности своих качеств. Здесь мы будем рассматривать SQL на основе наиболее полно поддерживаемых всеми СУБД вариантами ANSI-89 и ANSI-92.
Язык SQL предназначен для манипулирования данными в реляционных базах данных, определения структуры баз данных и для управления правами доступа к данным в многопользовательской среде. По этой причине язык SQL состоит из 3 составных частей:
- Языка манипулирования данными (Data Manipulation Language, DML);
- Языка определения данных (Data Definition Language, DDL);
- Языка управления данными (Data Control Language, DCL).
Все эти языки являются составной частью языка SQL. Фактически, каждый из них содержит набор команд SQL, предназначенных для своей области. В процессе работы с БД, пожалуй, чаще всего используется язык манипулирования данными, состоящий из 4 основных команд - SELECT, INSERT, UPADTE и DELETE, при помощи которых производится, соответственно, выборка, вставка, обновление и удаление данных.
Язык определения данных служит для создания и изменения структуры БД - таблиц, индексов и т.д. Он состоит из 3 групп команд - CREATE, ALTER и DROP (создание, изменение и удаление, соответственно), каждая из которых может манипулировать с одним из 6 объектов - базой данных, таблицей, виртуальной таблицей, индексом, триггером или хранимой процедурой. Таким образом, например, для команды CREATE мы получаем следующие 6 вариантов:
- CREATE DATABASE - создать базу данных;
- CREATE TABLE - создать таблицу;
- CREATE VIEW - создать виртуальную таблицу;
- CREATE INDEX - создать индекс;
- CREATE TRIGGER - создать триггер;
- CREATE PROCEDURE - создать хранимую процедуру.
Впрочем, триггеры и, в особенности, хранимые процедуры относятся исключительно к компетенции промышленных СУБД, которые мы в этой книге рассматривать не будем. То же самое относится и к языку управления данными (иногда его еще называют языком управления доступом) - он состоит из 2 основных команд - GRANT (дать права) и REVOKE (забрать права).
ПРИМЕЧАНИЕ
На самом деле, для современных клиент-серверных СУБД существует ряд дополнительных команд SQL, позволяющих, например, определять собственные функции для обработки данных (CREATE FUNCTION), создавать резервные копии таблиц, преобразовывать типы таблиц и т.д. Далеко не все они входят в состав стандарта языка SQL, поэтому информацию по ним следует брать из поставляемой вместе с конкретной СУБД документации.
Мы не будем рассматривать точный синтаксис всех команд SQL, поскольку это потребовало бы объема, соизмеримого с целой книгой. Вместо этого мы рассмотрим основные команды на примерах, что намного более важно для понимания SQL, чем точный синтаксис, который, при необходимости, всегда можно посмотреть в документации на используемую СУБД. А начнем с рассмотрения команд языка манипулирования данными.
Команда SELECT
Наиболее важной командой языка манипулирования данными является команда SELECT (выбрать). За кажущейся простотой ее синтаксиса скрывается огромное число возможностей. В простейшем случае с ее помощью можно просто отобразить все содержимое таблицы. В других случаях при помощи одной команды SELECT можно одновременно производить такие операции, как фильтрация, сортировка по одной лил нескольким таблицам сразу. В самой обобщенной форме синтаксис этой команды можно представить следующим образом:
SELECT выражения_для_выборки FROM таблицы [параметры выборки]
В качестве выражения для выборки обычно перечисляют поля таблиц. Если требуется вывести все поля, то используют символ "*":
SELECT BILL_ID, BILL_SUMM FROM BILL
SELECT * FROM BILL
В первом случае будут выведены поля BILL_ID и BILL_SUMM из таблицы BILL, а во втором - все поля из этой же таблицы. В этом нетрудно убедиться, воспользовавшись утилитой SQL Explorer для написания запроса. Для этого запустите SQL Explorer, откройте нужную БД, например, DATA1 (или предварительно создайте новый алиас для более полной версии БД, созданной для примера DBApp), и в правой части окна щелкните по закладке Enter SQL, после чего введите нужный код и нажмите на кнопку Execute Query. Результат выполнения запроса незамедлительно будет выведен в таблицу внизу (рис. 21.1).
Рис. 21.1. Приложение SQL Explorer
СОВЕТ
Если у вас установлена версия Delphi, не имеющая этого инструмента, то для экспериментов с командой SELECT вы можете использовать приложение SQLELE, которое можно найти в каталоге Tools\SQLE_LE.
При выборе можно сразу же произвести упорядочивание записей, причем SQL позволяет делать эту операцию по любому полю таблицы, а не только по индексированному. Например, чтобы вывести все счета в порядке возрастания суммы, достаточно написать следующее выражение:
SELECT * FROM bill ORDER BY BILL_SUMM
Если бы требовалось выполнить сортировку не по возрастанию, а по убыванию, то в конце выражения следовало бы добавить ключевое слово DESC:
SELECT * FROM bill ORDER BY BILL_SUMM DESC
Выборка записей из таблицы производится при помощи условия, определяемого после ключевого слова WHERE. Например, если надо выбрать только те счета, которые были после 15 декабря, следует написать следующий SQL-запрос (формат написания даты в данном случае зависит от системных установок):
SELECT * FROM bill WHERE BILL_DATE>'15.12.2005'
Если при этом так же требуется еще и выполнить сортировку, то в конец выражения добавляют определение ORDER BY:
SELECT * FROM bill WHERE BILL_DATE>'15.12.2005' ORDER BY BILL_SUMM
В случае, когда условий больше одного, то их объединяют при помощи логических AND или OR:
SELECT * FROM bill WHERE BILL_DATE>'15.12.2005' AND BILL_SUMM>100
SELECT * FROM bill WHERE BILL_DATE>'15.12.2005' OR BILL_SUMM>300
Все эти примеры иллюстрируют общую форму команды SELECT в языке SQL (для одной таблицы): SELECT (выбрать) указанные поля FROM (из) указанной таблицы WHERE (где) заданные условия истинны.
Немного более сложным вариантом является выборка данных из 2 и более таблиц сразу. Например, для наглядности не помешало бы вывести в виде таблице список счетов, содержащий не внутренние номера клиентов, а их имена. В этом случае придется задействовать 2 таблицы - bill и customer, причем связующим полем будет номер клиента, т.е. поле BILL_CUST в таблице счетов и поле CUST_ID в таблице клиентов. В результате, мы получаем следующее выражение:
SELECT CUST_NAME, BILL_SUMM FROM bill, customer WHERE CUST_ID = BILL_CUST
Опять-таки, к данному условию, связывающему поля таблиц, можно добавить дополнительные, скажем, ограничивающие даты или суммы, равно как и определить упорядочивание вывода. Например, можно добавить условие, что сумма счета должна быть свыше 250, а так же задать упорядочивание по имени клиента (рис. 21.2).
Рис. 21.2. Результат выполнения SQL-запроса по 2 таблицам
Следует учитывать, что подобное связывание таблиц - путем указания условия в конструкции WHERE, является устаревшим и применяется лишь тогда, когда требуется добиться совместимости с SQL89. Более новый стандарт, SQL92, рекомендует использовать для связывания таблиц ключевое слово JOIN. Таким образом, предыдущий пример можно переписать следующим образом:
SELECT CUST_NAME, BILL_SUMM FROM customer JOIN bill ON CUST_ID = BILL_CUST
Важно отметить, что условия объединения помещают после ключевого слова ON, в то время, как остальные условия можно указать после WHERE. Таким образом, если бы нам надо бы добавить ограничения на сумму, то можно было бы написать 2 равнозначных варианта:
SELECT CUST_NAME, BILL_SUMM FROM customer JOIN bill ON CUST_ID = BILL_CUST AND BILL_SUMM>250;
SELECT CUST_NAME, BILL_SUMM FROM customer JOIN bill ON CUST_ID = BILL_CUST WHERE BILL_SUMM>250;
Для удобства можно задавать локальные "имена" как для полей, так и для таблиц. Это бывает полезным в тех случаях, когда в большом и сложном запросе используется множество условий с длинными именами полей и таблиц:
SELECT EXTRA_USER_DATA_INFO AS a, EXTRA_USER_DATA_NAME as b FROM USER_TABLE WHERE a > 10 AND a < 100 AND b != 'Super' ORDER BY b
Следует отметить, что при выводе полей таблиц с помощью SQL можно производить некоторые вычисления. В этих случаях использование локальных имен бывает просто необходимым. Допустим, в счетах у нас хранятся суммы без НДС. В таком случае мы можем ввести сразу 2 столбца с суммами - с той, что хранится таблице и в виде вычисленного значения.
SELECT BILL_DATE, BILL_SUMM, BILL_SUMM * 1.18 as WITH_TAX FROM BILL ORDER BY WITH_TAX
В таком случае названием столбца с вычисленным значением будет "WITH_TAX" (см. рис. 21.3).
Рис. 21.3. SQL-запрос с вычислениями
При подобном переименовании столбцов следует быть внимательным, поскольку использование идентификаторов, совпадающих с ключевыми словами SQL, приведет к ошибке. Кроме того, следует избегать использования символов кириллицы, поскольку они не поддерживаются многими СУБД в данном контексте.
Если написание арифметического выражения приведет к изменению выводимых данных в записях столбца, то использование агрегирующих (агрегатных) функций поможет выполнить действия над всеми записями, удовлетворяющими указанным в запросе условиям. Пожалуй, чаще всего используется функция COUNT, позволяющая узнать количество записей. Например, чтобы узнать, сколько записей хранится в таблице счетов, достаточно написать следующий запрос:
SELECT COUNT(*) FROM bill
Результатом выполнения этого запроса будет число записей в таблице счетов. Если бы мы хотели узнать число записей, сделанных, скажем, до 1января, то могли бы написать такое выражение:
SELECT COUNT(*) FROM bill WHERE BILL_DATE < '01.01.2006'
Помимо COUNT, в стандартном SQL предусмотрено еще 4 агрегирующих функции - для вычисления суммы (SUM), максимального (SUM) и минимального (MIN) значений столбцов, а так же среднего арифметического (AVG). Результатом выполнения запроса по всем этим функциям для таблицы счетов будет таблица, состоящая из 4 столбцов, в каждом из которых будет находиться результат вычислений (рис. 21.4).
Рис. 21.4. Результат выполнения запроса с агрегирующими функциями
Что касается заданий ограничений на обрабатываемые данные, ровно, как и на выводимые при помощи "обычного" запроса строки, то помимо реляционных операторов сравнения - больше (>), меньше (<), равно (=), не равно (!= или <>), больше или равно (>=), меньше или равно (<=), - можно использовать следующие операторы:
- BETWEEN - задает диапазон значений, для которого выражение принимает истину;
- IN - проверяет, входит ли заданное значение, предшествующее ключевому слову "IN" (например, значение столбца) в указанный в скобках список;
- LIKE - проверяет, соответствует ли данное символьное значение строке с указанной маской (только для символьных данных);
В качестве примеров можно привести следующие варианты SQL-запросов:
SELECT BILL_SUMM FROM bill WHERE BILL_SUMM BETWEEN 100 and 200; /* Вывести счета с суммами от 100 до 200 */
SELECT BILL_SUMM, BILL_CUST FROM bill WHERE BILL_SUMM IN (100,150,200) /* Вывести счета с суммами, равными 100, 150 и 200 */
SELECT CUST_NAME FROM customer WHERE CUST_NAME LIKE 'OOO "Gamma%' /* Вывести клиентов, имя которых начнается с "ООО "Gamma" */
SELECT CUST_NAME FROM customer WHERE CUST_NAME LIKE 'OOO "_____"' /* Вывести клиентов, имя которых начнается с "ООО "" и имеет 5 символов в кавычках */
SELECT CUST_NAME FROM customer WHERE CUST_NAME LIKE '_%' /* Вывести клиентов, имя которых состоит хотя бы из 1 символа */
Что касается порядка вывода результатов, то помимо сортировки, еще одним способом повлиять на вывод результатов запроса является использование GROUP BY. Эта конструкция определяет порядок группировки полей при выводе. При этом следует учитывать, что должны быть указаны все поля, перечисленные в запросе:
SELECT BILL_SUMM, BILL_DATE FROM bill GROUP BY BILL_DATE, BILL_SUMM
В данном случае данные будут сгруппированы по датам. Впрочем, это не самый удачный пример, поскольку такого же результата можно было бы добиться простой сортировкой по полю BILL_DATE. Поэтому рассмотрим более сложный запрос, который будет выводить общие суммы всех счетов для каждого из клиентов. Для этого нам понадобится агрегирующая функция SUM:
SELECT BILL_CUST, SUM(BILL_SUMM) FROM BILL GROUP BY BILL_CUST
Наконец, усложним запрос, сделав результаты его работы более наглядными. Для этого нам потребуется связать таблицу счетов с таблицей клиентов, а заодно произвести сортировку по итоговому результату. Таким образом, мы получим следующий запрос:
SELECT CUST_NAME, SUM(BILL_SUMM) as TOTAL FROM BILL JOIN CUSTOMER ON CUST_ID=BILL_CUST GROUP BY BILL_CUST, CUST_NAME ORDER BY TOTAL DESC
Результатом его выполнения будет таблица, содержащая в первом столбце имена клиентов, а во втором - суммы всех их счетов, по которой и была произведена сортировка (рис. 21.5).
Рис. 21.5. Запрос с группировкой, агрегацией и упорядочиванием по вычисляемому полю
Все, что было на данный момент рассказано про команду SELECT, на самом деле, является лишь верхушкой айсберга ее возможностей. В то же время, даже такое беглое знакомство дает понять, что без знания SQL написать достаточно объемное приложение для БД крайне затруднительно. В то же время, каждая СУБД, как уже было отмечено, имеет свои тонкости в реализации языка, и команда SELECT, по причине своих огромных возможностей, является одним из наиболее частых претендентов на "модернизацию". По этой причине для дальнейшего изучения как команды SELECT, так и прочих возможностей SQL, следует использовать документацию к используемой СУБД.
Некоторые другие команды SQL
Помимо уже рассмотренных запросов на выборку данных при помощи команды SELECT, рассмотрим некоторые другие команды языка манипулирования данными. В частности, это команды INSERT (вставить), UPDATE (обновить) и DELETE (удалить).
Начнем со вставки - команды INSERT, которая добавляет запись с указанными значениями полей в конец таблицы. В упрощенной форме основной синтаксис этой команды можно представить следующим образом:
INSERT INTO таблица [(поля)] VALUES (значения)
В простейшем случае, когда надо заполнить все (или почти все) поля новой записи, достаточно написать подобное выражение:
INSERT INTO regions VALUES (77,'Москва')
В том же случае, если требуется указать значения только для определенных полей, то их предварительно перечисляют после имени таблицы:
INSERT INTO bill (BILL_CUST, BILL_SUMM, BILL_DATE) VALUES (1, 100, '01.01.2006')
В принципе, то, что выполняется при помощи команды SQL INSERT, похоже на то, что делает метод InsertRecord. Однако SQL предоставляет дополнительные возможности, в частности, вставку целой группы записей:
INSERT INTO bill (BILL_CUST, BILL_SUMM, BILL_DATE) VALUES (1, 100, '01.01.2006'), (1,200,'01.02.2006'), (1,150,'01.03.2006')
Еще более широкие возможности открываются при помощи комбинирования команды INSERT с командой SELECT, причем вставку можно производить как в ту же самую таблицу, так и в любую другую. Например, если нам надо в некую таблицу bill2 внести все записи из таблицы bill, имеющие суммы свыше 300, мы можем написать следующий запрос
INSERT INTO bill2 SELECT * FROM bill WHERE BILL_SUMM>300
Здесь предполагается, что структура таблиц bill и bill2 полностью идентична. Если же это было бы не так, то можно внести только часть полей (при условии, что типы полей будут совпадать). Например, если в таблице bill2 было бы поле для суммы, то для вставки всех подходящих сумм можно написать следующий запрос:
INSERT INTO bill2 (B2_SUMM) SELECT BILL_SUMM FROM bill WHERE BILL_SUMM>300
ПРИМЕЧАНИЕ
При помощи INSERT-SELECT можно очень просто копировать данные из одной таблицы в другую. В то же время, если надо просто вставить данные из подготовленного текстового файла, то ряд СУБД предлагает более удобные и быстрые команды, например, LOAD DATA INFILE в MySQL.
Другая команда SQL, применяемая для модификации данных - это UPDATE. Она позволяет изменять значения в столбцах данных таблицы. В общих чертах ее синтаксис можно представить следующим образом:
UPDATE таблица SET имя_поля = значение [WHERE условие]
В простейшем случае, когда надо изменить все записи в колонке таблицы, можно написать следующее выражение:
UPDATE bill SET BILL_DATE = '01.01.2006'
Можно так же указывать не абсолютные, а вычисляемые значения:
UPDATE bill SET BILL_SUMM = BILL_SUMM * 2
Но чаще всего все-таки указывают условия, по которым следует находить те записи, которые следует изменить. Например, чтобы пересчитать все счета, выставленные до 31 января 2005 года, можно написать следующее выражение:
UPDATE bill SET BILL_SUMM = BILL_SUMM + 10 WHERE BILL_DATE<'31.12.2005'
Наконец, когда требуется изменить лишь какую-то определенную запись, что в качестве условия достаточно указать уникальное поле. Например, для таблицы счетов это поле BILL_ID, по которому можно однозначно идентифицировать какую-либо запись в таблице:
UPDATE bill SET BILL_SUMM = 1000 WHERE BILL_ID = 10
ВНИМАНИЕ
Следует учитывать, что если предусмотренные в VCL методы для работы с БД, как правило, влияют лишь на одну запись (т.е. на ту, что выделена курсором), то в SQL изменению подвержены все записи указанной таблицы.
Наконец, остается рассмотреть последнее часто применяемое действие - удаление записей из таблицы. В SQL для этих целей используют команду DELETE, определенную следующим образом:
DELETE FROM таблица [WHERE условие]
Подобно команде UPDATE, если не указано условие, то удалению будут подвержены все данные в таблице. Например, чтобы удалить все записи в таблице счетов, достаточно написать:
DELETE FROM bill
В более вероятном случае требуется удалить лишь единичные записи. Например, удалению могут быть подвержены счета, выписанные до 2006 года, или же какой-либо определенный (ошибочный) счет:
DELETE FROM bill WHERE BILL_DATE<'01.01.2006'
DELETE FROM bill WHERE BILL_ID = 15
Таким образом, на текущий момент мы рассмотрели все основные команды SQL, относящиеся к языку манипулирования данными - SELECT, INSERT, UPDATE и DELETE. Во многих СУБД помимо этих команд имеется ряд дополнительных, например, TRUNCATE (аналог DELETE для всей таблицы) или REPLACE (вариация на тему вставки). Поэтому остается еще раз посоветовать ознакомиться с документацией на используемую СУБД.
То же самое касается и языка определения данных, представленного вариациями команд CREATE, DROP и ALTER: у каждой СУБД имеется собственный набор типов данных и свои тонкости работы с таблицами, индексами и т.д. В принципе, работая с "родными" для Delphi типами СУБД, вполне можно довольствоваться теми возможностями, что предоставляет утилита Database Desktop. Многие другие СУБД так же располагают собственными средствами, упрощающими процесс создания таблиц, в качестве примера можно привести широко распространенную утилиту phpMyAdmin, обеспечивающую управление СУБД MySQL через веб-интерфейс.
Компонент запроса Query
До текущего момента мы рассматривали язык SQL как таковой, оставляя вопросы его взаимодействия с Delphi. Переходя к практическим вопросам использования этого языка в приложениях, рассмотрим компонент Query. Компонент Query представляет собой компонент набора данных, записи которого формируются в результате выполнения SQL-запроса. При этом текст запроса также содержится в этом компоненте в виде свойства SQL типа TStrings.
В целом, с точки зрения использования в приложении, компонент Query похож на другой компонент BDE - Table. Подобно Table, он так же может выступать в качестве источника данных. Однако благодаря тому, что практически все параметры, относящиеся к выборке данных, определяются в тексте запроса, то среди свойств этого компонента, связывающих его с БД, имеется лишь DatabaseName.
Еще одним важным отличием компонента Query от Table является отсутствие у Query свойства ReadOnly. Дело в том, что компонент Query по своей сути обычно предоставляет данные, доступные только для чтения, т.е. связь получается односторонняя. Этот факт следует учитывать, т.к. в ряде случаев при установке взаимодействий между компонентами бывает необходимым ссылаться на источники данных, поддерживающих непосредственную правку. В то же время, если выполняется ряд определенных условий, в частности, запрос обращается только к одной таблице, сама таблица поддерживает запись, а свойство RecuestLive компонента Query установлено в истину, то к такому запросу можно будет обращаться точно так же, как к таблице.
В то же время, подобные ограничение вовсе не говорит о том, что при помощи Query сложно изменять данные - просто для этих целей понадобится создавать соответствующий SQL-запрос (например, с командой UPDATE), а не пытаться использовать свойства объектов Filed. Кроме того, к Query неприменимы такие методы, как FindFirst, FindLast и т.д.
В качестве примера простейшего приложения, использующего этот компонент, рассмотрим уже упоминавшийся SQL Explorer LE. В каталоге Tools\SQLE_LE, помимо самого приложения, находится его исходный код. Не вдаваясь в подробности реализации, отметим лишь, что в приложении задействовано 3 компонента, связанных с доступом к данным - это Database, Query и DataSource. Еще один невизуальный компонент - диалог открытия файла используется для выбора каталога с БД. Весь написанный код этого приложения приведен в листинге 21.1.
Листинг 21.1. Исходный код SQLE LE
procedure TMainFrm.OpenBtnClick(Sender: TObject);
begin
if not OpenDlg.Execute then exit;
MainDB.Connected:=false;
MainDB.Params.Clear;
MainDB.Params.Add('path='+ExtractFilePath(OpenDlg.FileName));
MainDB.Connected:=true;
RunBtn.Enabled:=true;
end;
procedure TMainFrm.RunBtnClick(Sender: TObject);
begin
Query1.Close;
Query1.SQL.Text:=Memo1.Text;
if pos('select',lowercase(Memo1.Text))=0 then Query1.ExecSQL else
Query1.Open;
end;
Здесь кнопка открытия БД устанавливает путь к базе данных (подразумевается, что используется СУБД Paradox), после чего делает соединение активным, а кнопку выполнения запроса - доступной. Код для кнопки выполнения запроса нуждается в некотором пояснении: дело в том, что у компонента Query имеется 2 способа выполнения запроса - при помощи методов Open и ExecSQL. Принципиальная разница между ними состоит в том, что метод Open используется для запросов, производящих выборку данных (т.е. SELECT), в остальных же случаях предпочтительнее использовать метод ExecSQL.
Особенности работы с запросами
При рассмотрении компонента Query мы уже отметили ряд различий в подходах к использованию этого компонента по сравнению с Table. Однако этим различия в приложениях, основанных на SQL, не ограничиваются. Основным преимуществом запросов на SQL является то, что они позволяют минимизировать объем данных, которыми обмениваются СУБД и приложение. Например, когда из таблицы требуется отобрать какую-то часть записей, то в случае использования фильтрации или иных "обычных" методов, приложение запрашивает у СУБД всю таблицу. В том же случае, когда используется запрос, приложение получает лишь то, что ему требуется. Это существенным образом сказывается на быстродействии, особенно если обрабатываются большие объемы данных и при передаче информации по сети.
Для иллюстрации возможностей SQL и компонента Query модифицируем приложение "База 1.0" таким образом, чтобы оно использовало данные технологии. В частности, SQL может нам понадобиться для таких вещей, как вывод счетов для клиента, подсчет суммы счетов, внесение новых счетов и клиентов или редактирование имеющихся.
При разработке нового варианта приложения заменять без оглядки все компоненты Table на Query не представляется рациональным. Например, CustTbl, представляющий таблицу клиентов, используется исключительно для вывода полного списка клиентов и для манипуляций над ним же. В то же время, компонент BillTbl было бы рационально заменить на компонент-запрос, поскольку по счетам будет производиться выборка. Кроме того, если рассматривать приближенный к практике случай, то таблица счетов со временем может стать весьма и весьма объемной, что в случае работы по сети может сказаться на производительности. Поэтому заменим этот компонент запросом Query и назовем его BillQry. При этом свойство DatabaseName у него так же будет MainDB. Кроме того, поскольку этот запрос у нас используется в качестве источника данных для таблицы DBGrid, в которую мы позволяем вносить правку, то свойство RecuestLive следует установить в истину. С учетом того, что в данном запросе используется только одна таблица (bill), это позволит нам обращаться с ним так же легко, как с обычной таблицей. После этого останется изменить в BillDS значение свойства DataSet - вместо ссылки на отсутствующий теперь компонент BillTbl укажем для него BillQry.
Наконец, нам понадобится еще один компонент Query - для выполнения вспомогательных задач - например, для добавления новых записей в таблицы. Назовем его StdQry и установим свойство DatabaseName в MainDB. Поскольку этот запрос не будет непосредственно выводить информацию, то добавляеть ему в пару компонент DataSource не требуется.
Теперь приступим к изменению в программном коде. Начнем с функции вывода баланса - обработчика события Click для кнопки BalanceBtn. Как раз в этом случае мы задействуем оба запроса - один для того, чтобы отобразить все нужные счета (BillQry), а другой - для того, чтобы вычислить сумму (StdQry). Вариант этой функции с использованием таблиц был приведен в листинге 20.4, теперь же мы рассмотрим новый вариант, с использованием SQL-запросов (листинг 21.2).
Листинг 21.2. Вывод счетов и вычисление суммы с использованием SQL
procedure TMainFrm.BalanceBtnClick(Sender: TObject);
begin
Data.BillQry.Close;
Data.BillQry.SQL.Text:='SELECT * FROM bill WHERE BILL_CUST=' +Data.CustTbl.FieldByName('CUST_ID').AsString;
Data.BillQry.Open;
Data.StdQry.Close;
Data.StdQry.SQL.Text:='SELECT SUM(BILL_SUMM) AS TOTAL FROM bill WHERE ' +'BILL_CUST='+Data.CustTbl.FieldByName('CUST_ID').AsString;;
Data.StdQry.Open;
BillsFrm.Caption:=Data.CustTbl.FieldByName('CUST_NAME').AsString+': ' +Data.StdQry.FieldByName('TOTAL').AsString;
BillsFrm.ShowModal;
end;
Очевидно, что вычислительные задачи в новом варианте, при помощи SQL мы полностью переложили на саму СУБД. Кроме того, объем информации, полученный приложением, заметно уменьшился, поскольку выборка нужных значений производится на стороне СУБД, и приложению будут переданы только те данные, которые реально необходимы.
Теперь перепишем код для кнопок "Новый счет" и "Новый клиент". В данном случае, поскольку производить выборки не требуется, нам будет достаточно только одного запроса - StdQry, как это показано в листинге 21.3.
Листинг 21.3. Создание новых записей в таблицах bill и customer при помощи SQL
procedure TMainFrm.NewBillBtnClick(Sender: TObject);
begin
with BillFrm do begin
Caption:='Счет для '+Data.CustTbl.FieldByName('CUST_NAME').AsString;
ShowModal;
if ModalResult<>mrOk then exit;
Data.StdQry.Close;
Data.StdQry.SQL.Text:='INSERT INTO bill(BILL_CUST, BILL_SUMM, BILL_DATE)' +' VALUES ('+Data.CustTbl.FieldByName('CUST_ID').AsString+', '+
SummEd.Text+', '''+DateToStr(DatePick.Date)+''')';
Data.StdQry.ExecSQL;
end;
end;
procedure TMainFrm.NewCustBtnClick(Sender: TObject);
begin
with CustFrm do begin
ShowModal;
if ModalResult<>mrOk then exit;
Data.StdQry.Close;
Data.StdQry.SQL.Text:='INSERT INTO customer (CUST_NAME, CUST_ADDRESS) ' +'VALUES ('''+NameEd.Text+''', '''+AddrEd.Text+''')';
Data.StdQry.ExecSQL;
Data.CustTbl.Refresh;
end;
end;
Здесь следует отметить следующий момент: после выполнения запроса на добавление клиента для компонента, представляющего таблицу клиентов (CustTbl), вызывается метод Refresh. Если этого не сделать, то, хотя данные и будут добавлены в таблицу физически, обновления таблицы на экране не произойдет. В то же время для добавления счетов подобной операции делать не требуется, поскольку для того, чтобы просмотреть список счетов, пользователю в любом случае придется нажимать на кнопку "Баланс", процедура обработки которой будет всякий раз составлять и вызывать новый запрос.
Что касается изменения учетной записи клиента, то в данном случае, в принципе, можно ничего не делать, поскольку мы оставили таблицу. С другой стороны, вполне можно использовать для этих целей язык SQL и компонент запроса StdQry. Для этого достаточно заменить последние 4 строчки кода в обработчике двойного щелчка мышью у компонента CustGrd:
//старый вариант
Data.CustTbl.Edit;
Data.CustTbl.FieldByName('CUST_NAME').AsString:=NameEd.Text;
Data.CustTbl.FieldByName('CUST_ADDRESS').AsString:=AddrEd.Text;
Data.CustTbl.Post;
//новый вариант
Data.StdQry.Close;
Data.StdQry.SQL.Text:='UPDATE customer SET CUST_NAME='''+NameEd.Text +''', CUST_ADDRESS='''+AddrEd.Text+'''';
Data.StdQry.ExecSQL;
В то же время, подобный код в форме BillsFrm, несмотря на то, что в данном случае источник данных изменился с таблицы на запрос, можно не менять - как раз в этом случае и пригодится установленное в истину свойство запроса RequestAlive.
С полным кодом переработанного приложения можно ознакомиться в каталоге Demo\Part4\DBAppSQL.
