游标的应用语句
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='陳先生'
作者 mmdlp3