Форум: "Начинающим";
Текущий архив: 2009.05.03;
Скачать: [xml.tar.bz2];

Вниз

Вызов SQL-функции с меняющимся аргументом   Найти похожие ветки 

← →
И. Павел   (2009-03-19 10:23) [0]

Здравствуйте.
Пишу запрос для работы с MS SQL Server. В запросе вызывается функция Func (), написанная в SQL SERVER в разделе User defined functions, которая принимает один аргумент типа int. Если написать так:

ADOQ.SQL.ADD(‘ SELECT tab.id, F.rez ’+
            ‘ FROM tab ’
            ‘ LEFT JOIN Func(123) AS F ON F.id=tab.id ‘)

то все работает, но для каждой записи в tab нужно вызывать функцию со своим аргументом:

ADOQ.SQL.ADD(‘ SELECT tab.id, F.rez ’+
            ‘ FROM tab ’
            ‘ LEFT JOIN Func(tab.id) AS F ON F.id=tab.id ‘)

при этом SQL Server выдает ошибку: “Incorrect Syntax near .”,
видимо по поводу выражения “Func(tab.id)”.

Если использовать параметры:

if Query.Parameters.FindParam(‘Arg’)=nil then Query.Parameters.AddParameter.Name:=’Arg’;
 Query.Parameters.ParamByName(’Arg’).DataType:=ftString;
 Query.Parameters.ParamByName(’Arg’).Value:=’tab.id’;
 Query.SQL.Add(‘SELECT * FROM tab LEFT JOIN Func(:Arg)’);

то вылетает ошибка, сообщающая о том, что ожидалось целое число, а пришла строка, то есть, видимо, в функцию Func сразу же передается выражение ‘tab.id’, а мне нужно, чтобы передавалось значение этого выражения.

Подскажите, пожалуйста, как сделать такой запрос, в котором вызывалась бы функция с меняющимся аргументом?

---

← →
ЮЮ ©   (2009-03-19 10:35) [1]

LEFT JOIN Func(tab.id) AS F ON F.id=tab.id

перепиши Func так, чтобы она была без параметров, но возвращала множество записей, а не одну, т.е. "полноценную таблицу", а уж затем связывай таблицы tab и Func AS F

Или Func() должна быть скалярной функцией и синтаксис будет другой

SELECT tab.id, Func(tab.id) as rez  FROM tab

---

← →
И. Павел   (2009-03-19 11:42) [2]

> ЮЮ ©


Спасибо. Сразу набирать большую таблицу – это выход. Только если такая функция будет медленно выполняться, возникнет таймаут.
Я пробовал увеличить время ожидания:

 ADOConnection1.Close;
 ADOConnection1.ConnectionTimeout:=1000000;
 ADOConnection1.Open;
 ADOConnection1.CommandTimeout:=1000000;

Но это ни к чему не приводит – предельное время выполнения запроса по прежнему остается равно 30 сек. Можно ли как-нибудь увеличить время ожидания, или вообще запретить таймауты?

---

← →
stas ©   (2009-03-19 13:22) [3]

из справки к mssql 2005
SELECT O.[Date], P.Model, P.Configuration, P.Price FROM Orders O
 OUTER APPLY GetProductDetails(O.ProductID) AS P

Но не думаю что это будет работать более быстро

---

← →
sniknik ©   (2009-03-19 13:49) [4]

> Можно ли как-нибудь увеличить время ожидания,
можно. но менять нужно именно время таймаута запроса, а не соединения...
ну и плюс в первых версиях ADO (вернее дельфевской обертки) были с ними глюки, приходилось лезть в генофонд... но потом стало все нормально.
блин, а вот для чего при посте вопроса в форум там стоят версии дельфей для указания используемой????

> или вообще запретить таймауты?
значение 0.

---

← →
И. Павел   (2009-03-19 15:54) [5]

> stas ©

Почему-то

SELECT *
FROM tab
LEFT OUTER JOIN APPLY Func(1234) F ON 1=1

тоже возвращает ошибку.


> sniknik ©

