Хранимые процедуры (запросы)

     Хранимая процедура (представление или запрос) равнозначна пара­метри­ческому запросу, но хранится в базе данных (например, в Access она хранится на странице зап­ро­­сов) и выполняется на серверах баз данных, что повышает скорость их выполнения. Такие процедуры хранятся в коллек­ции Catalog.Procedures. Объект Procedure имеет свойства: DateCreated, DateModified, Name, Command   (код процедуры).

Хранимая процедура имеет вид:

Parameters <список параметров и их типов через запятую>

<текст команды SQL>

     Параметр записывается в виде: [<имя параметра>] <тип параметра>.

     Пример создания хранимой процедуры “Два заказчика“ с двумя параметрами “Заказчик1” и “Заказчик2”.

Private Sub Command4_Click() ‘ создание хранимой процедуры

Dim Connection As New ADODB.Connection

Dim Catalog As New ADOX.Catalog, Command As New ADODB.Command

Connection.Open (“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=“ & _

App.Path & “\строительство.mdb“)                ‘подключение и открытие базы

Command.CommandText=“Parameters [Заказчик1] Text,[Заказчик2]Text;“& _

“Select * From [Заказчики] Where Nz=[Заказчик1] or Nz=[ Заказчик2]“

Set Catalog.ActiveConnection = Connection

Catalog.Procedures.Append “Два заказчика“,Command ‘сохранить процедуру

Set Command = Nothing: Set Catalog = Nothing ‘удаление

Connection.Close: Set Connection = Nothing      ‘закрытие и удаление объектов

End Sub

     Пример выполнения хранимой процедуры “Два заказчика“

Private Sub Command5_ Click() ‘выполнение хранимой процедуры

Dim Connection As New ADODB.Connection, Recordset As Recordset

Dim Catalog As New ADOX.Catalog, Command As New ADODB.Command

Connection.Open (“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=“ & _

                          App.Path & “\строительство.mdb“)

Set Catalog.ActiveConnection = Connection

‘загрузка текста хранимой процедуры “Два заказчика“ и ее выполнение

Set Command = Catalog.Procedures(“Два заказчика“).Command ‘загрузить

Set Recordset=Command.Execute(,Array(“Уралмаш“,“Химмаш“)) ‘выполнить

Recordset.MoveFirst                    ‘переход на первую запись таблицы

Do While Not Recordset.EOF      ‘цикл чтения записей

For Each field In Recordset.Fields ‘цикл вывода полей текущей записи

     Debug.Print field.Name & “ = “ & field.Value; “ “; ‘вывод на принтер

Next                                            ‘конец цикла

Recordset.MoveNext: Debug.Print ‘переход на следующую запись

Loop                                               ‘конец цикла

Recordset.Close: Set Recordset = Nothing: Set Command = Nothing

Set Catalog = Nothing: Connection.Close: Set Connection=Nothing ‘закрытие

End Sub

Отключение наборов данных

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

     Пример формирования списка наименований заказчиков в объекте ListBox (List1) из таблицы “Заказчики”.

Private Sub Command6_Click()                 ‘заполнение списка

Dim Connection As New ADODB.Connection ‘создание объекта Connection

Dim Recordset As New ADODB.Recordset ‘создание объекта Recordset

Dim Recordset2 As New ADODB.Recordset ‘создание объекта Recordset2

Connection.Provider = “Microsoft.Jet.OLEDB.4.0“ 'СУБД Access

Connection.ConnectionString = App.Path & “\строительство.mdb“ ‘база

Connection.Open                                           'подключение и открытие базы

Recordset.CursorLocation = adUseClient     ‘клиентский курсор

Recordset.CursorType = adOpenForwardOnly ‘перемещение только вперед

Recordset.LockType = adLockReadOnly     ‘только чтение набора

‘RecordSet.LockType=adLockBatchOptimistic ‘пакетная блокировка

‘RecordSet.CursorType=adOpenDynamic    ‘корректировка набора

Recordset.Open “Заказчики“, Connection    'открытие набора

Set Recordset.ActiveConnection=Nothing ‘отключение набора от сервера

Set Recordset2 = Recordset ‘возвращение набора Recordset в Recordset2

Set Recordset = Nothing     ‘удаление объекта Recordset

List1.Clear                           ‘очистка списка List1

Do While Not Recordset2.EOF ‘цикл чтения набора Recordset2

List1.AddItem Recordset2(“Nz“) ‘дополнение списка значением поля Nz

Recordset2.MoveNext        ‘переход к следующей записи набора Recordset2

Loop                                      ‘конец тела цикла чтения записей

‘SaveOffRecordSet Recordset2, Connection ‘пакетное обновление набора

Recordset2.Close: Set Recordset2 = Nothing ‘закрытие и удаление объектов

Connection.Close: Set Connection = Nothing ‘закрытие и удаление объектов

End Sub

     Обновление отключенного набора

     Обновляемый набор должен иметь свойства:

RecordSet.LockType=adLockBatchOptimistic ‘пакетная блокировка

RecordSet.CursorType=adOpenDynamic   ‘корректировка набора

     После корректировки отключенного набора следует подключиться к серверу и обновить ранее отключенный набор.

     Пример типовой процедуры обновления отключенного набора. Про­це­дура имеет два па­ра­метра: отключенный объект Recordset и объект свя­зи Connection. За­да­ние свойств RecordSet и обращение к процедуре по­ка­за­но в предыдущем при­ме­ре в виде строк с комментариями.