declare @a int
declare test_cursor cursor for
select a from test
open test_cursor
fetch next from test_cursor into @int
while @@FETCH_STATUS = 0
begin
print @int
fetch next from test_cursor into @int
end
close test_cursor
deallocate test_cursor
declare test_cursor cursor for
select a from test
open test_cursor
fetch next from test_cursor into @int
while @@FETCH_STATUS = 0
begin
print @int
fetch next from test_cursor into @int
end
close test_cursor
deallocate test_cursor
declare test_cursor cursor for
select a from test
open test_cursor
fetch next from test_cursor into @a
while @@FETCH_STATUS = 0
begin
print @a
fetch next from test_cursor into @a
end
close test_cursor
deallocate test_cursor
declare test_cursor cursor for
select * from test
open test_cursor
fetch next from test_cursor into @a,@b
while @@FETCH_STATUS = 0
begin
print @a
fetch next from test_cursor into @a,@b
end
close test_cursor
deallocate test_cursor
declare @i int
open cursor_insert
fetch cursor_insert into @i
while @@fetch_status=0
begin
print @i
fetch cursor_insert into @i
end
close cursor_insert
deallocate cursor_insert
FOR----------------------------------------------------------------选择
SELECT TABLE_NAME ----------------------------------------------游标体
FROM INFORMATION_SCHEMA.TABLES----------------------------------表名
OPEN tnames_cursor-------------------------------------------------打开游标
DECLARE @tablename sysname-----------------------------------------
--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename----------------------调用游标赋值
WHILE (@@FETCH_STATUS <> -1)---------------------------------------循环‘0’有‘-1’没找到‘-2’超出范围溢出
BEGIN
IF (@@FETCH_STATUS <> -2)---------------------------------------如果没有溢出
BEGIN
SELECT @tablename = RTRIM(@tablename)------------------------
EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
+ @tablename )
PRINT ' '
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
declare test_cursor cursor for
select * from test
open test_cursor
fetch next from test_cursor into @a,@b
while @@FETCH_STATUS = 0
begin
print @a
fetch next from test_cursor into @a,@b
end
close test_cursor
deallocate test_cursor
-- Declare and using a KEYSET cursor
-- =============================================
DECLARE <cursor_name, sysname, test_cursor> CURSOR
KEYSET
FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>DECLARE @name varchar(40)OPEN <cursor_name, sysname, test_cursor>FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
PRINT 'updating record for ' + @name
UPDATE pubs.dbo.authors
SET phone = replace(phone, ' ', '-')
WHERE CURRENT OF <cursor_name, sysname, test_cursor>
END
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @name
ENDCLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
GO