Вызов функций из команд SQL

В листинге 16.7, рассмотренном выше, были приведены примеры обращения и к стандартным функциям Access VBA (скажем, UCase), и к пользовательским функциям, онределенным в прикладной программе (таким как ICap). Если создаваемый SQL-код проявляет тенденцию к усложнению, попробуйте воспользоваться существующими функциями или написать собственные, чтобы упростить выражение SQL.

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

SQL предлагает цел1й ряд встроеннгх функций - достаточно назвать COUNT, предназначенную для подсчета числа записей, или SUM - для суммирования числовых значений. Примеры использования некотор1х функций ранее уже приводились в листингах этой главы (скажем, листинг 16.3 демонстрирует способы применения функций SUM и CDATE).

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

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

Хранимые процедуры

С появлением средств ActiveX Data Objects арсенал программиста, использующего Access, пополнился возможностями создания и применения хранимых процедур. Хранимая процедура в SQL равнозначна функции. Аппарат хранимых процедур в таких

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

программирования под названием PL/SQL.)

Среди многочисленных достоинств, присущих хранимым процедурам, достаточно отметить два основных: хранимые процедуры выполняются на серверах баз данных и позволяют определять интерфейсы. Понятно, что при запуске процедур на сервере (который, как правило, намного более производителен по сравнению с персональными станциями) прикладная программа будет выполняться существенно быстрее. Этот вывод справедлив даже в том случае, если Access 2002 работает на том же компьютере, что и ваше приложение, поскольку хранимые процедуры выполняются непосредственно ядром Access. А именованные интерфейсы обеспечивают настолько же простые

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

Процедуры хранятся в базе данных, в коллекции Procedures, входящей в состав

объекта Catalog. Каждый элемент коллекции Procedures - это объект класса Procedure. Объект Procedure содержит атрибуты DateCreated, DateModified, Name и Command. Command - это объект данных, содержащий собственно код тела процедуры.

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

сейчас вам вовсе не обязательно - доступ к процедурам вы получаете с

помощью коллекции Procedures объекта Catalog. Прежде чем восполь-

Т зоваться процедурой, ее необходимо создать и добавить в объект Catalog, о Ниже приведен синтаксис определения хранимой процедуры.

▼ PARAMETERS [Параметр!] Тип {, [Нараметр2] Тип, ...}; А Текст SQE

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

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

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

1: PARAMETERS [APublisher] TEXT;

2: SELECT First Name + + Last Name As Artist, Title,

Format: Publisher 3: FROM Music WHERE Publisher = [APublisher]

5: PARAMETERS [APublisiier] TEXT, [AFormat] TEXT;

6: SELECT First Name + + Last Name As Artist, Title,

Format, Publisher 7: FROM Music

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

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

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

таблицы MUSIC, содержимое поля PUBLISHER которых равно значению параметра. Текст второй процедуры приведен в строках 5-8. Она выполняет схожую операцию, но работает уже с двумя аргументами ([APublisher] и [AFormat]), задающими искомые значения полей PUBLISHER И FORMAT.

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

Чтобы хранимая процедура стала доступной для использования, ее следует добавить в базу данных с помощью свойств и методов объектов ADODB. Command и ADOX.Catalog. Собственно, многие из конструкций и выражений VBA, необходимых для выполнения такой операции, вам уже знакомы. Листинг 16.10 содержит простой

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

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

3 4 5

7 8 9 10

Su dProcedure ( )

Dim Connection As ADODB.Connection Set Connection = urrentProject.Connection Dim Command As New ADODB.Command Dim Catalog As New ADOX.Catalog Se WveConnection = Connection Command.CommandText = PARAMETERS [APublisher] SEDECT ARTIST, TITER, FORMAT, & PUBDISHER EROM Music WHERE Publisher = : Set Catalog.ActiveConnection = Connection

TEXT;

; APublisher]

11: Call Catalog.Procedures.Append!

12 : Set Command = Nothing

13; Set Cataiog = Nothing

14: Set Connection = Nothing

15: End Sub

Artist By Publisher , Command )

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

Сначала необходимо добавить ссылку на Microsoft ADO Ext, 2.7 for DEE в диалоговом окне Tools=>Options редактора Visual Basic. Чтобы решить задачу, следует открыть соединение с базой данных, в данном случае мы переместили фокус на CurentProj ect. Connection - соединение с активной базой данных. В строке 4 создается объект Command, предназначенный для занесения в него текста хранимой процедуры. В качестве непосредственного исполнителя основных действий по сохранению процедуры в базе данных выступает объект класса Catalog - он создается в строке 5. В строках 7-10 последовательность символов, содержащая текст процедуры, присваивается атрибуту CoromandText объекта Command. В строке 11 выполняется операция сохранения процедуры под именем Artist By Publisher в базе данных, а ниже осуществляются действия по очистке памяти.

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

процедуре. В принципе, сделать это достаточно один раз, в самом начале программы, а закрывать и удалять объекты целесообразно непосредственно перед ее завершением. Подробные сведения по таким вопросам изложены в главе 19-й час. Создание экранных форм .

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

Теперь, когда хранимая процедура создана, вы можете обращаться к ней в любой момент по мере надобности. Чтобы выполнить процедуру, следует воспользоваться методом Execute класса Command. Если хранимая процедура возвращает некий набор данных (как в нашем случае), надлежит присвоить результат выполнения метода Execute переменной класса ADODB.Recordset. Если же хранимая процедура выполняет команды SQL (такие как INSERT, DELETE или UPDATE), не возвращающие значений или наборов данных, объект Recordset не нужен.

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

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

3 . 4: 5: 6: 7 :

Sub ExecuteProcedure( )

Dim Connection As ADODB.Connection

Set Conneotion = CurrentProject.Connection

Dim Cataiog As New ADOX.Catalog

Set = Connection

Dim Command As ADODB.Command

Set Command = Catalog.Procedures( Artist By Publisher ).Command