Можно ли обойтись без курсора?

← →
Павел Калугин © (2007-05-18 11:20) [0]

СУБД MSSQL
на входе
таблицы вида
Остатки счет | субсчет | дата откр | сумма Списания Счет | Дата | Сумма

порядок списания сумм
с самого старого субсчета, если там недостаточно то списать под ноль и остаток со следующего

Очевидный способ в лоб - крутим цикл по субсчетам и списываем последовательно

Возможно ли вместо цикла завернуть операцию в update?
Если да, то как это сделать это эффективнее?

← →
Jan1 (2007-05-18 11:30) [1]

а как это выглядит в курсоре?

← →
ЮЮ © (2007-05-18 11:30) [2]

В triggere INSTEAD OF INSERT таблицы Списания ревлизовать всю описанную логику. Толко всё равно без циклов никак

на клиенте останется только вставлять записи
INSET INTO Списания(Счет, Дата, Сумма) VALUES (:Account, :Date, :Amount)

← →
Павел Калугин © (2007-05-18 11:46) [3]

> [2] ЮЮ © (18.05.07 11:30)

клиента нет. Это импорт операций из соседней СУБД

> [1] Jan1 (18.05.07 11:30)

примерно так
declare oper cursor for select acc, opsumma
from oper
where
order by op_data asc

open oper

-- проверки что на всех счетах суммарно есть средства на операции fetch oper into @acc, @opsumma while @@fetch_status=0 begin while abs(@opsumma)>0 begin select top1 @suma = suma, @subacc = subacc from acc where acc=@acc and suma>0 if @suma+@opsumma>0 begin update @suma = @suma+@opsumma set @opsumma = 0 end else begin set @opsuma = @suma+@opsumma update @suma = 0 end end fetch oper into @acc, @opsumma end

← →
Павел Калугин © (2007-05-18 11:47) [4]

то есть мотается цикл по каждой операции.
а хочется обойтись без цикла...

← →
Val © (2007-05-18 11:49) [5]

если
> Это импорт операций из соседней СУБД
, тем более, я бы писал на процедурном языке сервера, хотя бы для того, чтобы нормально отладить можно было, а также проще расширить функционал, при возможном усложнении алгоритма.

← →
Павел Калугин © (2007-05-18 11:52) [6]

> [5] Val © (18.05.07 11:49)

Спасибо большое за подсказку.
Видимо Вы не знаете, что TSql является языком запросов, работающем непосрелдственно на сервере MSSql
Уще раз, большое спасибо за попытку помочь.
PS
И прочитайте что нибудь про order by

← →
ЮЮ © (2007-05-18 11:54) [7]

> а хочется обойтись без цикла...

Без неявного цикла или рекурсии всё равно не обойтись, т.к. заранее неясно, сколько записей приднтся проадейтить

>порядок списания сумм

Каких?
Тех что в таблице списания? Или туда записываются списанные суммы? Откуда они берутся?
З.Ы. Всё это как то чревато. Или данных недостаточно. А если ктр-то запусти процедуру списания повторно?

← →
Val © (2007-05-18 12:03) [8]

>[6] Павел Калугин © (18.05.07 11:52)
скажи, как поможет мне order by постичь тайны "TSql, языка запросов работающего непосредственно на сервере MSSql"? 80

← →
Павел Калугин © (2007-05-18 12:16) [9]

> [7] ЮЮ © (18.05.07 11:54)
> Откуда они берутся?

С соседнего сервера Данные по операциям
Прредполагается что в одну дату двух одинаковых операций по одному счету нет

> А если ктр-то запусти процедуру списания повторно?

получит бейсбольной битой по голове.

← →
Павел Калугин © (2007-05-18 12:17) [10]

> [8] Val © (18.05.07 12:03)

<offtop>
это поможет ответить на твой вопрос из соседней ветки
</offtop>

← →
Val © (2007-05-18 12:27) [11]

>[10] Павел Калугин © (18.05.07 12:17)
<offtop>
черт, ты меня просто попалил. :))
</offtop>
попробуй все-таки осилить написанное мной в посте по теме ветки.
и не говори больше никому, что в майкрософт придумали свой язык запросов на сервере, т.к. SQL, видимо, они не поняли..а то новые в этом люди поверят и будут нести дальше идею о сервере с двумя языками запросов.
P.S.наискось читаешь, что ли... :(

← →
Павел Калугин © (2007-05-18 12:40) [12]

> [11] Val © (18.05.07 12:27)

ну так поясни что такое "тем более, я бы писал на процедурном языке сервера," если не SQL?

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

смеялся:)

