SQL游标（循环）单层和双层

双层循环

DECLARE OrgCursor CURSOR  FOR SELECT orgid FROM [ems_db].[dbo].[organization]

OPEN OrgCursor

DECLARE @orgid Int,@begintime time(0),@endtime time(0) FETCH NEXT FROM  OrgCursor INTO @orgid 

   WHILE @@FETCH_STATUS =0

   BEGIN

-- print @orgid 

DECLARE  ProCursor CURSOR  FOR SELECT productid FROM [ems_db].[dbo].[productlist]        

OPEN  ProCursor        

DECLARE @productid Int FETCH NEXT FROM  ProCursor INTO @productid         

while   @@FETCH_STATUS   =   0         

begin     

--  print @productid    

insert into [ems_db].[dbo].[productrecord] 

([orgid],[productid],[productdate],[updatedate],[value],[begintime],[endtime])

values(@orgid ,@productid,(Select Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate()+1)+' 00:00:00.000'),(Select Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())+' 00:00:00.000'),0,@begintime,@endtime)

                     

FETCH NEXT FROM  ProCursor INTO @productid     

end        

close   ProCursor         

    deallocate   ProCursor    

 

     

FETCH NEXT FROM  OrgCursor INTO @orgid

   END

CLOSE OrgCursor

DEALLOCATE OrgCursor

单层循环

DECLARE OrgCursor CURSOR  FOR SELECT orgid FROM [ems_db].[dbo].[organization]

OPEN OrgCursor

DECLARE @orgid Int,@begintime time(0),@endtime time(0) FETCH NEXT FROM  OrgCursor INTO @orgid 

   WHILE @@FETCH_STATUS =0

   BEGIN

-- print @orgid     

FETCH NEXT FROM  OrgCursor INTO @orgid

   END

CLOSE OrgCursor

DEALLOCATE OrgCursor