分享我的小工具, 把数据库中的表的数据导出到文件的例子

Question

create or replace function fun_table_data_to_file(in_table_name varchar2)
return number is
       type charArray is table of varchar2(500) index by binary_integer;
   v_file_handle UTL_FILE.FILE_TYPE;
   varcharTemp varchar2(500);
   theCursor sys_refcursor;
       tableName varchar2(200);
   headList charArray;
   dataTypeList charArray;
   dataArray charArray;
       
   sSQL varchar2(2000);
   
   columnName varchar2(500);
   columnDataType varchar2(30);
   
   unitCount number;
   columnCount number;
   rowsCount number;
   unit number;
       
   cursor columnCursor is
       select 
   column_name,
   data_type
   from 
   user_tab_columns
   where 
       table_name = tableName
   order by
       column_id; 
   
begin
       tableName := upper(trim(in_table_name));
    v_file_handle := utl_file.fopen('OUT_DIR',
                           tableName || '_' || TO_CHAR(SYSDATE, 'YYYY_MM_DD_HH24_MI_SS'),
                           'w');
   
   columnCount := 0;
   rowsCount := 0;
   unitCount := 0;
   
   open columnCursor;
   loop
        fetch columnCursor into columnName,columnDataType;
if (columnCursor%notfound) then
   exit;
end if;
columnCount := columnCount + 1;
headList(columnCount) := columnName;
dataTypeList(columnCount) := columnDataType;

sSQL := 'select ' || columnName || ' from ' || tableName;
rowsCount := 0;
open theCursor for sSQL;
loop
     fetch theCursor into dataArray(unitCount + 1);
 if (theCursor%notfound) then
     exit;
 end if;
 rowsCount := rowsCount + 1;
 unitCount := unitCount + 1;
    end loop;

   end loop;
       
   -- print head 
   for i in 1 .. headList.count loop
       --dbms_output.put(headList(i) || '    ');
   varcharTemp := headList(i) || '    '; 
   utl_file.put(v_file_handle,varcharTemp);
   end loop;
   --dbms_output.put_line('');
   utl_file.put_line(v_file_handle,'');
   
   -- print data
   for i in 1 .. rowsCount loop
       for j in 1 .. columnCount loop
       unit := (j - 1) * rowsCount + i;
   --dbms_output.put(dataArray(unit) || '    ');
   utl_file.put(v_file_handle,dataArray(unit) || '    ');
   end loop;
   --dbms_output.put_line('');
   utl_file.put_line(v_file_handle,'');
   end loop;
   utl_file.fclose(v_file_handle);  
   return 0;
exception
       when others then
        dbms_output.put_line('ERROR  - ' || to_char(sqlcode));
        return sqlcode;

end fun_table_data_to_file;

Answer 1

还可以再发挥一下, 把文件里的每条记录变成insert 语句, 导入数据时就方便多了. 

Answer 2

create or replace function fun_table_data_to_file(in_table_name varchar2,in_whereString varchar2 default null)
return number is
       type charArray is table of varchar2(500) index by binary_integer;
   fileHandler utl_file.file_type;
   varcharTemp varchar2(500);
   theCursor sys_refcursor;
       tableName varchar2(200);
   whereString varchar2(1000);
   headList charArray;
   dataTypeList charArray;
   dataArray charArray;
       
   sSQL varchar2(2000);
   
   columnName varchar2(500);
   columnDataType varchar2(30);
   
   unitCount number;
   columnCount number;
   rowsCount number;
   unit number;
       
   cursor columnCursor is
       select 
   column_name,
   data_type
   from 
   user_tab_columns
   where 
       table_name = tableName
   order by
       column_id; 
   
begin
       tableName := upper(trim(in_table_name));
   whereString := nvl(in_whereString,' ');
   fileHandler := utl_file.fopen(upper('out_dir'),
                           tableName || '_' || to_char(sysdate, 'yyyy_mm_dd.dat'),
                           'w');
   columnCount := 0;
   rowsCount := 0;
   unitCount := 0;
   
   open columnCursor;
   loop
        fetch columnCursor into columnName,columnDataType;
if (columnCursor%notfound) then
   exit;
end if;
columnCount := columnCount + 1;
headList(columnCount) := columnName;
dataTypeList(columnCount) := columnDataType;

sSQL := 'select ' || columnName || ' from ' || tableName || whereString;
rowsCount := 0;
open theCursor for sSQL;
loop
     fetch theCursor into dataArray(unitCount + 1);
 if (theCursor%notfound) then
     exit;
 end if;
 rowsCount := rowsCount + 1;
 unitCount := unitCount + 1;
    end loop;

   end loop;
       
   -- print head 
   for i in 1 .. headList.count loop
       --dbms_output.put(headList(i) || '    ');
   varcharTemp := headList(i) || '    '; 
   utl_file.put(fileHandler,varcharTemp);
   end loop;
   --dbms_output.put_line('');
   utl_file.put_line(fileHandler,'');
   
   -- print data
   for i in 1 .. rowsCount loop
       for j in 1 .. columnCount loop
       unit := (j - 1) * rowsCount + i;
   --dbms_output.put(dataArray(unit) || '    ');
   utl_file.put(fileHandler,dataArray(unit) || '    ');
   end loop;
   --dbms_output.put_line('');
   utl_file.put_line(fileHandler,'');
   end loop;
   utl_file.fclose(fileHandler);  
   return 0;
exception
       when others then
        dbms_output.put_line('ERROR  - ' || to_char(sqlcode));
        return sqlcode;

end fun_table_data_to_file;

Answer 3

支持下，不过像toad里直接就可以导出数据

Answer 4

最近也刚刚写了个批量导出数据表内容的脚本。
支持lz。
不过对于通用的导出还是用工具如toad、pl/sql,oraloader等比较好些。

Answer 5

直接写个bat，导入导出一句话不就可以完成吗？