по значениям поля набора данных 9 глава

WHERE C_Date BETWEEN "01.06.2002" AND "03.06.2002" GROUP BY C_Date

Для каждой даты из указанного периода выводится количество записей, в кото­рых она встречается. Если не выполнить группирование, то в набор данных по­падут все записи, а при использовании группирования все даты для полученно­го набора данных уникальны. Функция count выводит для каждой группы (сформированной по полю даты) число записей в группе. Полученный набор данных может иметь следующий вид:

C_Date COUNT(C_Date)

01.06.02 20

02.06.02 17

03.06.02 8

В этом примере поле даты c_Date использовано в инструкции select трижды, в общем случае можно использовать различающиеся поля.

Совместно с операндом group by можно использовать операнд having, с помо­щью которого задаются дополнительные условия группирования записей.

Рассмотрим пример запроса:

SELECT C_Date, COUNT(C_Date) FROM Cards GROUP BY C_Date HAVING COUNT(C_Date) > 50

Здесь отбираются данные для дат, когда движение товара было интенсивным — общее число записей в соответствующей группе превышало 50. Так как не ука­зан ограничивающий период времени, при отборе в набор данных будут учиты­ваться записи таблицы cards с любыми датами.

Сортировка записей

Как уже говорилось, сортировка представляет собой упорядочивание записей по возрастанию или убыванию значений полей. Список полей, по которым выпол­няется сортировка, указывается в операнде order by. Порядок полей в этом операнде определяет порядок сортировки: сначала записи упорядочиваются по значениям поля, указанного в этом списке первым, затем записи, имеющие одинаковое значение первого поля, упорядочиваются по второму полю и т. д.

Поля в списке обозначаются именами или номерами, которые соответствуют номерам в списке полей после слова select.

Глава 8. Реляционный доступ к данным с помощью BDE

255

По умолчанию сортировка происходит в порядке возрастания значений полей. Для указания обратного порядка сортировки по какому-либо полю нужно ука­зать после имени этого поля описатель desc.

СЗамечание^

В отличие от набора данных Table, средствами языка SQL можно выполнять сорти­ровку для набора данных Query и по неиндексированным полям. Однако по индекси­рованным полям таблицы сортировка выполняется быстрее. При этом состав полей индекса должен соответствовать списку полей, указанных в операнде order by.

Пример запроса на сортировку записей:

SELECT * FROM Personnel.db ORDER BY Name

Сортировка записей задана по полю Name. Полученный набор данных может иметь вид:

 

Code	Name	Position	Salary
	Васин Н.Е.	Водитель
	Иванов P.O.	Директор
	Кузнецов П.А.	Секретарь
	Петров А.П.	Менеджер
	Попов А.Л.	Водитель
	Семенова И. И.	Менеджер

Еще один пример запроса на сортировку — на этот раз по двум полям:

SELECT Name, Position, Salary

FROM Personnel.db

ORDER BY Position, Salary DESC ИЛИ

SELECT Name, Position, Salary FROM Personnel.db ORDER BY 2, 3 DESC

В набор данных входят поля Name, Position и salary всех записей. Записи от­сортированы ПО ПОЛЯМ Position И Salary, При ЭТОМ ПО ПОЛЮ Salary упорядо­чивание выполняется в порядке убывания значений. Полученный набор данных будет таким:

 

Name		Position	Salary
Васин Н.Е.		Водитель
Попов А.Л.		Водитель
Иванов P.O.		Директор
Петров А.П.		Менеджер
Семенова И.	И.	Менеджер
Кузнецов П.	А.	Секретарь

256

Часть II. Технологии доступа к данным

Если по полям Position и Salary построен индекс, то операции с набором данных будут выполняться быстрее.

При разработке приложения управление сортировкой осуществляется посредст­вом различных элементов формы, например, кнопок и переключателей.

Рассмотрим следующий пример. Пользователь управляет сортировкой с помо­щью двух групп переключателей: в первой задается вид, во второй — направле­ние сортировки. Сортировка выполняется после нажатия кнопки Отсортировать

(btnsort). На рис. 8.1 показан вид формы на этапе проектирования.

