谁用过游标,举个例子
如题 问题点数：100、回复次数：3Top
1 楼readersm68（地主）回复于 2004-09-01 08:35:16 得分 30
/*8 设置清单（预出库） 更新临时表中库存、在单量、当前订单量的数量（所有商品目录）*/
/*先取出销售订单清单中的订单数量，库存中的数量，预出库单中的数量，更新临时表中的数量*/
ALTER PROCEDURE SMSP_RefreshTempSaleOrderLog
@OrderOutOdd01 varchar(20)
AS
DECLARE
@Goods01 varchar(30), /*商品编号*/
@Num numeric(11,2), /*当前某一个订单清单的订货数量*/
@ResNum numeric(11,2), /*当前某一个订单清单的可发数量*/
@SaveNum Numeric(11,2), /*某一个商品的库存数量*/
@OrderNum Numeric(11,2), /*当前所有预出库清单的某一个预出库数量*/
@Goods02 varchar(30), /*商品名称*/
@Goods03 varchar(15), /*图（型）号*/
@Goods04 varchar(15), /*原厂编号*/
@Goods05 varchar(20), /*适用对象*/
@Goods11 varchar(4), /*计量单位*/
@OutPrice numeric(15,6) /*调拨价*/
/*用商品目录创建游标*/
DECLARE Goods CURSOR LOCAL FOR
SELECT Goods01,Goods02,Goods03,Goods04,Goods05,Goods11,Goods23
FROM Goods
OPEN Goods
FETCH NEXT FROM Goods INTO @Goods01,@Goods02,@Goods03,@Goods04,@Goods05,@Goods11,@OutPrice
WHILE @@FETCH_STATUS = 0
BEGIN
/*汇总库存中的结存数*/
SELECT @SaveNum=SUM(Storage12)
FROM Storage
WHERE Storage12>0
AND Goods01 = @Goods01
/*子查询 对应于汇总库存中的商品编号，汇总预出库清单中的商品数量，销售订单经过预出库，转化为预出库单，锁定库存数量
SELECT @OrderNum=SUM(OrderOutLog02)
FROM OrderOutLog
WHERE Goods01 = @Goods01
AND OrderOutOdd01 IN
(SELECT OrderOutOdd01 FROM OrderOutOdd WHERE OrderOutOdd18 IN ('2 审核','3 批准','4 财务审核','5 验货','6 质检'))*/
/*连接的性能比子查询要好*/
SELECT @OrderNum=SUM(OrderOutLog.OrderOutLog02)
FROM OrderOutLog
JOIN OrderOutOdd ON OrderOutLog.OrderOutOdd01=OrderOutOdd.OrderOutOdd01
WHERE OrderOutOdd.OrderOutOdd18 IN ('2 审核','3 批准','4 财务审核','5 验货','6 质检')
AND OrderOutLog.Goods01=@Goods01
/*对应于销售订单清单中的商品数量*/
SELECT @Num=SUM(OrderOutLog02)
FROM OrderOutLog
WHERE OrderOutOdd01= @OrderOutOdd01
AND Goods01 = @Goods01
AND OrderOutLog02>0
/*对应于销售订单清单中的可发数量*/
SET @ResNum =ISNUll(@SaveNum,0)-ISNULL(@OrderNum,0)
IF @ResNum=0
SET @ResNum = NULL
/*更新*/
UPDATE #TempSaleOrderLog SET SaveNum= @SaveNum, OrderNum= @OrderNum, Num=@Num,ResNum=@ResNum
WHERE Goods01 = @Goods01
FETCH NEXT FROM Goods INTO @Goods01,@Goods02,@Goods03,@Goods04,@Goods05,@Goods11,@OutPrice
END
CLOSE Goods
DEALLOCATE Goods
/*---------------*/
GO
Top
2 楼readersm68（地主）回复于 2004-09-01 08:37:53 得分 50
/*用创建游标*/
DECLARE mycursor CURSOR LOCAL FOR
SELECT c1
FROM T1
OPEN mycursor
FETCH NEXT FROM mycursor INTO @var
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM mycursor INTO @var
END
CLOSE mycursor
DEALLOCATE mycursor
Top
3 楼llthero（星晴(七里香)）回复于 2004-09-01 08:41:51 得分 20
这是一用品入库的游标
Declare @ThingCode Varchar(8),@Count Numeric(8),@amount Numeric(8,2),@BuyPrice Numeric(8,2),@BillNum Varchar(30)
Declare @Wbm Varchar(4),@Pym Varchar(4),@ThingSmallCode Varchar(5),@Result Varchar(30),@Unit Varchar(10),@WarehouseCode Varchar(4)
DECLARE CurSor1 CURSOR
FOR Select BillNum,ThingCode,Wbm,Pym,ThingSmallCode,Result,Unit,amount,BuyPrice,WarehouseCode From dbo.T_yprkxxk Where SelectSign='√' And JoinSign is Null
OPEN CurSor1
Fetch CurSor1 Into @BillNum,@ThingCode,@Wbm,@Pym,@ThingSmallCode,@Result,@Unit,@amount,@BuyPrice,@WarehouseCode
WHILE @@FETCH_STATUS=0
BEGIN
Select @Count=Count(*) From T_lbypk Where ThingCode=@ThingCode And WarehouseCode=@WarehouseCode
If @Count>0
Begin
Update T_lbypk Set amount=amount+@amount,Price=Price+Round(@BuyPrice*@amount,2) Where ThingCode=@ThingCode And WarehouseCode=@WarehouseCode
End Else
Begin
Insert Into T_lbypk(ThingCode,Wbm,Pym,ThingSmallCode,Result,Unit,amount,Price,WarehouseCode)
Values(@ThingCode,@Wbm,@Pym,@ThingSmallCode,@Result,@Unit,@amount,Round(@BuyPrice*@amount,2) ,@WarehouseCode)
End
Select @BillNum=Null,@ThingCode=Null,@Wbm=Null,@Pym=Null,@ThingSmallCode=Null,@Result=Null,@Unit=Null,@amount=Null,@BuyPrice=Null,@WarehouseCode=Null
Fetch CurSor1 Into @BillNum,@ThingCode,@Wbm,@Pym,@ThingSmallCode,@Result,@Unit,@amount,@BuyPrice,@WarehouseCode
END
Close CurSor1
Deallocate CurSor1Top
相关问题
关键词
得分解答快速导航
相关链接
广告也精彩
反馈
