Лекция № 23
Тема: «Работа с запросами»
План
1. Работа с запросами. Параметры запросов.
2. Отображение данных из подчиненной таблицы с помощью запроса.
3. Особенности работы с запросами как с источниками данных.
4. Обновление и удаление данных.
5. Расчет общих показателей по таблице.
1. Работа с запросами. Параметры запросов
Рассмотренные ранее компоненты ADOTable всегда загружают на машину клиента всю таблицу, что ведет к нерациональному расходованию сетевого трафика и ресурсов компьютера, если обращение к БД происходит по сети. Если в таблицах хранится большой массив информации и число пользователей системы велико, то будет увеличиваться время отклика программы на машинах клиентов.
В реальных условиях в определенный момент времени пользователю необходимо видеть на экране только часть данных, поэтому было бы целесообразно обеспечить загрузку информации нужными порциями. Это разгрузит сеть и увеличит скорость работы сетевого приложения.
Второй задачей, которую не может выполнить компонент ADOTable, является отображение информации из нескольких связанных таблиц как одного набора данных.
Кроме выборки данных язык может возникнуть потребность расчетов по определенным полям таблиц и вывод их на экран. Это тоже недоступно для компонента ADOTable.
Для решения обозначенных выше задач существует специальный компонент ADOQuery, имеющий свойства:
Active - true/false (включает или выключает запрос)
Connection - имя ADOConnection компонента для подключения к БД;
SQL - свойство-объект для работы с текстом запроса, является объектом с собственными свойствами и методами:
Text - свойство для задания текста запроса;
Parameters - свойство для работы с параметрами запроса.
Clear - метод очистки текста запроса;
Add('SQL команда') - добавление к тексту запроса указанной команды.
Данный компонент основном похож на ADOTable: он имеет такой же редактор полей для задания их свойств, поддерживает такие же методы навигации (First, Prior, Next, Last) и редактирования данных в запросе (Append, Insert, Edit, Post, Cancel, Delete).
Особую пользу при работе с запросами можно извлечь при использовании параметров. Каждый запрос может включать некоторые изменяемые в ходе выполнения программы части (ключи в условиях поиска, имена полей и т.п.). Технология параметров позволяет один раз создать некоторый запрос-шаблон, а затем в коде программы просто задавать параметрам нужные значения.
Чтобы создать параметр в тексте запроса его нужно записать с символа ":". например:
select * from students
where id_grup = :id_grup
В данном запросе указан параметр с именем id_grup (имя начинается c ":"). Имя параметра можно указать произвольное, но для простоты кодирования, имя параметра делают похожим на имя поля, с которым параметр взаимодействует (в нашем пример поле id_grup должно равняться параметру: id_grup).
Все параметры запроса отображаются в свойстве Parameters компонента ADOQuery. Изначально типы параметров часто определяются без указания типа, поэтому для корректной работы приложения для каждого параметра в свойствах DataType и Value-Type задайте правильный тип параметра.
Для задания параметру нужного значения употребляют команду вида:
ADOQuery1.Parameters.ParamByName('имя_параметра').Value:=значение;
Пример работы с параметрами рассмотрен в следующем разделе лекции.
2. Отображение данных из подчиненной таблицы с помощью запроса
При отображения данных через компонент ADOTable на машину клиента загружаются все данные из таблиц. Главная таблица может загружаться полностью. Но если речь идет о подчиненной таблице, то такая загрузка неверна, так как в определенный момент времени пользователю нужны только те подчиненные записи, которые относятся к выбранной им записи из главной таблицы.
То есть нужен механизм, при котором программа будет запрашивать только нужную информацию из подчиненной таблицы порциями.
Для решения подобной задачи подчиненная таблица должна отображаться с помощью запроса вида:
select * from подч_таблица
where поле = :поле
где поле - это имя поля подчиненной таблицы, по которому она связана с главной.
Обратите внимание, что в условии отбора используется имя параметра, которое можно назвать произвольно, но для удобства кодирования чаще всего называют по имени поля.
После задания запроса компонент ADOQuery связывают с отдельным компонентом DataSource, а DataSource - с сеткой DBGrid.
Теперь нужно сделать так, чтоб при выборе нужной записи в главной таблице, значение ее ключевого поля передавалось как параметр в текст нашего запроса, и в сетке отображались cответствующие подчиненные записи.
Пример. Пусть имеются две таблицы grup и stud, которые связаны по полю id_grup. Главная таблица grup подключается на форме с помощью компонента ADOTable1. Подчиненная таблица stud подключается на форме с помощью компонента ADOQuery1.
Для отображения подчиненной таблицы с помощью запроса для компонента ADOQuery1 в свойстве SQL задан текст запроса:
select * from stud
where id_grup = :id_grup
Для того, чтобы при переходе по группам в главной таблице, в запросе отображались соответствующие порции информации из подчиненной таблицы, необходимо в событии AfterScroll главной таблицы ADOTable1 ввести код:
//если ключевое поле в главной таблице пустое
//то есть запись новая
//выходим и з процедуры, код не выполняется
if ADOTable1.FieldByName('id_grup').AsString = '' then Exit;
//отключаем запрос
ADOQuery1.Active = false;
//параметру id_grup запроса присваиваем
//значения поля id_grup из главной таблицы
ADOQuery1.Parameters.ParamByName('id_grup'). Value:=ADOTable1.FieldByName('id_grup').Value;
//включаем запрос для отбора данных
ADOQuery1.Active = true;
Особенности сохранения подчиненной записи
Для корректного сохранения подчиненных записей необходимо, чтобы связанное поле в подчиненной таблице ОБЯЗАТЕЛЬНО заполнялось значением из ключевого поля главной таблицы. Для решения проблемы нужно в событии BeforPost (перед сохранением) подчиненного запроса ввести код:
ADOQuery1.FieldByName('id_grup').Value:=ADOTable1.FieldByName('id_grup').Value;
При условии, что таблицы в БД связаны по полю id_grup.
3. Особенности работы с запросами как с источниками данных
Ранее было сказано о том, что компонент ADOQuery имеет те же методы, что и компонент ADOTable. Однако есть некоторые отличия.
Сортировка данных в сетке
Сортировка данных в сетке рассматривалась ранее. При этом источником данных выступал компонент ADOTable. Напомним, что для сортировки данных в ADOTable используется свойство IndexFieldNames, которому присваивается нужное имя поля для сортировки.
Компонент ADOQuery для сортировки использует другое свойство – Sort. Поэтому, если данные запроса отображаются в сетке и вы хотите релазовать сортировку с помощью щелчка по заголовкам колонок сетки, то в событии сетки OnTitleClick введите код:
//если данные в запросе отсортированы по убыванию
If ADOQuery1.Sort = column.FieldName + ' DESC' then
//сортируем по возрастанию
ADOQuery1.Sort:=column.FieldName
//иначе, по убыванию
Else
ADOQuery1.Sort:=column.FieldName + ' DESC' ;
Переключение между связанными и полными данными
Данные из связанной таблицы подгружаются на форму порциями. Такой режим работы наиболее оптимален с точки зрения скорости работы приложения. Однако, может возникнуть ситуация, при которой в подчиненной сетке потребуется отобразить всю подчиненную таблицу, а позже опять вернуться к режиму просмотра только подчиненных записей.
Такая функция может быть реализована, например, с помощью группы переключателей RadioGroup.
Пример. Пусть имеется группа переключателей. Первый отображает записи в режиме подчиненных (выбран по умолчанию), второй – полным списком.
Вначале обеспечим отбор всех данных из подчиненной таблицы. Нанесите на форму отдельный компонент ADOTable и свяжите его с подчиненной таблицей.
Для группы переключателей в событии OnClick напишем код:
case radiogroup1.itemindex of
//если выбран первый переключатель
0: begin
//отключаем подчиненную общую таблицу
ADOTAble2.Active:=false;
//переключаем источник данных на запрос
//переходим на просмотр по подчиненным
DataSource2.DataSet:=ADOQuery1;
end;
//если выбран второй переключатель
1: begin
//включаем подчиненную общую таблицу
ADOTable2.Active:=true;
//переключаем источник данных на таблицу
//переходим на просмотр общим списком
DataSource2.DataSet:=ADOTable1;
end;
end;
4. Обновление и удаление данных
При работе с базами данных может возникнуть необходимость обновления или удаления больших наборов данных. Например, увеличить оклады всех сотрудников на 20%, удалить информацию о продажах за прошлый год и т.д. В больших таблицах ручное выполнение таких операций занимает много времени, поэтому существует способ автоматизации данных действий.
В языке SQL есть специальные типы запросов:
- запрос на обновление UPDATE;
- запрос на удаление DELETE.
Оба типа таких запросов выполняются через компонент ADOQuery, но так как данные запросы ничего не отбирают, а только изменяют состояние данных, то выполняют их с помощью команды
ADOQuery1.ExecSql;
Запрос на обновление данных
Пусть в базе данных имеется таблица sotr с полем oklad и ключевым полем tabnom.
Данные отображаются в сетке через компонент ADOTable.
Необходимо увеличить оклад всех сотрудников на n% (сумма процентов пусть задается в поле Edit1). Задача предусматривает использование запроса на обновление, которые записывается так:
update таблица set поле1=формула, ..., полеN=формула
[where условие]
Для формирования запроса на форму нанесите компонент ADOQuery1 и задайте свойство: Connection = ADOConnection1.
Код обновления будет иметь вид:
// описываем переменную для сохранения табельного номера текущей записи
var id:integer;
begin
// запоминаем ключевое поле текущей записи в таблице
Id:=ADOTable1.fieldbyname('tab_nom').AsInteger;
// формируем новый текст запроса
ADOQuery1.Text:='update sotr set oklad = oklad + oklad *' + Edit1.Text + ' / 100');
// выполняем запрос
ADOQuery1.ExecSql;
// обновляем данные в сетке
ADOTable1.Requery();
// устанавливаем указатель в сетке на сохраненное ключевое поле
ADOTable1.Locate('tabnom',id,[]);
// ставим курсор в сетку
DBGrid1.SetFocus;
end;
Запрос на удаление данных
Аналогично можно выполнять удаление данных. При этом текст запроса имеет вид:
delete from таблица [where условие]
Несмотря на то, что конструкция "where условие" необязательна, однако, в запросах на удаление она очень важна. Без ее указания из таблицы удалятся все записи.
Пример. Из таблицы sotr удалить записи, в которых сумма оклада равна числу, указанному в поле Edit1. Если на форме есть компонент ADOQuery1, то код имеет вид:
// формируем новый текст запроса
ADOQuery1.Sql.Text:='delete from sotr where oklad =' + Edit1.Text;
// выполняем запрос
ADOQuery1.ExecSql;
// обновляем данные в сетке
ADOTable1.Requery();
// ставим курсор в сетку
DBGrid1.SetFocus;
5. Расчет общих показателей по таблице
Если в таблице есть числовые поля, то по ним можно получать разную итоговую информацию: сумму, среднее, максимальное, минимальное, количество записей и т.д.
Для расчетов таких показателей можно применять запросы вида:
select функция(поле) as имя, функция(поле) as имя, ...
from таблица [where условие]
В качестве функций можно использовать такие:
count(*) – количество записей в Таблице;
count(поле) – количество записей с непустыми значениями в поле
sum(поле) – сумма значений в поле
max(поле) – максимальное значение в поле
min(поле) – минимальное значение в поле
avg(поле) – среднее значение в поле.
Пример. Необходимо вывести на форме суммарный и средний оклад по предприятию.
Нанесите на форму компонент ADOQuery, задайте свойство Connection = ADOConnection1.
Напишем код вида:
// формируем новую команду запроса
ADOQuery1.Sql.Text:='select sum(oklad) as summa, avg(oklad) as sred from sotr');
// активируем запрос, отбираем данные
ADOQuery1.Active:=true;
// в первой надписи Label1 выводим cуму
Label1.Caption:='Сумма =' + VarToStr(ADOQuery1.FieldByName('summa').Value);
// во второй надписи Label2 выводим среднее
Label2.Caption:='Среднее =' + VarToStr(ADOQuery1.FieldByName('sred').Value);
// закрываем запрос
ADOQuery1.Active:=false;
Примечание: обратите внимание, что при выдаче результатов используется функция конвертирования VarToStr, хотя по логике нужно использовать FloatToStr. Если в таблице не окажется данных, то вместо ответа запрос вернет пустоту (NULL), и функция FloatToStr будет выдавать ошибку конвертирования. Функция VarToStr работает корректно: если ответ - число, то будет выведено число; если ответ - NULL, то будет выведен пустой символ.
Данные показатели должны отображаться при открытии формы. Поэтому код нужно писать в события OnCreate формы.
Однако, если пользователь добавит или удалит сотрудника, то показатели нужно пересчитать. Если пользователь поменяет оклад сотруднику, то показатели нужно пересчитать. Возникает необходимость писать этот же код в событиях AfterPost и AfterDelete таблицы ADOTable1.
Размножения кода копированием делает трудно читается и трудно редактируется, поэтому целесообразнее оформить этот код в отдельную процедуру и вызывать ее в нужных событиях.
Вверху кода формы в разделе Interface найдите заголовки процедур модуля формы и добавьте заголовок своей процедуры:
procedure itog;
В разделе Implementation оформите рассмотренный выше пример в отдельную процедуру:
procedure tform1.itog;
begin
// формируем новую команду запроса
ADOQuery1.Sql.Text:='select sum(oklad) as summa, avg(oklad) as sred from sotr');
// активируем запрос, отбираем данные
ADOQuery1.Active:=true;
// в первой надписи Label1 выводим cуму
Label1.Caption:='Сумма =' + VarToStr(ADOQuery1.FieldByName('summa').Value);
// во второй надписи Label2 выводим среднее
Label2.Caption:='Среднее =' + VarToStr(ADOQuery1.FieldByName('sred').Value);
// закрываем запрос
ADOQuery1.Active:=false;
end;
В нужных событиях выполните вызов процедуры по ее имени:
// в события OnCreate формы
procedure tform1.FormCreate (Sender: Tobject)
begin
// вызов процедуры
itog;
end;
// в события AfterPost компонента ADOTable1
procedure tform1.ADOTable1AfterPost (Dataset: TDataset)
begin
itog;
end;
// в события AfterDelete компонента ADOTable1
procedure tform1.ADOTable1aFterDelete (Dataset: TDataset)
begin
itog;
end;
Пример. В таблице sotr найти запись с максимальным окладом.
Нанесите на форму компонент ADOQuery, задайте свойство Connection = ADOConnection1.
Напишите код вида:
// описываем переменную для сохранения максимального оклада
var m:real;
begin
// формируем новую команду запроса
ADOQuery1.Sql.Text:='select max(oklad) as maximum from sotr');
// активируем запрос, отбираем данные
ADOQuery1.Active:=true;
// в переменную m запоминаем найдено максимальное значение
m:=ADOQuery1.FieldByName('maximum').AsFloat;
// отключаем запрос
ADOQuery1.Active:=false;
// в поле oklad ищем значение, записанное в переменную m
ADOTable1.Locate('oklad', m, []);
end;
Вопросы для самоконтроля
1. Для чего используется компонент ADOQuery? Какие его возможности.
2. Какие свойства и методы компонента ADOQuery Вы знаете? Опишите работу с параметрами запроса.
3. Опишите процесс обновления данных в таблице с помощью запросов.
4. Опишите процесс удаления данных из таблицы с помощью запросов.
5. Как с помощью запросов можно рассчитать некоторые итоговые показатели по полям таблицы?
6. Приведите пример расчета и отображения суммы по некоторому полю таблицы на форме.