Ниже приводится код обработчика события нажатия кнопки btnSort.

procedure TForml.btnSortClick(Sender: TObject);

var s: string;

begin

Queryl.Close;

Queryl.SQL.Clear;

Queryl.SQL.Add('SELECT * FROM Personnel.db');

case RadioGroup2.Itemlndex of

0: s: =

1: s:= 'DESC;

end; case RadioGroupl.Itemlndex of

0: s:= 'ORDER BY P_Name ' + s;

1: s:= 'ORDER BY P_Birthday ' + s;

2: s: = 'ORDER BY P_Position ' + s + ', P_Salary ' + s;

3: s: =

end;

Глава 8. Реляционный доступ к данным с помощью BDE

257

Queryl.SQL.Add(s); Queryl.Open; end;

При нажатии кнопки btnsort для набора данных Queryl происходят подготовка и выполнение SQL-запроса. Текст запроса формируется в зависимости от со­стояния переключателей, управляющих сортировкой.

Когда в SQL-запросе отсутствует параметр order, по умолчанию записи упоря­дочиваются по первому полю. Поэтому в рассматриваемом примере отсутствие параметра сортировки и сортировка по полю Name приводят к одинаковому ре­зультату.

Соединение таблиц

В набор данных можно включать поля из разных таблиц, подобное включение называется соединением (связыванием) таблиц. Соединение таблиц может быть внутренним и внешним.

Внутреннее соединение представляет собой простейший случай, когда после слова select перечисляются поля разных таблиц. Например:

SELECT P.Name, P.Position, P.Salary, A.Birthday, A.Note FROM Personnel.db P, Advanced.db A

Таблицы Personnel и Advanced содержат основные и дополнительные сведения о сотрудниках организации. Таблицы связаны отношением "один-к-одному", т. е. каждой записи первой таблицы соответствует одна запись второй таблицы. Результирующий набор данных является объединением полей двух таблиц так, будто дополнительные данные соединены с основными. В таблицах могут быть выбраны не все поля, что не меняет принципа соединения. Результирующий набор будет иметь вид:

Исп. срок до 31.6.2002

Note

Name	Position	Salary	Birthay
Иванов P.O.	Директор		29.10.51
Петров А.П.	Менеджер		3.4.62
Семенова И.И.	Менеджер		12.10.64
Кузнецов П.А.	Секретарь		7.11.81
Васин Н.Е.	Водитель		20.5.78
Попов А.Л.	Водитель		3.2.75

Использование внутреннего соединения применимо не всегда. Например, при использовании внутреннего соединения таблиц с отношением "один-ко-многим" результат выполнения запроса может содержать избыточную информацию.

Рассмотрим в качестве примера запрос, в котором внутреннее соединение таб­лиц приводит к избыточной информации в результирующей выборке:

SELECT S.S_Name, C.C_Date, C.C_Move, S.S_Price FROM Store.db S, Cards.db С

258

Часть II. Технологии доступа к данным

Набор данных включает поля названия (s_Name) и цены (c_Date) товара из таблицы store склада, а также поля (c_Date) даты и количества (c_Move) товара из таблицы cards движения товара. Число записей набора данных является произведением числа записей в таблицах склада и движения товара. Результирующий набор данных может иметь вид:

 

S Name	C_Date	С Move	S Price
Морковь	12.05.02		7,5
Морковь	12.05.02		7,5
Морковь	13.05.02	-7,50	7,5
Морковь	14.05.02	-30	7,5
Морковь	15.05.02		7,5
Морковь	17.05.02		7,5
Морковь	18.05.02	-120	7,5
Морковь	20.05.02	-10	7,5
Морковь	21.05.02		7,5
Яблоки	12.05.02
Яблоки	12.05.02
Яблоки	13.05.02	-7,50

Помидоры 20.05.02 -10 40 Помидоры 21.05.02 250 40

Результирующая выборка содержит избыточную информацию и большое число записей, что не помогает, а, наоборот, мешает пользователю. Поэтому при внут­реннем соединении таблиц, связанных соотношением "один-ко-многим", обыч­но применяются критерии отбора, ограничивающие состав записей. Рассмотрим теперь такой запрос:

