FoxPro Club Главная

Конференция Решения Фотоальбом Сайт М.Дроздова Статьи Файловый архив Книга Visual FoxPro 9.0 Русский Help для Visual FoxPro
Пользователей: 9176
Вход

Владимир Журавлев , Олег Поль.

Некоторые аспекты использования пользовательских функций в предложениях SQL.

"Старый конь борозды не испортит "
" В чем преимущество склероза ?. В том, что все время узнаешь новое"

Уже надоело говорить о том, что ровным счетом ничего нового в этой жизни не происходит, а большинство нового - это хорошо забытое старое. Вот еще пример. Один автор этой статьи пожилой , но относительно молодой фоксист , начавший работать только с FXP2.6 под виндами, а второй -один из старейших клиперистов. Поэтому и приемы разные. У первого в основном новые методы ФОКСА, у второго вечные истины dbase. Оказывается, что старые рецепты часто дают много лучшие решения, чем новомодные навороты, зная которые , очень трудно использовать старые приемы, хотя и читал теорию и потенциально их знаешь. Потом, черт возьми, вечно попадаешь на агрессивную рекламу Микрософт с описанием новых, все более мощных методов. И часто веришь всему, что они говорят. Но вот берем тривиальные задачи, суем их на новую кухню и ждем вкусного пирога. А вместо него иногда - одна вонь.
Речь пойдет в основном о SQL select с предложениями outer left/right join, union и использовании групповых функций.
В одной из предыдущих статей мы уже упоминали о том, что генератор запросов и представлений view дает неправильный код с этими предложениями, если имеем несколько join.
Пример такого ошибочного кода:

LEFT OUTER JOIN dbf_s!valuta;
ON int(pd.valuta_id) == int(Valuta.valuta_id) ;
ON int(pd.kv_izg) == int(Valuta_a.valuta_id) ;
ON pd.shpz_id = Sc_shpz.shpz_id ;
ON pd.nc_id = bc_ac.nc_id ;
ON pd.country_id = country.country_id ;
ON pd.um_id = Sc_ed.um_id ;
LEFT OUTER JOIN dbf_s!valuta Valuta_a ;
LEFT OUTER JOIN dbf_s!Sc_shpz;
LEFT OUTER JOIN dbf_s!bc_ac;
LEFT OUTER JOIN dbf_s!Country ;
LEFT OUTER JOIN dbf_s!Sc_ed

А нужно-
LEFT OUTER JOIN dbf_s!valuta;
ON int(pd.valuta_id) == int(Valuta.valuta_id) ;
LEFT OUTER JOIN dbf_s!valuta Valuta_a ;
ON int(pd.kv_izg) == int(Valuta_a.valuta_id) ;
LEFT OUTER JOIN dbf_s!Sc_shpz;
ON pd.shpz_id = Sc_shpz.shpz_id ;
LEFT OUTER JOIN dbf_s!bc_ac;
ON pd.nc_id = bc_ac.nc_id ;
LEFT OUTER JOIN dbf_s!Country ;
ON pd.country_id = country.country_id ;
LEFT OUTER JOIN dbf_s!Sc_ed ;
ON pd.um_id = Sc_ed.um_id
Ну это не беда. "Кто предупрежден - тот вооружен".
Беда в другом - генератор позволяет к этому добавить предложение where, где можно использовать любые из уже использованных таблиц.
Вот тут-то (особенно если условие where в нем касается присоединенных таблиц) результат может быть и вовсе неверен. А как такое условие не добавить, если нужно взять товар за определенный интервал времени? Или цену товара из прайс листа, соответствующую нужному учетному периоду? Этого в join никак не засунешь.
Без outer join, казалось бы, тоже нельзя - ссылка на любой справочник может быть пустой, а строчку товара терять нежелательно. Поэтому условия из outer join не получится перенести в where . Ждать, когда Микрософт исправит ошибки - так наши клиенты ждать нас не будут и быстренько слиняют к более удачливым фирмам или программным продуктам. Поэтому, хочешь не хочешь, а давай правильный результат.
Сначала мы разбили запрос на два -один с outer join , второй с where. Работает весьма неплохо и при наличии нужных индексов достаточно быстро. Однако, иногда получается надо писать уж слишком много веток по разным условиям. Число SQL и их сложность растет и растет. Более того, приходится включать вторичные справочники с еще одним outer join на таблицу, которая сама уже висит на outer join. Вероятность ошибки в результате слишком
велика.
Вот тут то на помощь приходят старые, но от этого, не менее эффективные методы. А именно:
использование пользовательских функций. Идея состоит в том, чтобы убрать из предложения Select SQL все таблицы, которые привязаны к внешним объединениям, а искать нужные ссылки в этих таблицах в пользовательских функциях. Функция должна быть такая, что при наличии ссылки - получить ее значение, а при отсутствии вернуть "пустышку" нужного типа. То есть сделать работу outer join самим. Что для этого нужно: имя ключа, по которому в справочнике ищется справка, имя справочной таблицы, поле из справочника, имя тэга, по которому ведется поиск.

