CURSOR 的应用(SqlServer2005 截取字符)

IF EXISTS (SELECT * FROM sysobjects WHERE [NAME]='proc_split_Code')
DROP PROC proc_split_Code
GO
CREATE PROC  proc_split_Code
AS
BEGIN
    SET NOCOUNT ON    
    --声明游标
    DECLARE TempCursor CURSOR FOR
            SELECT String FROM Code
    DECLARE @String NVARCHAR(1000)--字符串
    OPEN TempCursor 
        --游标下一行
        FETCH NEXT FROM TempCursor INTO @String
        
        WHILE (@@FETCH_STATUS=0) 
        BEGIN
            PRINT @String
            DECLARE @BiaoJi INT  -- 标记 
            DECLARE @Temp INT  --分隔符的位置
            DECLARE @Star INT  --开始位置
            DECLARE @Code NVARCHAR(100) --
            SET @Star = 0

            SET @String = REVERSE(@String)
            SET @Temp    = CHARINDEX(',',@String,@Star)
            SET @BiaoJi = SUBSTRING(@String,@Star,@Temp)

            PRINT @BiaoJi

            SET @String = REVERSE(@String)
            SET @Temp    = CHARINDEX(',',@String,@Star)
            SET @Star    = 0
            
            WHILE(@Temp>0)
            BEGIN
                SET @Temp=CHARINDEX(',',@String,@Star)
                IF @Temp >0
                BEGIN
                    SET @Code=SUBSTRING(@String,@Star,@Temp-@Star)
                    PRINT @Code
                    --插入到相应的表中
                    INSERT INTO Record(BiaoJi,Value,Time)
                    VALUES (@BiaoJi,@Code,getdate())
                END
                SET @Star=@Temp+1
            END
            PRINT '--line--end--@@rowcount(' + CAST(@@ROWCOUNT AS NVARCHAR(50))+')--'
            --游标下一行
            FETCH NEXT FROM TempCursor INTO @String        
            PRINT '--line--end--@@rowcount(' + CAST(@@ROWCOUNT AS NVARCHAR(50))+')--'
        END
    --关闭游标并释放    
    CLOSE TempCursor;
    DEALLOCATE TempCursor; 
END
GO

EXEC proc_split_Code

 

 

 

IF EXISTS (SELECT * FROM sysobjects WHERE [NAME]='proc_split_Code')
DROP PROC proc_split_Code
GO
CREATE PROC  proc_split_Code
AS
BEGIN
	SET NOCOUNT ON	
	--声明游标
    DECLARE TempCursor CURSOR FOR
			SELECT String FROM Code
	DECLARE @String NVARCHAR(1000)--字符串
	OPEN TempCursor 
		--游标下一行
		FETCH NEXT FROM TempCursor INTO @String
		
		WHILE (@@FETCH_STATUS=0) 
		BEGIN
			PRINT @String
			DECLARE @BiaoJi INT  -- 标记 
			DECLARE @Temp INT  --分隔符的位置
			DECLARE @Star INT  --开始位置
			DECLARE @Code NVARCHAR(100) --
			SET @Star = 0

			SET @String = REVERSE(@String)
			SET @Temp	= CHARINDEX(',',@String,@Star)
			SET @BiaoJi = SUBSTRING(@String,@Star,@Temp)

			PRINT @BiaoJi

			SET @String = REVERSE(@String)
			SET @Temp	= CHARINDEX(',',@String,@Star)
			SET @Star	= 0
			
			WHILE(@Temp>0)
			BEGIN
				SET @Temp=CHARINDEX(',',@String,@Star)
				IF @Temp >0
				BEGIN
					SET @Code=SUBSTRING(@String,@Star,@Temp-@Star)
					PRINT @Code
					--插入到相应的表中
					INSERT INTO Record(BiaoJi,Value,Time)
					VALUES (@BiaoJi,@Code,getdate())
				END
				SET @Star=@Temp+1
			END
			PRINT '--line--end--@@rowcount(' + CAST(@@ROWCOUNT AS NVARCHAR(50))+')--'
			--游标下一行
			FETCH NEXT FROM TempCursor INTO @String		
			PRINT '--line--end--@@rowcount(' + CAST(@@ROWCOUNT AS NVARCHAR(50))+')--'
		END
	--关闭游标并释放	
	CLOSE TempCursor;
	DEALLOCATE TempCursor; 
END
GO

EXEC proc_split_Code

 

这是在上文中的做了修改用游标的方式实现一条一条记录的读取，其中也演示了FETCH NEXT ** 会影响@@ROWCOUNT的值.