用utl_file不可以得嘛?fields terminated by x'09'怎么个用法,楼上的能不能写的详细一点,小弟是第一次写PL/SQL。谢谢啦
在dos提示符下执行,不在pl/sql中执行 1. 生成一个如下的CONTROL文件,test.ctl: load data infile 'd:\test.csv' append into table table_name fields terminated by x'09' (field_name1,field_name2)2. 在DOS提示符运行以下导入语句: d:\sqlldr userid=test/pwd@server control=d:\test.ctl
存储过程的话,我这有个例子,发给你看看。create or replace procedure imp is i number(3); j number(3); id varchar2(10); zh varchar2(10); ph varchar2(10); flag varchar2(10); temp varchar2(300); handle utl_file.file_type; begin handle:=utl_file.fopen('e:\','ticket.txt','r'); loop begin utl_file.get_line(handle,temp); exception when no_data_found then exit; end;
select replace(temp,'"') into temp from dual; i:=instr(temp,',',1,1); id:=trim(substr(temp,1,i-1)); j:=instr(temp,',',1,2); zh:=trim(substr(temp,i+1,j-i-1)); i:=j; j:=instr(temp,',',1,3); ph:=trim(substr(temp,i+1,j-i-1)); flag:=trim(substr(temp,j+1)); insert into subwaydata(id,zh,ph,flag)values(id,zh,ph,flag); commit; end loop; utl_file.fclose(handle); end;
infile 'd:\test.csv'
append into table table_name
fields terminated by ','
(field_name1,field_name2)2. 在DOS提示符运行以下导入语句: d:\sqlldr userid=test/pwd@server control=d:\test.ctl
例如取出的字符串如下
ab<TAB>c<TAB>123<TAB>nnn<TAB>lzf
取第一个为
substr(field,1,instr(field,'TAB',1,1)-1)
取第二个到N个
substr(field,instr(field,'TAB',1,n-1)+1,instr(field,'TAB',1,n)-instr(field,'TAB',n-1))
最后一个为n
substr(field,instr(field,'TAB',1,n-1)+1)我的方法有点繁,我觉得通过utl_file无法解决,不知大家有没有别的好的方法
1. 生成一个如下的CONTROL文件,test.ctl: load data
infile 'd:\test.csv'
append into table table_name
fields terminated by x'09'
(field_name1,field_name2)2. 在DOS提示符运行以下导入语句: d:\sqlldr userid=test/pwd@server control=d:\test.ctl
存储过程的话,我这有个例子,发给你看看。create or replace procedure imp is
i number(3);
j number(3); id varchar2(10);
zh varchar2(10);
ph varchar2(10);
flag varchar2(10);
temp varchar2(300); handle utl_file.file_type;
begin
handle:=utl_file.fopen('e:\','ticket.txt','r'); loop
begin
utl_file.get_line(handle,temp);
exception
when no_data_found then exit;
end;
select replace(temp,'"') into temp from dual; i:=instr(temp,',',1,1);
id:=trim(substr(temp,1,i-1)); j:=instr(temp,',',1,2);
zh:=trim(substr(temp,i+1,j-i-1)); i:=j;
j:=instr(temp,',',1,3);
ph:=trim(substr(temp,i+1,j-i-1)); flag:=trim(substr(temp,j+1)); insert into subwaydata(id,zh,ph,flag)values(id,zh,ph,flag);
commit;
end loop; utl_file.fclose(handle);
end;