Oracle utl_file读取csv文件到数据库

来源：互联网发布：数据库原理课程设计编辑：程序博客网时间：2024/05/18 02:39

drop table test purge;
create table test
(
url varchar2(200),
res_time varchar2(10)
);

create or replace procedure loadfiledata(p_path varchar2,
p_filename varchar2) as
v_filehandle utl_file.file_type;
v_text varchar2(100);
v_url test.url%type;
v_res_time test.res_time%type;
v_firstlocation number;
begin
if (p_path is null or p_filename is null) then
goto to_end;
end if;
v_filehandle := utl_file.fopen(p_path, p_filename, 'r');
loop
begin
utl_file.get_line(v_filehandle, v_text);
exception
when no_data_found then
exit;
end;
v_firstlocation := instr(v_text, ',', 1, 1);
v_url := substr(v_text, 1, v_firstlocation - 1);
v_res_time := substr(v_text,v_firstlocation + 1);
insert into test values (v_url, v_res_time);

end loop;

commit;

<<to_end>>
null;
end loadfiledata;

CREATE DIRECTORY utl_dir AS 'D:\'; --必须是以目录的形式
call loadfiledata('UTL_DIR','test.csv');

select * from test;

test.csv
"web/tt/sys/dwr/interface/ChooseAction.js?v=","0.013"
"web/tt/sys/dwr/interface/ChooseAction.js?v=","0.013"
"web/tt/sys/dwr/interface/ChooseAction.js?v=","0.013"
"web/tt/sys/dwr/interface/ChooseAction.js?v=","0.016"
"web/tt/sys/dwr/interface/ChooseAction.js?v=","0.016"
"web/tt/workbench/component/underscore/module.underscore.js?v=","0.016"
"web/tt/workbench/component/underscore/module.underscore.js?v=","0.016"
"web/tt/sys/dwr/interface/ChooseAction.js?v=","0.016"

0 0