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