Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
 Динамический SQL. Можно ли обойтись без него.  [new]
Наивный
Guest
Реализую проект. Клиент на дельфи, БД Oracle 9i.

Все операции с БД производятся посредством вызовов процедур, которые хранятся в пакетах.
В том числе и выборки из всех таблиц.
Столкнулся со следующей проблемой.

В форме пользователь может задавать различные параметры для выбора определенных записей.
То есть определять некий фильтр.
Все заданные им значения передаются в ХП в качестве параметров.
Например страна, регион, район.
Причем параметры могут быть и не заданы в форме. В этом случае если какой-либо параметр не задан, значит нужно выбрать все записи.

И вот тут возникает проблема как же в ХП написать условия таким образом, чтобы выборка осуществлялась в зависимости от того, задано ли какое-то значение для параметра или нет.

  procedure SelectRecord (m_country in varchar2,
                                   m_region in varchar2,
                                   m_raion in varchar2
                                   crs out sys_refcursor)
  is
  begin

  if m_country = '' and m_region = '' and m_raion = '' then
        open crs for
        select tbl.*
        from tbl
  end if;

  if m_country <> '' and m_region = '' and m_raion = '' then
        open crs for
        select tbl.*
        from tbl
        where country = m_country
  end if;

  ...
  
  Ну и так далее


То есть приходится проверять все возможные комбинации параметров.
Что довольно таки тупо на мой взгляд.

А можно ли как то по другому решить данную задачу.
Или без динамического SQL никак?

ПыСы.
Способ формирования строки запроса на клиенте не подходит. Вся работа с БД должна осуществляться через ХП (пакеты). Таково требование. :(
2 июл 07, 13:09    [4338029]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
Наивный
А можно ли как то по другому решить данную задачу.
Или без динамического SQL никак?
ПыСы.
Способ формирования строки запроса на клиенте не подходит. Вся работа с БД должна осуществляться через ХП (пакеты). Таково требование. :(

1) Подход организационный: данное требование выдвинул заказчик? Тогда шлите лесом - заказчик волен выставлять требования к функционалу/производительности/надежности, а не к имплементации.
Но шлите, ессно, не впрямую - просто попросите обосновать. Тогда появится предмет для дискуссии, в ходе которой можно убедить клиента, что в данном конкретном месте этим конкретным требованием лучше поступиться ради функционала.

2) Подход кодерский: если ничто не помогает но очень хочется, то можно формулировать условия в стиле:
where (:1 is null or :1=alias.field)
  and (:2 is null or alias.strfield like :2)
...
Однако в плане оптимизации запроса подобные предикаты далеко не фонтан.

3) Подход взвешенный: хорошая новость: если фантазия пользователей не безгранична (а она далеко не безгранична), то, если будете использовать bind variables в своем dynamic-sql решении, то под рабочей нагрузкой очень быстро образуется пяток-шесток наиболее типовых курсоров и shared pool скорее всего выживет.
2 июл 07, 13:33    [4338227]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
oragraf
Member

Откуда: Moscow
Сообщений: 1314
Наивный
  if m_country = '' and m_region = '' and m_raion = '' then

