SqlServer 存储过程使用游标循环处理数据

业务场景

TA表中有比例字段及付款间隔字段；TB表中有月份、合同额、应付款字段。

例如付款间隔为2个月，付款比例为80%，则

3月份应付款=1月份合同额*80%

4月份应付款=2月份合同额*80%

5月份应付款=3月份合同额*80%

6月份应付款=4月份合同额*80%

… …

ALTER PROCEDURE PROC_BI_HTEYC(@DWID VARCHAR(36),@ND VARCHAR(4),@QJ VARCHAR(2))
AS
BEGIN
DECLARE @BL FLOAT --比例
DECLARE @JG INT --间隔
DECLARE @CURDATE DATETIME --当前日期
DECLARE @HTE FLOAT --合同额

SELECT @BL =CBL,@JG = CJG FROM TA WHERE CMPID = @DWID AND YEAR = @ND AND DATE = @QJ

DECLARE CUR_LOOP CURSOR FOR
SELECT CDATE,CFK FROM TB WHERE CMPID = @DWID AND YEAR = @ND AND DATE = @QJ ORDER BY DATA_KEY

OPEN CUR_LOOP
FETCH NEXT FROM CUR_LOOP INTO @CURDATE,@HTE
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE TB SET CYFK = @HTE*@BL WHERE DATEDIFF(MONTH,@CURDATE,CDATE)=@JG AND CMPID = @DWID AND YEAR = @ND AND DATE = @QJ
FETCH NEXT FROM CUR_LOOP INTO @CURDATE,@HTE
END
CLOSE CUR_LOOP

BEGIN

EXEC(‘SELECT CYFK FROM TB WHERE YEAR=’’’+@ND+’’’ and DATE=’’’+@QJ+’’’ AND CMPID = ‘’’+@DWID+’’’ ORDER BY DATA_KEY’)
END
END