Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
Сергей Прокопенко 8
Member

Откуда: Sevastopol
Сообщений: 20
Объясните пожалуйста в чем затруднение, или подскажите если не трудно другой способ решения задачи:

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

Поскольку в колонке изначально значения нет, то порядковый номер беру(возможно ошибочно) из суммы пустых полей этой таблицы:
SELECT count(*)
FROM  table1 t1
where t1.DATEGEN = @date_akt -- месячная выборка
and t1.cod <> 0


Результат этого запроса инкриминирую и помещаю в пользовательскую функцию вместе с алгоритмом расчета:

CREATE FUNCTION get_cod(@head char(8), @date_akt date) -- кроме даты константа для расчета
	RETURNS char(14)
	BEGIN
		declare @used_cod int;
		declare @used_cod_char char(5);
		declare @core char(13);
                ....
		declare @result char(14);
		SET @used_cod = (SELECT count(*) -- место, где считаю сколько уже занесено кодов
			FROM  table1 t1
			where t1.DATEGEN = @date_akt
			and t1.cod <> 0)
		SET @used_cod = @used_cod +1 -- получаю след.номер
		SET @used_cod_char= STUFF('00000',6-(len(@used_cod)),len(@used_cod),RTRIM(LTRIM(CAST(@used_cod AS char))));	-- это маска для №3 будет например 00003
		SET @core= @head + @used_cod_char --- продолжаю собирать контрольную сумму
		SET @noteven = (select CAST(left(@core,1) AS int)
			+ CAST(SUBSTRING(@core,3,1) AS int)
                 .....   --- тело формулы по условию, всякие расчеты по чет/не чет и т.д. 
		SET @result = @core + CAST(@remainder AS char(1)) -- конец вычисления контрольного числа, тоже к теме не имеет отношения
		RETURN @result
END


Прогнав на select-е при пустой колонке cod и при полностью заполненной доп. запросом убеждаюсь что контрольная сумма 1-го и последних чисел верна. Вроде-бы функция написана верно.

Теперь запускаю запрос на заполнение:

Update t1 set t1.cod = dbo.get_cod('77720190228',@dategen)
FROM   dbo.person p
INNER JOIN table1 t1 on (t1.acc = p.c_acc)	 
where p.n_district=2 and t1.DATEGEN = @dategen --  в реальной таблице связей больше, но к теме это не имеет отношения ets.


Запрос отрабатывает на ожидаемом кол-ве строк, но выдает в колонке cod только 1-ю контрольную сумму на всей выборке.
Почему? не сработало!
Подсознательно понимаю... что транзакция update запроса не завершилась до конца все выборки и в поле cod пусто, и функция выгребая кол-во заполненных полей не получает постепенное нарастание т.к. запрос вызвавший ее еще на отработал. Наверно это
самое простое объяснение, но мне как быть, был бы это PHP поставил бы глобальную переменную и черпал бы из нее нарастающую величину. А тут как быть...

Обернул запрос в пару BEGIN TRANSACTION - WAITFOR DELAY '00:00:01.00' -COMMIT TRAN но это ожидаемо не помогло.

Помогите пожалуйста советом малопрактикующему sql-програмисту.

Сообщение было отредактировано: 28 фев 19, 17:36
28 фев 19, 17:28    [21822019]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Сергей Прокопенко 8,

Через cross apply вызови, а так бред чистой воды
28 фев 19, 18:02    [21822049]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
court
Member

Откуда:
Сообщений: 2013
TaPaK
Сергей Прокопенко 8,

Через cross apply вызови, а так бред чистой воды
не поможет

ТС, хочет видеть при каждом "следующем" вызове функции в Update , к-во "обновившихся" записей в этом же Update !
Вообщем, там "проце-дурный" подход в самом кондовом его виде :)

ТС,

