DECLARE @nmSupport NUMERIC(14,4),@nmOutst NUMERIC(14,4),@chPart VARCHAR(16) SELECT @chPart = 'XXX' SELECT @nmOutst = 0 SELECT @nmSupport = 0 UPDATE acdb.PAC06.AC06004A SET nmSupport = CASE WHEN chPart = @chPart THEN CASE WHEN nmQty > @nmOutst THEN @nmOutst ELSE nmQty END ELSE CASE WHEN nmQty > nmOutst THEN nmOutst ELSE nmQty END END, nmOutst = CASE WHEN chPart = @chPart then case when @nmOutst>=@nmSupport then @nmOutst-@nmSupport else 0 end else nmOutst-nmSupport END, @nmOutst = CASE WHEN chPart = @chPart then case when @nmOutst>=@nmSupport then @nmOutst-@nmSupport else 0 end else nmOutst-nmSupport END, @nmSupport = case when chPart = @chPart then case when nmQty > @nmOutst then @nmOutst else nmQty end ELSE CASE WHEN nmQty > nmOutst THEN nmOutst ELSE nmQty END end, @chPart = chPart改成用游标遍历的方式
时间: 2024-03-11 08:46:59 浏览: 26
好的,以下是将代码改写为使用游标遍历的方式:
```
DECLARE @nmSupport NUMERIC(14,4), @nmOutst NUMERIC(14,4), @chPart VARCHAR(16)
-- 声明游标
DECLARE cur CURSOR FOR
SELECT chPart, nmQty, nmOutst, nmSupport
FROM acdb.PAC06.AC06004A
-- 初始化变量
SET @chPart = 'XXX'
SET @nmOutst = 0
SET @nmSupport = 0
-- 打开游标
OPEN cur
-- 循环遍历游标中的每一行数据
FETCH NEXT FROM cur INTO @chPart, @nmQty, @nmOutst, @nmSupport
WHILE @@FETCH_STATUS = 0
BEGIN
-- 更新 nmSupport 和 nmOutst 字段的值
UPDATE acdb.PAC06.AC06004A
SET nmSupport = CASE
WHEN chPart = @chPart
THEN CASE
WHEN nmQty > @nmOutst
THEN @nmOutst
ELSE nmQty
END
ELSE CASE
WHEN nmQty > nmOutst
THEN nmOutst
ELSE nmQty
END
END,
nmOutst = CASE
WHEN chPart = @chPart
THEN CASE
WHEN @nmOutst >= @nmSupport
THEN @nmOutst - @nmSupport
ELSE 0
END
ELSE nmOutst - nmSupport
END
WHERE CURRENT OF cur
-- 更新变量的值
SET @nmOutst = CASE
WHEN chPart = @chPart
THEN CASE
WHEN @nmOutst >= @nmSupport
THEN @nmOutst - @nmSupport
ELSE 0
END
ELSE nmOutst - nmSupport
END
SET @nmSupport = CASE
WHEN chPart = @chPart
THEN CASE
WHEN nmQty > @nmOutst
THEN @nmOutst
ELSE nmQty
END
ELSE CASE
WHEN nmQty > nmOutst
THEN nmOutst
ELSE nmQty
END
END
SET @chPart = chPart
-- 继续遍历下一行数据
FETCH NEXT FROM cur INTO @chPart, @nmQty, @nmOutst, @nmSupport
END
-- 关闭游标
CLOSE cur
DEALLOCATE cur
```
以上代码使用游标遍历了表中的每一行数据,并按原代码的逻辑更新了 nmSupport 和 nmOutst 字段的值。同时,变量的更新也改为了在循环中进行。
相关推荐
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![](https://img-home.csdnimg.cn/images/20210720083646.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)