← →
ЮЮ © (2007-05-18 12:41) [13]

> получит бейсбольной битой по голове.

Садюга :)

Я бы, например, сделал бы так:

XП Списать(Счет, Дата, Сумма)
сразу можно и проверить (без биты) было списание или нет, достаточно средств или нет
если все хорошо, единственный update самого старого субсчета на всю сумму
В триггере INSTEAD OF UPDATE на Остатки при попытке установить отрицательный остаток устанавливал 0, а с излишками поступал бы внвдогично:
update самого старого субсчета, кроме себя любимого, на всю сумму.

Итого рекурсивно update вызвался бы столько раз, сколько потребовалость бы.

На клиенте (илм соседнем сервере, который тоже клиет для этого сервера) потребуется только единстаенный для одного счетв вызов ХП.
P.S. да и курсора вроде нет.
Нсли тправд никто не задумает групповой UPDATE для записей в Остатки. Тогда курсор по inserted/deleted надо будет предусмотреть

← →
Val © (2007-05-18 12:45) [14]

>[12] Павел Калугин © (18.05.07 12:40)
>если не SQL?
TSQL в данном случае, раз СУБД MSSQL.
>смеялся:)
досмеешься, прочитай что есть процедурные расширения языка SQL в промышленных СУБД.

← →
Павел Калугин © (2007-05-18 12:49) [15]

> [13] ЮЮ © (18.05.07 12:41)
>сразу можно и проверить (без биты) было списание или нет, достаточно средств или нет

Естественно проверяю Но тем не меннее бита всегда на готове. А то любят удалить старую операцию во вчера и провести аналогичную сегодня. Тут без биты никак

> Итого рекурсивно update вызвался бы столько раз, сколько
> потребовалость бы.

в таком случае работать оно будет быстрее курсора? существенно?

> Нсли тправд никто не задумает групповой UPDATE для записей
> в Остатки.

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

← →
Павел Калугин © (2007-05-18 12:50) [16]

> [14] Val © (18.05.07 12:45)
> досмеешься, прочитай что есть процедурные расширения языка
> SQL в промышленных СУБД.

почитаю с удовольствием если дашь ссылку где

← →
Павел Калугин © (2007-05-18 12:52) [17]

В основном я знаю о процедурных расширениях в связке со словом "PL/SQL", но не "TSQL"

← →
Павел Калугин © (2007-05-18 12:58) [18]

> [14] Val © (18.05.07 12:45)

В отличие от самого языка SQL, который отвечает требованиям стандартов ANSI, расширения SQL не стандартизованы. Любой производитель СУБД может разрабатывать собственный диалект расширенного SQL (в Oracle он называется PL/SQL, в Microsoft SQL Server — Transact-SQL, а в Microsoft Access — Jet SQL).
и в чем меня таки стремишся убедить? если и так на TSQL написано?

← →
Val © (2007-05-18 13:00) [19]

>почитаю с удовольствием если дашь ссылку где
уж будь добр, поищи сам.
>..связке со словом "PL/SQL", но не "TSQL"
а если оно в IB/FB PSQL зовется - расстроишься?

← →
ЮЮ © (2007-05-18 13:02) [20]

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

Они все принимают одно и тоже значение? :)
т.е. возможен запрос
UPDATE Остатки SET сумма = 10000, т.е.ОДИН UPDATE охватывает НЕСКОЛЬКО записей.

Я всё-таки полагая, сто на входе имеем столько записей, со скольки счетов надо списать, каждая со своей суммой, чтоо предрлагает столько же вызовов ХП или вставок в таблицу Списания с INSTEAD OF INSERT на ней. Удаление и редактирование в ней я бы вообще пользователям запретил, во избежание " любят удалить старую операцию во вчера и провести аналогичную сегодня".

З.Ы. А откуда дентги на таблицы вида Остатки берутся, если их только списывают?

← →
Val © (2007-05-18 13:05) [21]

>и в чем меня таки стремишся убедить?если и так на TSQL написано?
наконец-то. стремлюсь убедить не задаваться идеей переписать в запрос.
в данном случае, даже если бы это было возможно! - это не нужно, поскольку, появись у тебя новые условия(что более чем возможно) тебе пришлось бы перестраивать и без того сложный запрос, судя из условия задачи, если не менять его на процедуру. а в процедурном исполнении ты можешь контролировать каждую запись и добавлять сколь-угодно сложные новые условия обработки.

← →
Empleado © (2007-05-18 13:08) [22]

> СУБД MSSQL

2000 или 2005?