1. Вы написали неправильно!
if m_country is null and m_region is null and m_raion is null then
2. DynamicSQL+ CONTEXT
2 июл 07, 13:37    [4338248]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 63951
Блог
Наивный
Таково требование. :(

Эффективнее объяснить автору требования, что он идиот (если нужно, вежливо и аккуратно).
2 июл 07, 13:46    [4338296]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 63951
Блог
andrey_anonymous
то, если будете использовать bind variables в своем dynamic-sql решении,

Отметим, что это не так-то просто. dbms_sql-ный курсор не преобразуется в ref cursor, а open-for плохо приспособлен для работы с переменным списком параметров.
2 июл 07, 13:49    [4338316]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
Наивный
Guest
Большое спасибо за отклики.

softwarer

Эффективнее объяснить автору требования, что он идиот (если нужно, вежливо и аккуратно).


К сожалению данный вариант полностью отпадает.
Все возможные попытки ни к чему не привели.

Проблема в том, что проект работает с уже существующей БД. Это что-то типа нового модуля.
ИТишники заказчика стоят на том, чтобы вся работа с БД осуществлялась через ХП.
Таким образом они якобы конктролируют безопасность работы с БД.
А если запросы будут осуществляться из приложения, то для них это "черный ящик", "они не уверены", "они опасаются" .. бла бла бла.

Кто платит тот и заказывает музыку короче.

Динамический SQL применять не хотелось бы. Так как нагрузка будет ощутимой и я опасаюсь за производительность.
А писать кучу статических запросов как-то не очень хочется.
Но видимо придется.
2 июл 07, 13:55    [4338375]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
Mikst
Member

Откуда: Москва
Сообщений: 983
andrey_anonymous

2) Подход кодерский: если ничто не помогает но очень хочется, то можно формулировать условия в стиле:
where (:1 is null or :1=alias.field)
  and (:2 is null or alias.strfield like :2)
...
Однако в плане оптимизации запроса подобные предикаты далеко не фонтан.


а вариант

select ... where ... t.field=nvl(param1,t.field) 

как оптимизируется? (во всяком случае он более читабельный)
2 июл 07, 13:58    [4338393]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
Mikst
как оптимизируется? (во всяком случае он более читабельный)

Не сильно лучше
Дело в том, что всевозможных вариантов поиска может быть достаточно много, и единым планом отделаться тут может и не получиться.
Ну и повышение общей сложности запроса (or-условия, как и NVL, могут разворачиваться в конкатенацию) может отрицательно повлиять на итоговый план ввиду ограниченности ресурсов, которыми располагает оптимизатор.
2 июл 07, 14:13    [4338514]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
Наивный
Динамический SQL применять не хотелось бы. Так как нагрузка будет ощутимой и я опасаюсь за производительность.

Если все правильно сделаете, то производительность, скорее всего, пострадает несильно.
2softwarer: курсоры dbms_sql возможно преобразовать к ref посредством pipelined
2 июл 07, 14:14    [4338530]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54381
мы иногда пишем и так... (универсальный просмотр)
работает быстро
SELECT	*
FROM	<средних размеров таблица :-) > sc,
		<Вьюха с десятком таблиц>	 fc
WHERE	fc.id_sh = sc.id_sh
	AND	sc.datan >= :p_datan
	AND	sc.datak <= :p_datak
	AND	sc.idvers = :p_idvers
	AND	sc.plan_fakt IN (1, 2)
	AND	(:p_plan_fakt IS NULL OR sc.plan_fakt = :p_plan_fakt)
	AND	(:p_spis_pred IS NULL OR :p_spis_pred LIKE '%,' || fc.pred || ',%')
	AND	(:p_spis_cex IS NULL OR :p_spis_cex LIKE '%,' || fc.cex || ',%')
	AND	(:p_spis_kp	 IS NULL OR :p_spis_kp	LIKE '%,' || fc.kp  || ',%')
	AND	(:p_spis_rk	 IS NULL OR :p_spis_rk	LIKE '%,' || fc.rk  || ',%')
	AND	(:p_spis_prk IS NULL OR :p_spis_prk	LIKE '%,' || fc.prk || ',%')
	AND	(:p_spis_cp	 IS NULL OR :p_spis_cp	LIKE '%,' || fc.cp	|| ',%')
	AND	(:p_spis_nazn IS NULL OR :p_spis_nazn	LIKE '%,' || fc.nazn|| ',%')
	AND	(:p_spis_bals IS NULL OR :p_spis_bals	LIKE '%,' || fc.bals|| ',%')
	AND	(:p_spis_srt  IS NULL OR :p_spis_srt	LIKE '%,' || fc.srt || ',%')
	AND	(:p_spis_mg	  IS NULL OR :p_spis_mg		LIKE '%,' || fc.mg	|| ',%')
	AND	(:p_spis_id_sh	  IS NULL OR :p_spis_id_sh	LIKE '%,' || sc.id_sh	|| ',%')
	AND	(:p_spis_nn	  IS NULL OR :p_spis_nn		LIKE '%,' || fc.nn	|| ',%')
	AND	(:p_spis_nk	  IS NULL OR :p_spis_nk		LIKE '%,' || fc.nk	|| ',%')
	AND	(:p_spis_id_nk	  IS NULL OR :p_spis_id_nk		LIKE '%,' || fc.id_nk	|| ',%')
	AND	(:p_spis_id_nn	  IS NULL OR :p_spis_id_nn		LIKE '%,' || fc.id_nn	|| ',%')
	AND	(:p_spis_id		  IS NULL OR :p_spis_id		LIKE '%,' || sc.id	|| ',%')
