用游标打开一百万条记录的问题，进者又分！

Question

在oracle中游标的处理我是用这种方式，这种方式效率要高些：
For  Rec_Dispatch    In  ( Select  Sizerun ,
                                        Qty
                                  From  f_Dispatch_d
                                 Where  Fact_No      =  vi_fact_no
                                   And  Dis_No       =  vi_dis_no
                                   And  Fact_Odr_No  =  vi_odr_no
                                   And  Bat_No       =  vi_bat_no 
                                   And  Qty          >  0 
                              Order By  Sizerun   )
     Loop 
         Vn_Barcode_Count := Vn_Barcode_Count + Vn_BarSize_Count ;
         Vn_BarSize_Count := Ceil( Rec_Dispatch.Qty/vi_barcode_qty ) ;
         Vn_LastBar_Qty   := Mod( Rec_Dispatch.Qty,vi_barcode_qty ) ;
         
         If Vn_LastBar_Qty = 0 Then
            Vn_LastBar_Qty := vi_barcode_qty ;
         End If ; 
         
         Vc_BarNo :=   Vc_Lotnom||'21'||To_Char( Vn_Barcode_Seq+Vn_Barcode_Count+Vn_BarSize_Count,'fm0000' ) ;
         
          Insert Into Barm_M( Fact_No        ,Bar_no          ,Lot_No       ,Fact_Odr_No    ,Sizerun ,
                                Qty          ,Add_Mark        ,Dis_No       ,Bat_No         ,Bar_Date                    )
                      Values(vi_fact_no      ,Vc_BarNo        ,Vc_Lotnom    ,vi_odr_no      ,Rec_Dispatch.Sizerun,
                             Vn_LastBar_Qty  ,VC_BAR_ADD_MK1  ,vi_dis_no    ,vi_bat_no      ,To_Char(SysDate,'YYYYMMDD') );         
                             
          Vc_BarNo :=   Vc_Lotnom||'31'||To_Char( Vn_Barcode_Seq+Vn_Barcode_Count+Vn_BarSize_Count,'fm0000' ) ;
         
          Insert Into Barm_M( Fact_No        ,Bar_no          ,Lot_No       ,Fact_Odr_No    ,Sizerun ,
                                Qty          ,Add_Mark        ,Dis_No       ,Bat_No         ,Bar_Date                    )
                      Values(vi_fact_no      ,Vc_BarNo        ,Vc_Lotnom    ,vi_odr_no      ,Rec_Dispatch.Sizerun,
                             Vn_LastBar_Qty  ,VC_BAR_ADD_MK1  ,vi_dis_no    ,vi_bat_no      ,To_Char(SysDate,'YYYYMMDD') );         
                             
          Vc_BarNo :=   Vc_Lotnom||'51'||To_Char( Vn_Barcode_Seq+Vn_Barcode_Count+Vn_BarSize_Count,'fm0000' ) ;
         
          Insert Into Barm_M( Fact_No        ,Bar_no          ,Lot_No       ,Fact_Odr_No    ,Sizerun ,
                                Qty          ,Add_Mark        ,Dis_No       ,Bat_No         ,Bar_Date                    )
                      Values(vi_fact_no      ,Vc_BarNo        ,Vc_Lotnom    ,vi_odr_no      ,Rec_Dispatch.Sizerun,
                             Vn_LastBar_Qty  ,VC_BAR_ADD_MK1  ,vi_dis_no    ,vi_bat_no      ,To_Char(SysDate,'YYYYMMDD') );         
         -------------------------------------------------------------------------    
         
         Vn_Count :=  Vn_Count + 1 ;                         
     End Loop ;

Answer 1

For  Rec_Dispatch    In  ( Select  Sizerun ,
                                        Qty
                                  From  f_Dispatch_d
                                 Where  Fact_No      =  vi_fact_no
                                   And  Dis_No       =  vi_dis_no
                                   And  Fact_Odr_No  =  vi_odr_no
                                   And  Bat_No       =  vi_bat_no 
                                   And  Qty          >  0 
                              Order By  Sizerun   )
Loop 
  /*处理过程*/
End Loop

Answer 2

不需要有特殊的顾虑，只要你的SQL语句本身利用索引正确……