The method of inserting cursors

In fact, the storage process is the expansion of the use of the database, sometimes we use SQL can not complete some complex logic, can only rely on the process, the cursor is simulated C++,java and other language line processing ideas, different from the SQL by set processing ideas,

Sometimes we need a cursor nesting, which is actually similar to the fish for ()

for ()

A nested loop like this is still very simple, here's an example

CREATE PROCEDURE [DBO]. [Sp_reportecert] asbegin declare  @id_owner  varchar, @ownername  varchar, @mcertid  varchar (20), @ Mgrarea varchar, @mcertstate  varchar, @contactor  varchar, @address  varchar (20), @ Origorgan varchar, @countVehicle  integercreate table  #TMP   (Ownername varchar ( , Mcertid varchar (), Mgrarea varchar (20), Mcertstate varchar (Contactor varchar), Address varchar (Origorgan varchar), Countvehicle integer) declare c1 cursor  for select top 100 id_owner,ownername,mcertid,mgrarea,mcertstate,contactor,address, origorgan from t_owner --Outer Loop Open c1fetch next  from c1 into   @id_owner  , @ownername  , @mcertid  , @mgrarea  , @mcertstate, @contactor  , @address &NBSP, @origorgan  ; while @ @fetch_status  = 0 --Cycle conditions (similar to While tRue) Begindeclare c2 cursor for  select count (*)  from t_vehicle  where id_owner =  @id_owner; open c2    --outer fetch next   from c2 into  @countVehicle; while @ @fetch_status  = 0begininsert into  #TMP  values (@ownername  , @mcertid  , @mgrarea  , @mcertstate, @contactor  , @address  , @origorgan  , @countVehicle); fetch next  from c2 into  @countVehicle; endclose c2deallocate c2fetch  NEXT  FROM C1 INTO  @id_owner  , @ownername  , @mcertid  , @mgrarea  , @mcertstate, @contactor  , @address  , @origorgan  ;endclose c1deallocate c1select  * from  #TMP; END

This article is from the "Data Domain" blog, so be sure to keep this source http://charlist.blog.51cto.com/1616209/1697391

The method of inserting cursors