а от nvl(:p_param, field) давно отошли - не прижилось
2 июл 07, 14:20    [4338591]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
Наивный
Guest
Спасибо за помощь.

Подскажите пожалуйста еще. Я в Оракле не шибко силен.
Раньше подобные задачи я решал путем формирования sql строки непосредственно в приложении.

Если все же реализовать на каждую комбинацию свой статический запрос в ХП.
Понятно что писанины будет больше.
Но в плане производительности это будет лучше других вариантов?

И не будет ли этот вариант выглядеть "лузерским" что ли? :)
2 июл 07, 14:22    [4338604]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
oragraf
Member

Откуда: Moscow
Сообщений: 1314
Наивный
И не будет ли этот вариант выглядеть "лузерским" что ли? :)

Ваша задача - уложиться в сроки, бюджет, требования функциональности/производительности. Все остальное - от лукавого/лузерство.
2 июл 07, 14:36    [4338709]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
Proteus
Member

Откуда:
Сообщений: 1348
Почти нет разницы между динамическим курсором и статическим.
Конечно меня сейчас начнут убеждать в обратном.
Но мне кажется, что для вашего уровня знаний, разница будет несущественной.
Основное отличие статического курсора это проверка его правильности и связывание на этапе компиляции пакета а не на этапе выполнения.

Но Вам следует понимать что из 4 параметров можно предположить 16 вариантов условий выборки, а при не слабом запросе вы можете не смочь перебрать все варианты условий. Да и сопровождать данный код очень накладно. ошибка в запросе требует переписывания запросов и для всех остальных условий.

Могу предложить Вам вот такой вариант.
В БД прописываете шаблон запроса с определенными коментариями по его тексту которые перед выполнением заменяются на строки с условиями сформированными на клиенте. а затем запрос выполняется через dbms_sql и выдается. И не забудьте передавать параметры не как строки а как переменные запроса.
2 июл 07, 14:41    [4338747]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
Наивный
Понятно что писанины будет больше.

Ее будет не просто больше.
Писанины будет до несопровождаемости много.
Просто оцените число незапрещенных комбинаций критериев поиска, каждая комбиначия - отдельный запрос... А ведь еще можно предусматреть варианты "или" :)
"По скорости" будет баш на баш. Что в лоб, что по лбу - если есть 200 различных запросов, то их выполнение потребует 200 различных курсоров и не менее 200 разборов что с динамиком, что со статической имплементацией вариаций поискового запроса.
Статический же "универсальный" запрос (подобный предложению andreymx) имеет ограничения как по логике поиска, так и по эффективности в зависимости от используемых предикатов (единственный план "на все случаи жизни").
2 июл 07, 14:44    [4338775]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
Proteus
Member

Откуда:
Сообщений: 1348
andrey_anonymous

Статический же "универсальный" запрос (подобный предложению andreymx) имеет ограничения как по логике поиска, так и по эффективности в зависимости от используемых предикатов (единственный план "на все случаи жизни").


Причем этот план может поменяться после сбора статистики или остановки базы и то какой план будет зависит от набора параметров при первом запуске.
2 июл 07, 14:52    [4338822]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 63951
Блог
Наивный
ИТишники заказчика стоят на том, чтобы вся работа с БД осуществлялась через ХП.
Таким образом они якобы конктролируют безопасность работы с БД.

