基本游标

#所有 SQL 语句都在行集上运行。SELECT 语句返回包含所有满足 SELECT 语句限定条件的行的完整结果集。
#应用程序需要一次处理结果集的一行或一个行块。游标是结果集的逻辑扩展，它使应用程序得以逐行处理结果集。
#1.基本游标使用

DECLARE MyCursor CURSOR
FOR SELECT strName,iPeriod from tbl_periodPara

declare @tmpStrName varchar(40),@tmpiPeriod varchar(40)

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @tmpStrName,@tmpiPeriod

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'strName =' + @tmpStrName + ' iPeriod =' + @tmpiPeriod
FETCH NEXT FROM MyCursor
INTO @tmpStrName,@tmpiPeriod
END

CLOSE MyCursor
DEALLOCATE MyCursor

#2.创建以游标为返回参数的存储过程
if EXISTS (SELECT * FROM dbo.sysobjects where name = 'TestCursor')
drop procedure TestCursor
GO

create procedure TestCursor
@startTime int,
@endTime int,
@iCursor CURSOR VARYING OUTPUT
as
declare @result int
set @result = 2
SET @iCursor = CURSOR FORWARD_ONLY STATIC FOR select strName,iPeriod from tbl_periodPara where iPeriod > @startTime and iPeriod < @endTime
print 'result is ' + CAST(@result as char(10))
OPEN @iCursor
return @result
GO


print 'before execute TestCursor'
declare @result int
declare @MyCursor CURSOR
declare @tmpStrName varchar(40),@tmpiPeriod varchar(40)
exec @result = GetOrderInfoBetweenTime1andTime3 1,721,@MyCursor output
print @result
FETCH NEXT FROM @MyCursor
INTO @tmpStrName,@tmpiPeriod

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'strName =' + @tmpStrName + ' iPeriod =' + @tmpiPeriod
FETCH NEXT FROM @MyCursor
INTO @tmpStrName,@tmpiPeriod
END
print 'after execute TestCursor'

GO