oracle存储过程游标用法

create or replace procedure zx_proc_chaifen_temp is

  p_placepointid number(10);
  p_goodsid     number(10);
  p_chayiQty    NUMBER(16, 6);
  p_chayiMoney  NUMBER(12, 4);
  p_selldate    date;
  p_curQty      NUMBER(16, 6);
  p_currealmoney NUMBER(12, 4);
  p_unitPrice   NUMBER(16, 6);

  CURSOR p_goodschayi_cur IS
    select placepointid, goodsid, chayiQty, chayiMoney
      from zx_chayi_alan_temp;

begin
  open p_goodschayi_cur;
  fetch p_goodschayi_cur
    into p_placepointid, p_goodsid, p_chayiQty, p_chayiMoney;
  while p_goodschayi_cur%found loop
  
    p_unitPrice := round(p_chayiMoney / p_chayiQty, 4);
  
    if p_chayiQty > 0 then
    
      while p_chayiQty > 0 loop
     
       SELECT to_date(TRUNC(DBMS_RANDOM.VALUE(to_number(to_char(to_date('20190101',
                                                             'yyyymmdd'),
                                                      'J')),
                                       to_number(to_char(to_date('20191231',
                                                             'yyyymmdd') + 1,
                                                      'J')))),
                    'J') + DBMS_RANDOM.VALUE(1, 3600) / 3600
         into p_selldate
         FROM dual;
     
       if p_chayiqty >= 1 then
         p_curQty      := 1;
         p_currealmoney := round(p_unitPrice * p_curQty, 2);
       else
         p_curQty      := p_chayiqty;
         p_currealmoney := p_chayiMoney;
       end if;
     
       insert into zx_chaifen_alan_temp
       values
         (p_placepointid, p_goodsid, p_curQty, p_currealmoney, p_selldate);
     
       p_chayiQty  := p_chayiQty - 1;
       p_chayiMoney := p_chayiMoney - p_currealmoney;
     
      end loop;
    
    else
      while p_chayiQty < 0 loop
     
       SELECT to_date(TRUNC(DBMS_RANDOM.VALUE(to_number(to_char(to_date('20190101',
                                                             'yyyymmdd'),
                                                      'J')),
                                       to_number(to_char(to_date('20191231',
                                                             'yyyymmdd') + 1,
                                                      'J')))),
                    'J') + DBMS_RANDOM.VALUE(1, 3600) / 3600
         into p_selldate
         FROM dual;
     
       if p_chayiqty <= -1 then
         p_curQty      := -1;
         p_currealmoney := round(p_unitPrice * p_curQty, 2);
       else
         p_curQty      := p_chayiqty;
         p_currealmoney := p_chayiMoney;
       end if;
     
       insert into zx_chaifen_alan_temp
       values
         (p_placepointid, p_goodsid, p_curQty, p_currealmoney, p_selldate);
     
       p_chayiQty  := p_chayiQty + 1;
       p_chayiMoney := p_chayiMoney - p_currealmoney;
      end loop;
    
    end if;
  
    fetch p_goodschayi_cur
      into p_placepointid, p_goodsid, p_chayiQty, p_chayiMoney;
  end loop;
  close p_goodschayi_cur;

end;