游标的应用

   

create proc Running_Total
(
  @CustName varchar(10)
)
as
declare cur cursor for
select Order_Date,Cust_Name,
Qty  from Orders  where 1=1 and Cust_Name=@CustName order by Order_Date
--2.建立暂存数据表
--select Order_Date,Cust_Name,Qty,QTY AccQty into #Result from Orders where 1=0--空表,里面没有任何的数据
--变量声明
Declare @Order_Date DateTime ,@Cust_Name varchar(20),
@Qty NUMeric ,@AccQty Numeric
begin
set @AccQty=0--将累计变量初始化
--打开Cur
open cur
fetch next from cur into @Order_Date,@Cust_Name,@Qty
while  @@FETCH_STATUS=0--判断fetch 是否顺利执行
begin
set @AccQty =@AccQty+@Qty
 insert into  order1(Order_Date,Cust_Name,Qty,AccQty)
 values(@Order_Date,@Cust_Name,@Qty,@AccQty)
fetch next from cur into @Order_Date,@Cust_Name,@Qty
end
close cur
Deallocate cur
select distinct  * from order1 where Cust_Name=@CustName
end

exec Running_Total '陳先生'
select * from Orders
select * from order1 where Cust_Name='陳先生'

转载于:https://www.cnblogs.com/mmdlp/archive/2012/08/13/2636915.html