要在存储过程里写文件只能用utl_file包 如果想导出数据,可用sqlplus的spool命令,如: set trimspool on set linesize 120 set pagesize 2000 set newpage 1 set heading off set term off spool 路径+文件名 select col1||','||col2||','||col3||','||col4||'..' from tablename; spool off
BEGIN v_FileHandle:=UTL_FILE.FOPEN('/TMP/','utl.file.csv', 'w'); UTL_FILE.PUT_LIVE(v_FileHandle,'THIS IS LINE 1!'); FOR v_Counter IN 2..5 loop UTL_FILE.PUTF(v_FileHandle,'THIS IS LINE %S!\n',v_Counter); END LOOP; UTL_FILE.FCLOSE(v_FileHandle); END;
http://topic.csdn.net/t/20060712/15/4875926.html
在函数里怎么写呢? 先 select 很多数据,然后把检索的数据生成csv文件?
--tom写的 --将查询语句的结果保存到文件里,类似sqlplus的spool create or replace function dump_csv( p_query in varchar2, p_separator in varchar2 default ',', p_dir in varchar2 , p_filename in varchar2 ) return number AUTHID CURRENT_USER is l_output utl_file.file_type; l_theCursor integer default dbms_sql.open_cursor; l_columnValue varchar2(2000); l_status integer; l_colCnt number default 0; l_separator varchar2(10) default ''; l_cnt number default 0; begin l_output := utl_file.fopen( p_dir, p_filename, 'w' ); dbms_sql.parse( l_theCursor, p_query, dbms_sql.native ); for i in 1 .. 255 loop begin dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 ); l_colCnt := i; exception when others then if ( sqlcode = -1007 ) then exit; else raise; end if; end; end loop; dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 ); l_status := dbms_sql.execute(l_theCursor); loop exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 ); l_separator := ''; for i in 1 .. l_colCnt loop dbms_sql.column_value( l_theCursor, i, l_columnValue ); utl_file.put( l_output, l_separator || l_columnValue ); l_separator := p_separator; end loop; utl_file.new_line( l_output ); l_cnt := l_cnt+1; end loop; dbms_sql.close_cursor(l_theCursor); utl_file.fclose( l_output ); return l_cnt; end dump_csv;
这是我正在用的一个实际例子: DECLARE CURSOR C1 IS SELECT GLDH, to_char(JLRQ,'yyyy/mm/dd') JLRQ, to_char(JHRQ,'yyyy/mm/dd') JHRQ, replace(DDBH,',',null) DDBH, replace(MT,',','/') MT, replace(GG,',','/') GG, KHM, to_char(SL) SL, BZMJ, YSCC2, to_char(BZSL1) BZSL1, to_char(PSI) PSI, DECODE(BZLB,1,'裱坑',2,'对裱',3,'手工裱') BZLB FROM BOMD16 WHERE TO_CHAR(JLRQ,'yyyy/mm/dd') BETWEEN :EXP_DATA.JLRQF AND :EXP_DATA.JLRQT;
如果想导出数据,可用sqlplus的spool命令,如:
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool 路径+文件名
select col1||','||col2||','||col3||','||col4||'..' from tablename;
spool off
v_FileHandle:=UTL_FILE.FOPEN('/TMP/','utl.file.csv', 'w');
UTL_FILE.PUT_LIVE(v_FileHandle,'THIS IS LINE 1!');
FOR v_Counter IN 2..5 loop
UTL_FILE.PUTF(v_FileHandle,'THIS IS LINE %S!\n',v_Counter);
END LOOP;
UTL_FILE.FCLOSE(v_FileHandle);
END;
先 select 很多数据,然后把检索的数据生成csv文件?
--将查询语句的结果保存到文件里,类似sqlplus的spool
create or replace function dump_csv( p_query in varchar2,
p_separator in varchar2
default ',',
p_dir in varchar2 ,
p_filename in varchar2 )
return number
AUTHID CURRENT_USER
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' ); dbms_sql.parse( l_theCursor, p_query, dbms_sql.native ); for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop; dbms_sql.define_column( l_theCursor, 1, l_columnValue,
2000 ); l_status := dbms_sql.execute(l_theCursor); loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
utl_file.put( l_output, l_separator ||
l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor); utl_file.fclose( l_output );
return l_cnt;
end dump_csv;
DECLARE
CURSOR C1 IS
SELECT GLDH,
to_char(JLRQ,'yyyy/mm/dd') JLRQ,
to_char(JHRQ,'yyyy/mm/dd') JHRQ,
replace(DDBH,',',null) DDBH,
replace(MT,',','/') MT,
replace(GG,',','/') GG,
KHM,
to_char(SL) SL,
BZMJ,
YSCC2,
to_char(BZSL1) BZSL1,
to_char(PSI) PSI,
DECODE(BZLB,1,'裱坑',2,'对裱',3,'手工裱') BZLB
FROM BOMD16
WHERE TO_CHAR(JLRQ,'yyyy/mm/dd') BETWEEN :EXP_DATA.JLRQF AND :EXP_DATA.JLRQT;
file_name text_io.file_type;
vstr varchar2(10000):=null;
v_gysmc varchar2(50):=null;
v_bj varchar2(50):=null;
v_mj1 number;
v_mj2 number;
BEGIN
WORKING(TRUE); file_name := text_io.fopen(:exp_data.filename,'w'); vstr:='工程单号'||',';
vstr:=vstr||'建立日期'||',';
vstr:=vstr||'交货日期'||',';
vstr:=vstr||'订单号'||',';
vstr:=vstr||'唛头'||',';
vstr:=vstr||'规格'||',';
vstr:=vstr||'客户'||',';
vstr:=vstr||'订单数量'||',';
vstr:=vstr||'长*宽'||',';
vstr:=vstr||'损耗'||',';
vstr:=vstr||'数量'||',';
vstr:=vstr||'总面积'||',';
vstr:=vstr||'类别'||',';
text_io.put_line(file_name,vstr);
vstr:=null; FOR I IN C1 LOOP
vstr:=i.gldh||','||
i.jlrq||','||
i.jhrq||','||
i.ddbh||','||
i.mt||','||
i.gg||','||
i.khM||','||
i.sl||','||
i.YSCC2||','||
i.psi||','||
i.bzsl1||','||
i.bzmj||','||
i.bzlb||',';
text_io.put_line(file_name,vstr);
END LOOP; text_io.fclose(file_name);
WORKING(FALSE);
messagebox('请查看文件:'||:exp_data.filename);
exception
when others then
WORKING(FALSE);
messagebox('先关闭 Excel 文!!!');
END;