大
大脚王
Unregistered / Unconfirmed
GUEST, unregistred user!
我原来使用SQL在SQL 2000中编写了一个存储过程，现在想移植到DB2数据库中，但是对于游标的使用不太明白，感觉差异挺大，请各位大虾指点以下，存储过程如下：
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE GETPENSION
@ANNALS NVARCHAR(20) --年报的时间，取值为（上半年、下半年）
AS
DECLARE @C_NAME NVARCHAR(100)
DECLARE @C_BIRTHDAY DATETIME
DECLARE @C_AGE INT
DECLARE @START_AGE INT
DECLARE @END_AGE INT
DECLARE @DESERVED_PENSION INT
DECLARE @DESERVED_PENSION1 INT
DECLARE @TAKEBOON_MONTH INT
DECLARE @TAKEBOON_MONTH1 INT
DECLARE @CURRENT_MONTH NVARCHAR(270)
DECLARE @C_MONTH INT
DECLARE @C_DAY INT
DELETE FROM TEMP_PENSION
DECLARE ALLPEPOLE CURSOR FOR SELECT PNAME, BIRTHDAY, YEAR(CURRENT TIMESTAMP)-YEAR(BIRTHDAY) FROM ADMINISTRATOR.ASSOCIATOR WHERE BOON = 1 ORDER BY KIND_ID,INTEGER(FAMILIAL_ID), PEOPEL_ID
OPEN ALLPEPOLE
FETCH NEXT FROM ALLPEPOLE
INTO @C_NAME, @C_BIRTHDAY, @C_AGE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DESERVED_PENSION = 0
SET @DESERVED_PENSION1 = 0
SET @TAKEBOON_MONTH = 0
SET @TAKEBOON_MONTH1 = 0
DECLARE MYCURSOR CURSOR SCROLL FOR SELECT STARAGE, ENDAGE, PENSION FROM 退休金基本信息表 ORDER BY STARAGE
OPEN MYCURSOR
FETCH ABSOLUTE 1 FROM MYCURSOR INTO @START_AGE,@END_AGE,@DESERVED_PENSION
WHILE @@FETCH_STATUS = 0
BEGIN
SET @C_MONTH = MONTH(@C_BIRTHDAY)
SET @C_DAY = DAY(@C_BIRTHDAY)
--等于最小的年龄段，则需要根据出生月份来判断享受的福利
IF (@C_AGE = @START_AGE) OR (@C_AGE = @END_AGE)
BEGIN
IF @ANNALS = '上半年'
BEGIN
IF ((6 - @C_MONTH = 0) AND (@C_DAY > 15)) OR (@C_MONTH > 6)
BEGIN
SET @TAKEBOON_MONTH = 0
END
ELSE
BEGIN
IF (@C_AGE = 45) AND (@C_MONTH = 6) AND (@C_DAY > 15)
BEGIN
SET @TAKEBOON_MONTH =0
END
ELSE IF @C_DAY > 15
BEGIN
SET @TAKEBOON_MONTH = 6 - @C_MONTH
END
ELSE
BEGIN
SET @TAKEBOON_MONTH = 6 - @C_MONTH + 1
END
END
END
ELSE IF @ANNALS = '下半年'
BEGIN
IF (@C_AGE = 45) AND (@C_MONTH = 12) AND (@C_DAY > 15)
BEGIN
SET @TAKEBOON_MONTH = 0
END
ELSE IF (@C_AGE = 45) AND ((@C_MONTH - 6) <= 0)
BEGIN
SET @TAKEBOON_MONTH = 6
END
ELSE IF (@C_AGE > 45) AND ((@C_MONTH - 6) <= 0)
BEGIN
SET @TAKEBOON_MONTH = 0
END
ELSE IF (@C_AGE = 45) AND ((@C_MONTH - 6) > 0)
BEGIN
IF @C_DAY > 15
BEGIN
SET @TAKEBOON_MONTH = @C_MONTH - 6
END
ELSE
BEGIN
SET @TAKEBOON_MONTH = @C_MONTH - 6 - 1
END
END
ELSE
BEGIN
IF @C_DAY > 15
BEGIN
SET @TAKEBOON_MONTH = @C_MONTH - 6
END
ELSE
BEGIN
SET @TAKEBOON_MONTH = @C_MONTH - 6 - 1
END
END
IF (@C_AGE > 45) AND ((6 - @TAKEBOON_MONTH) > 0)
BEGIN
SET @TAKEBOON_MONTH1 = 6 - @TAKEBOON_MONTH
FETCH NEXT FROM MYCURSOR INTO @START_AGE,@END_AGE,@DESERVED_PENSION
SET @DESERVED_PENSION1 = @DESERVED_PENSION
FETCH PRIOR FROM MYCURSOR INTO @START_AGE,@END_AGE,@DESERVED_PENSION
END
END
IF (@TAKEBOON_MONTH > 0) OR (@TAKEBOON_MONTH1 > 0)
BEGIN
INSERT INTO TEMP_PENSION (NAME,BIRTHDAY, AGE, PENSION1,MONTH1,PENSION2,MONTH2) VALUES (@C_NAME,@C_BIRTHDAY, @C_AGE, @DESERVED_PENSION,@TAKEBOON_MONTH,@DESERVED_PENSION1,@TAKEBOON_MONTH1)
BREAK
END
END
ELSE IF (@C_AGE > @START_AGE ) AND (@C_AGE < @END_AGE)
BEGIN
SET @TAKEBOON_MONTH = 6
INSERT INTO TEMP_PENSION (NAME,BIRTHDAY, AGE, PENSION1,MONTH1,PENSION2,MONTH2) VALUES (@C_NAME,@C_BIRTHDAY, @C_AGE, @DESERVED_PENSION,@TAKEBOON_MONTH,@DESERVED_PENSION1,@TAKEBOON_MONTH1)
BREAK
END
FETCH NEXT FROM MYCURSOR INTO @START_AGE,@END_AGE,@DESERVED_PENSION
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR
FETCH NEXT FROM ALLPEPOLE
INTO @C_NAME, @C_BIRTHDAY, @C_AGE
END
CLOSE ALLPEPOLE
DEALLOCATE ALLPEPOLE
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE GETPENSION
@ANNALS NVARCHAR(20) --年报的时间，取值为（上半年、下半年）
AS
DECLARE @C_NAME NVARCHAR(100)
DECLARE @C_BIRTHDAY DATETIME
DECLARE @C_AGE INT
DECLARE @START_AGE INT
DECLARE @END_AGE INT
DECLARE @DESERVED_PENSION INT
DECLARE @DESERVED_PENSION1 INT
DECLARE @TAKEBOON_MONTH INT
DECLARE @TAKEBOON_MONTH1 INT
DECLARE @CURRENT_MONTH NVARCHAR(270)
DECLARE @C_MONTH INT
DECLARE @C_DAY INT
DELETE FROM TEMP_PENSION
DECLARE ALLPEPOLE CURSOR FOR SELECT PNAME, BIRTHDAY, YEAR(CURRENT TIMESTAMP)-YEAR(BIRTHDAY) FROM ADMINISTRATOR.ASSOCIATOR WHERE BOON = 1 ORDER BY KIND_ID,INTEGER(FAMILIAL_ID), PEOPEL_ID
OPEN ALLPEPOLE
FETCH NEXT FROM ALLPEPOLE
INTO @C_NAME, @C_BIRTHDAY, @C_AGE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DESERVED_PENSION = 0
SET @DESERVED_PENSION1 = 0
SET @TAKEBOON_MONTH = 0
SET @TAKEBOON_MONTH1 = 0
DECLARE MYCURSOR CURSOR SCROLL FOR SELECT STARAGE, ENDAGE, PENSION FROM 退休金基本信息表 ORDER BY STARAGE
OPEN MYCURSOR
FETCH ABSOLUTE 1 FROM MYCURSOR INTO @START_AGE,@END_AGE,@DESERVED_PENSION
WHILE @@FETCH_STATUS = 0
BEGIN
SET @C_MONTH = MONTH(@C_BIRTHDAY)
SET @C_DAY = DAY(@C_BIRTHDAY)
--等于最小的年龄段，则需要根据出生月份来判断享受的福利
IF (@C_AGE = @START_AGE) OR (@C_AGE = @END_AGE)
BEGIN
IF @ANNALS = '上半年'
BEGIN
IF ((6 - @C_MONTH = 0) AND (@C_DAY > 15)) OR (@C_MONTH > 6)
BEGIN
SET @TAKEBOON_MONTH = 0
END
ELSE
BEGIN
IF (@C_AGE = 45) AND (@C_MONTH = 6) AND (@C_DAY > 15)
BEGIN
SET @TAKEBOON_MONTH =0
END
ELSE IF @C_DAY > 15
BEGIN
SET @TAKEBOON_MONTH = 6 - @C_MONTH
END
ELSE
BEGIN
SET @TAKEBOON_MONTH = 6 - @C_MONTH + 1
END
END
END
ELSE IF @ANNALS = '下半年'
BEGIN
IF (@C_AGE = 45) AND (@C_MONTH = 12) AND (@C_DAY > 15)
BEGIN
SET @TAKEBOON_MONTH = 0
END
ELSE IF (@C_AGE = 45) AND ((@C_MONTH - 6) <= 0)
BEGIN
SET @TAKEBOON_MONTH = 6
END
ELSE IF (@C_AGE > 45) AND ((@C_MONTH - 6) <= 0)
BEGIN
SET @TAKEBOON_MONTH = 0
END
ELSE IF (@C_AGE = 45) AND ((@C_MONTH - 6) > 0)
BEGIN
IF @C_DAY > 15
BEGIN
SET @TAKEBOON_MONTH = @C_MONTH - 6
END
ELSE
BEGIN
SET @TAKEBOON_MONTH = @C_MONTH - 6 - 1
END
END
ELSE
BEGIN
IF @C_DAY > 15
BEGIN
SET @TAKEBOON_MONTH = @C_MONTH - 6
END
ELSE
BEGIN
SET @TAKEBOON_MONTH = @C_MONTH - 6 - 1
END
END
IF (@C_AGE > 45) AND ((6 - @TAKEBOON_MONTH) > 0)
BEGIN
SET @TAKEBOON_MONTH1 = 6 - @TAKEBOON_MONTH
FETCH NEXT FROM MYCURSOR INTO @START_AGE,@END_AGE,@DESERVED_PENSION
SET @DESERVED_PENSION1 = @DESERVED_PENSION
FETCH PRIOR FROM MYCURSOR INTO @START_AGE,@END_AGE,@DESERVED_PENSION
END
END
IF (@TAKEBOON_MONTH > 0) OR (@TAKEBOON_MONTH1 > 0)
BEGIN
INSERT INTO TEMP_PENSION (NAME,BIRTHDAY, AGE, PENSION1,MONTH1,PENSION2,MONTH2) VALUES (@C_NAME,@C_BIRTHDAY, @C_AGE, @DESERVED_PENSION,@TAKEBOON_MONTH,@DESERVED_PENSION1,@TAKEBOON_MONTH1)
BREAK
END
END
ELSE IF (@C_AGE > @START_AGE ) AND (@C_AGE < @END_AGE)
BEGIN
SET @TAKEBOON_MONTH = 6
INSERT INTO TEMP_PENSION (NAME,BIRTHDAY, AGE, PENSION1,MONTH1,PENSION2,MONTH2) VALUES (@C_NAME,@C_BIRTHDAY, @C_AGE, @DESERVED_PENSION,@TAKEBOON_MONTH,@DESERVED_PENSION1,@TAKEBOON_MONTH1)
BREAK
END
FETCH NEXT FROM MYCURSOR INTO @START_AGE,@END_AGE,@DESERVED_PENSION
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR
FETCH NEXT FROM ALLPEPOLE
INTO @C_NAME, @C_BIRTHDAY, @C_AGE
END
CLOSE ALLPEPOLE
DEALLOCATE ALLPEPOLE
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO