Динамические 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;
Надеюсь, основные идеи понятны?
Дальше – сами :)
Дайджест новых статей по интернет-маркетингу на ваш email
Новые статьи и публикации
- 2024-04-22 » Комментирование кода и генерация документации в PHP
- 2024-04-22 » SEO в России и на Западе: в чем основные отличия
- 2024-04-22 » SEO для международного масштабирования
- 2024-04-22 » Как использовать XML-карты для продвижения сайта
- 2024-04-22 » Цифровой маркетинг: инструменты для продвижения и рекламы в 2024 году
- 2024-04-22 » Что такое CSS-модули и зачем они нам?
- 2024-04-17 » 23 сервиса для эффективного экспресс-аудита любого сайта
- 2024-04-08 » Яндекс переходит на новую версию Wordstat
- 2024-04-08 » Яндекс интегрировал в свой облачный сервис эмпатичную нейросеть
- 2024-04-08 » Новая версия нейросети Claude превзошла по мощности аналоги Google и OpenAI
- 2024-04-08 » Как пользоваться GPT 4 и Claude бесплатно и без VPN
- 2024-03-13 » Стратегии SEO на 2024 год
- 2024-03-13 » Как использовать анимацию с помощью JavaScript-библиотеки GSAP
- 2024-03-13 » Использование GSAP 3 для веб-анимации
- 2024-03-13 » Cогласование топографической съёмки с эксплуатирующими организациями
- 2024-02-19 » Теряются лиды? Как настроить сквозную аналитику
- 2024-02-17 » Мерч и IT: на что обратить внимание в 2024 году
- 2024-02-16 » Копируем с RSync: основные примеры синхронизации файлов
- 2024-02-15 » Лучшие noCode AI платформы для создания диалоговых ботов
- 2024-02-14 » Факторы ранжирования Google 2024 — исследование Semrush
- 2024-02-12 » Перенос сайта на другой хостинг
- 2024-02-05 » В России сформирован реестр хостинг-провайдеров
- 2024-02-04 » Использование SSH для подключения к удаленному серверу Ubuntu
- 2024-02-03 » Подключаемся к серверу за NAT при помощи туннеля SSH. Простая и понятная инструкция
- 2024-02-02 » Настройка CI/CD для Gitlab-репозитория: схемы и гайд по шагам
- 2024-02-01 » GitLab CI Pipeline. Запуск сценария через SSH на удаленном сервере
- 2024-01-29 » Introduction to GitLab’s CI/CD for Continuous Deployments
- 2024-01-26 » Настройка GitLab CI/CD
- 2024-01-25 » Установка shell gitlab runner
- 2024-01-25 » Установка и регистрация gitlab-runner в docker контейнере
Жизнь подобна универмагу: в ней находишь всё, кроме того, что ищешь Кроткий Эмиль - (1892—1963) - русский поэт–сатирик, юморист и афорист |
Мы создаем сайты, которые работают! Профессионально обслуживаем и продвигаем их , а также по всей России и ближнему зарубежью с 2006 года!
Как мы работаем
Заявка
Позвоните или оставьте заявку на сайте.
Консультация
Обсуждаем что именно Вам нужно и помогаем определить как это лучше сделать!
Договор
Заключаем договор на оказание услуг, в котором прописаны условия и обязанности обеих сторон.
Выполнение работ
Непосредственно оказание требующихся услуг и работ по вашему заданию.
Поддержка
Сдача выполненых работ, последующие корректировки и поддержка при необходимости.