TEXT_IO 读 TXT 文件-优快云博客

本文链接：https://blog.youkuaiyun.com/zhang_viking/article/details/4734903

本文介绍了一种使用Oracle过程从CSV文件批量导入数据到数据库的方法。该过程利用了TEXT_IO包来读取文件，并通过字符串操作函数处理每行数据，最终将数据插入到指定表中。文章还提到了如何正确处理首次读取的表头行及提交事务的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

PROCEDURE COMPANYREADER IS READER TEXT_IO.FILE_TYPE; FIRST_ROW BOOLEAN DEFAULT TRUE; BUF VARCHAR2(500); BEG_POSITION NUMBER DEFAULT 0; END_POSITION NUMBER DEFAULT 1; CID VARCHAR2(10); CNAME VARCHAR2(200); ENAME VARCHAR2(200); COUNTRY VARCHAR2(10); BEGIN READER := TEXT_IO.FOPEN(:CONTROL.TXT_FOLDER ||:CONTROL.TXT_FILE,'r'); IF TEXT_IO.IS_OPEN(READER) THEN LOOP BEGIN TEXT_IO.GET_LINE(READER,BUF); EXCEPTION WHEN OTHERS THEN EXIT; END; IF NOT(FIRST_ROW) THEN END_POSITION := INSTR(BUF,',',BEG_POSITION+1); CID := SUBSTR(BUF,BEG_POSITION+1,END_POSITION-1); BEG_POSITION := END_POSITION; END_POSITION := INSTR(BUF,',',BEG_POSITION+1); ENAME := SUBSTR(BUF,BEG_POSITION+1,END_POSITION-BEG_POSITION-1); BEG_POSITION := END_POSITION; END_POSITION := INSTR(BUF,',',BEG_POSITION+1); CNAME := SUBSTR(BUF,BEG_POSITION+1,END_POSITION-BEG_POSITION-1); BEG_POSITION := END_POSITION; END_POSITION := INSTR(BUF,',',BEG_POSITION+1); COUNTRY := SUBSTR(BUF,BEG_POSITION+1,END_POSITION-BEG_POSITION-1); BEG_POSITION := 0; INSERT INTO GUID_COMPANY (COMPANY_ID,COMPANY_CNAME,COMPANY_ENAME,COMPANY_COUNTRY) VALUES (CID,CNAME,ENAME,COUNTRY); ELSE FIRST_ROW := FALSE; END IF; END LOOP; --:SYSTEM.MESSAGE_LEVEL :='25'; --COMMIT; --:SYSTEM.MESSAGE_LEVEL := '0'; MESSAGE('FILE READED COMPLETELY.'); TEXT_IO.FCLOSE(READER); SET_CANVAS_PROPERTY('TAB_DATA',topmost_tab_page,'PG_COMPANY'); GO_BLOCK('COMPANY'); SETBLOCKPROPERTY('COMPANY',true,false,false,false); EXECUTE_QUERY; ELSE MESSAGE('FILE OPENED FAILURE.'); END IF; EXCEPTION WHEN OTHERS THEN MESSAGE('ERROR...'); END;

必须显示的调用Commit，不然数据不会插入到数据库中;

并且必须调整MESSAGE_LEVEL, 不然oracle 报 "no change to save" error.

当然 commit 可以在调用的外面使用：

COMPANYREADER; :SYSTEM.MESSAGE_LEVEL :='25'; COMMIT; :SYSTEM.MESSAGE_LEVEL := '0';