Спасибо. Поменял таймаут в TADOQuery - заработало. Пишу на Delphi 2005.

---

← →
Ю.Ю.   (2009-03-19 22:11) [6]

>
> тоже возвращает ошибку.

а сервер у тебя 2005?

---

← →
stas ©   (2009-03-19 22:25) [7]

Join ненадо

---

← →
И. Павел   (2009-03-20 08:36) [8]

> Ю.Ю.   (19.03.09 22:11) [6]
>
> >
> > тоже возвращает ошибку.
>
> а сервер у тебя 2005?


У меня SQL SERVER 2000. Можно ли в нем как нибудь выполнить такой запрос? Кроме моей программы с SQL сервером работает еще много других приложений, и вряд ли те, кто администрируют SQL SERVER будут его обновлять по моей просьбе...


> stas ©   (19.03.09 22:25) [7]
> Join ненадо


Пытался сделать так:

SELECT * FROM tab
OUTER APPLY Func(tab.id)
Ошибка: Incorrect syntax near the keyword "OUTER".

SELECT * FROM tab
LEFT OUTER APPLY Func(tab.id)

Ошибка: "APPLY" is not a recognized join option.

Все дело в старом SQL SERVER, или я просто не правильно составляю запрос? Если второе, подскажите, пожалуйста, как правильно?

---

← →
stas ©   (2009-03-20 08:52) [9]

И. Павел   (20.03.09 08:36) [8]
нет надо 2005 SQL.
Только из-за этого не стоит переустанавливать SQL.

---

← →
ЮЮ ©   (2009-03-20 09:25) [10]

> Только если такая функция будет медленно выполняться, возникнет
> таймаут.


Может функцию покажешь?

---

← →
sniknik ©   (2009-03-20 10:32) [11]

и еще объясни чего ты хочеш добиться от запроса,
SELECT tab.id, F.rez
FROM tab
LEFT JOIN Func(tab.id) AS F ON F.id=tab.id
идеологию т.сказать.

джойн объединяет рекордсеты, т.е. то что уже имеется до запроса (даже подзапрос если он там стоит, выполняется до основного).
а что должно в твоем случае происходить? объединение с кучей рекордсетов? (т.е.  по одному на каждую итерацию цикла выборки) как ты вообще представляешь что должно получиться от твоей конструкции?
"на пальцах", т.е. приведи пару записей + результат функции, и что должно быть в итоге.

---

← →
И. Павел   (2009-03-20 10:51) [12]

Функция для одной задачи такая (переводит, главным образом, числа в проценты, например было 2.5, 2.5 и 5, в результате получается 25%, 25% и 100%):

CREATE FUNCTION dbo.GetVesa (@zadacha int)
RETURNS @TabVesa TABLE
(
 idzadacha int,
 begin_status datetime,
 status int,
 tab_num int,
 fio varchar(50),
 fct float,
 ych_fct int,
 pl float,
 ych_pl int,
 ych1 int,
 ych2 int,
 ych3 int,
 ocenka1 int,
 ves1 float,
 ocenka2 int,
 ves2 float
)

AS

