windows 里excel 转到linux 里有tab分隔符,导数据_存储过程导入数据_从excel或tab键为分隔的导入数据...

CREATE OR REPLACE PROCEDURE p_read(

file_name  VARCHAR2

)AS

v_text                 VARCHAR2(4000);

v_firstlocation1       NUMBER;

v_firstlocation2       NUMBER;

v_firstlocation3       NUMBER;

vfile                  utl_file.file_type;

vempno                 VARCHAR2(50);

vename                 VARCHAR2(50);

VHIREDATE              VARCHAR2(50);

BEGIN

vfile := utl_file.fopen('TEST',file_name,'r');

--utl_file.put_line(vfile,'导出emp表数据');

LOOP

BEGIN

utl_file.get_line(vfile,v_text);

EXCEPTION

WHEN NO_DATA_FOUND THEN

EXIT;

END;

v_firstlocation1 := instr(v_text,chr(9),1,1);---文本文件第一个,位置

v_firstlocation2 := instr(v_text,chr(9),v_firstlocation1,2);---文本文件第二个,位置

--为excel或tab为分隔符的文本导入

vempno := substr(v_text,1,v_firstlocation1-1);

vename := substr(v_text,v_firstlocation1,v_firstlocation2-v_firstlocation1+1);

VHIREDATE := substr(v_text,v_firstlocation2,length(v_text)-1);

BEGIN

INSERT INTO test(

vempno,

vename,

VHIREDATE

)

SELECT

vempno,

vename,

VHIREDATE

FROM dual;

IF SQL%ROWCOUNT = 0 THEN

ROLLBACK;

ELSE

COMMIT;

END IF;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

END LOOP;

utl_file.fclose(vfile);

EXCEPTION

WHEN OTHERS THEN

utl_file.fclose(vfile);

END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24805255/viewspace-721340/，如需转载，请注明出处，否则将追究法律责任。