Динамические 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;

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