BEGIN
 DECLARE @norma int;
 DECLARE @otv int;
 DECLARE @ocenka1 int;
 DECLARE @ocenka2 int;
 DECLARE @VesChasa float;
 DECLARE @VesZadachi float;
 DECLARE @stat int;
 DECLARE @BegStat datetime;

 DECLARE @cur_tab_num int;
 DECLARE @cur_fct float;
 DECLARE @cur_pl float;

 DECLARE @SumFct float;
 DECLARE @SumPl float;
 DECLARE @SumYchFct int;
 DECLARE @SumYchPl int;
 DECLARE @MaxFctTabNum int;
 DECLARE @MaxPlTabNum int;

 SELECT @norma=zadachi.norma, @otv=historyzadachi2.otv, @ocenka1=zadachi.ocenka1, @ocenka2=zadachi.ocenka2,
        @VesChasa=spr_vesa.veschasa, @stat=historyzadachi2.status, @BegStat=historyzadachi2.dates
 FROM zadachi
 LEFT JOIN historyzadachi2 ON historyzadachi2.datePo="9999.12.31" AND historyzadachi2.id_zadachi=@zadacha
 LEFT JOIN spr_vesa ON spr_vesa.tipzadachi=zadachi.tipzadachi AND spr_vesa.scale=zadachi.scale
 LEFT JOIN sotrudniki ON sotrudniki.tab_num=historyzadachi2.otv
 WHERE zadachi.id=@zadacha
 GROUP BY zadachi.norma, historyzadachi2.otv, zadachi.ocenka1, zadachi.ocenka2,
          spr_vesa.veschasa, historyzadachi2.status, historyzadachi2.dates;

 SELECT @VesZadachi=@VesChasa*@norma;

 INSERT INTO @TabVesa(idzadacha, begin_status, status, tab_num, fio, fct, pl, ych1, ych2, ych3, ocenka1, ves1, ocenka2, ves2)
   SELECT @zadacha, @BegStat, @Stat, sotrudniki.tab_num, sotrudniki.fio,
   isnull(sum(fact.fact), 0),
   isnull( (
             SELECT isnull(sum(AAhistoryzadania2.trudoemkost), 0)
             FROM plan_sotr AS AAplan_sotr
             LEFT JOIN historyzadania2 AS AAhistoryzadania2 ON AAhistoryzadania2.datepo="9999.12.31" AND AAhistoryzadania2.id_zadania=AAplan_sotr.id
             WHERE AAplan_sotr.sotrudnik=sotrudniki.tab_num AND AAplan_sotr.namez=@zadacha
             GROUP BY AAplan_sotr.namez
           ), 0),
   isnull(vesa.ych1, 0), isnull(vesa.ych2, 0), isnull(vesa.ych3, 0), @ocenka1, (@ocenka1-3)*@VesZadachi*0.05, @ocenka2, (@ocenka2-3)*@VesZadachi*0.05
   FROM sotrudniki
   LEFT JOIN plan_sotr ON plan_sotr.sotrudnik=sotrudniki.tab_num AND plan_sotr.namez=@zadacha
   LEFT JOIN vesa ON vesa.namez=@zadacha AND vesa.sotrudnik=sotrudniki.tab_num
   LEFT JOIN historyzadania2 ON historyzadania2.datepo="9999.12.31" AND historyzadania2.id_zadania=plan_sotr.id
   LEFT JOIN fact ON  fact.idplsotr=plan_sotr.id
   WHERE sotrudniki.tab_num=@otv
   GROUP BY sotrudniki.tab_num, sotrudniki.fio, vesa.ych1, vesa.ych2, vesa.ych3

   UNION SELECT @zadacha, @BegStat, @Stat, sotrudniki.tab_num, sotrudniki.fio,
   isnull(sum(fact.fact), 0),
   isnull( (
             SELECT isnull(sum(AAhistoryzadania2.trudoemkost), 0)
             FROM plan_sotr AS AAplan_sotr
             LEFT JOIN historyzadania2 AS AAhistoryzadania2 ON AAhistoryzadania2.datepo="9999.12.31" AND AAhistoryzadania2.id_zadania=AAplan_sotr.id
             WHERE AAplan_sotr.sotrudnik=sotrudniki.tab_num AND AAplan_sotr.namez=@zadacha
             GROUP BY AAplan_sotr.namez
           ), 0),
   isnull(vesa.ych1, 0), isnull(vesa.ych2, 0), isnull(vesa.ych3, 0), 0, 0, 0, 0
   FROM sotrudniki
   INNER JOIN plan_sotr ON plan_sotr.sotrudnik=sotrudniki.tab_num AND plan_sotr.namez=@zadacha
   LEFT JOIN vesa ON vesa.namez=@zadacha AND vesa.sotrudnik=sotrudniki.tab_num
   LEFT JOIN historyzadania2 ON historyzadania2.datepo="9999.12.31" AND historyzadania2.id_zadania=plan_sotr.id
   LEFT JOIN fact ON  fact.idplsotr=plan_sotr.id
   WHERE sotrudniki.tab_num<>@otv
   GROUP BY sotrudniki.tab_num, sotrudniki.fio, vesa.ych1, vesa.ych2, vesa.ych3
   ORDER BY sotrudniki.fio ASC;

 SELECT @SumFct=(SELECT isnull(sum(fct), 0) FROM @TabVesa);
 SELECT @SumPl=(SELECT isnull(sum(pl), 0) FROM @TabVesa);

 SELECT @MaxFctTabNum=(SELECT TOP 1 tab_num FROM @TabVesa ORDER BY fct DESC);
 SELECT @MaxPlTabNum=(SELECT TOP 1 tab_num FROM @TabVesa ORDER BY pl DESC);

 if @SumFct<>0
 Begin
   UPDATE @TabVesa SET ych_fct=round(fct*100/@SumFct, 0) WHERE tab_num<>@MaxFctTabNum;
   SELECT @SumYchFct=(SELECT isnull(sum(ych_fct), 0) FROM @TabVesa WHERE tab_num<>@MaxFctTabNum);
   UPDATE @TabVesa SET ych_fct=100-@SumYchFct WHERE tab_num=@MaxFctTabNum;
 end
 else UPDATE @TabVesa SET ych_fct=0;

 
 if @SumPl<>0
 Begin
   UPDATE @TabVesa SET ych_pl=round(pl*100/@SumPl, 0) WHERE tab_num<>@MaxPlTabNum;
   SELECT @SumYchPl=(SELECT isnull(sum(ych_pl), 0) FROM @TabVesa WHERE tab_num<>@MaxPlTabNum);
   UPDATE @TabVesa SET ych_pl=100-@SumYchPl WHERE tab_num=@MaxPlTabNum;
 end
 else UPDATE @TabVesa SET ych_pl=0;

 RETURN;
