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
|
本类阅读TOP10