使用預存程序將Oracle資料大量匯出為多個csv檔案

標籤：

資料庫有如下表結構：

user_info (   user_id           NUMBER primary key,  user_name     VARCHAR2(200) NOT NULL,         user_age        VARCHAR2(80),  create_date    DATE,  create_order   NUMBER)

現在有100萬條記錄，要求每1000條記錄匯出為一個csv檔案，內容用逗號分隔，共計1000個檔案，

檔案名稱格式為output1.csv，output1.csv......output1000.csv。

/*====================  第一步：建立一張表====================*/CREATE TABLE USER_INFO (   USER_ID        NUMBER PRIMARY KEY,  USER_NAME      VARCHAR2(200) not null,         USER_AGE       VARCHAR2(80),  CREATE_DATE    DATE,  CREATE_ORDER   NUMBER)

/*====================  第二步：初始化資料、粘貼在命令列執行====================*/declare i number;        v_age number;begin  i:=0;  for i in 1..1000000 loop     --隨機產生1~100的數字     select round(mod(dbms_random.value*100,100)) into v_age from dual;     insert into user_info(user_id,user_name,user_age,create_date,create_order)     values(SEQ_ALL_TABLE.NEXTVAL ,‘翁林‘||i,v_age,sysdate,i);       --每1000行提交一次     if mod(i,1000) = 0 then         commit;     end if;      end loop;end;

/*====================  第三步：具體過程實現  一次性將100萬條記錄全部查詢出來放到遊標中，每1000條寫一個檔案，

   經測試，使用遊標方式，使用該表初始化的資料，整個過程執行時間長度大致40s左右。====================*/create or replace procedure export_to_csv(--1、入口參數部分       p_dir varchar2)is--2、內部變數部分v_errorcode varchar2(30);v_errormsg  varchar2(100);--顯示遊標、一次性將資料全部讀完cursor mycur is select * from user_info order by user_id; --如果不排序，你將發現寫入結果順序將達不到你的期望,該懂得的。--行記錄myrecord user_info%rowtype;csv_output utl_file.file_type;out_file_name varchar2(20);  begin_time number;  end_time number;  count_num number;begin--3、主體部分      begin_time :=dbms_utility.get_time;      open mycur;      for i in 1..1000 loop            out_file_name := ‘output‘||i||‘.csv‘;            csv_output := utl_file.fopen(p_dir,out_file_name,‘W‘);            count_num := 0;          while count_num < 1000 loop --每1000行寫入一個檔案                fetch mycur into myrecord;                utl_file.put_line(csv_output                 ,myrecord.user_id|| ‘,‘ ||                myrecord.user_name||‘,‘||                myrecord.user_age||‘,‘||                to_char(myrecord.create_date,‘YYYY-MM-DD HH24:MI:SS‘)||‘,‘||                myrecord.create_order);                count_num := count_num + 1;          end loop;                  utl_file.fclose(csv_output);      end loop;      close mycur;       end_time := dbms_utility.get_time;         dbms_output.put_line(‘total time=‘ || (end_time-begin_time)*10 || ‘ms.‘);  --4、異常處理   exception    when others then     v_errorcode:=sqlcode;     v_errormsg :=sqlerrm;     rollback;     --輸出異常資訊     dbms_output.put_line(v_errorcode||v_errormsg);          end export_to_csv;

/*====================  第四步：建立輸出目錄,並授權給相關使用者====================*/CREATE or replace DIRECTORY MYDIR AS ‘d:\tmp\‘;GRANT READ,WRITE ON DIRECTORY MYDIR TO wenglin;/*====================  第五步：命令列執行，查看輸出檔案

若想看到命令列列印結果，請執行：set serveroutput on====================*/exec export_to_csv(‘MYDIR‘);

其他說明：該樣本參考：http://blog.csdn.net/chjttony/article/details/7466159

使用預存程序將Oracle資料大量匯出為多個csv檔案