ПОИСК И ВЫБОРКА ЗАПИСЕЙ

Одной из основных задач при работе с таблицами является поиск и выборка записей удовлетворяющих определенным условиям.

find01.jpg


ПОИСК ЗАПИСИ (ОПЕРАТОР FIND)


Оператор FIND осуществляет поиск записи в таблице удовлетворяющей определенным условиям.

FIND [FIRST | NEXT | LAST | PREV] record
     [constant]
     [OF table]
     [WHERE search-condition]
     [USE-INDEX index-name]
     [USING [FRAME frame] field
       [AND [FRAME frame] field] ...
     ]
     [SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK]
     [NO-WAIT]
     [NO-PREFETCH]
     [NO-ERROR].
  • record – идентификатор буфера таблицы, в которой осуществляется поиск;
  • tabel - идентификатор связанной таблицы;
  • search-condition – условие поиска;
  • index-name  идентификатор индекса.

Оператор FIND в чистом виде осуществляет поиск однозначной (единственной) записи в таблице, удовлетворяющей указанным условиям поиска. Такой поиск записи осуществляется по уникальному индексу (UNIQUE) таблицы. В случае, если в таблице имеется две и более записи, или ни одной записи удовлетворяющих указанному условию поиска, такой поиск завершается ошибкой.

FIND acct WHERE acct.acct = "42301810000000000001".

Если в таблице имеется более одной записи, удовлетворяющих условию поиска, и все же необходимо получить одну из них, то потребуется каким-то образом определить, какую именно запись из удовлетворяющих нашему условию поиска мы хотим получить. Например, нас может интересовать первая найденная запись в таблице удовлетворяющая условию поиска, или же наоборот последняя. Для этого оператор FIND необходимо использовать с [FIRST | NEXT | LAST | PREV].

  • FIRST – ищет первую запись удовлетворяющую условию поиска начиная с первой записи в таблице;
  • NEXT – продолжает поиск следующей записи в таблице удовлетворяющей условию поиска;
  • LAST – находит последнюю запись в таблице удовлетворяющей условию поиска;
  • PREV – ищет предыдущую запись от текущей удовлетворяющую условию поиска.

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

FIND FIRST person WHERE person.birthday < 01/01/1990 
NO-LOCK NO-ERROR.

Данный оператор осуществляет поиск по таблице person (физические лица) первой записи физического лица с датой рождения (person.birthday) ранее 01/01/1990. Очевидно, что таких клиентов будет несколько, запись какого клиента будет возвращена?

Для реализации данного запроса AVM будет определен индекс, по которому записи таблицы будут предварительно отсортированы. После чего, будет выбрана первая удовлетворяющая запросу запись. От того, какой именно индекс будет применен для реализации запроса зависит его результат. С помощью опции USE-INDEX index-name разработчик может явно указать какой индекс необходимо применить.

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

FIND FIRST loan WHERE loan.close-date < TODAY NO-LOCK.
DISPL loan.contract loan.cont-code.

В результате, будет возвращен не закрытый, как ожидалось, а действующий договор.

FIND LAST acct WHERE acct.cust-cat EQ "Ч"
AND acct.cust-id EQ 1
AND CAN-DO("423*,426*", acct.acct) 
                NO-LOCK NO-ERROR.

Данный оператор осуществляет поиск последнего счета физического лица, id которого равно 1, соответствующего маске 423*,426*.

FIND NEXT и FIND PREV осуществляют поиск последующей и предыдущей первой записи, удовлетворяющей условию поиска, относительно записи находящейся в буфере записи. Если буфер записи таблицы пуст, то поиск осуществляется с первой записи таблицы для FIND NEXT и с последней для FIND PREV.

FIND FIRST acct WHERE acct.currency EQ "840"
NO-LOCK NO-ERROR.

FIND NEXT acct WHERE acct.acct BEGINS "40817"
NO-LOCK NO-ERROR. 

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

