Динамічні 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:



Процес отримання результатів розіб’ємо на дві частини: побудова тексту SQL-запиту і, власне, його виконання. Можна оформити це як дві процедури, що зберігаються, можна як одну – хай розробник сам вирішує. Текст SQL-запиту можна формувати як в один рядок, так і у вигляді колекції – на випадок, якщо текст виявиться занадто довгим. В нашому випадку будемо використовувати колекцію – не дивлячись на те, що довжина тексту запиту буде невеликою. Навіщо? А просто так, для прикладу.

Домовимося також, що в збережену процедуру будуть передаватися наступні параметри, що управляють пошуком:






Якщо як якого-небудь з параметрів передано значення 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;


Сподіваюся, основні ідеї зрозумілі?
Далі – самі ……

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*