各给兄弟姐妹:
在SQL SERVER 中想像写类似这样的循环该怎么办?
while(count > 0)
{
do something;
cout --;
}
比如:
set @count = (select count(*) from StockBillEntry where DocID = @DocID)
-- 遍历所有分录
while(@count > 0)
begin
--就是想按序号遍历记录集,可是在下面我写的地方会出现选出的记录不止一条
set @MateriaID = (select MaterialID from StockBillEntry where DocID = @DocID)
set @LocID = (select LocID from StockBillEntry where DocID = @DocID)
set @BachID = (select BatchNum from StockBillEntry where DocID = @DocID)
set @Duredate = (select StoragePeriod from StockBillEntry where DocID = @DocID)
set @ProductDate = (select ProductDate from StockBillEntry where DocID = @DocID)
set @GoodMove = (select LogisticCode from StockBillEntry where DocID = @DocID)
set @Amount = (select BasicQty from StockBillEntry where DocID = @DocID)
set @Money = (select PogOC from StockBillEntry where DocID = @DocID)
-- 找此记录是否存在,如果存在就更新,否则就插入
declare @Exist int
set @Exist = (select count(*) from IcInventory where AccountNO = @CompanyID and MaterialID = @MateriaID and StorehouseID = @StockHouse and LocID = @LocID and BatchNum = @BachID and StoragePeriod = @Duredate)
if(@Exist = 0)
-- 新增记录
insert into IcInventory(AccountNO ,MaterialID , StorehouseID ,LocID , BatchNum ,StoragePeriod,BasicQty,PogOC) values(@CompanyID,@MateriaID ,@StockHouse ,@LocID ,@BachID,@Duredate,@Amount,@Money)
else
begin
declare @Basic decimal
declare @Cost decimal
set @Cost = (select PogOC from IcInventory where AccountNO = @CompanyID and MaterialID = @MateriaID and StorehouseID = @StockHouse and LocID = @LocID and BatchNum = @BachID and StoragePeriod = @Duredate)
set @Cost = @Cost + @Money
-- 入库
if(@OpCode = 1)
set @Cost = @Cost * @IsRed * @InOut*1
-- 出库
if(@OpCode = 2)
set @Cost = @Cost * @IsRed * @InOut*-1
set @Basic = (select BasicQty from IcInventory where AccountNO = @CompanyID and MaterialID = @MateriaID and StorehouseID = @StockHouse and LocID = @LocID and BatchNum = @BachID and StoragePeriod = @Duredate)
set @Basic = @Basic + @Amount
set @Basic = @Basic * @IsRed * @InOut
-- 更新原有记录
update IcInventory set BasicQty = @Basic,PogOC = @Cost where AccountNO = @CompanyID and MaterialID = @MateriaID and StorehouseID = @StockHouse and LocID = @LocID and BatchNum = @BachID and StoragePeriod = @Duredate
end
set @count = @count - 1
end
在SQL SERVER 中想像写类似这样的循环该怎么办?
while(count > 0)
{
do something;
cout --;
}
比如:
set @count = (select count(*) from StockBillEntry where DocID = @DocID)
-- 遍历所有分录
while(@count > 0)
begin
--就是想按序号遍历记录集,可是在下面我写的地方会出现选出的记录不止一条
set @MateriaID = (select MaterialID from StockBillEntry where DocID = @DocID)
set @LocID = (select LocID from StockBillEntry where DocID = @DocID)
set @BachID = (select BatchNum from StockBillEntry where DocID = @DocID)
set @Duredate = (select StoragePeriod from StockBillEntry where DocID = @DocID)
set @ProductDate = (select ProductDate from StockBillEntry where DocID = @DocID)
set @GoodMove = (select LogisticCode from StockBillEntry where DocID = @DocID)
set @Amount = (select BasicQty from StockBillEntry where DocID = @DocID)
set @Money = (select PogOC from StockBillEntry where DocID = @DocID)
-- 找此记录是否存在,如果存在就更新,否则就插入
declare @Exist int
set @Exist = (select count(*) from IcInventory where AccountNO = @CompanyID and MaterialID = @MateriaID and StorehouseID = @StockHouse and LocID = @LocID and BatchNum = @BachID and StoragePeriod = @Duredate)
if(@Exist = 0)
-- 新增记录
insert into IcInventory(AccountNO ,MaterialID , StorehouseID ,LocID , BatchNum ,StoragePeriod,BasicQty,PogOC) values(@CompanyID,@MateriaID ,@StockHouse ,@LocID ,@BachID,@Duredate,@Amount,@Money)
else
begin
declare @Basic decimal
declare @Cost decimal
set @Cost = (select PogOC from IcInventory where AccountNO = @CompanyID and MaterialID = @MateriaID and StorehouseID = @StockHouse and LocID = @LocID and BatchNum = @BachID and StoragePeriod = @Duredate)
set @Cost = @Cost + @Money
-- 入库
if(@OpCode = 1)
set @Cost = @Cost * @IsRed * @InOut*1
-- 出库
if(@OpCode = 2)
set @Cost = @Cost * @IsRed * @InOut*-1
set @Basic = (select BasicQty from IcInventory where AccountNO = @CompanyID and MaterialID = @MateriaID and StorehouseID = @StockHouse and LocID = @LocID and BatchNum = @BachID and StoragePeriod = @Duredate)
set @Basic = @Basic + @Amount
set @Basic = @Basic * @IsRed * @InOut
-- 更新原有记录
update IcInventory set BasicQty = @Basic,PogOC = @Cost where AccountNO = @CompanyID and MaterialID = @MateriaID and StorehouseID = @StockHouse and LocID = @LocID and BatchNum = @BachID and StoragePeriod = @Duredate
end
set @count = @count - 1
end
解决方案 »
- 查询不重复的列
- sql关联查询问题
- SELECT * FROM {0} WHERE Remark LIKE '%{1}%'
- SQL中怎样排出年份的影响,按照月份、日期进行排序?
- 请问在2000 professional中能装SQl server 2000吗?
- aa是一个SMALLDATETIME型,请问它如何跟 date()比较大小?????
- 为什么建立索引后查询速度反而变慢了
- 祝贺游弋在数据库中的程序员MM和JJ们节日快乐!
- 。。。。。。。。。。。。。。。。。我不明白我已经按照规则写了这个存储过程那里有错?
- SQL Server请大侠指教!
- 如何将SQL server的表格高效率数据导出到Access
- 远程SQLServer服务器数据导出到本地Excel,最节省资源的方式?
IF (@count%100)=0 --每100行更新一次,根据实际测试动态SQL语句的长度来确认多少行更新一次。
BEGIN
EXECUTE(构造的更新SQL语句);
END在循环体外还要判断动态SQL语句 是否为空,不为空的时候 再次执行EXECUTE(构造的更新SQL语句);还有就是:
set @MateriaID = (select MaterialID from StockBillEntry where DocID = @DocID)
set @LocID = (select LocID from StockBillEntry where DocID = @DocID)
set @BachID = (select BatchNum from StockBillEntry where DocID = @DocID)
set @Duredate = (select StoragePeriod from StockBillEntry where DocID = @DocID)
set @ProductDate = (select ProductDate from StockBillEntry where DocID = @DocID)
set @GoodMove = (select LogisticCode from StockBillEntry where DocID = @DocID)
set @Amount = (select BasicQty from StockBillEntry where DocID = @DocID)
set @Money = (select PogOC from StockBillEntry where DocID = @DocID)
根据实际可以优化一下,不要在WHILE里面加很多SELECTFROM TableN ...语句,最好能优化一下。就这2个方法,就可以大大提高执行效率。
比如:
set @count = (select count(*) from StockBillEntry where DocID = '000000120070510000000000000055')
DECLARE authors_cursor CURSOR scroll FOR
SELECT entryid FROM stockbillentry where docid = '000000120070510000000000000055'
OPEN authors_cursor
-- 遍历所有分录
while(@count > 0)
begin
FETCH next FROM authors_cursor
-- 如果能把fetch出来的放到一个变量中就能解决我的问题了。
--SELECT entryid as dd FROM stockbillentry where docid = '000000120070510000000000000055'
set @count = @count -1
end
CLOSE authors_cursor
DEALLOCATE authors_cursor
set @MateriaID = (select MaterialID from StockBillEntry where DocID = @DocID)
set @LocID = (select LocID from StockBillEntry where DocID = @DocID)
set @BachID = (select BatchNum from StockBillEntry where DocID = @DocID)
set @Duredate = (select StoragePeriod from StockBillEntry where DocID = @DocID)
set @ProductDate = (select ProductDate from StockBillEntry where DocID = @DocID)
set @GoodMove = (select LogisticCode from StockBillEntry where DocID = @DocID)
set @Amount = (select BasicQty from StockBillEntry where DocID = @DocID)
set @Money = (select PogOC from StockBillEntry where DocID = @DocID)可以改成:
SELECT @MateriaID=MaterialID,@LocID=LocID,@BachID=BachID,@Duredate=Duredate ... FROM
StockBillEntry WHERE DocID = @DocID
这样是不是方便了很多,而且速度又加快了。