Oracle read_csv

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