http://blog.csdn.net/wuxiaokaixinguo/article/details/8016147

搞了几天，终于把使用Oracle存储过程导入导出数据做成功了。现在分享一下，供大家共同进步。在这里我非常非常感谢“往事如梦”，以及“Oracle爱好者”上好友的帮助。

    首先判断utl_file_dir的目录

 SQL> show parameter utl_file_dir
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      C:\logmnr

  修改utl_file_dir目录的方法：

在D:\oracle\product\10.2.0\db_1\database目录下的initSID.ora文件中添加utl_file_dir=C:\logmnr 即可（也可修改为其它目录）

在这里以utl_file_dir=c:\logmnr为列子进行测试；

使用Oracle编写存储过程导出数据的存储过程为：

CREATE OR REPLACE PROCEDURE "EXP_DATA" (         P_QUERY IN VARCHAR2,              -- sql语句 例子: 'select * from TEST'         P_DIR IN VARCHAR2,                -- 目录  用这个命令查看目录show parameter utl_file_dir         P_FILENAME IN VARCHAR2            --  要生成的文件名     )     IS         L_OUTPUT UTL_FILE.FILE_TYPE;         L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;         L_COLUMNVALUE VARCHAR2(4000);         L_STATUS INTEGER;         L_COLCNT NUMBER := 0;         L_SEPARATOR VARCHAR2(1);         L_DESCTBL DBMS_SQL.DESC_TAB;         P_MAX_LINESIZE NUMBER := 32000;     BEGIN         --OPEN FILE         L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);         --DEFINE DATE FORMAT         EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';         --OPEN CURSOR         DBMS_SQL.PARSE( L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE );         DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );         --DUMP TABLE COLUMN NAME       FOR I IN 1 .. L_COLCNT LOOP           -- UTL_FILE.PUT( L_OUTPUT,  L_DESCTBL(I).COL_NAME || ' ' );            DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );            L_SEPARATOR := '';        END LOOP;       --  UTL_FILE.NEW_LINE( L_OUTPUT );         --EXECUTE THE QUERY STATEMENT         L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);         --DUMP TABLE COLUMN VALUE         WHILE ( DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0 ) LOOP             L_SEPARATOR := ',';             FOR I IN 1 .. L_COLCNT LOOP                 if i< L_COLCNT then                   begin                 DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );                 UTL_FILE.PUT( L_OUTPUT,                 TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE,',',' ')));                 UTL_FILE.PUT(L_OUTPUT,',');                 end;                 else                   begin                  DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );                 UTL_FILE.PUT( L_OUTPUT,                 TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE,',',' ')));                   end;                 end if;                 L_SEPARATOR := ',';             END LOOP;             UTL_FILE.NEW_LINE( L_OUTPUT );         END LOOP;         --CLOSE CURSOR         DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);         --CLOSE FILE         UTL_FILE.FCLOSE( L_OUTPUT );     END;

运行存储过程：

SQL> exec exp_data('select * from A000067','c:\logmnr','A000067.txt');
 
PL/SQL procedure successfully completed

到C:\logmnr目录下可以发现生成了A000067.txt文件

SQL> exec exp_data('select * from A000067','c:\logmnr','A000067.csv');
 
PL/SQL procedure successfully completed

到C:\logmnr目录下可以发现生成了A000067.csv文件

使用Oracle编写存储过程导入数据的存储过程为：

A000067表结构为：

SQL> desc A000067;
Name          Type          Nullable Default Comments
------------- ------------- -------- ------- --------
ID            NUMBER(38)    Y                        
GETINFOTIME   DATE          Y                        
INSERTTIME    DATE          Y                        
WATERHEIGHT   FLOAT         Y                        
WATERSPEED    FLOAT         Y                        
LISTENERPOWER FLOAT         Y                        
WEATHER       NVARCHAR2(10) Y                        
TEMPERATURE   NVARCHAR2(10) Y                        
WINDGRADE     NVARCHAR2(10) Y                        
WINDDIRECTION NVARCHAR2(10) Y                        
HUMIDITY      NVARCHAR2(10) Y                        
RAINSPEED     NVARCHAR2(10) Y                        
RAINORNOT     NUMBER(38)    Y                        
 

使用Oracle编写存储过程导入数据的存储过程为

