Некоторые аспекты использования пользовательских функций в предложениях 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 retcr


Теперь достаточно записать Select val,getvcr(valuta.valuta_id) as cour from valuta - и все.

А хотите курс на конкретную дату - укажите ее во втором параметре.

Так и вспоминается анекдот про двух бычков, старого и молодого. Молодой все норовил быстро побежать к коровкам. Нам же по нраву принцип старого бычка- пойдем медленно медленно, но поимеем все , что хотели и может даже обгоним кого и помоложе.

Читать комменты и комментировать

Добавить комментарий / отзыв



Защитный код
Обновить

Некоторые аспекты использования пользовательских функций в предложениях SQL. | | 2010-09-14 22:43:27 | | Базы данных | | Уже надоело говорить о том, что ровным счетом ничего нового в этой жизни не происходит, а большинство нового - это хорошо забытое старое. Вот еще пример. Один автор этой статьи пожилой , но | РэдЛайн, создание сайта, заказать сайт, разработка сайтов, реклама в Интернете, продвижение, маркетинговые исследования, дизайн студия, веб дизайн, раскрутка сайта, создать сайт компании, сделать сайт, создание сайтов, изготовление сайта, обслуживание сайтов, изготовление сайтов, заказать интернет сайт, создать сайт, изготовить сайт, разработка сайта, web студия, создание веб сайта, поддержка сайта, сайт на заказ, сопровождение сайта, дизайн сайта, сайт под ключ, заказ сайта, реклама сайта, хостинг, регистрация доменов, хабаровск, краснодар, москва, комсомольск |
 
Поделиться с друзьями: