拆BOM

set serveroutput on;
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)='01';
  loop
    v_cnt := 0;
    v_flag:= 'Y';
    fetch bma_cursor into v_bma01;
    exit when bma_cursor%notfound;
    open bmb_cursor for select bmb03 from bmb_file where bmb01 = v_bma01 and (bmb05 is null or bmb05<to_date('2012/12/03','yyyy/mm/dd')) and substr(bmb03,1,2)='08';
    loop
      fetch bmb_cursor into v_bmb03;
      exit when bmb_cursor%notfound;
      select count(1) into v_cnt from lee_tep where ima01 = v_bmb03;
      if v_cnt = 0 then
        v_flag:= 'N';
      end if; 
    end loop;
    if v_flag = 'Y'  then
      dbms_output.put_line(v_bma01 ||'  Y');
    end if;
    v_cnt := 0;
    v_flag:= 'Y';
  end loop;                 
end;
/