oracle存储过程读取文件--按照自定格式存入数据库

感谢我的老师【云淡风轻】的帮助
别的不说，直接上代码：

CREATE OR REPLACE PROCEDURE SZ_READFILEDAT(V_FILEPATH VARCHAR2,--文件路径
                                      V_FILENAME VARCHAR2,--文件名称
                                      V_ROWS     OUT NUMBER)--返回处理记录数

AUTHID CURRENT_USER AS
  V_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 MYFILEDIR  AS ''' || V_FILEPATH || '''';
  EXECUTE IMMEDIATE V_SQL;

  --创建表
  SELECT COUNT(*) INTO IS_EXISTS  FROM USER_OBJECTS  WHERE 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+1
  open 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 := ' create  sequence 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;