Oracle 快速卸载数据到文本文件

-- 建立目录  
create or replace directory "mydir" as '/home/oracle/';  
  
-- 建立对象  
create or replace type dump_ot as object  
(  
   file_name varchar2 (128),  
   directory_name varchar2 (128),  
   no_records number,  
   session_id number,  
   start_dttm timestamp with time zone,  
   end_dttm timestamp with time zone  
);  
  
-- 建立对象表  
create or replace type dump_ntt as table of dump_ot;  
  
-- 建立函数  
create or replace function data_unload (  
   p_source            in sys_refcursor,  
   p_filename          in varchar2,  
   p_directory         in varchar2,  
   p_unique_filename   in varchar2 default 'n',  
   p_create_log_file   in varchar2 default 'n')  
   return dump_ntt  
   pipelined  
   parallel_enable(partition p_source by any)  
as  
   /*  
   p_source           sql query you spool  
   p_filename         targe file name  
   p_directory        targe oracle directory  
   p_unique_filename  create unique file name? y/n (appends unique sid to p_filename) - useful only for parallel unload  
   p_create_log_file  create log file? y/n (creates separate log file and logs every 1mm rows) - has very small performance hit on the spool  
   */  
  
   type row_ntt is table of varchar2 (32767);  
  
   v_rows                 row_ntt;  
   v_file                 utl_file.file_type;  
   v_log_file             utl_file.file_type;  
   v_buffer               varchar2 (32767);  
   v_sid                  varchar (255);  
   v_name                 varchar2 (255);  
   v_lines                pls_integer := 0;  
   v_start_dttm           timestamp with time zone := systimestamp;  
   v_end_dttm             timestamp with time zone;  
   v_create_log           boolean := false;  
  
   c_eol         constant varchar2 (1) := chr (10);  
   c_eollen      constant pls_integer := lengthb (c_eol);  
   c_maxline     constant pls_integer := 32767;  
   c_log_limit   constant pls_integer := 1000000;  
begin  
   v_sid := lpad (sys_context ('userenv', 'sid'), 10, '0');  
   v_name := p_filename;  
  
   if trim (upper (p_create_log_file)) = 'y'  
   then  
      v_create_log := true;  
   end if;  
  
   -- add sid (must be used for parallel spooling, single spooling or spooling across db-link creates alsway one file)  
   if upper (p_unique_filename) = 'y'  
   then  
      v_name := v_name || '_' || to_char (v_sid);  
   end if;  
  
   v_file :=  
      utl_file.fopen (p_directory,  
                      v_name,  
                      'w',  
                      c_maxline);  
  
   if v_create_log  
   then  
      v_log_file :=  
         utl_file.fopen (p_directory,  
                         v_name || '.log',  
                         'w',  
                         c_maxline);  
      utl_file.put_line (  
         v_log_file,  
         to_char (v_start_dttm, 'yyyy-mm-dd hh24:mi:ss:ff3') || ' --> start');  
      utl_file.fflush (v_log_file);  
   end if;  
  
   loop  
      fetch p_source  
         bulk collect into v_rows  
         limit 10000;  
  
      for i in 1 .. v_rows.count  
      loop  
         if lengthb (v_buffer) + c_eollen + lengthb (v_rows (i)) <= c_maxline  
         then  
            v_buffer := v_buffer || c_eol || v_rows (i);  
         else  
            if v_buffer is not null  
            then  
               utl_file.put_line (v_file, v_buffer);  
            end if;  
  
            v_buffer := v_rows (i);  
         end if;  
      end loop;  
  
      v_lines := v_lines + v_rows.count;  
  
      if v_create_log and mod (v_lines, c_log_limit) = 0  
      then  
         utl_file.put_line (  
            v_log_file,  
               to_char (systimestamp, 'yyyy-mm-dd hh24:mi:ss:ff3')  
            || ' --> '  
            || v_lines);  
         utl_file.fflush (v_log_file);  
      end if;  
  
      exit when p_source%notfound;  
   end loop;  
  
   close p_source;  
  
   utl_file.put_line (v_file, v_buffer);  
   utl_file.fclose (v_file);  
   v_end_dttm := systimestamp;  
  
   if v_create_log  
   then  
      utl_file.put_line (  
         v_log_file,  
         to_char (v_end_dttm, 'yyyy-mm-dd hh24:mi:ss:ff3') || ' --> ' || v_lines);  
      utl_file.put_line (  
         v_log_file,  
         to_char (v_end_dttm, 'yyyy-mm-dd hh24:mi:ss:ff3') || ' --> end');  
      utl_file.fclose (v_log_file);  
   end if;  
  
   pipe row (dump_ot (v_name,  
                      p_directory,  
                      v_lines,  
                      v_sid,  
                      v_start_dttm,  
                      v_end_dttm));  
   return;  
end;  
/  
  
-- 显示执行时间  
set timing on;  
  
-- 调用函数，并行生成10个csv文件  
select *  
  from table (  
          data_unload (  
             cursor (  
                select /*+ parallel(u,10) */  
                      log_id  
                       || ','  
                       || typeid  
                       || ','  
                       || to_id  
                       || ','  
                       || to_msg  
                       || ','  
                       || userid  
                       || ','  
                       || nickname  
                       || ','  
                       || showing  
                       || ','  
                       || fromip  
                       || ','  
                       || to_char (createtime, 'yyyy-mm-dd hh24:mi:ss')  
                  from u1.user_visit u),  
             'a.txt',  
             'mydir',  
             'y',  
             'n'));