SELECT S.S_Name, C.C_Date, C.C_Move, S.S_Price FROM Store.db S, Cards.db С WHERE C.C_Code = S.S_Code

В отличие от предыдущего примера, число записей набора данных равно числу записей таблицы движения товара, т. к. отбираются записи, для которых совпадают значения полей кода. Результирующий набор данных будет таким:

 

S Name	C_Date	С Move	S Price
Морковь	12.05.02		7,5
Яблоки	12.05.02
Морковь	13.05.02	-7,50	7,5
Морковь	14.05.02	-30	7,5
Морковь	15.05.02		7,5
Яблоки	17.05.02
Яблоки	18.05.02	-120
Морковь	20.05.02	-10	7,5
Помидоры	21.05.02

Глава 8. Реляционный доступ к данным с помощью BDE

259

При внутреннем соединении все таблицы, поля которых указываются в SQL-запросе, являются равноправными.

При внешнем соединении таблиц можно указать, какая из таблиц будет главной, а какая — подчиненной. При использовании внешнего соединения операнд from имеет следующий формат:

FROM <Таблица1> [<Вид соединения>] JOIN <Таблица2> ON <Условия отбора>

Критерий отбора после слова on, как и ранее, задает условие включения запи­сей в набор данных, связываемые таблицы указываются слева и справа от слова join. Какая из двух таблиц будет главной, определяет вид соединения, который может иметь одно из следующих значений:

□ left (главная таблица указана слева);

□ right (главная таблица указана справа) — по умолчанию. Вот запрос, в котором используется внешнее связывание таблиц:

SELECT S.S_Name, C.C_Date, C.C_Move, S.S_Price FROM Store.db S LEFT JOIN Cards.db С ON C.C_Code = S.S_Code

Как и в предыдущем примере, связываются таблицы склада store и движения товара cards. Главной является таблица store.

Модификация записей

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

Для модификации записей используются инструкции update, insert и delete, которые соответствующим образом изменяют записи и возвращают в качестве результата набор данных, состоящий из модифицированных записей, удовлетво­ряющих критерию отбора.

Редактирование записей

Редактирование записей представляет собой изменение значений полей в группе записей. Оно выполняется инструкцией update следующего формата:

UPDATE <Имя таблицы>

SET <Имя поля1> = <Выражение1>,

<Имя поляЫ> = <Выражение]Я> [WHERE <Условия отбора>];

260

Часть II. Технологии доступа к данным

После выполнения инструкции update для всех записей, удовлетворяющих ус­ловию отбора, изменяются значения полей, имя поля указывает модифицируе­мое поле всей совокупности записей, а выражение определяет значение, которое будет присвоено этому полю. Например:

UPDATE Personnel

SET Salary = Salary + 200 WHERE Salary < 1500

Если сотрудник имеет оклад менее 1500 (рублей), то оклад увеличивается на 200 (рублей).

Критерий отбора, указанный в операнде where, не отличается от критерия, зада­ваемого в инструкции select. Если он не задан, то изменяются значения всех указанных полей.

Вот соответствующий запрос:

UPDATE Store

SET S_Price = S_Price * 1.28;

После его выполнения цена всех товаров увеличивается на 28%.

В одной инструкции update можно изменить значения нескольких полей, в этом случае для каждого из них указывается соответствующее значение. Например:

UPDATE Store

SET S_Quantity = 0, S_Note = "Обнулено" WHERE S_Quantify BETWEEN - 0.5 AND 0.5

Для всех записей, у которых значение поля s_Quantity находится в диапазоне -0,5.. 0,5, этому полю присваивается значение о, а в поле s_Note примеча­ния записывается слово обнулено.

Вставка записей

Вставка записей осуществляется с помощью инструкции insert, которая позволяет добавлять к таблицам одну или несколько записей. При добавлении одной записи инструкция insert имеет формат:

INSERT INTO <Имя таблицы> [(<Список полей>)] VALUES (<Список значений>);

