--> 构造测试数据 declare @tb table(pname varchar(50)) insert @tb select '电视机' union all select '电冰箱' union all select '洗衣机' union all select '空调' union all select '微波炉'--> 游标的使用 declare my_cursor cursor scroll dynamic for select pname from @tb open my_cursor declare @pname sysname fetch next from my_cursor into @pname while(@@fetch_status=0) begin print 'Product Name: ' + @pname fetch next from my_cursor into @pname end fetch first from my_cursor into @pname close my_cursor deallocate my_cursor--> 结果 /**Product Name: 电视机 Product Name: 电冰箱 Product Name: 洗衣机 Product Name: 空调 Product Name: 微波炉 **/
declare c_dropTempTable cursor scroll dynamic for select name from sysobjects where name like '%temp%' and xtype = 'U'declare @sql nvarchar(4000)open c_dropTempTable declare @tableName nvarchar(100) fetch next from c_dropTempTable into @tableName while(@@fetch_status = 0) begin set @sql = 'drop table ' + @tableName; exec(@sql); fetch next from c_dropTempTable into @tableName; end;fetch first from c_dropTempTable into @tableName;close c_dropTempTable; deallocate c_dropTempTable;
declare @str nvarchar(4000) set @str = '';declare c_colToLine cursor scroll dynamic for select name from sysobjects where name like '%temp%' and xtype = 'U';open c_colToLine declare @tableName nvarchar(100)fetch next from c_colToLine into @tableNamewhile(@@fetch_status = 0) begin set @str = @str + @tableName + ','; fetch next from c_colToLine into @tableName; end;fetch first from c_colToLine into @tableName;close c_colToLine; deallocate c_colToLine;print @str;
主要的都在上面了
declare @tb table(pname varchar(50))
insert @tb
select '电视机' union all
select '电冰箱' union all
select '洗衣机' union all
select '空调' union all
select '微波炉'--> 游标的使用
declare my_cursor cursor scroll dynamic
for
select pname from @tb
open my_cursor
declare @pname sysname
fetch next from my_cursor into @pname
while(@@fetch_status=0)
begin
print 'Product Name: ' + @pname
fetch next from my_cursor into @pname
end
fetch first from my_cursor into @pname
close my_cursor
deallocate my_cursor--> 结果
/**Product Name: 电视机
Product Name: 电冰箱
Product Name: 洗衣机
Product Name: 空调
Product Name: 微波炉
**/
declare c_dropTempTable cursor scroll dynamic
for
select name
from sysobjects
where name like '%temp%'
and xtype = 'U'declare @sql nvarchar(4000)open c_dropTempTable
declare @tableName nvarchar(100)
fetch next from c_dropTempTable into @tableName
while(@@fetch_status = 0)
begin
set @sql = 'drop table ' + @tableName;
exec(@sql);
fetch next from c_dropTempTable into @tableName;
end;fetch first from c_dropTempTable into @tableName;close c_dropTempTable;
deallocate c_dropTempTable;
declare @str nvarchar(4000)
set @str = '';declare c_colToLine cursor scroll dynamic
for
select name
from sysobjects
where name like '%temp%'
and xtype = 'U';open c_colToLine
declare @tableName nvarchar(100)fetch next from c_colToLine into @tableNamewhile(@@fetch_status = 0)
begin
set @str = @str + @tableName + ',';
fetch next from c_colToLine into @tableName;
end;fetch first from c_colToLine into @tableName;close c_colToLine;
deallocate c_colToLine;print @str;