Пусть читают о проблемах безопасности, существующих в ХП :) Я бы, кстати, отметил очень важную деталь - запросы выполняются с правами текущего пользователя, а вот ХП как правило выполняются с правами создателя.

Наивный
Кто платит тот и заказывает музыку короче.

Платят не ИТ-шники, а их начальство. Которому можно сказать, что ИТ-шники своей некомпетентностью увеличивают стоимость проекта на X% и расписать прочие ужасы, ну а в конце посоветовать пригласить эксперта... того же Андрея :)

Наивный
Динамический SQL применять не хотелось бы. Так как нагрузка будет ощутимой и я опасаюсь за производительность. А писать кучу статических запросов как-то не очень хочется.
Но видимо придется.

В куче статических запросов вы утонете навсегда. Это же не один запрос с тремя полями фильтрации - а даже если и так, в ходе сопровождения будет расти и расти. Если поднимать лапки, то нужно делать динамический SQL, думать над его скоростью и скорее всего передавать параметры через контекст.

Хотя... за что я в том числе уважаю одного из своих начальников, так это за то, что он при мне отказался от довольно большого проекта, заявив клиенту практически следующее: ребят, мы с вами немного поработали, и нам не понравилось; если хотите, мы выкатим вам предложение на следующие этапы, но гарантирую, сумма там будет с учетом всех геморроев, которые вы организовываете.
2 июл 07, 14:55    [4338837]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 63951
Блог
andrey_anonymous
2softwarer: курсоры dbms_sql возможно преобразовать к ref посредством pipelined

Безусловно. Можно придумать и еще более геморройные варианты - скажем, через dbms_sql делать insert/select во временную таблицу и возвращать select * оттуда.
2 июл 07, 15:00    [4338863]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
mcureenab
Member

Откуда: Murmansk
Сообщений: 5928
Наивный
Спасибо за помощь.

Подскажите пожалуйста еще. Я в Оракле не шибко силен.
Раньше подобные задачи я решал путем формирования sql строки непосредственно в приложении.

Если все же реализовать на каждую комбинацию свой статический запрос в ХП.
Понятно что писанины будет больше.
Но в плане производительности это будет лучше других вариантов?

И не будет ли этот вариант выглядеть "лузерским" что ли? :)


1. Динамическим формированием at hoc SQL запроса под заданные критерии обычно занимается библиотека компонентов доступа к данным. На дворе XXI век! Не гоже тратить время на создание колеса.
2. Предложенным способом ты рискуешь получить комбинаторный взрыв.
3. Производительность может быть выше, если оптимизировать и настраивать каждый запрос, но боюсь, заказчик (в силу п.2) никогда не получит работающее решение. Динамический SQL от т.н. статического отличается тем, что статический SQL запрос проверяется на этапе компиляции, а на этапе выполнения его курсор кэшируется PL/SQL машиной для повторного выполнения, что имеет смысл только для часто повторяющихся запросов в рамках сессии. Так что пользуй динамический SQL, только делай это правильно, используя переменные привязки и т.п. правила хорошего тона.
4. Не знаю точно требований, но передача только параметров предиката редко бывает достаточной. Как например быть, если пользователь хочет в выборке видеть два, три региона, или все регионы на букву 'М'. Это ещё больше усгубляет п.2, за счёт комбинаторики самих условий отбора.
5. В данном случае оптимально передавать в ХП фрагменты предиката выборки, которые затем объединять выражениями AND и OR. Имеет смысл написать одну процедуру, которая будет формировать текст любого SQL запроса из массива параметров.
2 июл 07, 15:05    [4338906]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
Proteus
Member

Откуда:
Сообщений: 1348
2Softwarer: А как можно в динамический курсор забиндить переменное количество переменных если это не DBMS_SQL?
Есть ли вообще такая возможность?
2 июл 07, 15:07    [4338914]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
softwarer
andrey_anonymous
2softwarer: курсоры dbms_sql возможно преобразовать к ref посредством pipelined