В результате выполнения этой инструкции к таблице, имя которой указано по­сле слова into, добавляется одна запись. Для добавленной записи заполняются поля, перечисленные в списке. Значения полей берутся из списка, расположен­ного после слова values. Список полей и список значений должны соответст­вовать друг другу по числу элементов и по типу. При присваивании значений для первого поля берется первое значение, для второго — второе и т. д. При этом порядок полей и значений может отличаться от порядка полей в таблице.

Глава 8. Реляционный доступ к данным с помощью BDE

261

Пример запроса на добавление записи:

INSERT INTO Store

(S_Name, S_Price, S_Quantity) VALUES ("Торшер", 499.9, 10);

Здесь в таблицу склада store добавляется новая запись, в которой присваиваются значения полям названия товара, его цены и количества.

Список полей в инструкции insert может отсутствовать, в этом случае необходимо указать значения для всех полей таблицы. Порядок и тип этих значений должны соответствовать порядку и типу полей таблицы.

При добавлении к таблице сразу нескольких записей инструкция insert имеет формат:

INSERT INTO <Имя таблицы> (<Список полей>) Инструкция SELECT;

В данном случае значения полей новых записей определяются через значения полей записей, отобранных с помощью инструкции select. Число добавленных записей равно числу отобранных записей. Список значений полей, возвращае­мых инструкцией select, должен соответствовать списку инструкции insert по числу и типу полей.

С помощью вставки группы записей можно скопировать данные из одной таб­лицы в другую, например, при резервном копировании или архивировании за­писей. При этом обе таблицы обычно имеют одинаковую структуру или их структуры частично совпадают.

Вот запрос на добавление нескольких записей:

INSERT INTO CardsArchives (Code, Move, Date) SELECT C_Code, C_Move, C_Date FROM Cards WHERE C_Date BETWEEN 1.1.02 AND 31.12.02

В архивную таблицу CardsArchives добавляется группа записей из таблицы Cards движения товара. Для записей, сделанных в 2002 году, в архив копируются код товара, приход или расход и дата.

Если необходимо выполнить не копирование, а перемещение записей в архив, то после успешного копирования можно удалить записи в исходной таблице с

ПОМОЩЬЮ ИНСТРУКЦИИ DELETE.

СЗамечание^

Перемещение записей целесообразно оформлять в рамках транзакции, чтобы обеспечить надежность выполнения операции и сохранение целостности БД.

262

Часть II. Технологии доступа к данным

Удаление записей

Для удаления группы записей используется инструкция delete, имеющая формат:

DELETE FROM <Имя таблицы>

[WHERE <Условия отбора>];

В результате выполнения этой инструкции из таблицы, имя которой указано по­сле слова from, удаляются все записи, которые удовлетворяют критерию отбора.