FIND FIRST op WHERE op.op-date EQ TODAY NO-LOCK NO-ERROR.
FIND FIRST op-entry OF op NO-LOCK NO-ERROR.   

В данном примере осуществляется поиск первой записи в таблице op (документы) с датой документа равной текущей. Далее осуществляется поиск первой связанной с записью найденного документа записи в таблице op-entry. Связь таблиц op и op-entry осуществляется по полю op. С помощью WHERE выражения данный запрос может быть записан следующим образом:

FIND FIRST op WHERE op.op-date EQ TODAY NO-LOCK NO-ERROR.
FIND FIRST op-entry WHERE op-entry.op = op.op NO-LOCK NO-ERROR.                                         

В случае, если удовлетворяющая условию поиска запись была найдена, то она помещается в буфер записи и становится доступной для чтения, редактирования и удаления. В противном случае возникает ошибка поиска и программное прерывание. Опция NO-ERROR отключает такую реакцию AVM на отрицательный результат поиска.

Обработать подобную ситуацию можно с помощью функции AVAILABLE, которая возвращает значение TRUE, если запись найдена и FALSE, если записи удовлетворяющей условию поиска не удалось обнаружить.

FIND FIRST person WHERE person.person-id = 0 NO-ERROR. 
IF NOT AVAILABLE person THEN
MESSAGE "Запись не найдена".

Как мы сказали в начале, искомая с помощью FIND без опций FIRST, NEXT, PREV или LAST запись, может оказаться недоступной по двум причинам: когда запись не найдена и когда в таблице имеется две и более записей, удовлетворяющих условию поиска.

Отличить эти ситуации можно с помощью функции AMBIGUOUS, возвращающей логическое значение TRUE в случае когда FIND завершился с ошибкой по причине наличия двух и более записей.

AMBIGUOUS record

где, record - буфер записи используемый в операторе FIND.

FIND acct WHERE acct.acct = "42301810000000000001" NO-ERROR.
IF NOT AVAILABLE person THEN
IF AMBIGUOUS acct THEN
MESSAGE "Запись не уникальна".
ELSE MESSAGE "Запись не найдена".


ВЫБОРКА ЗАПИСЕЙ (ОПЕРАТОР FOR)


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

[label:]
FOR
[EACH | FIRST | LAST] record
   [OF table]
  
[WHERE search-condition]

   [USE-INDEX index-name]
   [SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK]

   [BREAK]
   [BY field [DESCENDING]
      | COLLATE (string, strength [, collation]) [DESCENDING]
]
   [, [EACH | FIRST | LAST]
record]
      [OF table-name]
     
[WHERE search-condition]
      [USE-INDEX index-name]
      [SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK]

      [BREAK]
      [BY field [DESCENDING]
        | COLLATE (string, strength [, collation]) [DESCENDING]
   ] ...
   [variable = expression1 TO expression2 [BY k]]
   [WHILE expression]
   [TRANSACTION]
   [STOP-AFTER expression]
   [on-error-phrase]
   [on-endkey-phrase]
   [on-quit-phrase]
   [on-stop-phrase]
   [frame-phrase] : 

   for-body


END.

  • record – идентификатор буфера таблицы, по которой осуществляется поиск;
  • tableидентификатор буфера связанной таблицы;
  • search-condition – условие поиска;
  • index-name  идентификатор индекса;

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

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

FOR EACH acct NO-LOCK:
   DISPL acct.acct.
END.

Данный запрос осуществляет вывод на экран всех записей таблицы acct.

Обработка отобранной записи осуществляется в теле блока, где она автоматически помещается в буфер записи. По завершению обработки записи осуществляется поиск следующей удовлетворяющей условию поиска записи и ее обработка и так далее.

FOR EACH acct WHERE acct.currency = "840" NO-LOCK:
   DISPL acct.acct.
END.

Данный запрос осуществляет последовательный перебор и вывод на экран всех долларовых счетов из таблицы acct (счета).

