Горев А., Макашарипов С., Ахаян Р. Эффективная работа с СУБД
Подождите немного. Документ загружается.
Последние две кнопки выбора позволяют вам решить, как будут производиться изменения в
исходной таблице: путем удаления и вставки новой записи или путем модификации записи.
Второй путь предпочтительнее, так как результат достигается много быстрее.
Свойства WhereType и UpdateType можно установить глобально для всего Visual FoxPro,
используя диалоговое окно Options на странице Remote Data. Там есть два раскрывающихся
списка, объединенных общим заголовком SQL Updates, - Criteria и Method, как это показано на
рис. 7.5.
Рис. 7.5. Установка параметров обновления данных
Мы уже говорили, что создать представление можно программным путем с помощью команды
CREATE SQL VIEW. Свойства, с помощью которых регулируются данные в исходных таблицах,
устанавливаются также программным путем с помощью функций DBSETPROP() и
CURSORSETPROP(). Узнать значения установленных свойств можно с помощью функций с
похожими названиями: DBGETPROP() и CURSORGETPROP(). Отличие их в том, что функции
CURSORGETPROP() и CURSORSETPROP() работают с представлениями, которые в данный
момент используются в какой-либо из рабочих областей. Функции DBGETPROP() и
DBSETPROP() устанавливают свойства для любого представления, которое содержится в
текущей базе данных. В приводимой ниже табл. 7.1 обратите внимание на то, что многие
свойства можно установить только программным путем. Свойства, которые приводятся в табл.
7.1, относятся к полям представления. То есть вторым аргументом функции DBSETPROP() или
функции DBGETPROP() будет "FIELD". Например:
DBSETPROP("account_and_customer.account","FIELD","comment",; "Используйте это поле для
ввода номера счета")
converted to PDF by HupBaH9I
Обратите особое внимание, что название поля приводится вместе с названием представления
"account_and_customer.account". В противном случае Visual FoxPro не сможет найти это поле.
Таблица 7.1 Свойства полей представлений
Свойство Тип
Описание
Caption C Заголовок поля. Доступно для
чтения и записи.
Comment С Комментарий поля. Доступно для
чтения и записи.
DataType С Данное свойство игнорируется
для локальных представлений.
Доступно для чтения-записи при
работе с внешними
представлениями.
Default value С Значение по умолчанию для поля.
Доступно для чтения и записи.
KeyField L Содержит .T.,, если поле указано
как ключевое индексное
выражение. Данное свойство
можно установить визуально в
Конструкторе представлений на
странице Update Criteria.
Доступно для чтения и записи.
RuleExpression
C Правило проверки ввода уровня
поля. Доступно для чтения и
записи.
RuleText C
Сообщение, выводимое на экран в
случае нарушении правил
проверки ввода уровня поля.
Доступно для чтения и записи.
Updatable L Содержит истину (.T.),, если поле
доступно для изменений. Это
свойство можно установить
визуально с помощью
Конструктора представлений на
странице Update Criteria.
Доступно для чтения и записи.
UpdateName C Название поля, которое
используется, когда поле
модифицируется на таблице
внешнего формата. По умолчанию
совпадает с именем поля во
внешней таблице. Доступно для
чтения и записи.
В следующей таблице приводятся некоторые свойства представления, которые можно
использовать как для локальных, так и внешних представлений. Для их чтения и изменения
необходимо использовать функции DBGETPROP() и DBSETPROP() со вторым аргументом VIEW.
В следующем примере мы устанавливаем свойство Comment для всего представления, а затем
выводим его на экран:
=DBSETPROP("account_and_customer","view", "Comment",
"Предназначено для корректировки поставок по счетам")
? DBGETPROP("account_and_customer","view", "Comment")
Таблица 7.2. Свойства полей для представлений
Свойство Тип
Описание
Comment C Текст комментария представления. Доступно для
чтения и записи.
FetchMemo L Равняется .T., если данные из полей примечаний
или типа General выбираются с результатами
представления. Имеет смысл ставить значение
converted to PDF by HupBaH9I
этого поля в .F. В таком случае данные поля этого
типа будут выводиться только при прямом
обращении к ним. По умолчанию имеет значение
.T.. Доступно для чтения и записи.
MaxRecords N Устанавливает максимальное значение, которое
выбирается в представлении. По умолчанию
равно 1, то есть выбираются все записи. Если
установить это значение равным 0, то не будут
выводиться никакие результаты. Доступно для
чтения и записи.
RuleExpression
C Правило проверки вводимых данных уровня
записи. Доступно для чтения и записи.
RuleText C Сообщение, выводимое на экран при нарушении
правил проверки ввода уровня записи. Доступно
для чтения и записи.
SendUpdates L Eсли установлено в .T., то изменения будут
посылаться в исходные таблицы. Доступно для
чтения и записи.
SourceType N Равно 1, если представление использует только
локальные таблицы. Равно 2, если представление
использует внешние таблицы. Доступно только
для чтения.
SQL C Возвращает строку запроса, который выбирает
данные для представления. Доступно только для
чтения.
Tables C Разделенный пробелом список таблиц,
участвующих в выборке представления Доступно
только для чтения.
UpdateType N Равняется 1, если старые данные в исходных
таблицах модифицируются, 2 - если данные
вначале удаляются, а затем добавляются.
Доступно для чтения и записи.
WhereType N Определяет, по какому принципу будет
происходить поиск записи в исходных таблицах
при модификации данных в представлении.
1 - Только по ключевым полям. Можно
использовать DB_KEY из Foxpro.h
2 - По ключевым полям и полям, разрешенным
для изменений. Можно использовать
DB_KEYANDUPDATABLE из Foxpro.h
3 - По ключевым и измененным полям.
Используйте DB_KEYANDMODIFIED из Foxpro.h
4 - Используется только для внешних
представлений. Сравнение проводится по полю
TIMESTAMP, если оно поддерживается во
внешнем представлении.
К открытому в текущий момент представлению для установки его свойств применяется
функция CURSORSETPROP(). И, соответственно, функция CURSORGETPROP() для чтения
свойств представления. Обращаем внимание, что эти две функции работают с любыми
курсорами, открытыми в любой рабочей области. Курсоры могут отображать как данные из
представлений, так и данные из таблиц. При этом таблицы могут быть свободными, то есть не
принадлежать никакой из баз данных. Многие свойства можно изменять как с помощью функции
DBSETPROP(), так и с помощью функции CURSORSETPROP(). Есть некоторые свойства,
которые характерны только для курсора, такие как Database, которое доступно только для
чтения и указывает полный путь к базе данных, служащей контейнером для объекта, данные из
которого отображает курсор. Если в свойствах полей представления не указаны ключевые и
разрешенные для модификации поля, то можно указать их для уже активного курсора. Например,
вы можете проверить для активного курсора представления с помощью функции
CURSORGETPROP() наличие ключевого поля и в случае отсутствия установить:
USE Account_and_customer
If LEN(ALLT(CURSORGETPROP("keyfieldlist")))=0
=CURSORSETPROP("keyfieldlist",;
converted to PDF by HupBaH9I
"account.account,customer.name_customer")
ENDIF
Аналогичным способом мы можем проверить наличие полей, разрешенных для модификации и
переустановить их, помня при этом, что сначала мы должны иметь ключевые поля для каждой
таблицы, участвующей в построении представления. Таким образом мы можем дописать
предыдущий пример:
USE Account_and_customer
IF LEN(ALLT(CURSORGETPROP("keyfieldlist")))=0
=CURSORSETPROP("keyfieldlist",;
"account,name_customer")
ELSE
IF LEN(ALLT(CURSORGETPROP("updatablefieldlist")=0
=CURSORSETPROP("updatablefieldlist",;
"key_customer,selled,summa,;
name_customer,address")
ENDIF
ENDIF
Но это еще не все, так как необходимо установить свойство UpdatenameList, которое служит
для связи между полями в представлении и полями в исходных таблицах. И помимо этого
обязательно установите свойство SendUpdates. В итоге для рассматриваемого случая мы
получаем следующую процедуру:
USE Account_and_customer
IF LEN(ALLT(CURSORGETPROP("keyfieldlist")))=0
=CURSORSETPROP("keyfieldlist",;
"account,name_customer")
ELSE
IF LEN(ALLT(CURSORGETPROP("updatablefieldlist")=0
=CURSORSETPROP("updatablefieldlist",;
"key_customer,selled,summa,;
name_customer,address")
=CURSORSETPROP("updatenamelist",;
"key_customer account.key_customer,;
selled account.selled,summa account.summa,;
name_customer customer.name_customer,;
address customer.address" )
ENDIF
ENDIF
=CURSORSETPROP("SendUpdates",.T.)
В следующем примере программным путем создается представление c помощью запроса к двум
таблицам: Account и Customer. C помощью функции DBSETPROP() назначаются свойства
установки ключевого поля и свойства, устанавливающие возможность модификации полей в
исходных таблицах.
IF NOT DBUSED("auto_store")
OPEN DATABASE "auto store"
ENDIF
CREATE SQL VIEW Account_and_customer ;
AS SELECT Account.*, Customer.name_customer, ; Customer.address;
FROM "auto store!account", "auto store!customer";
WHERE Customer.key_customer = Account.key_customer;
AND Account.selled = .f.
=DBSETPROP("account_and_customer.account",;
"FIELD","KeyField",.T.)
=DBSETPROP("account_and_customer.name_customer",;
"FIELD","KeyField",.T.)
=DBSETPROP("account_and_customer.date_write",;
"FIELD","Updatable",.T.)
=DBSETPROP("account_and_customer.selled",;
"FIELD","Updatable",.T.)
=DBSETPROP("account_and_customer.summa",;
"FIELD","Updatable",.T.)
converted to PDF by HupBaH9I
=DBSETPROP("account_and_customer.address",;
"FIELD","Updatable",.T.)
USE Account_and_customer
=CURSORSETPROP("SendUpdates",.T.)
=CURSORSETPROP("WhereType",3)
GO 3
REPLACE selled WITH .T.
SKIP -1
SELECT Account
BROWSE
В Visual FoxPro существует понятие буферизации, установку которой контролирует свойство
Buffering. Значение этого свойства можно менять с помощью функции CURSORSETPROP() и
читать с помошью функции CURSORGETPROP().
Свойство Buffering может принимать пять значений:
1. - отсутствие какой-либо буферизации.
2. - пессимистическая буферизации записи. При этом значении блокируется редактируемая
запись. Блокировка автоматически снимается, и изменения записываются на диск, как
только пользователь переходит на другую запись или закрывает таблицу. Другим
способом записи изменений на диск может служить использование функции
TABLEUPDATE().
3. - оптимистическая буферизация записи. Запись блокируется только в то время, когда она
записывается на диск. Для представлений эта блокировка является значением по
умолчанию.
4. - пессимистическая буферизация таблицы. Как только вы начинаете редактирование,
блокируется вся таблица. При этом запись на диск может произойти только при вызове
функции TABLEUPDATE() или закрытии таблицы.
5. - оптимистическая буферизация таблицы. Таблица блокируется в момент записи
изменений на диск. Для записи изменений на диск надо либо вызвать функцию
TABLEUPDATE(), либо закрыть таблицу.
7.5. Запросы в Microsoft Access
В этом параграфе вы изучите методику построения разнообразных типов запросов в Access.
Узнаете о возможностях визуального инструментария и способах использования для построения
запросов макрокоманд и объектов DAO.
Запросы, без преувеличения, являются главным инструментом работы с данными в Access.
Бесспорно, вы можете создать одну таблицу с огромным количеством полей, с помощью Мастера
изготовить форму для работы с ней и посчитать, что приложение готово. При этом можно сказать,
что существует определенный круг задач, для которых такая технология вполне приемлема. Но
мы будем рассматривать другие, более распространенные случаи и задачи, которые лучше
решать с помощью запросов. И уже на основе запросов в дальнейшем мы будем строить формы.
Access предоставляет нам несколько способов создания запросов. Самый распространенный -
создание запросов с помощью Конструктора запросов. Количество видов запросов, которые мы
можем создавать с помощью Конструктора, впечатляет и резко выделяет Access среди других
продуктов. Из Конструктора запросов легко перейти в режим редактора SQL либо в табличный
режим, где мы можем просмотреть результаты запросов. Режим редактора выражений SQL мы
будем рассматривать как второй способ создания запросов. Некоторые профессионалы на
начальном этапе изучают Конструктор запросов методом "от противного". То есть пишут запрос
вручную, а потом выходят в режим Конструктора. Новичкам имеет смысл почаще делать
обратную операцию, то есть создавать запросы в Конструкторе и переходить в режим редактора,
для того чтобы лучше изучить синтаксис SQL.
Третий способ - это программный способ создания запросов с помощью объектов доступа к
данным (DAO). Для этого используется объект QueryDef, который и хранит в себе описание SQL
запроса.
Последний способ - создание строки запроса и выполнение его с помощью команды
DoCmd.RunSQL. Этот способ достаточно популярен, при этом база данных становиться более
компактной, так как описание запроса хранится в виде строки кода, а не в виде описания
объекта. Его недостатком является то, что мы не можем получить таким образом объект
Recordset. В то же время, этот способ прекрасно годится для создания и выполнения запросов
действий (Update, Delete, Insert), так как часто приходится скрывать от пользователя суть
динамики изменения наборов данных. Хранение запросов модификации в базе данных, если мы
не хотим, чтобы пользователь без необходимости запускал эти запросы, заставляет нас думать об
converted to PDF by HupBaH9I
ограничении доступа. Поэтому логичней хранить запрос в строчках кода.
Для создания запросов с помощью Конструктора необходимо выполнить одну из следующих
последовательностей действий:
1. Перейти на страницу Запросы в Контейнере базы данных.
2. Нажать кнопку Создать.
3. В появившемся диалоге выбора таблиц выбрать нужные таблицы.
4. Связать таблицы, если они не связаны, постоянно хранимой связью, зарегистрированной
в базе данных.
Либо
1. Выбрать в меню Вставка команду Запросы. При этом надо помнить, что меню Access
изменяется в зависимости от того, какой объект активен в данный момент. Если,
например, вы находитесь в режиме редактирования таблицы, то меню Вставка не
содержит пункт Запросы.
2. Выполнить шаги 3 и 4 из предыдущей последовательности действий.
При использовании второго способа можно еще более облегчить свой труд. Для этого,
находясь на странице Таблицы Контейнера базы данных, выберите таблицу, которую
собираетесь использовать в запросе, как это показано на рис. 7.6. После этого выберите команду
Запросы меню Вставка. Вы попадете в Конструктор запросов с уже выбранной таблицей,
графический образ которой будет присутствовать в верхней половине Конструктора.
Рис. 7.6.
Здесь необходимо отметить, что запросы можно создавать не только к таблицам, но и к уже
существующим запросам. Если есть необходимость, то вы можете комбинировать табличные
данные и данные уже существующего запроса в новом запросе. В диалоговом окне Добавление
таблицы присутствуют три вкладки: Таблицы, Запросы и Таблицы и Запросы. С их помощью вы
можете ограничить необходимые вам объекты только таблицами, только запросами, либо,
напротив, включить в список как таблицы, так и запросы (рис. 7.7).
converted to PDF by HupBaH9I
Рис. 7.7.
Так же как в Visual FoxPro, Конструктор запросов состоит из двух частей. В верхней части
расположены графические образы таблиц и запросов, данные из которых используются в
текущем запросе. По умолчанию Конструктор запросов служит для построения запросов выборки.
При этом обязательно учитывайте, что при изменении значений в записях итоговых запросов
изменяются данные в исходных таблицах. Нижняя половина представляет собой таблицу, в
каждую строку которой мы можем заносить необходимую информацию. Несмотря на то, что
научиться пользоваться Конструктором достаточно легко, мы тем не менее позволим себе
остановиться на этом моменте более подробно. В первую строку заносится название поля.
Сделать это можно несколькими способами:
1. Перетащить графический образ поля из верхней части Конструктора в соответствующую
колонку.
2. Дважды щелкнуть мышкой на графическом образе поля.
3. Написать название поля непосредственно в первой строке колонки.
4. Выбрать название поля из раскрывающегося списка, как показано на рис.7.8.
Рис. 7.8.
Во вторую строчку заносим название таблицы. Это важно, если у нас больше, чем одна
таблица, так как в таблицах поля могут совпадать по названию. Если вы сомневаетесь, что
можете правильно занести название таблицы, то выбирайте ее из списка.
Очень часто необходимо выводить информацию, упорядочив ее по какому-нибудь из полей.
Для этого используется третья строка колонки поля.
С помощью переключателя, расположенного в четвертой строчке, вы определяете, будет ли
поле выводиться в выборке запроса или нет. Представьте ситуацию, в которой вам необходимо
отсортировать итоговую выборку по некоему полю, но присутствие этого поля в ней не нужно.
Тогда, установив сортировку по этому полю и отключив переключатель вывода на экран, вы
converted to PDF by HupBaH9I
добьетесь необходимой функциональности.
Одним из главных элементов запросов, если не самым главным, является условие выборки или
критерии, которые мы накладываем на исходные таблицы. Ведь совершенно нерационально
выводить все записи из таблицы, где их десятки тысяч. Поэтому активно используйте критерии
для ограничения числа записей. При этом, если в одной колонке вы напишете критерии в
строчках "условия отбора" и "или", то они свяжутся по условию OR. Если же вам необходимо
установить для одного и того же поля критерий по AND, то вы должны либо непосредственно
набрать условие с использованием этого оператора, либо использовать еще одну колонку для
этого поля, но с выключенным переключателем "Вывод на экран", как это показано на рис. 7.9.
Рис. 7.9. Построение критерия с использованием оператора AND. Оба выражения, связанные
оператором AND, накладываются на одно и то же поле Account
Выражения, представленные в табл. 7.3, называются совокупными функциями SQL или
функциями агрегирования. Чтобы построить запрос с использованием этих функций, необходимо
обязательно установить группировку по одному или более полям. Выберите в меню Вид команду
Групповые операции, либо соответствующий значок на панели инструментов. После того как вы
выполните эту команду, в колонках выбора полей появится строчка "Групповая операция".
Теперь из раскрывающегося списка вы можете выбрать одну из функций агрегирования для
поля, которое отображается в текущей колонке, либо установить группировку по данному полю.
Здесь следует обратить внимание на два последних пунктах раскрывающегося списка:
Выражение и Условие. Если вы выберете значение Условие, то возникнет необходимость
отключить вывод на экран этого поля, в противном случае вы получите сообщение от Access, в
котором вас будут просить об этом же. Основное назначение этого пункта - занести критерий,
который вы укажете в этой колонке, в предложение WHERE, так как если вы пишете критерий
для поля, по которому происходит группировка, то критерий заносится в предложении HAVING.
Таблица 7.3. Функции агрегирования в MS Access
Имя функции
Операции
Avg() Среднее арифметическое значений
Count() Количество записей в наборе
First() Значение первой записи в наборе
Last() Значение последней записи в наборе
Min() Минимальное значение в наборе
Max() Максимальное значение в наборе
Sum() Сумма всех записей
StDev() Стандартное отклонение
StDevP() Стандартное отклонение смещенное
Var() Дисперсия
VarP() Дисперсия смещенная
Иногда бывает недостаточно использовать только функцию агрегирования. Тогда можно
использовать выражения, созданные с помощью функций агрегирования для вывода на экран, но
при этом в строку Групповые Операции необходимо вынести значение "Выражение". На рис. 7.10
converted to PDF by HupBaH9I
приводится пример построения такого выражения. Критерий, указанный для колонки, которая
будет выводить выражение, также попадет в предложение HAVING.
Рис. 7.10.
Чтобы сделать запрос более актуальным, то есть иметь возможность менять содержимое
выводимых данных в зависимости от изменяющихся потребностей в получении данных, вы
можете использовать параметрические запросы. Параметры запроса устанавливаются с помощью
специального диалогового окна, представленного на рис. 7.11. При этом помните, что все
выражения тоже будут считаться параметрами, если Access не имеет возможности преобразовать
их.
Рис. 7.11.
На рис. 7.12 представлен запрос, который должен вызываться из формы
IKNOWWHATIWANTIWANTITNOW и выводить данные по счетам, которые являются текущими для
данной формы. В случае запуска этого запроса в момент, когда эта форма не является активной,
условие критерия
Forms![IknowwhatIwantIwantitnow].[account]
превратится в параметр, и перед вашими пользователями появится диалог, в котором будет
предложено ввести значение данного параметра. В то же время параметр SKOLKO
зарегистрирован как параметр и всегда будет считаться параметром, даже при наличии поля с
одноименным названием в какой-нибудь из форм, из которой будет вызываться запрос.
converted to PDF by HupBaH9I
Рис. 7.12. Параметрический запрос, требующий наличия открытой формы
В любой момент мы можем переключиться из окна Конструктора запросов в режим таблицы
или режим SQL. Это очень удобно, так как часто мы знаем, что хотим получить, но никак не
можем добиться нужного результата из-за неправильной логики запроса. Используя режим
таблицы, мы можем корректировать наш запрос до тех пор, пока не получим нужный результат.
Режим SQL полезен в плане изучения синтаксиса, кроме того, что очень удобно, если вы
правильно отредактируете запрос, то ваши изменения отразятся в окне Конструктора.
Существует определенный набор запросов, которые невозможно построить с помощью
Конструктора. О них будет упомянуто ниже.
Помимо запросов выборки с помощью Конструктора, можно создать запросы добавления,
обновления, удаления, перекрестный и создания таблиц.
Запрос добавления
Запросы добавления - это те запросы, которые начинаются с ключевого слова INSERT. Мы их
обсуждали выше, теперь рассмотрим, как их создавать визуально в Microsoft Access. По
умолчанию Конструктор создает запросы добавления с помощью предложения SELECT, то есть
использует следующий синтаксис:
INSERT INTO назначение [IN внешняя_база_данных]
[(поле1[,поле2[, ...]])]
SELECT [источник.]поле1[,поле2[, ...]]
FROM выражение
Если вы в режиме SQL создадите запрос, который использует синтаксис добавления одной
записи, то при повторном открытии запроса он все равно преобразуется в запрос с
вышеприведенным синтаксисом. Тем не менее на результаты запроса добавления данное
преобразование никак не повлияет. Рассмотрим следующий пример. В режиме SQL был создан
следующий запрос:
INSERT INTO Account ( account ) VALUES (109)
После перехода в режим Конструктора и возврат в режим SQL запрос был преобразован к
следующему виду:
INSERT INTO Account ( account )
SELECT 109 AS Выражение1;
Тем не менее и тот другой запрос выполняет совершенно одинаковые действия - добавляет
запись в таблицу Account, у которой поле Account имеет значение 109.
Для того чтобы перевести ваш запрос в запрос добавления, выберите в меню Запросы команду
Добавление, либо выберите соответствующий значок на панели инструментов Тип запроса. После
этого появится диалог, где будет предложено выбрать таблицу, в которую вы сможете добавить
одну или несколько записей, как это показано на рис. 7.13.
converted to PDF by HupBaH9I