存储过程中游标对两表字段更新

在Northwind数据库

利用游标

以Orders表的OrderID为索引，针对CustomerID

对新建表temOrders表的 CustomerID字段值进行更新

 

新建一个表

Create   table   tmpOrders   (   
            OrderID   int,   
            CustomerID   nchar(5)   
    )   

--把Orders   里的OrderID列全部插入,这样Orders与tmpOrders之间就是1:1关系了  

insert   into   tmpOrders   
  Select   distinct   orderID,'tmp'   from   Orders   

程序其实写了830条数据，即830个OrderId，看一下表中数据：

 

定义存储过程：

create   proc   test01   
      @StartOrderID   int,   
      @EndOrderID   int,   
      @Code   varchar(1000)   Out   
  As   
      Begin   
          Declare   @newOrderID   int   
          Declare   @newCustomerID   nchar(5)   
          Declare   @DummyInt   int   
          Declare   @DummyChar   nchar(5)   
          Set   @Code=''       
                  /* 
                                                                                                                                  1:1     
                          temp   table/formal   table   is   synchronized   tmpOrders     <--->   Orders   
                          fetch   from   Orders,   update   tmpOrders   
                  */   
    
            --   for   temp   table   
           Declare   #cur_tmpOrders     Cursor   for   select   OrderID,CustomerID   From   tmpOrders     
                                                       where    OrderID>=@startOrderID     
                                                       and      OrderID<=@EndOrderID                                     
                                                        for     update   
    
            --for   formal   table   
           Declare   #cur_orders   cursor     for      Select   OrderID,CustomerID     From   Orders     
                                                       where   OrderID>=@startOrderID     
                                                       and   OrderID<=@EndOrderID     
                                                       for   read   only   
          Open   #cur_Orders   
          Open   #cur_tmpOrders   
    
          fetch   next   from   #cur_tmpOrders   into   @DummyInt,@dummyChar   --Important!!!   
          fetch   next   from   #cur_orders      into   @NewOrderID,@NewCustomerID   
          while   @@fetch_Status=0   
                Begin 
  
                     --Set   @Code=@Code+'-'+convert(varchar(8),@NewOrderID)   
                     --update   tempOrders   use   corresponding   Orders'   data   
                      
                     Update   tmpOrders   set   customerID=@newCustomerID     
                                          where   current   of   #cur_tmpOrders  
   
                     --pay   attention   to   sequence   of   cursor   fetch   action!   
                     fetch   next   from   #cur_tmpOrders   into   @DummyInt,@dummyChar  
 
                     if   @@fetch_Status<>0     break;   --   没有行了
   
                     fetch   next   from   #cur_orders   into   @newOrderID,@NewCustomerID   
                End   
    
             close   #cur_Orders   
             close   #cur_tmpOrders   
    
             Deallocate   #cur_Orders   
             Deallocate   #cur_tmpOrders   
             Set   @Code='Ok'   
             return   
            
      End   

截图如下：

 

我们来看一下程序的执行过程：

执行存储程：

declare  @a varchar(1000)
exec test 10248, 10253 ,@a

           对tmpOrders表orderID在10248与10253的记录进行更新，tmpOrders中orderID在10248与10253范围内的CustomerID字段值由temp变为Orders  表中的 CustomerID     
 运行结果如下：

 

可见有tmpOrders表中有6条记录的CustomerID进行了更新，查看一下更新后的结果：

select * from tmpOrders order by  OrderID 

 

而其它的记录没有更新。