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

Хранимая процедура (представление или запрос) равнозначна пара­метри­ческому запросу, но хранится в базе данных (например, в 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

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

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

Примерформирования списка наименований заказчиков в объекте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и обращение к процедуре по­ка­за­но в предыдущем при­ме­ре в виде строк с комментариями.

Public Sub SaveOffRecordSet(rsOff As ADODB.Recordset,Cn As Connection)

Dim Rs As New ADODB.RecordSet 'создание объекта Rs типа RecordSet

'подключение набора (можно не указывать, если набор открывается)

SetRs.ActiveConnection=Cn'подключение набораRs(можно не указывать)

Rs.OpenrsOff,Cn‘повторное открытие набораRs

Rs.UpdateBatch'пакетное обновление набора

End Sub