Динамические 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
Источник: www.web-support.ru
|
