ORACLE之表数据导出

有时候会有一些需求，把ORACLE库中一些表进行导出，当然，这有很多种方法，在此我说的是用UTL_FILE来实现ORACLE存储过程数据导出， 可以实现通过一个配制表，配制要导出的表名，及一些增量导出条件等，不废话，直接程序

 

先建立oracle路径，

create or replace directory D_OUTPUT as '/outfile/out';    --路径对应ORACLE所在机器目录
grant read,write on directory D_OUTPUT to dw_dc;  
GRANT EXECUTE ON utl_file TO dw_dc; 

 

以下为程序

create or replace package etl_gd_data is
procedure Export_table(in_tab_Name varchar2,in_tab_where varchar2);
function   get_tab_select(in_tab_Name  varchar2 ,spacestr varchar2) return varchar2;
end ;
/
create or replace package body etl_gd_data is

/**
**
**/

  procedure Export_table(in_tab_Name varchar2,in_tab_where varchar2) is
  type cursor_type is ref cursor;
  cur01 cursor_type;
  sqlstr varchar2(4000);
   v_line varchar2(4000);
  sql_where varchar2(2000);
  sql_select varchar2(2000);
    v_file_path    constant varchar2(30):='D_OUTPUT';
    v_file_name    varchar2(50);
    v_File_Handle  UTL_FILE.FILE_TYPE;
   V_IDX NUMBER(12);
   spacestr varchar2(40);
  begin
  spacestr:='||''^|^''||';  --分隔符号
   sql_where  :=in_tab_where;
   sql_select :=get_tab_select(in_tab_Name,spacestr);
  sqlstr:= 'select  '||sql_select||' from  '||in_tab_Name || ' t0 where 1=1  '||sql_where ||' AND ROWNUM<100';
  v_file_name :=in_tab_Name||'_'||to_char(sysdate,'yyyymmdd')||'_01.dat';
  v_File_Handle:=UTL_FILE.FOPEN(v_file_path,v_file_name,'w');

  V_IDX:=0;
  open cur01 for sqlstr;
       loop
       fetch cur01 into v_line;
         exit when cur01%notfound;
      V_IDX:=V_IDX+1;
        UTL_FILE.PUT_LINE(v_file_handle,v_line);
          end loop;

         UTL_FILE.FCLOSE(v_file_handle);
    close cur01;

    --2.生成验证文件
    v_file_name :=in_tab_Name||'_'||to_char(sysdate,'yyyymmdd')||'_01.verf';
    v_File_Handle:=UTL_FILE.FOPEN(v_file_path,v_file_name,'w');
    v_line:='表名:'||in_tab_Name ||' 生成时间:'||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
    UTL_FILE.PUT_LINE(v_file_handle,v_line);
    v_line:='字段排列顺序:'||get_tab_select(in_tab_Name,',');
    UTL_FILE.PUT_LINE(v_file_handle,v_line);
     v_line:='字段间分隔符:'|| '''^|^''';
    UTL_FILE.PUT_LINE(v_file_handle,v_line);
    v_line:='查询条件:'||sql_where;
    UTL_FILE.PUT_LINE(v_file_handle,v_line);

    v_line:='总计条数:'||V_IDX;
    UTL_FILE.PUT_LINE(v_file_handle,v_line);
    UTL_FILE.FCLOSE(v_file_handle);

  end;

  ---得到查询内容
 function   get_tab_select(in_tab_Name  varchar2,spacestr varchar2)
 --'||''^|^''||'
 return varchar2 is

  sqlstr varchar2(4000);

  cursor cur001 is
   SELECT     case when  b.DATA_TYPE in ('DATE','TIMESTMP') THEN 'to_char('||b.COLUMN_NAME||',''yyyymmddhh24miss'')' ELSE
   b.COLUMN_NAME END  ff
 FROM  user_tab_columns b
WHERE b.TABLE_NAME=upper(in_tab_Name)
order by b.COLUMN_id ;

  begin
  sqlstr:='';
  for rec in cur001 loop
    sqlstr:=sqlstr||spacestr||rec.ff;

  end loop;

  return substr(sqlstr,length(spacestr)+1);
  end;

  

end ;
/
---------------

程序其实很简单，就是利用动态游标来实现查询出来的内容，当然，这不支持大字段， 最后加上一些验证文件信息， 简单的数据交互就可以这样实现了，

我的原则，能oracle内部做的事，尽量让ORACLE来做，尽量减少维护代价。。。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/134308/viewspace-732337/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/134308/viewspace-732337/