Отбираемые записи могут быть предварительно упорядочены по каким-либо полям. Для этого используется фраза BY field, где field - имя поля по которому будет осуществляться упорядочивание записей. При этом поля указываемые в BY не могут быть BLOB или CLOB типов.

FOR EACH person BY person.name-last:
   DISPLAY person.name-last.
END. 

Отобранные записи будут упорядочены по полю name-last (фамилия) в алфавитном порядке, причем записи будут упорядочены по возрастанию (т.е. от А до Я). Для сортировать записи по убыванию необходимо указать DESCENDING.

FOR EACH person BY person.name-last DESCENDING:
   DISPLAY person.name-last.
END.

Отобранные записи будут упорядочены по полю name-last по убыванию (т.е. от Я до А).

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

FOR EACH loan-acct WHERE loan-acct.contract EQ "dps"
                   NO-LOCK
                   BREAK BY loan-acct.cont-code
BY loan-acct.acct-type
BY loan-acct.since:
   IF FIRST-OF (loan-acct.cont-code) THEN
      DISPLAY loan-acct.cont-code
              loan-acct.acct
              loan-acct.acct-type
loan-acct.since.
   ELSE
      DISPLAY loan-acct.acct
              loan-acct.acct-type
loan-acct.since.
END.

В нашем примере осуществляется выборка счетов привязанных к договорам частных вкладов. При этом отбираемые счета будут первоначально упорядочены и объедены в блоки по полю  loan-acct.cont-code (номер договора). Внутри каждого этого блока счета будут упорядочены и разбиты на блоки по полю loan-acct.acct-type (роль счета), а внутри этого блока упорядочены и разбиты на блоки по полю loan-acct.since (дата привязки счета к договору).

Функция FIRST-OF (break-field) возвращает TRUE для первой записи в блоке break-field, а функция LAST-OF (break-field) для последней записи в блоке.

FOR [FIRST|LAST], осуществляет обработку единичной первой или последней записи соответственно, удовлетворяющей заданному условию поиска. 

Отличием FOR [FIRST|LAST] от FIND[FIRST|LAST] является тот факт, что в случае отсутствия искомой записи FOR [FIRST|LAST] не генерирует ошибки.

Таким образом, конструкция:

FOR FIRST person:
DISPLAY person.first-names.
END.

аналогична:

FIND FIRST person NO-ERROR.
IF AVAIL person THEN
DO:
   DISPLAY person.first-names.
END.

Как мы можем видеть, конструкция FOR более компактная по сравнению с FIND, следовательно более удобочитаемая. В тоже время она уступает по быстродействию оператору FIND.


ЗАПРОС (QUERY)


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

Для использования QUERY он должен быть предварительно объявлен.

DEFINE {[[NEW] SHARED] | [PRIVATE | PROTECTED] [STATIC]}
  QUERY query
  FOR buffer-name [field-list] [, buffer-name [field-list]] ...
  [CACHE n]
  [SCROLLING]
  [RCODE-INFORMATION]

  • query – идентификатор QUERY;
  • buffer-nameидентификатор буфера;
  • field-list список полей.

Условие поиска определяется в операторе OPEN QUERY.

OPEN QUERY query FOR EACH buffer-name WHERE seach-condition.

Доступ к данным QUERY осуществляется посредством оператора GET, который используется совместно с FIRST, LAST, NEXT, PREV.

GET [FIRST | LAST | NEXT | PREV ] query.

  • FIRST — первая запись списка QUERY;
  • LAST — последняя запись списка QUERY;
  • NEXT — следующая за текущей записью QUERY;
  • PREV — предыдущая от текущей запись QUERY.

Основным преимуществом QUERY является его доступность по его идентификатору в любое время выполнения кода процедуры. Т.е. список QUERY находится в буфере до тех пор пока не будет переопределен.

DEF QUERY qPerson FOR person.
OPEN QUERY qPerson FOR EACH person WHERE person.person-id < 10 NO-LOCK.

GET FIRST qPerson.
DISPL person.person-id person.name-last person.first-names.

PAUSE (10).

GET NEXT qPerson.
DISPL person.person-id person.name-last person.first-names.

В приведенном примере осуществляется формирование запроса с идентификатором qPerson по таблице person, в которой отбираются все записи клиентов с id < 10. Далее, с помощью оператора GET FIRST осуществляется помещение курсора на первую отобранную запись физ. лица, после чего она автоматически помещается в буфер записи и становится доступной для работы с ней. После вывода на экран первой записи идет 10 секундная пауза. По окончанию паузы осуществляется перемещение курсора на следующую запись запроса и вывод ее на экран.

При работе с QUERY могут использоваться следующие функции:

NUM-RESULTS (query)  - возвращает количество отобранных в запросе записей;

QUERY-OFF-END (query) - возвращает логическое значение TRUE если достигнут конец запроса, в противном случае возвращает FALSE.

DEF QUERY qPerson FOR person.
OPEN QUERY qPerson FOR EACH person WHERE person.person-id < 10 NO-LOCK.

GET FIRST qPerson.

REPEAT WHILE NOT QUERY-OFF-END("qPerson"):
   DISPL person.person-id person.name-last person.first-names.
   GET NEXT qPerson.
END.
CLOSE QUERY qPerson.

По завершению работы с QUERY его необходимо закрыть:

CLOSE QUERY query

При необходимости, закрытый QUERY может быть заного переоткрыт оператором OPEN QUERY.


РЕАЛИЗАЦИЯ ЗАПРОСОВ


Выполняя запрос, AVM постарается реализовать его с наименьшим числом обращений в базу данных. Для этого, перед тем как выполнить запрос, AVM осуществляет разбор WHERE выражения и определяет подходящие по его мнению индексы для оптимального выполнения запроса. Выбор индекса осуществляется на основе наличия в WHERE индексируемых полей (полей входящих в состав индексов) применяемых с операторами сравнения. Разработчик имеет возможность явно указать AVM, какой индекс необходимо применить для запроса с помощью опции USE-INDEX.

При выборе индексов компилятор руководствуется следующими приоритетами:

  1. Индекс, указанный в USE-INDEX;
  2. Индекс, по полям которого, в запросе присутствует больше сопоставлений на равенство;
  3. Индекс, по полям которого, в запросе присутствует больше сопоставлений по диапазону;
  4. Word-index, на который ссылается оператор CONTAIN;
  5. Индекс с наибольшим количеством сопоставлений по сортировке;
  6. PRIMARY индекс;
  7. Первый по алфавиту индекс.

Узнать какой индекс будет использован AVM можно из файла XREF.

Определив индексы, AVM осущесвляет формирование индексных брэкетов (index brackets), представляющих собой совокупности индексных записей. Различают два класса брэкетов equality brackets и range bracket.

equality brackets (брэкеты равенства) - индексные записи с совпадающими полностью или частично значениями. Другими словами, с индексными значениями, определяющими записи с одинаковыми значениями полей составляющими этот индекс.

FOR EACH person WHERE person.name-last = "Иванов" NO-LOCK:
   DISPL person.name-last person.first-names.
END. 

Таблица person имеет индекс name, который составляют поля name-last и name-first. Наш запрос в своем условии сдержит только одно из полей составляющих индекс - name-last, и следовательно индексы отбираемых записей будут совпадать частично.

range bracket (брэкеты диапазона) - определяющие индексные записи в заданном диапазоне.

FOR EACH person WHERE person.person-id >= 1
                  AND person.person-id <= 100
                NO-LOCK:
   DISPL person.name-last person.first-names.
END. 

Данная выборка осуществляется по полю person-id, составляющего индекс person. Результатом данного запроса будет набор из 100 прочитанных записей, индексы которых лежат в определенном диапазоне.