Будет что-то типа этого
;with cte as (
  SELECT 
    t1.*
    ,used_cod = sum(case when t1.cod <> 0 then 1 else 0 end)over()
    ,curr_cod_add = row_number()over(order by case when t1.cod <> 0 then 1 else 0 end)
  FROM   dbo.person p
  INNER JOIN table1 t1 on (t1.acc = p.c_acc)	 
  where p.n_district=2 and t1.DATEGEN = @dategen
)
update cte
set = /* вот тут должна быть вся та "вакханалия", которую ты делаешь в функции. Твой @used_cod (из функции) для каждой записи будет равен used_cod+curr_cod_add */
where t1.cod = 0

Выполни запрос из cte отдельно и посмотри результат, - думаю поймешь идею ...
28 фев 19, 18:28    [21822079]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
court
Member

Откуда:
Сообщений: 2013
fix
court
;with cte as (
  SELECT 
    t1.*
    ,used_cod = sum(case when t1.cod <> 0 then 1 else 0 end)over()
    ,curr_cod_add = row_number()over(order by case when t1.cod <> 0 then 1 else 0 end)
  FROM   dbo.person p
  INNER JOIN table1 t1 on (t1.acc = p.c_acc)	 
  where p.n_district=2 and t1.DATEGEN = @dategen
)
update cte
set cte.cod = /* вот тут должна быть вся та "вакханалия", которую ты делаешь в функции. Твой @used_cod (из функции) для каждой записи будет равен used_cod+curr_cod_add */
where cte.cod = 0
28 фев 19, 18:32    [21822081]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
Сергей Прокопенко 8
Member

Откуда: Sevastopol
Сообщений: 20
Спасибо court за помощь.
Я нарушил правило: не брать данные в функции из вне, кроме, как только из параметров функции.
1 шаг, создаю доп.поле cod1
2. обновляю его номером строки через over()
3. update таблицы той же функцией но уже с 3-мя параметрами, где новый - наращенный cod
4. подтираю - удаляю столбец сod1

Хотя можно и через CTE-красивее

Спасибо.
28 фев 19, 20:48    [21822192]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Сергей Прокопенко 8,
автор
Я нарушил правило: не брать данные в функции из вне, кроме, как только из параметров функции.

2 Правило бойцовского клуба?
28 фев 19, 21:06    [21822205]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
Сергей Прокопенко 8
Я нарушил правило: не брать данные в функции из вне, кроме, как только из параметров функции.
Вы в своем update всего-то нарвались на Halloween Problem.
28 фев 19, 21:19    [21822216]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
invm
Сергей Прокопенко 8
Я нарушил правило: не брать данные в функции из вне, кроме, как только из параметров функции.
Вы в своем update всего-то нарвались на Halloween Problem.

нет
28 фев 19, 21:22    [21822221]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
TaPaK
нет
План запросите у ТС'а.
28 фев 19, 21:30    [21822227]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
invm
TaPaK
нет
План запросите у ТС'а.

автор
SET @used_cod = (SELECT count(*) -- место, где считаю сколько уже занесено кодов
FROM table1 t1
where t1.DATEGEN = @date_akt
and t1.cod <> 0)

это явно не оттуда
28 фев 19, 21:31    [21822228]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
TaPaK,

https://sqlperformance.com/2013/02/sql-plan/halloween-problem-part-4
Раздел "SCHEMABINDING and T-SQL Scalar Functions"
1 мар 19, 10:24    [21822458]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
invm,
вот план его запроса, разве Eager Spool не есть "та защита" от Halloween ?

Картинка с другого сайта.
1 мар 19, 10:40    [21822475]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
TaPaK
invm,
вот план его запроса, разве Eager Spool не есть "та защита" от Halloween ?

Картинка с другого сайта.

Так там в конце как раз и рассказано, что использование не SCHEMABOUND функции, заставляет предполагать оптимизатор в необходимости защиты, а не сам факт ее необходимости.
1 мар 19, 10:55    [21822487]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Руслан Дамирович
TaPaK
invm,
вот план его запроса, разве Eager Spool не есть "та защита" от Halloween ?

Картинка с другого сайта.

