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

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

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

Статьи по теме

Оптимизация и тюнинг производительности MariaDB MySQL сервера внутри Docker

Содержание: Установка и знакомство с MySQLTuner Установка MySQL Tuning Primer Script Первичная оптимизация MySQL сервера ...

2023-11-08

Работа с базами данных MySQL в ISPManager 5 + Docker

В ISPManager 5 добавлена контейнерная виртуализация Docker для использования альтернативных версий MySQL. Цитата из документации ISPManager 5: ...

2023-10-27

«Облачные» хранилища данных: в ожидании доверия

Информационные технологии активно мигрируют в сторону «облаков». Эксперты прогнозируют, что одним из главных приоритетов для CIO в ближ...

2011-11-05

ГОСТ 34.602-89. на Техническое задание по разработке web проекта

ТЕХНИЧЕСКОЕ ЗАДАНИЕ НА СОЗДАНИЕ АВТОМАТИЗИРОВАННОЙ СИСТЕМЫ Information technology. Set of standards for automated systems. Technical directions ...

2010-12-28

Orange color: теория и практика

Знаете ли Вы, чем оранжевый цвет отличается от других ? Если нет, то знайте, что разница в скорости восприятия глазом отдельных цветов ра...

2010-12-27

FAQ по MS SQL 7.0

11. Как запускать-останавливать SQL Server через командную строку? Мне это нужно для использования в скриптах.Остановка:NET STOP MSSQLSERVERили если вы...

2010-09-17

Виртуальная реализация SQL Server 2005

Ранее в материале «Разбираем “айсберг” sql server 2005» читателям предлагалось воспользоваться опубликованной в internet серией информационных...

2010-09-17

SQL Server в вопросах и ответах

В: У меня на разных компьютерах установлено два sql server 7.0. Для обмена данными между ними я использовал data transformation services (dts), чтобы импортиро�...

2010-09-17

Один из вариантов соглашения об именах объектов MS SQL Server

Помните, что имена объектов sql сервера не могут превышать 30 символов. Не используйте зарезервированные слова, специальные символы, и не н...

2010-09-17

Создание и удаление БД в MS SQL Server

В sql server'е базы данных хранятся в виде обычных файлов на диске. Как минимум на одну БД приходится таких файлов 2: *.mdf и *.ldf. В первом хранятся...

2010-09-17

Создание и удаление таблиц в MS SQL Server

Все данные в БД sql server хранятся в таблицах.Таблицы состоят из колонок, объединяющих значения одного типа, и строк - записей в таблице. В од�...

2010-09-17

Единая аутентификация Windows NT/2000 и Oracle

При использовании Windows NT/2000 как платформы для работы Oracle, и на стороне сервера и на стороне клиентов, имеется возможность использовать ед...

2010-09-17

Oracle / PHP FAQ

Что такое PHP и что необходимо иметь для работы с Oracle?PHP - рекурсивный акроним "PHP Hypertext Preprocessor". Это интерпретируемый язык программирования ...

2010-09-17

Oracle и PHP - это очень просто

ВведениеСУБД Oracle, когда против желания фирмы-изготовителя, а когда согласно – в зависимости от направления политических ветров в конкр�...

2010-09-17

Сжатие данных в целях экономии места и ускорения работы Oracle

Используйте сжатие данных таблицы для экономии места на диске и повышения производительности запросов. В большинстве систем поддержки ...

2010-09-17