create table tb1(
 v_id number,
 v_wl varchar2(10),
 v_data1 date ,
 v_price1 number,
 v_data2 date,
 v_price2 number,
 v_data3 date,
 v_price3 number
);


create or replace procedure pro_test
is
  v_id number;
  v_wl varchar2(10);
  v_data1 date;--最近时间1
  v_price1 number;--最近价格1
  v_data2 date;
  v_price2 number;
  v_data3 date;
  v_price3 number;
  i number:=1;  
  
  cursor cur is select a.m_code from tb_material a,tb_m_price b where a.m_code=b.m_code group by a.m_code;
 
  
begin
   open cur;
   loop
     fetch cur into v_id;
     exit when cur% NOTFOUND; 
     dbms_output.put_line(v_id);
       <<jloop>>
       for rect in  (select a.m_code,a.m_name,b.price_date,b.price from tb_material a,tb_m_price b where a.m_code=b.m_code order by b.price_date  desc) loop      
       dbms_output.put_line(rect.m_code||'aaaa   '||rect.m_name ||'i'||i);
       if v_id=rect.m_code then
         v_wl:=rect.m_name;
         if i=1 then
         v_data1:=rect.price_date;
         v_price1:=rect.price;
         end if;
         if i=2 then
         v_data2:=rect.price_date;
         v_price2:=rect.price;
         end if;
         if i=3 then
         v_data3:=rect.price_date;
         v_price3:=rect.price;
         end if;
         i:=i+1;
       end if;
       EXIT jloop when i = 4;  
     end loop jloop;
     dbms_output.put_line(v_id || v_wl ||v_data1||v_price1||v_data2||v_price2||v_data3||v_price3);
     
     insert into tb1 values(v_id , v_wl ,v_data1,v_price1,v_data2,v_price2,v_data3,v_price3);
     v_data1:=null;
     v_price1:=null;
     v_data2:=null;
     v_price2:=null;
     v_data3:=null;
     v_price3:=null;
      i :=1;  
 end loop;
   close cur;
end;


SQL> select * from tb1;
 
V_ID V_WL   V_DATA1 V_PRICE1 V_DATA2  V_PRICE2 V_DATA3 V_PRICE3
---- ------  -------- --------- --------- ------- ----------- --------
   1 物料1  2012-4-6 110      2012-3-5  107 2012-2-4       105
   2 物料2  2012-4-6 310      2012-3-5  337 2012-2-4       205