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

时间：2022-04-20 04:02:33
搞了几天，终于把使用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

标签：

相关文章