游标替代
USE TEST
GO
CREATE TABLE LX1(
INT_I INT,
VAR_A VARCHAR(32),
VAR_B VARCHAR(32)
)
GO
INSERT INTO LX1 VALUES(1,'A','E')
INSERT INTO LX1 VALUES(2,'B','F')
INSERT INTO LX1 VALUES(3,'C','G')
INSERT INTO LX1 VALUES(4,'D','H')
GO
---------------------------
--原表能确定唯一的单一主键。
--如果是多主键,可以在表变量中添加自增字段(IDENTITY)或字符型的组合字段( KEY1 + '|' + KEY2 + '| + KEY3 ... ) 。
---------------------------
DECLARE @INT_I INT
DECLARE @VAR_A VARCHAR(32)
DECLARE @VAR_B VARCHAR(32)
DECLARE @TAB_LX TABLE (INT_I INT,VAR_A VARCHAR(32),VAR_B VARCHAR(32))
INSERT INTO @TAB_LX SELECT * FROM LX1
SELECT @INT_I=MIN(INT_I) FROM @TAB_LX
WHILE NOT @INT_I IS NULL BEGIN
SELECT @VAR_A=VAR_A,@VAR_B=VAR_B FROM @TAB_LX WHERE INT_I=@INT_I
--提取值
PRINT @VAR_A + ' ' + @VAR_B
SELECT @INT_I=MIN(INT_I) FROM LX1 WHERE INT_I>@INT_I
END
GO
---------------------------
--原表没有唯一主键
--可在表变量中添加一个自增编号。
---------------------------
DECLARE @INT_ID INT
DECLARE @VAR_A VARCHAR(32)
DECLARE @VAR_B VARCHAR(32)
DECLARE @TAB_LX TABLE (INT_ID INT IDENTITY(1,1) ,INT_I INT,VAR_A VARCHAR(32),VAR_B VARCHAR(32))
INSERT INTO @TAB_LX (INT_I,VAR_A,VAR_B) SELECT * FROM LX1
SELECT @INT_ID=MIN(INT_ID) FROM @TAB_LX
WHILE NOT @INT_ID IS NULL BEGIN
SELECT @VAR_A=VAR_A,@VAR_B=VAR_B FROM @TAB_LX WHERE INT_ID=@INT_ID
---提取值。
PRINT @VAR_A + ' ' + @VAR_B
SELECT @INT_ID=MIN(INT_ID) FROM @TAB_LX WHERE INT_ID>@INT_ID
END
GO