Динамические SQL-запросы Oracle для ускорения выборок данных

Материал из DRKB

Динамические SQL-запросы Oracle для ускорения выборок данных[править | править код]

Типичная задача при работе с базами данных - выбрать информацию из разных таблиц, отфильтровать ее по определенным критериям, потом обработать и/или выдать пользователю для просмотра и визуального анализа. Если параметры отбора записей имеются в наличии и определены - эта задача решается тривиально, с помощью обычного оператора SQL "SELECT .. FROM .. WHERE" - где набор условий, располагаемых после WHERE, всегда определен. Однако, бывают случаи, когда набор параметров отбора данных определяется только перед самим отбором - а изначально, во время проектирования программы, не известен.

Например, надо выбрать клиентов, “засветившихся” в базе данных торговой фирмы за определенный срок; или сделавших покупки на сумму больше некоторой заданной. Или приходится искать конкретного человека, используя частично известные анкетные данные.. Ситуация усложняется еще больше, если для определения, какие записи нужно выбрать, а какие нет, надо вызывать какую-нибудь функцию, реализующую сложные и ресурсоёмкие вычисления. Разумеется, эту функцию без необходимости лучше в обработку не включать.

Все перечисленные проблемы можно решить с помощью динамического SQL. Динамический SQL позволяет строить текст запроса непосредственно внутри кода PL/SQL - и затем выполнять его. Соответственно, разработчик может построить текст запроса, включая в него только необходимые, задействованные в текущий момент условия (случай, когда текст SQL-запроса может быть сформирован внутри клиентского приложения, рассматривать не будем - всегда существуют ситуации, когда этого нельзя сделать по каким-нибудь причинам). За работу с динамическими SQL-запросами отвечает пакет dbms_sql. В общем, работа с ним происходит по следующей схеме.

  1. Строится сам текст запроса с метками для параметров. Текст запроса может быть представлен в виде строки или коллекции строк.
  2. Функцией dbms_sql.open_cursor выделяется идентификатор курсора, который будет использоваться для работы с запросом. Идентификатор ссылается на внутреннюю структуру oracle, определяющую курсор. Этот идентификатор используется процедурами пакета dbms_sql.
  3. Выполняется разбор текста запроса. dbms_sql.parse.
  4. Устанавливаются значения параметров запроса. dbms_sql.bind_variable.
  5. Если запрос возвращает данные, то определяются столбцы и буферные переменные, в которых будут размещаться возвращаемые данные. dbms_sql.define_column.
  6. Запрос выполняется. dbms_sql.execute.
  7. Если запрос возвращает данные, то производится выборка данных из курсора и необходимая их обработка. dbms_sql.fetch_rows, dbms_sql.column_value.
  8. Курсор закрывается. dbms_sql.close_cursor.

Ниже мы рассмотрим пример использования динамического SQL для поиска человека по (неполным) анкетным данным.

В начале определимся с используемыми структурами данных.

CREATE TABLE personparticulars(
id NUMBER(9) CONSTRAINT pk_personparticulars PRIMARY KEY NOT NULL,
family VARCHAR2(32) CONSTRAINT pp_chk_family NOT NULL,
firstname VARCHAR2(16) CONSTRAINT pp_chk_firstname NOT NULL)
TABLESPACE x;

Поля таблицы personparticulars:

  • id - уникальный номер анкетных данных
  • family - фамилия
  • firstname - имя
  • middlename - отчество

Процесс получения результатов разобьем на две части: построение текста SQL-запроса и, собственно, его выполнение. Можно оформить это как две хранимые процедуры, можно как одну - пусть разработчик сам решает. Текст SQL-запроса можно формировать как в одну строку, так и в виде коллекции - на случай, если текст окажется слишком длинным. В нашем случае будем использовать коллекцию - несмотря на то, что длина текста запроса будет небольшой. Зачем? А просто так, для примера.

Условимся также, что в хранимую процедуру будут передаваться следующие параметры, управляющие поиском:

  • familyfilter - шаблон для поиска по фамилии
  • firstnamefilter - шаблон для поиска по имени
  • middlenamefilter - шаблон для поиска по отчеству

Если в качестве какого-либо из параметров передано значение null - этот параметр при поиске игнорируем. Результаты поиска вернем в виде таблицы в памяти. Для простоты - это будут просто номера найденных людей (значения их id).

CREATE OR REPLACE PROCEDURE searchperson(
  familyfilter IN VARCHAR2,
  firstnamefilter IN VARCHAR2,
  middlenamefilter IN VARCHAR2,
  result IN OUT dbms_sql.varchar2s) IS
sqltext dbms_sql.varchar2s; /* Текст запроса */
whereclause dbms_sql.varchar2s; /* Часть … where… */
i integer; /* Счетчик */
c integer; /* Идентификатор курсора */
b_id number; /* Буферная переменная для результатов */
BEGIN
  whereclause(1):=true ;
  IF familyfilter IS NOT NULL THEN
    whereclause(whereclause.last+1):= and family like :xfamilyfilter;
  END IF;
  IF firstnamefilter IS NOT NULL THEN
    whereclause(whereclause.last+1):= and firstname like :xfirstnamefilter;
  END IF;
  IF middlenamefilter IS NOT NULL THEN
    whereclause(whereclause.last+1):= and middlename like :xmiddlenamefilter;
  END IF;
  /* На этом этапе у нас имеется часть запроса - where, в которой упомянуты только те условия, которые были заданы через непустые параметры хранимой процедуры */
  /* Теперь построим текст запроса полностью */
  sqltext(1):=select id;
  sqltext(2):=from personparticulars;
  FOR i IN whereclause.first..whereclause.last LOOP
    sqltext(sqltext.last+1):=whereclause(i);
  END LOOP;
  /* Получаем идентификатор курсора */
  c:=dbms_sql.open_cursor;
  /* Разборка текста запроса */
  dbms_sql.parse(c, sqltext, sqltext.first, sqltext.last, false, dbms_sql.native);
  /* Установка параметров запроса */
  IF familyfilter IS NOT NULL THEN
    dbms_sql.bind_variable(c,:xfamilyfilter,familyfilter);
  END IF;
  IF firstnamefilter IS NOT NULL THEN
    dbms_sql.bind_variable(c,:xfirstnamefilter,firstnamefilter);
  END IF;
  IF middlenamefilter IS NOT NULL THEN
    dbms_sql.bind_variable(c,:xmiddlenamefilter,middlenamefilter);
  END IF;
  /* Установка столбцов в запросе */
  dbms_sql.define_column(c,1,b_id);
  /* Выполнение запроса */
  dbms_sql.execute(c);
  /* Выборка результатов запроса */
  LOOP
    /* Выбираем следующую строку */
    IF dbms_sql.fetch_rows(c)>0 THEN
      dbms_sql.column_value(c,1,b_id);
      /* В этот момент в переменной b_id имеем текущее значение id очередной строки. Что с ней делать, уже дело разработчика */
    ELSE
      exit; /* Если нет больше строк, вываливаемся */
    END IF;
  END LOOP;
  /* Закрываем курсор */
  dbms_sql.close_cursor(c);
END;


Надеюсь, основные идеи понятны? Дальше - сами :)

Использованная литература: oracle8 application developer’s guide © oracle corporation


Author: Андрей Фионик
Source: http://doc.woweb.ru
ID: 02719