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;