oracle cursor 简单用法

procedure  changeSpecialDiscount
(
  compID_in       IN   VARCHAR2 ,
  ccID_in         IN   VARCHAR2 ,
  coNO_in         IN   VARCHAR2
)
IS
    sum_cc_all   number ( 14 , 4 ): =   0 ;
    wsp_disc     number ( 14 , 4 );
    wspcl_disc      number ;
    cnt_u          number ;
    cnt_i         number ( 4 );
    cnt2         number ( 4 ) : =   1 ;
    tot_disc     number ( 14 , 4 ) : =   0 ;
    act_cd         varchar2 ( 2 );
    
    witem_no     varchar2 ( 4 );
    wcost_cc     number ( 14 , 4 );
    wqty_order     number ( 14 , 4 );
    wlp_contr     number ( 14 , 4 );
    wstatus         varchar2 ( 4 );
    wsrce_type     varchar2 ( 1 );
    wsp_per_unit_contr  number ( 14 , 4 );

     cursor  c1  is
     select  
            ITEM_NO,COST_CC_CONTR,QTY_ORDER,LP_CUST_CONTR,STATUS,SRCE_TYPE,SP_PER_UNIT_CONTR
     from  
               tbco_item  
      where  
             COMP_ID  =  compID_in     AND  
            CC_ID  =  ccID_in         AND  
            CO_NO  =  coNO_in ;    
    
BEGIN            
        
       select  
              sum ( sp_per_unit_contr  *  qty_order )
      into  
             sum_cc_all
      from  
               tbco_item  
      where  
             COMP_ID  =  compID_in     AND  
            CC_ID  =  ccID_in         AND  
            CO_NO  =  coNO_in ;    
    
            
      select   
              DISC_AMT
      into     
              wspcl_disc         
      from  
                tbco_head  
      where  
                 COMP_ID  =  compID_in     AND  
             CC_ID  =  ccID_in         AND  
             CO_NO  =  coNO_in ;
    
     select  
            count ( * ) 
     into  
           cnt_u
     from  
           tbco_item
     where  
                 COMP_ID  =  compID_in     AND  
             CC_ID  =  ccID_in         AND  
             CO_NO  =  coNO_in         AND
             date_cancel  is   null      AND
             nvl( sp_per_unit_contr,  0  )  <>   0 ;
             
      select  
              count ( * ) 
      into  
             cnt_i
      from  
               tbco_item  
      where  
             COMP_ID  =  compID_in     AND  
            CC_ID  =  ccID_in         AND  
            CO_NO  =  coNO_in ;            
    
    
     open  c1;
     for  idx  in   1 ..cnt_i loop
     fetch  c1  into  witem_no, wcost_cc, wqty_order,
              wlp_contr, wstatus, wsrce_type, wsp_per_unit_contr;
              
     SELECT  
           ACTIVITY_CODE
     INTO  
           act_cd
     FROM  
           TBCM_STATUS
     WHERE  
           SYSTEM_CODE  =   ' CO '                      AND
           TABLE_LEVEL  =   ' 2 '                      AND
            DATA_TYPE    =  wsrce_type                AND
            (STATUS_NAME1  =  wstatus  OR  STATUS_NAME2  =  wstatus );          
              
     --  < Special discount distribution >    
     if  wsp_per_unit_contr  =   0   or  act_cd  =   ' 15 '   then
        wsp_disc : =   0 ;
    elsif cnt2  <  cnt_u  then
        wsp_disc : =   round (wspcl_disc  *  ( wsp_per_unit_contr  *  wqty_order  /  sum_cc_all), 2 );
        cnt2 : =  cnt2  +   1 ;
    elsif cnt2  >=  cnt_u  then
        wsp_disc : =  wspcl_disc  -  tot_disc;
     end   if ;    
    
     --  Accumulation of distributed selling price
    tot_disc : =  tot_disc  +   round (wsp_disc, 2 );
    
     --   Updating item special discount    
         update  
               tbco_item
         set  
            special_disc  =   round (wsp_disc, 2 ),
            date_modify  =  sysdate         
         where  
             COMP_ID  =  compID_in     AND  
            CC_ID  =  ccID_in         AND  
            CO_NO  =  coNO_in            AND
            item_no  =  witem_no;    
end  loop;
close  c1;                
END ;