Безусловно. Можно придумать и еще более геморройные варианты

Если ограничиваться технологией взаимодействия клиента и сервера на базе ref-курсоров предложенный вариант реализации динамических поисковых запросов с переменным количеством параметров средствами связки DBMS_SQL + pipelined представляется наиболее мощным решением при минимуме относительно разумных затрат.
С удовольствием почитаю про лучшие варианты.
2 июл 07, 15:11    [4338949]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
Fucker
Member [заблокирован]

Откуда:
Сообщений: 1525
По делу уже все сказали Анонимус с Софтварером, мне остались иллюстрации для вашего клиента:
http://udaff.com/photo/page4280.html

А если серьезно: надо либо додавить клиента, либо просто послать его на х....

Геморроя будет с таким подходом много, особенно если вы такого рода приложение пишете в первый раз...

Fucker
2 июл 07, 15:17    [4339007]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
Наивный
Guest
CREATE OR REPLACE PACKAGE "PKG_TEST"   as
  procedure SelectRecord (m_Country in varchar2,
                          m_Region in varchar2,
                          m_Raion in varchar2,
                          crs out sys_refcursor);
end;

/

CREATE OR REPLACE PACKAGE BODY "PKG_TEST"   as
  l_strSQL varchar2(100);
  procedure SelectRecord (m_Country in varchar2,
                          m_Region in varchar2,
                          m_Raion in varchar2,
                          crs out sys_refcursor)
  is
  begin
  
        l_strSQL := '';
        
        if not m_Country is null then
          l_strSQL := ' where Country = ' || m_Country;
        end if;

        if not m_Region is null then
          if length(l_strSQL) > 0 then
              l_strSQL := ' And Region = ' || m_Region;
          else
              l_strSQL := ' where Region = ' || m_Region;
          end if;
        end if;

        if not m_Raion is null then
          if length(l_strSQL) > 0 then
              l_strSQL := ' And Raion = ' || m_Raion;
          else
              l_strSQL := ' where Raion = ' || m_Raion;
          end if;
        end if;
        
       l_strSQL := 'open crs for select * from tbl ' || l_strSQL;
       
       execute immediate l_strSQL;
       
       

  end;

end;


Я так понимаю что данный вариант нерабочий? Хотя еще не проверил до конца. Так на скорую руку набросал.
Как лучше сделать можно пример небольшой.
Я с переменными привязки вообще не работал.
2 июл 07, 15:28    [4339098]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
Наивный
Как лучше сделать можно пример небольшой.
Я с переменными привязки вообще не работал.

- лучше в условной логике управлять только появлением AND/OR, where приписать уже после формирования строки.
- Не следует клеить литералы. Тут уже давали советы про CONTEXT, не пренебрегайте. Разберетесь - будут Вам бинды.
- "родные" бинды в Вашем случае к execute_immediate не прикрутить, поскольку количество параметров в запросе не постоянно.
2 июл 07, 15:33    [4339148]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
Наивный
Guest
Проверил свой пример и он естественно не работает :)
Неверное предложение SQL предложения.

Ладно с CONTEXT и биндами я сейчас разберусь.

А вот по поводу как возвратить курсор не поскажете?
Реф курсор и dbms курсор я так понял несовместимы.
Выше говорили о необходимости его преобразовывать.
Я так понял есть несколько механизмов.
В какую сторону лучше копать?
2 июл 07, 15:43    [4339257]     Ответить | Цитировать Сообщить модератору
 Re: Динамический SQL. Можно ли обойтись без него.  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18347
Наивный
В какую сторону лучше копать?

Определяйтесь - dbms_sql или контекст и ref-курсор (тоже рабочее, в общем-то, решение).
ref cursor возвращается так:
... open rc for sql_stmnt;return rc;
Преобразование курсора dbms_sql осуществить можно, например, так:
... open rc for select * from table(pipelined_function_using_dbms_sql(список_параметров,список значений));
2 июл 07, 15:49    [4339301]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Oracle Ответить