Большой архив статей, книг, документации по программированию, вебдизайну, компьютерной графике, сетям, операционным системам и многому другому
 
<Добавить в Избранное>    <Сделать стартовой>    <Реклама на сайте>    <Контакты>
  Главная Документация Программы Обои   Экспорт RSS E-Books
 
 

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


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

Ссылки по теме
Так как же восстановить данные таблицы?
Заморочки от Oracle, или знать бы, где упасть
Моделирование групп объектов в Oracle
Не самые известные сведения о внешних ключах
Cегменты отката в СУБД Oracle
 

Компьютерная документация от А до Я - Главная

 

 
Интересное в сети
 
10 новых программ
CodeLobster PHP Edition 3.7.2
WinToFlash 0.7.0008
Free Video to Flash Converter 4.7.24
Total Commander v7.55
aTunes 2.0.1
Process Explorer v12.04
Backup42 v3.0
Predator 2.0.1
FastStone Image Viewer 4.1
Process Lasso 3.70.4
FastStone Image Viewer 4.0
Xion Audio Player 1.0.125
Notepad GNU v.2.2.8.7.7
K-Lite Codec Pack 5.3.0 Full


Наши сервисы
Рассылка новостей. Подпишитесь на рассылку сейчас и вы всегда будете в курсе последних событий в мире информационных технологий.
Новостные информеры. Поставьте наши информеры к себе и у вас на сайте появится дополнительный постоянно обновляемый раздел.
Добавление статей. Если вы являетесь автором статьи или обзора на тему ИТ присылайте материал нам, мы с удовольствием опубликуем его у себя на сайте.
Реклама на сайте. Размещая рекламу у нас, вы получите новых посетителей, которые могут стать вашими клиентами.
 
Это интересно
 

Copyright © CompDoc.Ru
При цитировании и перепечатке ссылка на www.compdoc.ru обязательна. Карта сайта.