> [20] ЮЮ © (18.05.07 13:02)

т.е. возможен запрос
UPDATE Остатки SET сумма = 10000

скорее не так
update остатки
SET сумма =сумма+(select опер_сумма
from операции ....
)
....

C зачислениями проще - зачисление создает новый субсчет

> Я всё-таки полагая, сто на входе имеем столько записей,
> со скольки счетов надо списать

на входе имеем столько записей, сколько операций за период проведено
есть счета по которым не было операций есть несколько операций по счету (тут sum() рулит)

> [21] Val © (18.05.07 13:05)
> в данном случае, даже если бы это было возможно! - это не
> нужно,

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

> [22] Empleado © (18.05.07 13:08)

2000

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

> [24] Val © (18.05.07 13:39)
> получишь те
> же полные переборы при трудночитаемом/управляемом коде.

Убедил, речистый... :)
блин как ни крути а три вложенных цикла надо вертеть.. и никуда мне от них не дется..
или в лоб их или рекурсивно в триггере. а все равно крутить..

фуух, пойду, положу камешек в мешочек добрых дел :)

> фуух, пойду, положу камешек в мешочек добрых дел :)

Камушки таких размеров лучше хранить за пазухой - всегда под рукой, когда понадобятся :)

Наличие FROM у UPDATE позволяет построить сколь угодно сложный запрос, дающий набор записей, подлежащих корректировке, тем самым мы получим искомое - динамческое, зависящих от данных, кол-во строк, подлежащих изменению. Более того, отлаживая этот запрос в SELECT, мы сможем сначала убедиться в правильности предстоящих изменеиий:

1) неложно построит запрс (UDF) из Остатки такого вида
счет | субсчет | дата откр | сумма| сумма по более старым субсчетам (назовем его ОстаткиСуммарные)

2) пусть запрос(UDF) ОперацииСуммарные возвращает данные в виде
Счет | Дата | Сумма

Тогдв запрос
SELECT
o.[счет], o.[субсчет], o.[дата откр],
Case
when o.[сумма по более старым субсчетам] + o.[Сумма] < s.[Сумма]
then o.[Сумма]
else s.[Сумма] - o.[сумма по более старым субсчетам]
end НадоСписать
FROM
ОстаткиСуммарные o
JOIN ОперацииСуммарные s ON o.[счет] = s.[счет] AND o.[сумма по более старым субсчетам] < s.[Сумма]

вернет только те записи, которые требуеся изменть, а в поле НадоСписать рассчитано сколько именно надо вычесть от прежнего значенимя Сумма, чтобы оно обнулилось у части записей и уменьшилось у одной; при этом остальные записи не будут затронуты

Теперь помещаем этот запрос в FROM часть и получаем один Update без курсоров.

Я правильно понял, что UDF - это "определенная пользователем функция"?
может тогда удобнее первый запрос во вьюху завернуть?
то есть как оно правильнее с точки зрения сервера?
вот так
CREATE FUNCTION RestWithPrevRestF () RETURNS TABLE AS RETURN (select a.portfolioID as account_id,a.branchID as subaccount_id,a.ownerID,a.startDate, isnull(a.lastVolume,0) as Rest, sum(isnull(b.lastVolume, 0)) as SumRest from branches a left outer join branches b on b.portfolioID = a.portfolioID and b.ownerID = a.ownerID and b.startDate<a.startDate where a.lastVolume >0 -- Субсчет с нулевым остатком закрыт и не рассматривается group by a.portfolioID,a.branchID,a.lastVolume,a.ownerID,a.startDate )

или вот так
CREATE View RestWithPrevRestV AS select a.portfolioID as account_id,a.branchID as subaccount_id,a.ownerID,a.startDate, isnull(a.lastVolume,0) as Rest, sum(isnull(b.lastVolume, 0)) as SumRest from branches a left outer join branches b on b.portfolioID = a.portfolioID and b.ownerID = a.ownerID and b.startDate<a.startDate where a.lastVolume >0 -- Субсчет с нулевым остатком закрыт и не рассматривается group by a.portfolioID,a.branchID,a.lastVolume,a.ownerID,a.startDate

Я просто предпочитаю функции, потому как иногда одним SELECT-ом не обйдешься. Приходится и циклы и курсоры использовать.
+ Параметры, позволяющие уменьшить выборку, а View - это всё-таки ко всей таблице, а затем WHERE

> [29] ЮЮ © (22.05.07 12:16)

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

ЮЮ
Спасибо большое.
Заработало, подпрыгнуло и полетело!!!

Можно ли обойтись без курсора? Найти похожие ветки