将文件按照分隔符截取数据，截取的数据，根据要求插入到不同的表中



create or replace procedure P_hy as
  v_time      char(8);
  v_path      varchar2(255);
  filehandle  UTL_FILE.FILE_TYPE;
  filebuffer  varchar(200);
  str         varchar2(200);
  type node_type is table of varchar2(2000);
  node  node_type := node_type();
  i     number;
  lt    number;
  v_idx varchar2(2000);
  v_seq number;
BEGIN
  v_time := to_char(sysdate, 'yyyymmdd');
  v_path := 'M_OUTPUT_IPAMN';

  BEGIN
    filehandle := utl_file.fopen(v_path, 'AT250'||v_time||'.dat', 'r');
    loop
      begin
        i := 1;
        node.extend(8);
        utl_file.get_line(filehandle, filebuffer);
        str := convert(filebuffer, 'UTF8', 'ZHS16GBK'); --进行字符集转换
      --根据分割符截取数据，依次插入到数组中
        loop
          v_idx := instr(str, '_+|');
          if v_idx = 0 then
            lt := length(str);
            node(i) := substr(str, 1, length(str) - 4);
            exit;
          else
            node(i) := substr(str, 1, v_idx - 1);
            str := substr(str, v_idx + 3);
            i := i + 1;
          end if;
        end loop;
       --将数组中的值插入到表中
        v_seq := seq_c_object.nextval;
        insert into C_OBJECT
          (id, name, code, description, objecttype_id, old_id)
        VALUES
          (v_seq, node(3), node(4), node(7), '121008', node(1));
        insert into EXT_OBJECT_ATMNODE
          (id, area, connectperson, address, status)
        VALUES
          (v_seq, node(2), node(5), node(6), node(8));
      EXCEPTION
        WHEN no_data_found THEN
          exit;
      End;
   
    END LOOP;
    utl_file.fclose(filehandle);
    COMMIT;
  END;

end;