create or replace procedure IMPDATAA000067 (p_path     varchar2,                                          p_filename varchar2) as   v_filehandle     utl_file.file_type; --定义一个文件句柄   v_text           varchar2(1000); --存放文本   V_ID            A000067.ID%type;   V_GETINFOTIME  A000067.GETINFOTIME%type;   V__INSERTTIME   A000067.INSERTTIME%type;   V_WATERHEIGHT   A000067.WATERHEIGHT%type;   V_WATERSPEED    A000067.WATERSPEED%type;   V_LISTENERPOWER A000067.LISTENERPOWER%type;   V_WEATHER       A000067.WEATHER%type;   V_TEMPERATURE   A000067.TEMPERATURE%type;   V_WINDGRADE     A000067.WINDGRADE%type;   V_WINDDIRECTION A000067.WINDDIRECTION%type;   V_HUMIDITY      A000067.HUMIDITY %type;   V_RAINSPEED     A000067.RAINSPEED%type;   V_RAINORNOT     A000067.RAINORNOT%type;   v_location1  number;   v_location2 number;   v_location3 number;   v_location4 number;   v_location5 number;   v_location6 number;   v_location7 number;   v_location8 number;   v_location9 number;   v_location10 number;   v_location11 number;   v_location12 number;   v_totalinserted  number; begin   if (p_path is null or p_filename is null) then     goto to_end;   end if;   v_totalinserted := 0;   /*open specified file*/   v_filehandle := utl_file.fopen(p_path, p_filename, 'r');   loop     begin       utl_file.get_line(v_filehandle, v_text);     exception       when no_data_found then         exit;     end;     v_location1 := instr(v_text, ',', 1, 1);     v_location2 := instr(v_text, ',', 1, 2);     v_location3 := instr(v_text, ',', 1, 3);     v_location4 := instr(v_text, ',', 1, 4);     v_location5 := instr(v_text, ',', 1, 5);     v_location6 := instr(v_text, ',', 1, 6);     v_location7 := instr(v_text, ',', 1, 7);     v_location8 := instr(v_text, ',', 1, 8);     v_location9 := instr(v_text, ',', 1, 9);     v_location10 := instr(v_text, ',', 1,10);     v_location11:= instr(v_text, ',', 1, 11);     v_location12 := instr(v_text, ',', 1, 12);     V_ID            := substr(v_text, 1, v_location1 - 1);     V_GETINFOTIME  :=to_date(substr(v_text, v_location1 + 1,                                v_location2 - v_location1 - 1),'yyyy-MM-dd HH24:MI:SS');     V__INSERTTIME         :=to_date(substr(v_text,v_location2 + 1,v_location3-v_location2 - 1),'yyyy-MM-dd HH24:MI:SS');     V_WATERHEIGHT        := to_number(substr(v_text, v_location3+1,v_location4-v_location3-1));     V_WATERSPEED        := to_number(substr(v_text, v_location4+1,v_location5-v_location4-1));     V_LISTENERPOWER        := to_number(substr(v_text, v_location5+1,v_location6-v_location5-1));     V_WEATHER        := substr(v_text, v_location6+1,v_location7-v_location6-1);     V_TEMPERATURE        := substr(v_text, v_location7+1,v_location8-v_location7-1);     V_WINDGRADE        := substr(v_text, v_location8+1,v_location9-v_location8-1);     V_WINDDIRECTION        := substr(v_text, v_location9+1,v_location10-v_location9-1);     V_HUMIDITY        := substr(v_text, v_location10+1,v_location11-v_location10-1);     V_RAINSPEED         := substr(v_text, v_location11+1,v_location12-v_location11-1);     V_RAINORNOT   := to_number(substr(v_text,v_location12+1));              /*插入数据库操作*/     insert into A000067 values (V_ID,V_GETINFOTIME,V__INSERTTIME,V_WATERHEIGHT,V_WATERSPEED,V_LISTENERPOWER,V_WEATHER,V_TEMPERATURE,V_WINDGRADE,V_WINDDIRECTION,V_HUMIDITY,V_RAINSPEED,V_RAINORNOT);     commit;   end loop;   <<to_end>>   null; end ;

测试存储过程

create or replace procedure IMP_DATATEST AS   v_path     varchar2(200);   v_filename varchar2(200); begin   v_path     := 'C:\logmnr';   v_filename := 'A000067.txt';   IMPDATAA000067(v_path, v_filename); end;

运行效果

SQL> select count(*) from A000067;
 
  COUNT(*)
----------
       312
 
SQL> exec imp_datatest;
 
PL/SQL procedure successfully completed
 
SQL> select count(*) from A000067;
 
  COUNT(*)
----------
       468