END

---

← →
И. Павел   (2009-03-20 10:52) [13]

Общая функция, выбирающая большое число полей:

CREATE FUNCTION dbo.GetForShowVesa()
RETURNS @TabAllVesa TABLE
(
 idzadacha int,
 begin_status datetime,
 status int,
 tab_num int,
 fio varchar(50),
 fct float,
 ych_fct int,
 pl float,
 ych_pl int,
 ych1 int,
 ych2 int,
 ych3 int,
 ocenka1 int,
 ves1 float,
 ocenka2 int,
 ves2 float
)

AS

BEGIN
 DECLARE @id int;  

DECLARE @Tab123 TABLE
(
 idzadacha int,
 begin_status datetime,
 status int,
 tab_num int,
 fio varchar(50),
 fct float,
 ych_fct int,
 pl float,
 ych_pl int,
 ych1 int,
 ych2 int,
 ych3 int,
 ocenka1 int,
 ves1 float,
 ocenka2 int,
 ves2 float
)

 DECLARE CZadachiId CURSOR READ_ONLY
 FOR SELECT DISTINCT namez FROM Vesa;
 OPEN CZadachiId;

 FETCH NEXT FROM CZadachiId INTO @id;
 WHILE (@@FETCH_STATUS = 0)
 BEGIN
   INSERT INTO @TabAllVesa(idzadacha, begin_status, status, tab_num, fio, fct, ych_fct, pl, ych_pl, ych1, ych2, ych3, ocenka1, ves1, ocenka2, ves2)
     SELECT V.idzadacha, V.begin_status, V.status, V.tab_num, V.fio, V.fct, V.ych_fct, V.pl, V.ych_pl, V.ych1, V.ych2, V.ych3, V.ocenka1, V.ves1, V.ocenka2, V.ves2 FROM GetVesa(@id) AS V

   FETCH NEXT FROM CZadachiId INTO @id;
 END
   
 CLOSE CZadachiId;
 DEALLOCATE CZadachiId;

 Return;
end

---

← →
И. Павел   (2009-03-20 10:52) [14]

