递归bom主料件的元件信息

set serveroutput on;
exec dbms_output.enable(buffer_size => null);
declare
  v_cnt  type_file.num5%TYPE;
  v_flag type_file.chr1%TYPE;
  v_bma01 bma_file.bma01%TYPE;
  v_bmb03 bmb_file.bmb03%TYPE;
  type bma_cursor_type is ref cursor;
  type bmb_cursor_type is ref cursor;
  bma_cursor bma_cursor_type;
  bmb_cursor bmb_cursor_type;
begin
  open bma_cursor for select bma01 from bma_file where substr(bma01,1,2)='02' order by 1;
  loop
    fetch bma_cursor into v_bma01;
    exit when bma_cursor%notfound;
    open bmb_cursor for 
      select distinct bmb03 from bmb_file join bma_file on bmb01 = bma01
        where bma10 = '2'
         and  bma05 <= to_date('2012/12/05','yyyy/mm/dd')
         and (bmb05 >= to_date('2012/12/05','yyyy/mm/dd') or bmb05 is null)
         and  bmb14 = '0'
         and  substr(bmb03,1,2)='01'
        connect by prior bmb03 = bmb01
        start with bmb01 = v_bma01 
        order by 1;  
    loop
      fetch bmb_cursor into v_bmb03;
      exit when bmb_cursor%notfound;
      dbms_output.put_line(v_bma01 || '  ' || v_bmb03);
    end loop;
  end loop;                 
end;
/