Отберем среди первых 100 клиентов, тех, у кого в адресе присутствует "Москва".

FOR EACH person WHERE person.person-id >= 1
                  AND person.person-id <= 100
               CAN-DO ("*Москва*", person.address)
               NO-LOCK:

      DISPL person.name-last person.first-names.
END.

Для выполнения данного запроса AVM будет использовать индекс person и сформирует по нему брэкет. Из таблицы базы данных "сервером" будут прочитаны 100 записей. Далее эти записи будут переданы "клиенту" для наложения иных условий, т.е. для отбора среди них клиентов, у которых в адресе присутствует "Москва".

Составим запрос, который выбирает среди 100 клиентов, клиентов с фамилией Иванов.

FOR EACH person WHERE person.person-id >= 1
                  AND person.person-id <= 100
                  AND person.name-last = "Иванов"
                NO-LOCK:
   DISPL person.name-last person.first-names.
END. 

Для реализации данного запроса можно взять все записи отобранные по person-id и отобрать среди них клиентов с фамилией Иванов. Можно наоборот, за основу взять выборку клиентов по фамилии (name-last) и отобрать среди них клиентов у которых person-id лежит в заданном диапазоне. Выборка по фамилии может оказаться значительно больше выборки по person-id и следовательно потребуется больше времени на ее обработку. Решение, какой из вариантов использовать, т.е какой применить индекс: name или person, будет принимать AVM. Повлиять на выбор индекса разработчик может с помощью опции USE-INDEX, указав желаемый индекс.

FOR EACH person WHERE person.person-id >= 1
                  AND person.person-id <= 100
                  AND person.name-last = "Иванов"
USE-INDEX person

                NO-LOCK:
   DISPL person.name-last person.first-names.
END. 

Применение USE-INDEX иногда может оказать негативное влияние, так как с помощью данной опции можно указать всего один индекс.

Немного изменим запрос:

FOR EACH person WHERE (person.person-id >= 1
                  AND person.person-id <= 100)
                  OR person.name-last = "Иванов"
                NO-LOCK:
   DISPL person.name-last person.first-names.
END. 

Для данного запроса будет сформировано два брэкета: один для индекса person, второй для индекса name. Если данный запрос осуществлялся бы с опцией USE-INDEX, то независимо от того какой индекс был бы указан, запрос осуществлялся бы полным перебором всех записей таблицы.

FOR EACH person WHERE CAN-DO("*Иванов*", person.name-last)
                NO-LOCK:
   DISPL person.name-last person.first-names.
END. 

Для выполнения этого запроса AVM, согласно приоритетам, будет выбран PRIMARY индекс. Несмотря на то, что PRIMARY индексом является индекс name, сформированный брэкет будет содержать все записи таблицы person, т.е. из базы данных будет прочитана вся таблица person. Далее все записи будут перданы "клиенту" для дальнейшего анализа. Другими словами, такой запрос будет осуществлен путем полного перебора всех записей таблицы person. 

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

 


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

Такая ситуация возможна например, когда отбор записи осуществлялся без ее блокировки, т.е. с опцией NO-LOCK, вследствие чего, запись была доступной для редактирования другим клиентам. Более подробно про блокировки записей см. Блокировки записей.

Для сравнения записи находящейся в буфере записи с записью в таблице ее необходимо предварительно перечитать:

FIND CURRENT record [SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK]
                    [NO-WAIT]
                    [NO-ERROR].

Для конструктора QUERY:

GET CURRENT query-name.

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

CURRENT-CHANGED record

FIND FIRST loan WHERE loan.contract   EQ "Кредит"
AND loan.close-date EQ ?
NO-LOCK.
...

FIND CURRENT loan.
IF CURRENT-CHANGED loan THEN
MESSAGE "Запись была изменена другим пользователем" VIEW-AS ALERT-BOX.
Вы здесь: Главная Основы ABL ПОИСК И ВЫБОРКА ЗАПИСЕЙ