В дальнейшем база данных будет расти, и функция будет работать все медленнее. Пользователь будет читать таблицу не полностью, а выбирать определенные записи через фильтр. Поэтому я хочу сделать такой запрос, в котором функция считалась бы только для записей, которые будут выводиться на экран. Сейчас я пишу скалярные функции, так фильтр будет работать, но для расчета поля A требуется вызвать GetVesa1(...) а для расчета поля B нужно GetVesa1(...)*100, и тогда функция GetVesa1 будет выполняться 2 раза для каждой записи (хотя это лучше, так как только для отфильтрованных записей).


> sniknik ©

Запрос довольно большой, использую FROM LEFT JOIN LEFT JOIN ... я выбераю много полей из разных таблиц, связанных между собой. Теперь для каждой записи мне нужно добавить несколько полей, которые вычисляет функция GetVesa.

Записи примерно такие:
задача сотрудник [информация о сотруднике, задаче]   %   вес
 1          1234                    ...                                    10   10*X
 1          123                      ...                                    20   20*X
 2          345                     ...                                     70   70*X

Кроме этих полей вычисляются и другие. Идеальным вариантом было бы считать все эти поля в одной функции, так как они взаимосвязаны.

---

← →
sniknik ©   (2009-03-20 11:08) [15]

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

+ вызов вернет рекордсет.
повторю вопрос. как ты думаешь должно работать объединение если вместо одного раза в начале ему будут подсовывать рекордсет на каждую итерацию?

или поменять логику.
см. ЮЮ ©   (19.03.09 10:35) [1]

---

← →
ЮЮ ©   (2009-03-20 11:17) [16]

> Пользователь будет читать таблицу не полностью, а выбирать
> определенные записи через фильтр


надеюсь через WHERE, а не локальный фильтр :)

Как вариант, добавлять параметры фильтра в параметры функции и использовать из в WHERE запроса, выбирающего записи, подлежащие обсчёту.

Хотя главный запрос
  SELECT ...
  FROM sotrudniki
  WHERE sotrudniki.tab_num=@otv
  UNION
  SELECT ...
  FROM sotrudniki
  WHERE sotrudniki.tab_num<>@otv
говорит о том, что фильтр-то и невозможен :)

Кстати, если стоит ORDER BY и алгорит различен, то
1) заменить UNION  на UNION ALL
2) отакзаться от UNION, различия алгоритма поместить в case ... end

---

← →
ЮЮ ©   (2009-03-20 11:33) [17]

вместо Update @TabVesa проще сделать операции в селесте на первым запросом:

SELECT
 isnull(sum(fct), 0) SumFct, ...
FROM (

) g

---

← →
И. Павел   (2009-03-20 12:02) [18]

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


Можно ли это сделать, и, если можно, то как? В конечной таблице 60 полей, на каждое пользователь может наложить фильтр, выбрав операцию (=, >, <, <>) и значение (причем, в дальнейшем я буду писать другие виды фильтров: по диапазону, исключающие). Если передать в SQL-функцию фильтр через переменную типа Text, можно ли значение этой переменной использовать в предложении WHERE?


> как ты думаешь должно работать объединение если вместо одного
> раза в начале ему будут подсовывать рекордсет на каждую
> итерацию?


Я думал, что из базы записи выбираются так: для каждой записи в основной таблице, указанной в FROM выполняется первый JOIN, для каждой записи первого JOIN - 2-ой. Можно, ведь, написать так:
...
LEFT JOIN tab1 ON 1=1
LEFT JOIN tab2 ON tab2.id=tab1.id
...
Или же tab1 и tab2 сразу куда-то выгружаются, и потом уже оттуда идет их соединение. Если так, то в SQL функциях в JOIN вообще нельзя использовать меняющиеся аргументы.


> надеюсь через WHERE, а не локальный фильтр

Фильтр в Where


> ЮЮ ©

Спасибо за советы и варианты оптимизации. Попробую пока написать скалярные функции отдельно для ych_fct и ych_pl.

---

Страницы: 1 вся ветка

Форум: "Начинающим";
Текущий архив: 2009.05.03;
Скачать: [xml.tar.bz2];

Наверх

Память: 0.53 MB
Время: 0.006 c