alter procedure proex128(@syear char(5)) --定义存储过程
as
begin
declare @no char(8),@sum numeric(7,2),@total numeric(7,2) --定义变量
create table #nzj(
salerNo44 char(8),
total numeric(7,2)) --创建临时表
declare curex128 cursor for
select salerNo44,sum(total)
from OrderMaster44 a,(select c.orderNo44,sum(c.quantity44*price44) total
from OrderDetail44 c,OrderMaster44 d
where c.orderNo44=d.orderNo44 and c.orderNo44 like '@syear'
group by c.orderNo44) b
where a.orderNo44=b.orderNo44
group by salerNo44 -- 定义游标
open curex128
fetch curex128 into @no,@sum
while(@@fetch_status=0)
begin
if @sum>5000
select @total=(@sum-5000)*0.15+500
else
select @total=@sum*0.1
insert into #nzj values (@no,@total)
fetch curex128 into @No,@sum
end --循环
close curex128
deallocate curex128
select * from #nzj --显示临时表的内容,同时将临时表的内容返回给调用者
end
as
begin
declare @no char(8),@sum numeric(7,2),@total numeric(7,2) --定义变量
create table #nzj(
salerNo44 char(8),
total numeric(7,2)) --创建临时表
declare curex128 cursor for
select salerNo44,sum(total)
from OrderMaster44 a,(select c.orderNo44,sum(c.quantity44*price44) total
from OrderDetail44 c,OrderMaster44 d
where c.orderNo44=d.orderNo44 and c.orderNo44 like '@syear'
group by c.orderNo44) b
where a.orderNo44=b.orderNo44
group by salerNo44 -- 定义游标
open curex128
fetch curex128 into @no,@sum
while(@@fetch_status=0)
begin
if @sum>5000
select @total=(@sum-5000)*0.15+500
else
select @total=@sum*0.1
insert into #nzj values (@no,@total)
fetch curex128 into @No,@sum
end --循环
close curex128
deallocate curex128
select * from #nzj --显示临时表的内容,同时将临时表的内容返回给调用者
end