Файл: Освой самостоятельно программирование для MS Access 2002 за 24 часа [П.Киммел].pdf

После служебного слова PARAMETERS следует список пар Параметр Тлп, опреде-

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

сти синтаксического элемента, как прежде. (Здесь — во избежание недоразумений -
необязательные элементы отмечены фигурными скобками.) Список параметров за-
вершается символом точки с запятой, после которого набирается текст тела процеду-
ры на языке SQL. Листинг 16.9 демонстрирует два примера хранимых процедур: пер-
вая возвращает набор записей таблицы MUSIC, соответствующих указанному значению
поля PUBLISHER, а вторая отбирает записи той же таблицы, удовлетворяющие задан-
ным значениям полей PUBLISHER и FORMAT.

Листинг 16.9. Примеры хранимых процедур

1: PARAMETERS [APublisher]

2: SELECT

 +

 +

 As  A r t i s t ,

Format, Publisher

3: FROM Music WHERE Publisher = [APublisher]

4:

5: PARAMETERS [APublisher] TEXT,

 TEXT;

6: SELECT First_Name + ' ' +

 As Artist,

Format, Publisher

 FROM Music

8: WHERE Publisher = [APublisher] AND Format = [AFormat]

Строки 1-3 задают текст первой процедуры — далее будет показано, как
в виде единой строки он присваивается

 Command-

Text объекта

 Command. Процедура принимает текстовое значение,

переданное в качестве параметра

 и отбирает все строки

таблицы MUSIC, содержимое поля PUBLISHER которых равно значению
параметра. Текст второй процедуры приведен в строках

 Она выпол-

няет схожую операцию, но работает уже с двумя аргументами

 и

 задающими искомые значения полей

PUBLISHER И FORMAT.

Добавление хранимой процедуры в каталог

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

вить в базу данных с помощью свойств и методов объектов

 Command и

 Собственно, многие из конструкций и выражений VBA, необходимых

для выполнения такой операции, вам уже знакомы. Листинг 16.10 содержит простой

пример, иллюстрирующий все действия по созданию и сохранению первой из проце-

дур листинга 16.9.

Листинг

 Пример добавления хранимой процедуры в базу данных

1

2
3
4
5

6

7

8
9
10

Sub

 )

Dim Connection As
Set Connection =
Dim Command As New
Dim Catalog As New
Set

 = Connection

 = "PARAMETERS [APublisher]

 &

"SELECT ARTIST, TITLE,

 &

"PUBLISHER FROM Music WHERE Publisher =

: Set

 = Connection

290

Часть V. Программирование и базы данных Access

 Call

 I

 Set Command = Nothing
 Set Catalog = Nothing

14: Set Connection = Nothing
15: End Sub

"Artist By Publisher", Command )

Обратите внимание, что в команде SELECT фигурирует столбец ARTIST,
а не два отдельных столбца — FIRST Name и LAST NAME.

Сначала необходимо добавить ссылку на Microsoft ADO Ext, 2.7 for DLL
в диалоговом окне

 редактора Visual Basic. Чтобы решить

задачу, следует открыть соединение с базой данных, в данном случае мы
переместили фокус на

 Connection — соединение с ак-

тивной базой данных. В строке 4 создается объект Command, предназна-
ченный для занесения в него текста хранимой процедуры. В качестве не-
посредственного исполнителя основных действий по сохранению проце-
дуры в базе данных выступает объект класса Catalog — он создается в
строке 5. В строках

 последовательность символов, содержащая текст

процедуры, присваивается атрибуту

 объекта Command.

В строке

 выполняется операция сохранения процедуры под именем

"Artist By Publisher" в базе данных, а ниже осуществляются дейст-

вия по очистке памяти.

Важно понимать, что все операции по созданию вспомогательных объектов приве-

дены в тексте листинга 16.10 только в целях демонстрации — вам вовсе не обязатель-

