用oracle utl_file包读取数据写入文件

create or replace directory MY_DIR as '/usr/test/';

 

create or replace function f_exportTxt(
  --传入参数
  i_query in varchar2,
  i_separator in varchar2,
  i_dir in varchar2,
  i_filename in varchar2

) return number

is
  /**
  ** 函数名:f_exportTxt
  ** 参数:1.i_query 查询sql语句; 2.i_separator 分隔符,默认为',' ;
  **      3.i_dir存放目录; 4.i_filename文件名,默认在文件名前加上yyyymmddhh24mi
  ** 返回: 大于等于0即为写入文件记录数,负为异常
  ** 功能: 用户数据抽取,并生成文件到指定目录下
  ** 备注:
  ** 作者:lingo
  ** 修改日期:2010-04-15
  **/

  --定义参数
  v_file utl_file.file_type;
  v_theCursor integer default dbms_sql.open_cursor;
  v_columnValue varchar2(2000); --临时(列值)
  v_colCnt number default 0; --列总数
  v_separator varchar2(10) default ',';--分隔符,默认为#@
  v_cnt number default 0;  --记录总数
  v_filename varchar2(100);--时间
  v_status integer;--执行SQL后返回状态值

  v_count number default 10000 ; --每次查询的数量，大于该数量则多次读取数据到游标
  v_tmp number ;--临时(总记录数,通过SQL统计算出,假如v_cnt不等于v_tmp,则导出有误)
  v_sql varchar2(2000) ; --组合sql语句
  v_loops number ; --循环次数

begin

  --select to_char(sysdate,'yyyymmddhh24mi') into v_filename from dual; --取时间年月日时分做文件名前缀
  v_filename :='';
  v_filename := v_filename||i_filename ; --组建文件名

  v_sql := 'select count(''x'') from (' ||i_query||')' ;--统计总数

  execute immediate v_sql into v_tmp;

  select trunc(v_tmp/v_count) into v_loops from dual ; --循环次数
  if mod(v_tmp,v_count) > 0 then
    v_loops := v_loops+1;
  end if;

  v_file := utl_file.fopen(i_dir,v_filename,'W'); --打开文件

  for i in 1 .. v_loops loop

      v_sql := 'select * from ( select m.*,rownum r from ('|| i_query ||' ) m where rownum <= '
           ||i*v_count ||') where r >'|| (i-1)*v_count ;

      dbms_sql.parse(v_theCursor, v_sql, dbms_sql.native);  --解析动态SQL语句

      for i in 1 .. 255 loop
        begin
          dbms_sql.define_column(v_theCursor, i, v_columnValue, 2000); --定义列
          v_colCnt := i;
        exception
          when others then
            if (sqlcode = -1007) then
              exit;
            else
              raise;
            end if;
        end;
      end loop;

      dbms_sql.define_column(v_theCursor, 1, v_columnValue, 2000);

      v_status := dbms_sql.execute(v_theCursor); --执行SQL

      loop
        exit when(dbms_sql.fetch_rows(v_theCursor) <= 0);  --游标记录小于0退出
        v_separator := ''; --分隔符,第一次为''
        for i in 1 .. v_colCnt-1 loop  --循环列
          dbms_sql.column_value(v_theCursor, i, v_columnValue); --将当前行的查询结果写入上面定义的列中
          utl_file.put(v_file, v_separator || v_columnValue); --将值写入文件中
          v_separator := i_separator;
        end loop;
        utl_file.new_line(v_file);--换行
        v_cnt := v_cnt + 1;--记录写入记录总数
      end loop;

  end loop ;

  dbms_sql.close_cursor(v_theCursor); --关闭

  utl_file.fclose(v_file);--关闭写入文件

  return v_cnt ;
 
  exception
    when others then
      dbms_sql.close_cursor(v_theCursor);
      utl_file.fclose(v_file);
      v_cnt := -1 ;
      return v_cnt ;

end f_exportTxt;