第一种方式—创建临时表
CREATE or REPLACE PROCEDURE DBO.P_TEST(
COLUMNNAMESTR VARCHAR(3000),
FILEPATH VARCHAR(100))
as
ERR_MSG VARCHAR;
FILENAME varchar(100);
SQL1 varchar(2000);
SQL2 varchar(2000);
SQL3 varchar(2000);
SQL4 varchar(2000);
COLUMNNAMESTRS varchar(2000);--要创建临时表的列名字符串
NUM INT;
begin
--call DBO.P_TEST('','','DeptNum1,DeptNum2,DeptNum3,DeptNum4',
--'/linux-files2/2017888.csv');
--SELECT * FROM DBO.Service5Person
--外部表文件名:当作临时表,操作完成后删除;每个存储过程使用一个编号,避免重复;
FILENAME:='DBO.T01'; --临时表名
COLUMNNAMESTRS:=replace(COLUMNNAMESTR,',',' VARCHAR(200),')||' VARCHAR(200)';--转成带字段类型的字符串
SQL1:='create external table DBO.T01 ('||COLUMNNAMESTRS||') from datafile '''||FILEPATH||''' parms(fields delimited by '','', records delimited by 0x0d0a);';
execute immediate SQL1;
SQL2:='insert into DBO.Service5Person(DeptNum1,DeptNum2,DeptNum3,DeptNum4)
(select DeptNum1,DeptNum2,DeptNum3,DeptNum4 FROM DBO.T01)';
execute immediate SQL2;
commit;
SQL3:='drop table DBO.T01;';
execute immediate SQL3;
end;
第二种方式 通过utl_file.fopen打开文件
第一步 创建表b
CREATE TABLE "DBO"."B"
(
"ID" INT IDENTITY(1, 1) NOT NULL,
"DEPTNUM" VARCHAR(50),
"CONN" VARCHAR(50),
NOT CLUSTER PRIMARY KEY("ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;
第二步 创建存储过程aaaaa,把2017123.csv文件导入到B表
/***Manager***/CREATE OR REPLACE PROCEDURE "DBO"."AAAAA"("P_PATH" IN VARCHAR2(32767),"P_FILENAME" IN VARCHAR2(32767))
AUTHID DEFINER
AS
--call DBO.AAAAA('/linux-files2/','2017123.csv');
ERR_MSG VARCHAR;
filehandle utl_file.file_type; --定义一个文件句柄
filebuffer varchar2(500); --存放文本
v_firstlocation number;--暂时无用
SQLSTR varchar2(500);
e1 EXCEPTION;
begin
filehandle := utl_file.fopen(p_path,p_filename,'R');
--filehandle := utl_file.fopen('/linux-files2/','2017123.csv','R');
IF utl_file.is_open(filehandle) THEN
PRINT('file is open!');
END IF;
loop
begin
utl_file.get_line(filehandle,filebuffer);
--PRINT(filebuffer);
EXCEPTION
WHEN no_data_found THEN
exit ;
--WHEN OTHERS THEN
--PRINT('EXCEPTION1:'||SUBSTR(SQLERRM, 1, 100)) ;
end;
--v_firstlocation:=instr(filebuffer,',',1,1);
--v_DEPTNNUM:=substr(filebuffer,1,v_firstlocation-1);
--v_CONN:=substr(filebuffer,v_firstlocation+1);
--print(v_DEPTNNUM||','||v_CONN);
SQLSTR :='INSERT INTO DBO.B (DEPTNUM,CONN) VALUES ('||filebuffer||')';
execute immediate SQLSTR;
COMMIT;
end loop;
select '1';
EXCEPTION
WHEN e1 THEN
select '0';
end;
csv文件数据格式:'a0001','GZZY-ZX-001'