но заново создавать и открывать объекты классов Connection и Catalog в каждой
процедуре. В принципе, сделать это достаточно один раз, в самом начале программы,
а закрывать и удалять объекты целесообразно непосредственно перед ее завершением.

Подробные сведения по таким вопросам изложены в главе "19-й час. Создание эк-

ранных форм".

Выполнение хранимой процедуры

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

момент по мере надобности. Чтобы выполнить процедуру, следует воспользоваться
методом Execute класса Command. Если хранимая процедура возвращает некий набор

данных (как в нашем случае), надлежит присвоить результат выполнения метода Exe-

cute переменной класса

 Если же хранимая процедура выполняет

команды SQL (такие как INSERT, DELETE или UPDATE), не возвращающие значений
или наборов данных, объект Recordset не нужен.

Листинг

 демонстрирует действия, необходимые для выполнения хранимой

процедуры.

Листинг 16.11. Пример выполнения хранимой процедуры

1: Sub
2:
3:
4:

5:
6:
7 :

Dim Connection As
Set Connection =

Dim Catalog As New
Set

 = Connection

Dim Command As
Set Command =

 By

16-й час. Применение языка SQL

291

11: Dim Publishers As
12 : Dim

 As Long

 =

14: Set Publishers =
15:
16:

 Do While

 = False

18:

 "Artist" )

 Loop

21:
22:
23: Set Publishers = Nothing
24: Set Command = Nothing
25: Set Catalog = Nothing
26: Set Connection = Nothing

27: End Sub

Анализ

Многие строки кода вам уже знакомы, поэтому остановимся лишь на

главном. Вам следует выделить объекты соединения и каталога. Обратите
внимание, что в строке 11 не создается новый объект Recordset — в
нем нет необходимости, поскольку данный объект возвращается коман-

дой

 в строке 14. Поэтому дополнительные операции

выделения памяти в строке

 не нужны. В строке

 определяется зна-

чение Parameters объекта Command, а в строке 14 вызывается метод
Execute, возвращающий набор данных, которые являются результатом
выполнения запроса хранимой процедуры

Полученный в

 выполнения команды строки 14 набор данных, может при-

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

Создание запросов в базах
формата SQL Server

В проектах Access SQL можно использовать тремя способами: создавать соедине-

ния с SQL-базами данных, преобразовывать базы данных Access в формат SQL Server,
устанавливать связь с SQL-таблицами, используя команду

 Выбор зависит от целей проекта и ус-

ловий его разработки. Для повышения надежности, вероятно имеет смысл использо-

вать формат SQL Server. А если для работы требуется всего лишь некоторые данные
из другой базы данных, достаточно создать связь с таблицей или использовать объект
Connection.

Если вы планируете устанавливать связь с таблицами SQL Server, рекомендуется

создать объект источника данных. Либо же использовать OLE DB и ADO для непо-
средственного указания информации о провайдере (Provider). Эти операции были
описаны в предыдущих главах.

После того как доступ к данным SQL Server обеспечен, работа с такой базой дан-

ных, по большому счету, не будет отличаться от работы с данными Access. Необходи-
мо получить объект Recordset, определить запрос в зависимости от требуемой опе-
рации и открыть набор записей. С этого момента управление данными происходит
так же, как и в Access — с помощью SQL или VBA-кода либо их комбинации.

292 Часть V. Программирование и базы данных Access

Вам необходимо лишь помнить, что SQL Server — другой сервер баз данных, поэтому

текст запросов может несколько отличаться в зависимости от используемой версии SQL

Server. Разобраться с подобными тонкостями поможет хорошая книга по работе с при-

меняемой версией сервера баз данных, эксперименты, и, конечно же, терпение.

Резюме

Да, это был серьезный и продолжительный урок. Вам пришлось много потрудиться.

Впрочем, в этой книге изложены лишь общие концепции программирования на SQL. Ес-

