|
游标的应用语句 create proc Running_Total ( @CustName varchar(10) ) www.2cto.com 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 www.2cto.com 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 |