|
Динамические 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 Developers Guide © Oracle Corporation
www.cybersecurity.ru
Что необходимо знать программисту 13-03-2007
Работа с информацией на предприятиях 21-09-2008 Что необходимо знать программисту
CodeNet / Остальное / Разное Автор: Рудюк С.А., Компания НеРуСофт3 октября 2005 годаhttp://nerusoft.com/Мы постоянно слышим такие слова, как <век информации>, <информационные технологии>, <кто владеет информацией, тот владеет миром> и т.д. и т.п. В наше сознание уже внедрена мысль, что информация - это все.Но, что делать с этой информацией и для чего она, в конечном счете, нужна и вообще, что это?Большинство лю...
Sun запустила проект Kentai для хостинга открытого программного обеспечения 16-09-2008 Что необходимо знать программисту
Компания Sun Microsystems намерена конкурировать с Google в части проектов Google Code или системой GitHub. Производитель ОС Solaris сегодня сообщил о запуске проекта Kenai, цель которого заключается в предоставлении хостинга для открытого программного обеспечения. Первая публичная бета-версия проекта появилась в конце прошлой недели.На серверах проекта Kentai разработчики могут бесплатно размещать свои проекты, причем в Kentai изначально реализо...
Использование шаблонов при программировании WEB-приложений 07-09-2008 Что необходимо знать программисту
(c) Сергей Томулевич, 13.08.2003www.webscript.ruЗачем вообще использовать шаблоны?Может, кто-то скажет, а зачем выносить HTML код в отдельный файл если его можно спокойно писать в скрипте? Да конечно в скрипте можно писать HTML код, но просмотреть полностью <собранную>, или частично <собранную> страницу которую выводит скрипт, возможно только при его нормальной работе, а во время отладки скрипта, как правило, возникают некот...
|