(_____ ЗамечаниеJ

Если критерий отбора не задан, то из таблицы будут удалены все записи.

Вот соответствующий запрос:

DELETE FROM Store

WHERE S_Quantity = 0

Из таблицы склада store удаляются все записи о товаре, которого нет на складе. Отметим, что если с записями этой таблицы связаны записи другой таблицы, на­пример, движения товара, то может потребоваться их предварительное удаление, что связано с действием бизнес-правил и налагаемыми ими ограничениями.

Статический и динамический запросы

Как отмечалось, в зависимости от способа формирования SQL-запрос может быть

□ статическим; □ динамическим.

Текст статического SQL-запроса формируется при разработке приложения и в процессе выполнения приложения не может быть изменен. Такой запрос обыч­но используется в случаях, когда код запроса заранее известен и во время рабо­ты приложения не требует модификации.

Динамический SQL-запрос формируется или изменяется при выполнении при­ложения. Такой запрос обычно применяется, если его текст зависит от действий пользователя, например, при управлении сортировкой набора данных, когда в тексте запроса изменяются названия полей и добавляется или исключается описатель desc.

Рассмотрим в качестве примера процедуру, в которой осуществляется формиро­вание динамического запроса:

procedure TForml.btnSortClick(Sender: TObject);

var str: string;

begin

Queryl.Close;

Queryl.SQL.Clear;

Queryl.SQL.Add('SELECT * FROM Personnel.db');

Глава 8. Реляционный доступ к данным с помощью BDE

263

case RadioGroupl.Itemlndex of

0: str:= 'ORDER BY Name ';

1: str:= 'ORDER BY BirthDay '; end; case RadioGroup2.Itemlndex of

0:;

1: str: = str + ' DESC;

end; Queryl.SQL.Add(str); Queryl.Open; end;

При НажаТИИ КНОПКИ btnSort СПИСОК СОТРУДНИКОВ Сортируется ПО ПОЛЯМ Name ИЛИ

BirthDay. Пользователь управляет выбором поля с помощью группы переключа­телей RadioGroupl. В группе RadioGroup2 выбирается порядок сортировки.

Для настройки статического SQL-запроса во время выполнения приложения в его тексте можно использовать параметры. Параметр это специальная перемен­ная, перед именем которой ставится двоеточие в тексте запроса. Двоеточие не является частью имени параметра и ставится только в тексте запроса. Как и для обычных переменных программы, в процессе выполнения приложения вместо параметра подставляется его значение.

Параметры удобно использовать для передачи в текст SQL-запроса внешних значений. Например, если необходимо вывести фамилии сотрудников с окладом не менее указанного в редакторе Editi, то организовать формирование и вы­полнение динамического запроса можно так:

procedure TForml.ButtonlClick(Sender: TObject);

begin

Queryl.Close;

Queryl.SQL.Clear;

Queryl.SQL.Add('SELECT Name, Position, Salary');

Queryl.SQL.Add('FROM Personnel.db');

Queryl.SQL.Add('WHERE Salary >= ' + Editl.Text);

Queryl.Open;

end;

С помощью параметров эту задачу можно решить проще, например, через вклю­чение в текст запроса параметра prmsaiary:

SELECT Name, Position, Salary FROM Personnel.db WHERE Salary >=:prmSalary

Система Delphi автоматически учитывает все указанные в SQL-запросе парамет­ры в специальном списке параметров, являющемся для набора данных Query

264

Часть II. Технологии доступа к данным

значением свойства Params типа TParams, представляющим собой массив. Это свойство позволяет получить доступ к каждому параметру как при разработке, так и при выполнении приложения. Чтобы обратиться к параметру во время выполнения приложения, следует указать его номер (индекс) в списке парамет­ров. Например, для обращения ко второму параметру указывается Params[i]. На этапе разработки приложения можно вызвать Редактор параметров (рис. 8.2) в Инспекторе объектов. Тип каждого параметра необходимо указать в свойстве DataType типа TFieidType. Например, для параметра prmSaiary установлен тип ftFloat.

 

В последующем перед выполнением запроса вместо параметра необходимо подста­вить его значение, в данном случае из редактора Editi. Ниже приведен код обра­ботчика события нажатия кнопки Buttoni, выполняющий указанное действие.

procedure TForml.ButtonlClick(Sender: TObject);

begin

Queryl.Close;

Queryl.ParamByName('prmSaiary').AsFloat:= StrToFloat(Editl.Text);

Queryl.Open;

end;

Для доступа к параметру во время выполнения приложения используется метод ParamByName, отличающийся от аналогичного метода FieidByName тем, что вме­сто имени поля указывается имя параметра.

При использовании параметров можно не изменять текст SQL-запроса во время выполнения приложения и тем не менее передавать в него различные значения. То есть статический запрос как бы превращается в динамический.

Замечание

J

Статические запросы, в которых использованы параметры, иногда также называют изменяющимися (т. е. фактически динамическими).

Обычно текст SQL-запроса проверяется и выполняется при кажцом открытии набора данных Query. Если текст запроса при выполнении приложения не из­меняется, то его можно предварительно подготовить, а после этого только ис-

Глава 8. Реляционный доступ к данным с помощью BDE

265

пользовать такой подготовленный к выполнению запрос. Это позволяет уско­рить обработку статических запросов, в том числе имеющих параметры.

Подготовку запроса к выполнению осуществляет метод Prepare, который мож­но вызывать при создании формы. Чтобы определить, была ли произведена предварительная подготовка запроса, необходимо проанализировать свойство Prepared типа Boolean, которое после вызова метода Prepare устанавливается в

значение True.

Если текст подготовленного к выполнению запроса изменился (к изменению значений параметров это не относится), то автоматически вызывается метод UnPrepare, И СВОЙСТВО Prepared устанавливается В False.

Глава 9

Технология dbExpress

Общая характеристика

В основе технологии dbExpress лежит использование множества легковесных драйверов, компонентов, объединяющих соединения, транзакции, запросы и наборы данных, а также интерфейсов, реализующих универсальный доступ к соответствующим функциям.

По сравнению с использованием механизма BDE технология dbExpress обеспе­чивает построение более легковесных (по объему кода) приложений для работы с базами данных. При ее применении для доступа к данным используются SQL-запросы. Технология dbExpress обеспечивает легкую переносимость приложе­ний, допускает работу приложений баз данных под управлением Windows и Linux.

Для использования технологии dbExpress достаточно включить в распространяе­мое приложение динамически подключаемую библиотеку с драйвером, взаимо­действующим с клиентским программным обеспечением для нужного сервера базы данных (рис. 9.1).

Драйверы, используемые для доступа к различным серверам баз данных по тех­нологии dbExpress, реализованы в виде динамически подключаемых библиотек (DLL) (табл. 9.1). Драйверы dbExpress в составе Delphi размещаются в папке..\Borland\Delphi7\BIN.

Глава 9. Технология dbExpress

267

На странице dbExpress Палитры компонентов Delphi находятся компоненты, используемые в технологии dbExpress: SQLConnection (Database), SQLDataSet, SQLQuery (Query), SQLStoredProc (StoredProc), SQLTable (Table), SQLMonitor (утилита SQL Monitor) и simpleDataSet (BDECiientDataSet) (см. главу 2). Для наглядности в круглых скобках указаны аналоги этих компонентов, используе­мые в случае механизма BDE. Как видим из приведенного списка, у компонента SQLDataSet нет аналога для механизма BDE. Кроме того, ряд компонентов из механизма BDE не имеет аналогов для технологии dbExpress.

Определенным недостатком технологии dbExpress является то, что несколько из перечисленных компонентов (SQLDataSet, SQLQuery, SQLStoredProc И SQLTable) являются однонаправленными наборами данных, в которых отсутст­вует буферизация. Эти наборы данных обеспечивают более быстрый доступ к данным и предъявляют меньшие требования к ресурсам, но при этом на них накладываются заметные ограничения. Для компонента SimpleDataSet боль­шинство из этих ограничений не действует.

Установление соединения с сервером

Для установления соединения с сервером базы данных служит компонент SQLConnection, который представляет собой аналог компонента DataBase в BDE.

Этот компонент взаимодействует с двумя файлами, расположенными в каталоге..\Common Files\Borland Shared\DBExpress. Файл dbxdrivers.ini содержит список инсталлированных драйверов серверов БД и для каждого драйвера список ди­намически подключаемых библиотек и параметров соединений, установленных по умолчанию. Список соединений с параметрами соединений содержится в файле dbxconnections.ini.

Поместив компонент SQLConnection в форму (или модуль данных) на этапе разработки приложения, можно выбрать одно из существующих соединений,

268

Часть II. Технологии доступа к данным

либо создать новое соединение с помощью диалогового окна Редактора соеди­нений (рис. 9.2). Вызов указанного окна можно выполнить выбором пункта Edit Connection Properties контекстного меню компонента.

Рис. 9.2. Диалоговое окно Редактора соединений

Редактор соединений позволяет настроить существующее соединение dbExpress или создать новое соединение, а также проверить правильность настроек с по­мощью кнопки (Test Connection). Параметры соединения можно настраивать также с помощью Инспектора объектов (свойство Params типа TStrings).

Параметры соединения для основных серверов баз данных приведены в табл. 9.2.

 

Глава 9. Технология dbExpress

269

 

 

270

Часть II. Технологии доступа к данным

(свойство connectionName) для нужного сервера баз данных. При этом автома­тически устанавливаются значения связанных с ним свойств: DriverName (имя драйвера); LibraryName (имя динамически подключаемой библиотеки драйвера); Params (параметры соединения) и vendorLib (имя динамически подключаемой библиотеки клиентской части сервера).