游標代碼舉例

2024-07-21 02:09:21

字體：大中小

來源：轉載

供稿：網友

1.對低于當前平均價格的書，均提價50%

2對于高于或等于當前平均價格的書，均降價25%

代碼如下：

declare title_update cursor

for select title_id,price from titles

for update

go

局部變量

declare @avg_price money,@title_id tid,@price money

open title_update

begin tran

計算平均書價

select @avg_price=avg(price) from titles holdlock

fetch title_update into @title_id,@price

while @@sqlstatus!=2

begin

if @@sqlstatus=1

begin

rollback tran

raiserror 21001 "fetch failed in cursor"

close title_update

deallocate cursor title_update

return

end

if @price<@avg_price

提價50%

update titles set price = price * $1.50

where current of title_update

else

降價25%

update titles set price = price * $.75

where current of title_update

if @@error!=0

begin

rollback tran

raiserror 22001 "update failed"

close title_update

deallocate cursor title_update

return

end

fetch title_update into @title_id,@price

end

commit

close title_update

deallocate cursor title_update

go

網站運營seo文章大全

提供全面的站長運營經驗及seo技術!

上一篇：數據倉庫之我見(概念篇）

下一篇：SQLSREVER如何創建和使用動態游標