1、实现oracle读取本地csv文件,代码如下:
Create Or Replace Procedure read_csv(p_dir in varchar2,
p_filename in varchar2,
p_separator in varchar2 default ',',
p_temptable in varchar2,
p_colnums in number,
p_columns in varchar2)
Authid Current_User
Is
l_input utl_file.file_type;
l_step number(8);
l_position number(8);
l_linetext varchar2(4000);
l_colvalue varchar2(4000);
l_rowvalues varchar2(4000);
Begin
if p_filename is null or p_dir is null then
--return;
raise_application_error(-20001,'[路径或文件不存在]');
end if;
select length(p_separator) into l_step from dual;
l_input := utl_file.fopen(p_dir, p_filename, 'r');
loop
begin
utl_file.get_line(l_input, l_linetext);
exception
when no_data_found then
exit;
end;
if p_colnums = 1 then
l_rowvalues := replace(l_linetext,'"','''');
else
for i in 1 .. p_colnums-1 loop
if i < p_colnums-1 then
l_position := instr(l_linetext, '","', 1, 1);
l_colvalue := replace(substr(l_linetext,1,l_position),'"','''');
l_linetext := substr(l_linetext,l_position+l_step-1);
else
l_colvalue := replace(l_linetext,'"','''');
end if;
if l_rowvalues is null or l_rowvalues='' then
l_rowvalues := l_colvalue;
else
l_rowvalues := l_rowvalues||','||l_colvalue;
end if;
end loop;
end if;
--[后续处理]--
--raise_application_error(-20001,'l_rowvalues='||l_rowvalues);
dbms_output.put_line(l_rowvalues);
--[重置变量]--
l_rowvalues := '';
end loop;
End read_csv;
/
Show Err;
2、测试文本emp.csv
"7369","SMITH","CLERK","7902","1980-12-17 00:00:00","800","","20"
"7499","ALLEN","SALESMAN","7698","1981-02-20 00:00:00","1600","300","30"
"7521","WARD","SALESMAN","7698","1981-02-22 00:00:00","1250","500","30"
"7566","JONES","MANAGER","7839","1981-04-02 00:00:00","2975","","20"
"7654","MARTIN","SALESMAN","7698","1981-09-28 00:00:00","1250","1400","30"
"7698","BLAKE","MANAGER","7839","1981-05-01 00:00:00","2850","","30"
"7782","CLARK","MANAGER","7839","1981-06-09 00:00:00","2450","","10"
"7788","SCOTT","ANALYST","7566","1987-04-19 00:00:00","3000","","20"
"7839","KING","PRESIDENT","","1981-11-17 00:00:00","5000","","10"
"7844","TURNER","SALESMAN","7698","1981-09-08 00:00:00","1500","0","30"
"7876","ADAMS","CLERK","7788","1987-05-23 00:00:00","1100","","20"
"7900","JAMES","CLERK","7698","1981-12-03 00:00:00","950","","30"
"7902","FORD","ANALYST","7566","1981-12-03 00:00:00","3000","","20"
"7934","MILLER","CLERK","7782","1982-01-23 00:00:00","1300","","10"
3、测试结果
set serveroutput on;
exec read_csv('TEMP','emp.csv','","','mk',8,'a,b,c,d,e,f,g,h');
'7369','SMITH','CLERK','7902','1980-12-17 00:00:00','800','','20'
'7499','ALLEN','SALESMAN','7698','1981-02-20 00:00:00','1600','300','30'
'7521','WARD','SALESMAN','7698','1981-02-22 00:00:00','1250','500','30'
'7566','JONES','MANAGER','7839','1981-04-02 00:00:00','2975','','20'
'7654','MARTIN','SALESMAN','7698','1981-09-28 00:00:00','1250','1400','30'
'7698','BLAKE','MANAGER','7839','1981-05-01 00:00:00','2850','','30'
'7782','CLARK','MANAGER','7839','1981-06-09 00:00:00','2450','','10'
'7788','SCOTT','ANALYST','7566','1987-04-19 00:00:00','3000','','20'
'7839','KING','PRESIDENT','','1981-11-17 00:00:00','5000','','10'
'7844','TURNER','SALESMAN','7698','1981-09-08 00:00:00','1500','0','30'
'7876','ADAMS','CLERK','7788','1987-05-23 00:00:00','1100','','20'
'7900','JAMES','CLERK','7698','1981-12-03 00:00:00','950','','30'
'7902','FORD','ANALYST','7566','1981-12-03 00:00:00','3000','','20'
'7934','MILLER','CLERK','7782','1982-01-23 00:00:00','1300','','10'
PL/SQL procedure successfully completed