ли вы нуждаетесь в более подробной информации, настоятельно рекомендуем обратиться

к пособиям, специально посвященным SQL, — например, к книге

 (Ryan

Stephens)

 Sams Teach Yourself SQL in 24 Hours.

 SQL — это мощный самостоятельный язык

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

Access, вы предполагаете работать с другими системами, будет лучше воспользоваться спе-

циальными руководствами и фирменной документацией.

В ходе этого занятия вы ознакомились с различными конструкциями одной из ос-

новных команд SQL — SELECT, включая возможности объединения таблиц
(предложение JOIN) и запросов (UNION), а также построения подчиненных (вложенных)
запросов. Мы кратко рассмотрели средства добавления (INSERT), обновления (UPDATE)
и удаления (DELETE) данных. Вы научились пользоваться предложением WHERE
(поддерживаемым почти во всех названных командах) для фильтрации данных.

Механизмы создания и использования хранимых процедур весьма серьезны и за-

служивают особого внимания. Теперь, благодаря объектам ADO, доступ к ним полу-

чили и программисты, работающие с Access. Достаточно хорошо освоить базовые кон-
струкции SQL — и хранимые процедуры станут вашим надежным помощником.

А теперь сделайте еще одно усилие и ознакомьтесь с оставшимися разделами этой

главы.

Вопросы и ответы

Вопрос. Действительно ли SQL, как и VBA, обладает собственным набором служеб-

ных слов и синтаксических правил?

Ответ.

 Да. Все языки (и языки программирования в том числе) обладают собст-

венным словарем и грамматикой. Словарь перечисляет допустимые (значимые) слова,

а грамматика диктует правила их употребления. Теперь вы знакомы уже с двумя язы-
ками программирования (речь не идет о более опытных читателях). Осталось лишь
научиться с их помощью понятно выражать свои мысли.

Вопрос.

 Существуют

 ли какие-либо ограничения, связанные с объемом единого запро-

са на языке SQL?

Ответ.

 Нет. Вы можете создавать настолько сложные и широкие запросы, насколь-

ко это необходимо и целесообразно. Но SQL — это, как говорят, "монолитный" язык.

Каждый запрос всегда содержит только одно выражение, пусть даже весьма обшир-

ное. Впрочем, и для SQL справедливо общее эмпирическое правило — чем проще,
тем лучше.

Вопрос. Что вы можете сказать о стиле программирования на SQL?

Ответ.

 Правила "хорошего тона" в программировании едины. Не пытайтесь

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

16-й час. Применение языка SQL 293

конструкций SQL и связанных командами на языке

 Этим вы существенно упро-

стите обязанности по дальнейшему развитию и сопровождению программного проекта.

Вопрос. Где я могу найти информацию об определенной версии SQL?

Ответ.

 Посетите Web-сайт ANSI

 либо обратитесь за лите-

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

Задания

Ниже приведены тестовые задания и упражнения, которые помогут вам

тельно проверить уровень освоения материала, изложенного в этой главе. Ответы на

вопросы вы найдете в приложении.

Тесты

1. Что такое подчиненный (вложенный) запрос?

2. С какой целью употребляется служебное слово ALL, используемое совместно с

UNION?

3. Можно ли применять вложенный запрос в контексте команды DELETE? Если да, то

зачем?

4. Ограничиваются ли возможности хранимых процедур использованием команды

SELECT?

1. Напишите предложение WHERE с предикатом IN, позволяющее отобрать все записи

таблицы MUSIC, в поле PUBLISHER которых содержатся значения Elektra или Em-
pire.

2. Исправьте команду UPDATE, приведенную в строке 2 листинга 16.6, таким образом,

чтобы в верхний регистр были переведены первые символы имени и фамилии ис-

полнителя.

3. Создайте хранимую процедуру, позволяющую удалить из таблицы MUSIC запись по

заданному значению поля LAST NAME.

294 Часть V. Программирование и базы данных Access