SQL使用游标遍历多层table获取每行的数据进行操作

最新推荐文章于 2024-03-14 13:15:13 发布

原创最新推荐文章于 2024-03-14 13:15:13 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

本文提供了一个使用SQL游标的示例，展示了如何通过嵌套游标从两个表中提取和处理数据。首先声明变量并定义外部游标来获取主表的数据，然后对于每个主表记录，打开一个内部游标来获取关联的详细信息。

declare @name nvarchar(50);declare @id nvarchar(50)  
declare pro_cur cursor for select  id,name from T1
open pro_cur  
fetch next from pro_cur into @id,@name  
while (@@fetch_status = 0)  
begin  
 ---------
print @name
----遍历城市
declare ccur cursor for select cityid,cityName from T2 WHERE T2.T1_ID=@id
open ccur
	fetch next from ccur into @id,@name 
	while(@@fetch_status=0)
		begin
			print (@name)
		    fetch next from ccur into @id,@name 
		end
	close ccur
	deallocate ccur
 ---------
 fetch next from pro_cur into @id,@name 
end  
close pro_cur  
deallocate pro_cur