Некоторые хитрости.
Положим, что для одного ключа из справочника должно быть найдено несколько разных полей. Не хочется перегружать работу поиска второй раз. Если нужная запись в справочнике найдена - для другого поля ни к чему заново делать поиск.
Второе.
SQL select обладает одним неприятным свойством: длина результирующего поля оценивается во время инициализации. Поэтому, если для первой записи справка отсутствует, а для следующей справка есть, то и для пустой записи нужно вернуть поле нужной длины. Иначе записи из справочника будут урезаны по одной букве.
Вот пример такой функции
Lparameters pr_id,mydat
Local retcr

If order('val_course')!='datcur'
Set order to datcur in val_course
Endif
retcr=0
If empty(mydat)
set exact off
If seek(str(pr_id,3),'val_course')
retcr=course
Endif
Else
If set('NEAR')='OFF'
Set near on
Endif
=seek(str(pr_id,3)+dtos(mydat),'val_course')
If pr_id=val_course.valuta_id
retcr=val_course.course
Endif
Set near off
Endif
Return retcr:

Function getref
Lparameters fld,pr_id,tb,tg

Local fl,tpt,rt,rtt,lnn, fl,fll

fl=tb+'.'+alltrim(fld)
fll=tb+'.'+tg
*** Если совпал ключ, можно не искать, иначе - ищем
If not empty(pr_id) and ;
(eval(fll)=pr_id or seek(pr_id,tb,tg))
rtt=eval(fl)
Return rtt
Else
rt=eval(fl)
tpt=type('rt')
Do case
Case tpt='N'
rtt=0
Case tpt='C'
Lnn=len(eval(fl))
rtt=pad('',lnn)
Case tpt='D'
rtt=ctod('..')
Case tpt='L'
rtt=.f.
Endcase
Return rtt
Endif

Lparameters pr_id,mydat

Local retcr

If order('val_course')!='datcur'
Set order to datcur in val_course
Endif
retcr=0
If empty(mydat)
set exact off
If seek(str(pr_id,3),'val_course')
retcr=course
Endif
Else
If set('NEAR')='OFF'
Set near on
Endif
=seek(str(pr_id,3)+dtos(mydat),'val_course')
If pr_id=val_course.valuta_id
retcr=val_course.course
Endif
Set near off
Endif
Return retcr

Пример использования. Пусть у нас был запрос SQL следующего вида:

Select doc.num,val from doc LEFT OUTER JOIN valuta;
ON doc.valuta_id= Valuta.valuta_id


Теперь пишем

Select doc.num getref('val','doc.valuta_id','valuta','valuta_id) as val from doc


Здесь конечно получилось более громоздко, чем в исходном примере, зато можно добавлять еще кучу полей из справочников и кучу справочников и не боятся при этом использовать предложение where в этом же sql

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

Решение первое (неправильное):

Select val,max(dat),cour from valuta, vcour where valuta.valuta_id=vcour.valuta_id union ;
Select val, ctod('..'), 0 as cour from valuta where valuta_id not in ( select valuta_id from vcour)

Беда в том, что функция max, работая по всему диапазону записей, дату-то дает правильную, зато курс - из последней по счету записи. Если курс вводился не в хронологическом порядке - курс получается не последней даты, а последней записи, хотя сама дата в выборке - последняя.
Решение в стиле Microsoft - правильное, но не оптимальное.

Select max(dat)as dt,valuta_id from ;
vcour group by valuta_id into cursor qr
Select distinct val,cour,dat from ;
valuta,vcour,qr ;
where valuta.valuta_id=vcour.valuta_id ;
and qr.dt=vcour.dat and ;
qr.valuta_id=valuta.valuta_id ;
union ;
Select distinct val,1 as cour,ctod('..') as dat from valuta where ;
valuta.valuta_id not in (select distinct valuta_id from vcour);
into cursor vcou


Решение по дедовским заветам. Самое быстрое.

Создадим в таблице курса валют композитный индекс datcur с выражением
STR(valuta_id,3)+DTOS(dat)
Сделаем функцию Getvcr
Lparameters pr_id,mydat

Local retcr

If order('vcour')!='datcur'
Set order to datcur in vcour
Endif
retcr=0
If empty(mydat)
set exact off
If seek(str(pr_id,3),'vcour')
retcr=vcour.cour
Endif
Else
If set('NEAR')='OFF'
Set near on
Endif
=seek(str(pr_id,3)+dtos(mydat),'vcour')
If pr_id=vcour.valuta_id
retcr=vcour.cour
Endif
Set near off
Endif
Return retc
r

Теперь достаточно записать Select val,getvcr(valuta.valuta_id) as cour from valuta - и все.
А хотите курс на конкретную дату - укажите ее во втором параметре.
Так и вспоминается анекдот про двух бычков, старого и молодого. Молодой все норовил быстро побежать к коровкам. Нам же по нраву принцип старого бычка- пойдем медленно медленно, но поимеем все , что хотели и может даже обгоним кого и помоложе.



Вернуться к списку статей






© 2000-2017 Fox Club 
При размещении любых материалов с сайта на других ресурсах- прямая ссылка на www.foxclub.ru обязательна
Яндекс.Метрика