oracle存储过程读取文件--按照自定格式存入数据库
感谢我的老师【云淡风轻】的帮助http://www.agoit.com/images/smiles/icon_idea.gif别的不说，直接上代码：
CREATE OR REPLACE PROCEDURE SZ_READFILEDAT(V_FILEPATH VARCHAR2,--文件路径 V_FILENAME VARCHAR2,--文件名称 V_ROWS OUT NUMBER)--返回处理记录数AUTHID CURRENT_USER ASV_FILE_HANDLE UTL_FILE.FILE_TYPE;V_SQL VARCHAR2(2000);IS_EXISTS NUMBER(10);MAX_ID NUMBER(10);COL1 VARCHAR2(1000); type c_cursor IS REF CURSOR; c1 c_cursor; --V_ROWS NUMBER(10);BEGIN--文件路径和文件名--V_FILEPATH := '/HOME/ORACLE';--V_FILENAME := 'AAA.DAT';--建立ORACLE文件路径V_SQL := 'CREATE OR REPLACE DIRECTORY MYFILEDIRAS ''' || V_FILEPATH || '''';EXECUTE IMMEDIATE V_SQL;--创建表SELECT COUNT(*) INTO IS_EXISTSFROM USER_OBJECTSWHERE OBJECT_TYPE = 'TABLE'AND OBJECT_NAME = 'PSZ_TEMP_STB';IF IS_EXISTS = 0 THEN V_SQL := 'CREATE TABLE PSZ_TEMP_STB ( ID INTEGER NOT NULL, BSM VARCHAR2(20), JCKA VARCHAR2(40), JYDWBH VARCHAR2(10), HZDWDM VARCHAR2(10), HZDWSZDQ VARCHAR2(5), YSFSDM VARCHAR2(1), MYFS VARCHAR2(4), MZXS VARCHAR2(3), QDG VARCHAR2(3), CZG VARCHAR2(3), XKZBH VARCHAR2(20), BAH VARCHAR2(12), MZ INTEGER, JGRQ VARCHAR2(8), SPBH VARCHAR2(8), FJBH VARCHAR2(8), DYSL INTEGER, DYJLDW VARCHAR2(2), DRSL INTEGER, DRJLDW VARCHAR2(2), RMB INTEGER, MY INTEGER, MZFS VARCHAR2(1), SZSK INTEGER, SZGS INTEGER, JMS INTEGER, JMGS INTEGER, KBX VARCHAR2(6) )'; EXECUTE IMMEDIATE V_SQL;END IF;--创建序列，序列最小值为PSZ_TEMP_STB表中是最大ID+1open c1 for 'select nvl(max(id),0)+1 from PSZ_TEMP_STB';fetch c1 into MAX_ID;close c1;V_SQL := 'DROP SEQUENCE SEQ_PSZ_TEMP_STB'; begin EXECUTE IMMEDIATE V_SQL;exception when others then null;end;V_SQL := ' createsequence SEQ_PSZ_TEMP_STB minvalue '||MAX_ID||' maxvalue 99999999 start with '||MAX_ID||' increment by 1 cache 20'; EXECUTE IMMEDIATE V_SQL; V_FILE_HANDLE := UTL_FILE.FOPEN('MYFILEDIR', V_FILENAME, 'R');V_ROWS := 0; --处理记录数LOOP BEGIN --将文件信息读取到COL1中,每次读取一行 UTL_FILE.GET_LINE(V_FILE_HANDLE, COL1); --取序列值 open c1 for 'select SEQ_PSZ_TEMP_STB.nextval from dual'; fetch c1 into MAX_ID; close c1; --插入数据 execute immediate 'INSERT INTO PSZ_TEMP_STB (ID, BSM, JCKA, JYDWBH, HZDWDM, HZDWSZDQ, YSFSDM, MYFS, MZXS, QDG, CZG, XKZBH, BAH, MZ, JGRQ, SPBH, FJBH, DYSL, DYJLDW, DRSL, DRJLDW, RMB, MY, MZFS, SZSK, SZGS, JMS, JMGS, KBX) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29 )' using MAX_ID, SUBSTR(COL1, 1, 20), SUBSTR(COL1, 21, 4), SUBSTR(COL1, 25, 10), SUBSTR(COL1, 35, 10), SUBSTR(COL1, 45, 5), SUBSTR(COL1, 50, 1), SUBSTR(COL1, 51, 4), SUBSTR(COL1, 55, 3), SUBSTR(COL1, 58, 3), SUBSTR(COL1, 61, 3), SUBSTR(COL1, 64, 20), SUBSTR(COL1, 84, 12), SUBSTR(COL1, 96, 11), SUBSTR(COL1, 107, 8), SUBSTR(COL1, 115, 8), SUBSTR(COL1, 123, 8), SUBSTR(COL1, 131, 11), SUBSTR(COL1, 142, 2), SUBSTR(COL1, 144, 11), SUBSTR(COL1, 155, 2), SUBSTR(COL1, 157, 11), SUBSTR(COL1, 168, 11), SUBSTR(COL1, 179, 1), SUBSTR(COL1, 180, 11), SUBSTR(COL1, 191, 11), SUBSTR(COL1, 202, 11), SUBSTR(COL1, 203, 11), SUBSTR(COL1, 224, 6); V_ROWS := V_ROWS + 1; --每一万条提交一次 IF MOD(V_ROWS, 10000) = 0 THEN COMMIT; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; WHEN OTHERS THEN NULL; END;END LOOP;COMMIT;--关闭UTL_FILE.FCLOSE(V_FILE_HANDLE);--dbms_output.put_line(V_ROWS);END SZ_READFILEDAT;
调用的代码如下：
无输出参数时：
exec pro（v1,v2）;
有输出参数时：
DECLARE V_ROWS NUMBER(10); BEGIN readfile1(v_filepath =>'C:/TEMPCLF', v_filename =>'AAA.DAT', v_rows =>v_rows);END;
页:
[1]