导数据_存储过程导入数据_

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,',',1,1);---文本文件第一个,位置
         v_firstlocation2 := instr(v_text,',',v_firstlocation1,2);---文本文件第二个,位置
         
         --分隔符为逗号的
        
         vempno := substr(v_text,1,v_firstlocation1-1);
         vename := substr(v_text,v_firstlocation1+1,v_firstlocation2-v_firstlocation1-1);
         VHIREDATE := substr(v_text,v_firstlocation2+1,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-721341/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24805255/viewspace-721341/