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

[pauliuyou]

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;

[pauliuyou]

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

[pauliuyou]

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;

[pauliuyou]

嘿嘿, 还是下面的好看

[hebo2005]

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

[pauliuyou]

呵呵, 这个可控性更高些. 发挥的空间也更大.

[flg_inwind]

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

[chen08]

支持 学习

[poi_9]

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