Так там в конце как раз и рассказано, что использование не SCHEMABOUND функции, заставляет предполагать оптимизатор в необходимости защиты, а не сам факт ее необходимости.

и?
1 мар 19, 10:57    [21822488]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Руслан Дамирович,

точнее если читать не только поперёк, а ещё и вдоль (как крокодила), то вся суть сводится к
автор
Unless a scalar function is declared with the SCHEMABINDING option, SQL Server assumes the function accesses tables.

а тут хоть вдоль хоть поперёк обращение к таблице есть. Что соответчсеввно с доблаением даже SCHEMABINDING в планах ничего не меняет
1 мар 19, 11:02    [21822493]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
TaPaK,

Лично я считаю, что дело в детерминированности, а не в SCHEMABINDING.
В недетерминированной скалярной функции потенциально возможен доступ к данным, поэтому в план добавляется halloween protection.
1 мар 19, 11:31    [21822512]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
invm
TaPaK,

Лично я считаю, что дело в детерминированности, а не в SCHEMABINDING.
В недетерминированной скалярной функции потенциально возможен доступ к данным, поэтому в план добавляется halloween protection.

так проблема не в Halloween Problem?
1 мар 19, 11:32    [21822514]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
TaPaK
так проблема не в Halloween Problem?
Решили попридираться к словам?
Ок, дело в защите от Halloween Problem.
1 мар 19, 11:42    [21822537]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
court
...

Тогда уж так как-то:
+
CREATE FUNCTION get_cod( @head CHAR(8), @used_code INT )
RETURNS CHAR(20)
WITH SCHEMABINDING
	BEGIN
		DECLARE
      @char CHAR(12),
		  @core CHAR(20),
      ....
		  @result char(20)
    ;
		SET @char = RIGHT( REPLICATE( '0', 11 ) + CONVERT( VARCHAR(12), @used_code ), 11 );	-- это маска для №3 будет например 00003
		SET @core= @head + @char --- продолжаю собирать контрольную сумму
		SET @noteven = (select CAST(left(@core,1) AS int)
			+ CAST(SUBSTRING(@core,3,1) AS int)
                 .....   --- тело формулы по условию, всякие расчеты по чет/не чет и т.д. 
		SET @result = @core + CAST( @remainder AS char(1) ) -- конец вычисления контрольного числа, тоже к теме не имеет отношения
		RETURN @result
END
;
DECLARE @dategen DATE = GETDATE()
;
WITH 
cte as (
  SELECT 
    t1.[cod],
    [used_code] = row_number()over(order by case when t1.cod = 0 then 1 else 0 end)
  FROM
    dbo.person p
    INNER JOIN table1 t1 on (t1.acc = p.c_acc)	 
  WHERE
    p.n_district=2 and t1.DATEGEN = @dategen
)
UPDATE 
  cte
SET 
  cod = get_cod( '77720190228',[used_code] )
WHERE 
  cod = 0
;

1 мар 19, 17:07    [21822924]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7754
Подозреваю, что автор хотел на самом деле вот это сделать:

declare @val int = 0
update tbl set @val = @val + 1, fn_some_calculation(@val, tbl.field1, tbl.field2)
1 мар 19, 18:58    [21823041]     Ответить | Цитировать Сообщить модератору
 Re: Возвращение из пользовательской функции значения по наростающей в Update запрос  [new]
Сергей Прокопенко 8
Member

Откуда: Sevastopol
Сообщений: 20
Все гениальное просто:

Владислав Колосов
Подозреваю, что автор хотел на самом деле вот это сделать:

declare @val int = 0
update tbl set @val = @val + 1, fn_some_calculation(@val, tbl.field1, tbl.field2)

Спасибо, Владислав Колосов

У CTE есть 1н большой минус, коллеги замещающие тебя во время больничного или отпуска решительно не приемлют такую форму записи и норовят впихнуть пароль от TeamViewer-ра.
2 мар 19, 22:01    [21823471]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить