Oracle10g通过sqlplus导出excel

set linesize 200 

set term off verify off feedback off pagesize 999 

set markup html on entmap ON spool on preformat off

spool /home/oracle/bristol/tables.xls

@/home/oracle/bristol/get_tables.sql

spool off

exit

vim a2.sql

select * from CHANNELTYPE;

2、执行并获得输出

sqlplus "test/test" @/home/oracle/bristol/a1.sql

3、下载输出的excel结果

Oracle10g通过sqlplus导出excel

标签：oracle10g通过sqlplus导出excel

小编还为您整理了以下内容，可能对您也有帮助：

oracle中如何把查询结果导出到excel里面

main.sql 注意，需要在sqlplus下运行 非plsql命令行下set linesize 200set term off verify off feedback off pagesize 999 set markup html on entmap ON spool on preformat offspool test_tables.xls@get_tables.sqlspool offexit

sql脚本，get_tables.sql

--get_tables.sqlselect * from all_objects where rownum<=1000;

然后在sqlplus下运行main.sql

使用包体编程：

create or replace package smt_xlsx_maker_pkg is /****************************************************************************** NAME: SMT_XLSX_MAKER_PKG PURPOSE: XLSX 生成 Pkg，主要是从Oracle数据库端生成Xlsx二进制的文件。 REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 2011/2/19 Anton Scheffer 1,New Create the pkg 1.1 2015/6/10 Sam.T 1.优化核心处理生成xlsx的代码，使得生成文档的执行效率大大提高！ 1.1 2015/6/10 Sam.T 1.query2sheet增加绑定变量的可选输入参数。 1.2 2015/6/15 Sam.T 1.代码增加调试模式。直接设置G_DEBUG_MODE变量即可。 1.2 2015/6/20 Sam.T 1.为方便使用，再次封装一些简单易用的过程，生成xlsx文档 1.2 2015/7/5 Sam.T 1.单条SQL生成xlsx文档的内容，增加是否显示foot，以及显示的行数。 jinzhao 解决数据量较大情况下导出excel报错xml的问题。将dbms_lob.writeappend修改为dbms_lob.append ******************************************************************************/ --------- /********************************************** ****************************************************************************** Copyright (C) 2011, 2012 by Anton Scheffer Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. ****************************************************************************** ******************************************** */ /* 使用方法： declare l_sql varchar2(30000); begin l_sql := ‘select * from all_objects‘; smt_xlsx_maker_pkg.query2sheet(l_sql, true, ‘XLS_DIR‘, ‘Export2.xlsx‘); end; 除此之外，基本上Excel 中有的效果它都可以生成。 以下一个例子，包括居中，合并单元格，底色，新增工作表等： begin smt_xlsx_maker_pkg.clear_workbook; smt_xlsx_maker_pkg.new_sheet; smt_xlsx_maker_pkg.cell(5, 1, 5); smt_xlsx_maker_pkg.cell(3, 1, 3); smt_xlsx_maker_pkg.cell(2, 2, 45); smt_xlsx_maker_pkg.cell(3, 2, ‘Anton Scheffer‘, p_alignment => smt_xlsx_maker_pkg.get_alignment(p_wraptext => true)); smt_xlsx_maker_pkg.cell(1, 4, sysdate, p_fontid => smt_xlsx_maker_pkg.get_font(‘Calibri‘, p_rgb => ‘FFFF0000‘)); smt_xlsx_maker_pkg.cell(2, 4, sysdate, p_numfmtid => smt_xlsx_maker_pkg.get_numfmt(‘dd/mm/yyyy h:mm‘)); smt_xlsx_maker_pkg.cell(3, 4, sysdate, p_numfmtid => smt_xlsx_maker_pkg.get_numfmt(smt_xlsx_maker_pkg.orafmt2excel(‘dd/mon/yyyy‘))); smt_xlsx_maker_pkg.cell(5, 5, 75, p_borderid => smt_xlsx_maker_pkg.get_border(‘double‘, ‘double‘, ‘double‘, ‘double‘)); smt_xlsx_maker_pkg.cell(2, 3, 33); smt_xlsx_maker_pkg.hyperlink(1, 6, ‘http://www.cnblogs.com/mellowsmile‘, ‘jinzhao site‘); smt_xlsx_maker_pkg.cell(1, 7, ‘Some merged cells‘, p_alignment => smt_xlsx_maker_pkg.get_alignment(p_horizontal => ‘center‘)); smt_xlsx_maker_pkg.mergecells(1, 7, 3, 7); for i in 1 .. 5 loop smt_xlsx_maker_pkg.comment(3, i + 3, ‘Row ‘ || (i + 3), ‘Anton‘); end loop; smt_xlsx_maker_pkg.new_sheet; smt_xlsx_maker_pkg.set_row(1, p_fillid => smt_xlsx_maker_pkg.get_fill(‘solid‘, ‘FFFF0000‘)); for i in 1 .. 5 loop smt_xlsx_maker_pkg.cell(1, i, i); smt_xlsx_maker_pkg.cell(2, i, i * 3); smt_xlsx_maker_pkg.cell(3, i, ‘x ‘ || i * 3); end loop; smt_xlsx_maker_pkg.query2sheet(‘select rownum, x.* , case when mod( rownum, 2 ) = 0 then rownum * 3 end demo , case when mod( rownum, 2 ) = 1 then ‘‘demo ‘‘ || rownum end demo2 from dual x connect by rownum <= 5‘); smt_xlsx_maker_pkg.save(‘XLS_DIR‘, ‘Export3.xlsx‘); end; */ g_debug_mode boolean := false; ---DBMS_OUTPUT直接输出/FILE_OUTPUT文档输出/REQUEST_OUTPUT请求日志输出/CONTEXT_OUTPUT 将日志改为上下文输出 g_debug_type varchar2(240) := ‘DBMS_OUTPUT‘; ---绑定变量用的临时表变量 type rec_col_value is record( col_id number(3), col_value varchar2(2400)); type tab_col_value is table of rec_col_value index by binary_integer; -- type tp_alignment is record( vertical varchar2(11), horizontal varchar2(16), wraptext boolean); -- procedure clear_workbook; -- procedure new_sheet(p_sheetname varchar2 := null); -- function orafmt2excel(p_format varchar2 := null) return varchar2; -- function get_numfmt(p_format varchar2 := null) return pls_integer; -- function get_font(p_name varchar2, p_family pls_integer := 2, p_fontsize number := 11, p_theme pls_integer := 1, p_underline boolean := false, p_italic boolean := false, p_bold boolean := false, p_rgb varchar2 := null -- this is a hex ALPHA Red Green Blue value ) return pls_integer; -- function get_fill(p_patterntype varchar2, p_fgrgb varchar2 := null -- this is a hex ALPHA Red Green Blue value ) return pls_integer; -- function get_border(p_top varchar2 := ‘thin‘, p_bottom varchar2 := ‘thin‘, p_left varchar2 := ‘thin‘, p_right varchar2 := ‘thin‘) /* none thin medium dashed dotted thick double hair mediumDashed dashDot mediumDashDot dashDotDot mediumDashDotDot slantDashDot */ return pls_integer; -- function get_alignment(p_vertical varchar2 := null, p_horizontal varchar2 := null, p_wraptext boolean := null) /* horizontal center centerContinuous distributed fill general justify left right */ /* vertical bottom center distributed justify top */ return tp_alignment; -- procedure cell(p_col pls_integer, p_row pls_integer, p_value number, p_numfmtid pls_integer := null, p_fontid pls_integer := null, p_fillid pls_integer := null, p_borderid pls_integer := null, p_alignment tp_alignment := null, p_sheet pls_integer := null); -- procedure cell(p_col pls_integer, p_row pls_integer, p_value varchar2, p_numfmtid pls_integer := null, p_fontid pls_integer := null, p_fillid pls_integer := null, p_borderid pls_integer := null, p_alignment tp_alignment := null, p_sheet pls_integer := null); -- procedure cell(p_col pls_integer, p_row pls_integer, p_value date, p_numfmtid pls_integer := null, p_fontid pls_integer := null, p_fillid pls_integer := null, p_borderid pls_integer := null, p_alignment tp_alignment := null, p_sheet pls_integer := null); -- procedure hyperlink(p_col pls_integer, p_row pls_integer, p_url varchar2, p_value varchar2 := null, p_sheet pls_integer := null); -- procedure comment(p_col pls_integer, p_row pls_integer, p_text varchar2, p_author varchar2 := null, p_width pls_integer := 150 -- pixels , p_height pls_integer := 100 -- pixels , p_sheet pls_integer := null); -- procedure mergecells(p_tl_col pls_integer -- top left , p_tl_row pls_integer, p_br_col pls_integer -- bottom right , p_br_row pls_integer, p_sheet pls_integer := null); -- procedure list_validation(p_sqref_col pls_integer, p_sqref_row pls_integer, p_tl_col pls_integer -- top left , p_tl_row pls_integer, p_br_col pls_integer -- bottom right , p_br_row pls_integer, p_style varchar2 := ‘stop‘ -- stop, warning, information , p_title varchar2 := null, p_prompt varchar := null, p_show_error boolean := false, p_error_title varchar2 := null, p_error_txt varchar2 := null, p_sheet pls_integer := null); -- procedure list_validation(p_sqref_col pls_integer, p_sqref_row pls_integer, p_defined_name varchar2, p_style varchar2 := ‘stop‘ -- stop, warning, information , p_title varchar2 := null, p_prompt varchar := null, p_show_error boolean := false, p_error_title varchar2 := null, p_error_txt varchar2 := null, p_sheet pls_integer := null); -- procedure defined_name(p_tl_col pls_integer -- top left , p_tl_row pls_integer, p_br_col pls_integer -- bottom right , p_br_row pls_integer, p_name varchar2, p_sheet pls_integer := null, p_localsheet pls_integer := null); -- procedure set_column_width(p_col pls_integer, p_width number, p_sheet pls_integer := null); -- procedure set_column(p_col pls_integer, p_numfmtid pls_integer := null, p_fontid pls_integer := null, p_fillid pls_integer := null, p_borderid pls_integer := null, p_alignment tp_alignment := null, p_sheet pls_integer := null); -- procedure set_row(p_row pls_integer, p_numfmtid pls_integer := null, p_fontid pls_integer := null, p_fillid pls_integer := null, p_borderid pls_integer := null, p_alignment tp_alignment := null, p_sheet pls_integer := null); -- procedure freeze_rows(p_nr_rows pls_integer := 1, p_sheet pls_integer := null); -- procedure freeze_cols(p_nr_cols pls_integer := 1, p_sheet pls_integer := null); -- procedure freeze_pane(p_col pls_integer, p_row pls_integer, p_sheet pls_integer := null); -- procedure set_autofilter(p_column_start pls_integer := null, p_column_end pls_integer := null, p_row_start pls_integer := null, p_row_end pls_integer := null, p_sheet pls_integer := null); -- function finish return blob; -- procedure save(p_directory varchar2, p_filename varchar2); -- ---当p_footer设定参数为真，这个g_query2sheet_footer有值，则会显示这个值的内容。没有则默认是Generated xxxx ---有一点说明的是，&ROWS 字段会被自动替换为结果返回的行数。 g_query2sheet_footer varchar2(1000); g_query2sheet_rows number; --结果返回的是多少行记录 --这个是这个程序的"完全体" procedure query2sheet(p_sql varchar2, p_col_value_tab smt_xlsx_maker_pkg.tab_col_value ---运行的动态SQL的绑定变量 , p_column_headers boolean := true, p_directory varchar2 := null, p_filename varchar2 := null, p_sheet pls_integer := null, p_footer boolean := true, x_retcode out number ---0:成功 非0:失败( 或者：0:成功 1:警告 2:错误 ----注意：确定警告的时候要做什么动作) , x_errbuf out varchar2 ---具体的错误信息 ); ---默认用上绑定变量的逻辑！ procedure query2sheet(p_sql varchar2, p_column_headers boolean := true, p_directory varchar2 := null, p_filename varchar2 := null, p_sheet pls_integer := null, p_footer boolean := true, x_retcode out number ---0:成功 非0:失败( 或者：0:成功 1:警告 2:错误 ----注意：确定警告的时候要做什么动作) , x_errbuf out varchar2 ---具体的错误信息 ); ---最简单的使用版本 procedure query2sheet(p_sql varchar2, p_column_headers boolean := true, p_directory varchar2 := null, p_filename varchar2 := null, p_sheet pls_integer := null, p_footer boolean := true); ---参考老外的，另外封装的一个好用的过程！游标直接输出excel。未测试过~ procedure cursor2sheet(p_sql in sys_refcursor, p_column_headers boolean := true, p_directory varchar2 := null, p_filename varchar2 := null, p_sheet pls_integer := null, p_footer boolean := true); --end;

create or replace package body smt_xlsx_maker_pkg is -- c_local_file_header constant raw(4) := hextoraw(‘504B0304‘); -- Local file header signature c_end_of_central_directory constant raw(4) := hextoraw(‘504B0506‘); -- End of central directory signature -- type tp_xf_fmt is record( numfmtid pls_integer, fontid pls_integer, fillid pls_integer, borderid pls_integer, alignment tp_alignment); type tp_col_fmts is table of tp_xf_fmt index by pls_integer; type tp_row_fmts is table of tp_xf_fmt index by pls_integer; type tp_widths is table of number index by pls_integer; type tp_cell is record( value number, style varchar2(50)); type tp_cells is table of tp_cell index by pls_integer; type tp_rows is table of tp_cells index by pls_integer; type tp_autofilter is record( column_start pls_integer, column_end pls_integer, row_start pls_integer, row_end pls_integer); type tp_autofilters is table of tp_autofilter index by pls_integer; type tp_hyperlink is record( cell varchar2(10), url varchar2(1000)); type tp_hyperlinks is table of tp_hyperlink index by pls_integer; subtype tp_author is varchar2(32767 char); type tp_authors is table of pls_integer index by tp_author; authors tp_authors; type tp_comment is record( text varchar2(32767 char), author tp_author, row pls_integer, column pls_integer, width pls_integer, height pls_integer); type tp_comments is table of tp_comment index by pls_integer; type tp_mergecells is table of varchar2(21) index by pls_integer; type tp_validation is record( type varchar2(10), errorstyle varchar2(32), showinputmessage boolean, prompt varchar2(32767 char), title varchar2(32767 char), error_title varchar2(32767 char), error_txt varchar2(32767 char), showerrormessage boolean, formula1 varchar2(32767 char), formula2 varchar2(32767 char), allowblank boolean, sqref varchar2(32767 char)); type tp_validations is table of tp_validation index by pls_integer; type tp_sheet is record( rows tp_rows, widths tp_widths, name varchar2(100), freeze_rows pls_integer, freeze_cols pls_integer, autofilters tp_autofilters, hyperlinks tp_hyperlinks, col_fmts tp_col_fmts, row_fmts tp_row_fmts, comments tp_comments, mergecells tp_mergecells, validations tp_validations); type tp_sheets is table of tp_sheet index by pls_integer; type tp_numfmt is record( numfmtid pls_integer, formatcode varchar2(100)); type tp_numfmts is table of tp_numfmt index by pls_integer; type tp_fill is record( patterntype varchar2(30), fgrgb varchar2(8)); type tp_fills is table of tp_fill index by pls_integer; type tp_cellxfs is table of tp_xf_fmt index by pls_integer; type tp_font is record( name varchar2(100), family pls_integer, fontsize number, theme pls_integer, rgb varchar2(8), underline boolean, italic boolean, bold boolean); type tp_fonts is table of tp_font index by pls_integer; type tp_border is record( top varchar2(17), bottom varchar2(17), left varchar2(17), right varchar2(17)); type tp_borders is table of tp_border index by pls_integer; type tp_numfmtindexes is table of pls_integer index by pls_integer; type tp_strings is table of pls_integer index by varchar2(32767 char); type tp_str_ind is table of varchar2(32767 char) index by pls_integer; type tp_defined_name is record( name varchar2(32767 char), ref varchar2(32767 char), sheet pls_integer); type tp_defined_names is table of tp_defined_name index by pls_integer; type tp_book is record( sheets tp_sheets, strings tp_strings, str_ind tp_str_ind, str_cnt pls_integer := 0, fonts tp_fonts, fills tp_fills, borders tp_borders, numfmts tp_numfmts, cellxfs tp_cellxfs, numfmtindexes tp_numfmtindexes, defined_names tp_defined_names); workbook tp_book; lc_rows tp_rows; --new 2015.5.27 -- procedure debuglog(p_msg in varchar2) is begin if g_debug_type = ‘DBMS_OUTPUT‘ then dbms_output.put_line(p_msg); elsif g_debug_type = ‘FILE_OUTPUT‘ then --XYG_FND_FILE.PUT_LINE(FND_FILE.OUTPUT, P_MSG); null; elsif g_debug_type = ‘REQUEST_OUTPUT‘ then --LOG(P_MSG); --FND_FILE.PUT_LINE (FND_FILE.LOG, P_MSG); null; end if; end debuglog; procedure blob2file(p_blob blob, p_directory varchar2 := ‘MY_DIR‘, p_filename varchar2 := ‘my.xlsx‘) is t_fh utl_file.file_type; t_len pls_integer := 32767; begin t_fh := utl_file.fopen(p_directory, p_filename, ‘wb‘); for i in 0 .. trunc((dbms_lob.getlength(p_blob) - 1) / t_len) loop utl_file.put_raw(t_fh, dbms_lob.substr(p_blob, t_len, i * t_len + 1)); end loop; utl_file.fclose(t_fh); end; -- function raw2num(p_raw raw, p_len integer, p_pos integer) return number is begin return utl_raw.cast_to_binary_integer(utl_raw.substr(p_raw, p_pos, p_len), utl_raw.little_endian); end; -- function little_endian(p_big number, p_bytes pls_integer := 4) return raw is begin return utl_raw.substr(utl_raw.cast_from_binary_integer(p_big, utl_raw.little_endian), 1, p_bytes); end; -- function blob2num(p_blob blob, p_len integer, p_pos integer) return number is begin return utl_raw.cast_to_binary_integer(dbms_lob.substr(p_blob, p_len, p_pos), utl_raw.little_endian); end; -- procedure add1file(p_zipped_blob in out blob, p_name varchar2, p_content blob) is t_now date; t_blob blob; t_len integer; t_clen integer; t_crc32 raw(4) := hextoraw(‘00000000‘); t_compressed boolean := false; t_name raw(32767); begin t_now := sysdate; t_len := nvl(dbms_lob.getlength(p_content), 0); if t_len > 0 then t_blob := utl_compress.lz_compress(p_content); t_clen := dbms_lob.getlength(t_blob) - 18; t_compressed : var cpro_id = "u6292429"; 
oracle中如何把查询结果导出到excel里面
 main.sql 注意，需要在sqlplus下运行 非plsql命令行下set linesize 200set term off verify off feedback off pagesize 999 set markup html on entmap ON spool on preformat offspool test_tables.xls@get_tables.sqlspool offexit

sql脚本，get_tables.sql

--get_tables.sqlselect * from all_objects where rownum<=1000;

然后在sqlplus下运行main.sql

使用包体编程：

create or replace package smt_xlsx_maker_pkg is /****************************************************************************** NAME: SMT_XLSX_MAKER_PKG PURPOSE: XLSX 生成 Pkg，主要是从Oracle数据库端生成Xlsx二进制的文件。 REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 2011/2/19 Anton Scheffer 1,New Create the pkg 1.1 2015/6/10 Sam.T 1.优化核心处理生成xlsx的代码，使得生成文档的执行效率大大提高！ 1.1 2015/6/10 Sam.T 1.query2sheet增加绑定变量的可选输入参数。 1.2 2015/6/15 Sam.T 1.代码增加调试模式。直接设置G_DEBUG_MODE变量即可。 1.2 2015/6/20 Sam.T 1.为方便使用，再次封装一些简单易用的过程，生成xlsx文档 1.2 2015/7/5 Sam.T 1.单条SQL生成xlsx文档的内容，增加是否显示foot，以及显示的行数。 jinzhao 解决数据量较大情况下导出excel报错xml的问题。将dbms_lob.writeappend修改为dbms_lob.append ******************************************************************************/ --------- /********************************************** ****************************************************************************** Copyright (C) 2011, 2012 by Anton Scheffer Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. ****************************************************************************** ******************************************** */ /* 使用方法： declare l_sql varchar2(30000); begin l_sql := ‘select * from all_objects‘; smt_xlsx_maker_pkg.query2sheet(l_sql, true, ‘XLS_DIR‘, ‘Export2.xlsx‘); end; 除此之外，基本上Excel 中有的效果它都可以生成。 以下一个例子，包括居中，合并单元格，底色，新增工作表等： begin smt_xlsx_maker_pkg.clear_workbook; smt_xlsx_maker_pkg.new_sheet; smt_xlsx_maker_pkg.cell(5, 1, 5); smt_xlsx_maker_pkg.cell(3, 1, 3); smt_xlsx_maker_pkg.cell(2, 2, 45); smt_xlsx_maker_pkg.cell(3, 2, ‘Anton Scheffer‘, p_alignment => smt_xlsx_maker_pkg.get_alignment(p_wraptext => true)); smt_xlsx_maker_pkg.cell(1, 4, sysdate, p_fontid => smt_xlsx_maker_pkg.get_font(‘Calibri‘, p_rgb => ‘FFFF0000‘)); smt_xlsx_maker_pkg.cell(2, 4, sysdate, p_numfmtid => smt_xlsx_maker_pkg.get_numfmt(‘dd/mm/yyyy h:mm‘)); smt_xlsx_maker_pkg.cell(3, 4, sysdate, p_numfmtid => smt_xlsx_maker_pkg.get_numfmt(smt_xlsx_maker_pkg.orafmt2excel(‘dd/mon/yyyy‘))); smt_xlsx_maker_pkg.cell(5, 5, 75, p_borderid => smt_xlsx_maker_pkg.get_border(‘double‘, ‘double‘, ‘double‘, ‘double‘)); smt_xlsx_maker_pkg.cell(2, 3, 33); smt_xlsx_maker_pkg.hyperlink(1, 6, ‘http://www.cnblogs.com/mellowsmile‘, ‘jinzhao site‘); smt_xlsx_maker_pkg.cell(1, 7, ‘Some merged cells‘, p_alignment => smt_xlsx_maker_pkg.get_alignment(p_horizontal => ‘center‘)); smt_xlsx_maker_pkg.mergecells(1, 7, 3, 7); for i in 1 .. 5 loop smt_xlsx_maker_pkg.comment(3, i + 3, ‘Row ‘ || (i + 3), ‘Anton‘); end loop; smt_xlsx_maker_pkg.new_sheet; smt_xlsx_maker_pkg.set_row(1, p_fillid => smt_xlsx_maker_pkg.get_fill(‘solid‘, ‘FFFF0000‘)); for i in 1 .. 5 loop smt_xlsx_maker_pkg.cell(1, i, i); smt_xlsx_maker_pkg.cell(2, i, i * 3); smt_xlsx_maker_pkg.cell(3, i, ‘x ‘ || i * 3); end loop; smt_xlsx_maker_pkg.query2sheet(‘select rownum, x.* , case when mod( rownum, 2 ) = 0 then rownum * 3 end demo , case when mod( rownum, 2 ) = 1 then ‘‘demo ‘‘ || rownum end demo2 from dual x connect by rownum <= 5‘); smt_xlsx_maker_pkg.save(‘XLS_DIR‘, ‘Export3.xlsx‘); end; */ g_debug_mode boolean := false; ---DBMS_OUTPUT直接输出/FILE_OUTPUT文档输出/REQUEST_OUTPUT请求日志输出/CONTEXT_OUTPUT 将日志改为上下文输出 g_debug_type varchar2(240) := ‘DBMS_OUTPUT‘; ---绑定变量用的临时表变量 type rec_col_value is record( col_id number(3), col_value varchar2(2400)); type tab_col_value is table of rec_col_value index by binary_integer; -- type tp_alignment is record( vertical varchar2(11), horizontal varchar2(16), wraptext boolean); -- procedure clear_workbook; -- procedure new_sheet(p_sheetname varchar2 := null); -- function orafmt2excel(p_format varchar2 := null) return varchar2; -- function get_numfmt(p_format varchar2 := null) return pls_integer; -- function get_font(p_name varchar2, p_family pls_integer := 2, p_fontsize number := 11, p_theme pls_integer := 1, p_underline boolean := false, p_italic boolean := false, p_bold boolean := false, p_rgb varchar2 := null -- this is a hex ALPHA Red Green Blue value ) return pls_integer; -- function get_fill(p_patterntype varchar2, p_fgrgb varchar2 := null -- this is a hex ALPHA Red Green Blue value ) return pls_integer; -- function get_border(p_top varchar2 := ‘thin‘, p_bottom varchar2 := ‘thin‘, p_left varchar2 := ‘thin‘, p_right varchar2 := ‘thin‘) /* none thin medium dashed dotted thick double hair mediumDashed dashDot mediumDashDot dashDotDot mediumDashDotDot slantDashDot */ return pls_integer; -- function get_alignment(p_vertical varchar2 := null, p_horizontal varchar2 := null, p_wraptext boolean := null) /* horizontal center centerContinuous distributed fill general justify left right */ /* vertical bottom center distributed justify top */ return tp_alignment; -- procedure cell(p_col pls_integer, p_row pls_integer, p_value number, p_numfmtid pls_integer := null, p_fontid pls_integer := null, p_fillid pls_integer := null, p_borderid pls_integer := null, p_alignment tp_alignment := null, p_sheet pls_integer := null); -- procedure cell(p_col pls_integer, p_row pls_integer, p_value varchar2, p_numfmtid pls_integer := null, p_fontid pls_integer := null, p_fillid pls_integer := null, p_borderid pls_integer := null, p_alignment tp_alignment := null, p_sheet pls_integer := null); -- procedure cell(p_col pls_integer, p_row pls_integer, p_value date, p_numfmtid pls_integer := null, p_fontid pls_integer := null, p_fillid pls_integer := null, p_borderid pls_integer := null, p_alignment tp_alignment := null, p_sheet pls_integer := null); -- procedure hyperlink(p_col pls_integer, p_row pls_integer, p_url varchar2, p_value varchar2 := null, p_sheet pls_integer := null); -- procedure comment(p_col pls_integer, p_row pls_integer, p_text varchar2, p_author varchar2 := null, p_width pls_integer := 150 -- pixels , p_height pls_integer := 100 -- pixels , p_sheet pls_integer := null); -- procedure mergecells(p_tl_col pls_integer -- top left , p_tl_row pls_integer, p_br_col pls_integer -- bottom right , p_br_row pls_integer, p_sheet pls_integer := null); -- procedure list_validation(p_sqref_col pls_integer, p_sqref_row pls_integer, p_tl_col pls_integer -- top left , p_tl_row pls_integer, p_br_col pls_integer -- bottom right , p_br_row pls_integer, p_style varchar2 := ‘stop‘ -- stop, warning, information , p_title varchar2 := null, p_prompt varchar := null, p_show_error boolean := false, p_error_title varchar2 := null, p_error_txt varchar2 := null, p_sheet pls_integer := null); -- procedure list_validation(p_sqref_col pls_integer, p_sqref_row pls_integer, p_defined_name varchar2, p_style varchar2 := ‘stop‘ -- stop, warning, information , p_title varchar2 := null, p_prompt varchar := null, p_show_error boolean := false, p_error_title varchar2 := null, p_error_txt varchar2 := null, p_sheet pls_integer := null); -- procedure defined_name(p_tl_col pls_integer -- top left , p_tl_row pls_integer, p_br_col pls_integer -- bottom right , p_br_row pls_integer, p_name varchar2, p_sheet pls_integer := null, p_localsheet pls_integer := null); -- procedure set_column_width(p_col pls_integer, p_width number, p_sheet pls_integer := null); -- procedure set_column(p_col pls_integer, p_numfmtid pls_integer := null, p_fontid pls_integer := null, p_fillid pls_integer := null, p_borderid pls_integer := null, p_alignment tp_alignment := null, p_sheet pls_integer := null); -- procedure set_row(p_row pls_integer, p_numfmtid pls_integer := null, p_fontid pls_integer := null, p_fillid pls_integer := null, p_borderid pls_integer := null, p_alignment tp_alignment := null, p_sheet pls_integer := null); -- procedure freeze_rows(p_nr_rows pls_integer := 1, p_sheet pls_integer := null); -- procedure freeze_cols(p_nr_cols pls_integer := 1, p_sheet pls_integer := null); -- procedure freeze_pane(p_col pls_integer, p_row pls_integer, p_sheet pls_integer := null); -- procedure set_autofilter(p_column_start pls_integer := null, p_column_end pls_integer := null, p_row_start pls_integer := null, p_row_end pls_integer := null, p_sheet pls_integer := null); -- function finish return blob; -- procedure save(p_directory varchar2, p_filename varchar2); -- ---当p_footer设定参数为真，这个g_query2sheet_footer有值，则会显示这个值的内容。没有则默认是Generated xxxx ---有一点说明的是，&ROWS 字段会被自动替换为结果返回的行数。 g_query2sheet_footer varchar2(1000); g_query2sheet_rows number; --结果返回的是多少行记录 --这个是这个程序的"完全体" procedure query2sheet(p_sql varchar2, p_col_value_tab smt_xlsx_maker_pkg.tab_col_value ---运行的动态SQL的绑定变量 , p_column_headers boolean := true, p_directory varchar2 := null, p_filename varchar2 := null, p_sheet pls_integer := null, p_footer boolean := true, x_retcode out number ---0:成功 非0:失败( 或者：0:成功 1:警告 2:错误 ----注意：确定警告的时候要做什么动作) , x_errbuf out varchar2 ---具体的错误信息 ); ---默认用上绑定变量的逻辑！ procedure query2sheet(p_sql varchar2, p_column_headers boolean := true, p_directory varchar2 := null, p_filename varchar2 := null, p_sheet pls_integer := null, p_footer boolean := true, x_retcode out number ---0:成功 非0:失败( 或者：0:成功 1:警告 2:错误 ----注意：确定警告的时候要做什么动作) , x_errbuf out varchar2 ---具体的错误信息 ); ---最简单的使用版本 procedure query2sheet(p_sql varchar2, p_column_headers boolean := true, p_directory varchar2 := null, p_filename varchar2 := null, p_sheet pls_integer := null, p_footer boolean := true); ---参考老外的，另外封装的一个好用的过程！游标直接输出excel。未测试过~ procedure cursor2sheet(p_sql in sys_refcursor, p_column_headers boolean := true, p_directory varchar2 := null, p_filename varchar2 := null, p_sheet pls_integer := null, p_footer boolean := true); --end;

create or replace package body smt_xlsx_maker_pkg is -- c_local_file_header constant raw(4) := hextoraw(‘504B0304‘); -- Local file header signature c_end_of_central_directory constant raw(4) := hextoraw(‘504B0506‘); -- End of central directory signature -- type tp_xf_fmt is record( numfmtid pls_integer, fontid pls_integer, fillid pls_integer, borderid pls_integer, alignment tp_alignment); type tp_col_fmts is table of tp_xf_fmt index by pls_integer; type tp_row_fmts is table of tp_xf_fmt index by pls_integer; type tp_widths is table of number index by pls_integer; type tp_cell is record( value number, style varchar2(50)); type tp_cells is table of tp_cell index by pls_integer; type tp_rows is table of tp_cells index by pls_integer; type tp_autofilter is record( column_start pls_integer, column_end pls_integer, row_start pls_integer, row_end pls_integer); type tp_autofilters is table of tp_autofilter index by pls_integer; type tp_hyperlink is record( cell varchar2(10), url varchar2(1000)); type tp_hyperlinks is table of tp_hyperlink index by pls_integer; subtype tp_author is varchar2(32767 char); type tp_authors is table of pls_integer index by tp_author; authors tp_authors; type tp_comment is record( text varchar2(32767 char), author tp_author, row pls_integer, column pls_integer, width pls_integer, height pls_integer); type tp_comments is table of tp_comment index by pls_integer; type tp_mergecells is table of varchar2(21) index by pls_integer; type tp_validation is record( type varchar2(10), errorstyle varchar2(32), showinputmessage boolean, prompt varchar2(32767 char), title varchar2(32767 char), error_title varchar2(32767 char), error_txt varchar2(32767 char), showerrormessage boolean, formula1 varchar2(32767 char), formula2 varchar2(32767 char), allowblank boolean, sqref varchar2(32767 char)); type tp_validations is table of tp_validation index by pls_integer; type tp_sheet is record( rows tp_rows, widths tp_widths, name varchar2(100), freeze_rows pls_integer, freeze_cols pls_integer, autofilters tp_autofilters, hyperlinks tp_hyperlinks, col_fmts tp_col_fmts, row_fmts tp_row_fmts, comments tp_comments, mergecells tp_mergecells, validations tp_validations); type tp_sheets is table of tp_sheet index by pls_integer; type tp_numfmt is record( numfmtid pls_integer, formatcode varchar2(100)); type tp_numfmts is table of tp_numfmt index by pls_integer; type tp_fill is record( patterntype varchar2(30), fgrgb varchar2(8)); type tp_fills is table of tp_fill index by pls_integer; type tp_cellxfs is table of tp_xf_fmt index by pls_integer; type tp_font is record( name varchar2(100), family pls_integer, fontsize number, theme pls_integer, rgb varchar2(8), underline boolean, italic boolean, bold boolean); type tp_fonts is table of tp_font index by pls_integer; type tp_border is record( top varchar2(17), bottom varchar2(17), left varchar2(17), right varchar2(17)); type tp_borders is table of tp_border index by pls_integer; type tp_numfmtindexes is table of pls_integer index by pls_integer; type tp_strings is table of pls_integer index by varchar2(32767 char); type tp_str_ind is table of varchar2(32767 char) index by pls_integer; type tp_defined_name is record( name varchar2(32767 char), ref varchar2(32767 char), sheet pls_integer); type tp_defined_names is table of tp_defined_name index by pls_integer; type tp_book is record( sheets tp_sheets, strings tp_strings, str_ind tp_str_ind, str_cnt pls_integer := 0, fonts tp_fonts, fills tp_fills, borders tp_borders, numfmts tp_numfmts, cellxfs tp_cellxfs, numfmtindexes tp_numfmtindexes, defined_names tp_defined_names); workbook tp_book; lc_rows tp_rows; --new 2015.5.27 -- procedure debuglog(p_msg in varchar2) is begin if g_debug_type = ‘DBMS_OUTPUT‘ then dbms_output.put_line(p_msg); elsif g_debug_type = ‘FILE_OUTPUT‘ then --XYG_FND_FILE.PUT_LINE(FND_FILE.OUTPUT, P_MSG); null; elsif g_debug_type = ‘REQUEST_OUTPUT‘ then --LOG(P_MSG); --FND_FILE.PUT_LINE (FND_FILE.LOG, P_MSG); null; end if; end debuglog; procedure blob2file(p_blob blob, p_directory varchar2 := ‘MY_DIR‘, p_filename varchar2 := ‘my.xlsx‘) is t_fh utl_file.file_type; t_len pls_integer := 32767; begin t_fh := utl_file.fopen(p_directory, p_filename, ‘wb‘); for i in 0 .. trunc((dbms_lob.getlength(p_blob) - 1) / t_len) loop utl_file.put_raw(t_fh, dbms_lob.substr(p_blob, t_len, i * t_len + 1)); end loop; utl_file.fclose(t_fh); end; -- function raw2num(p_raw raw, p_len integer, p_pos integer) return number is begin return utl_raw.cast_to_binary_integer(utl_raw.substr(p_raw, p_pos, p_len), utl_raw.little_endian); end; -- function little_endian(p_big number, p_bytes pls_integer := 4) return raw is begin return utl_raw.substr(utl_raw.cast_from_binary_integer(p_big, utl_raw.little_endian), 1, p_bytes); end; -- function blob2num(p_blob blob, p_len integer, p_pos integer) return number is begin return utl_raw.cast_to_binary_integer(dbms_lob.substr(p_blob, p_len, p_pos), utl_raw.little_endian); end; -- procedure add1file(p_zipped_blob in out blob, p_name varchar2, p_content blob) is t_now date; t_blob blob; t_len integer; t_clen integer; t_crc32 raw(4) := hextoraw(‘00000000‘); t_compressed boolean := false; t_name raw(32767); begin t_now := sysdate; t_len := nvl(dbms_lob.getlength(p_content), 0); if t_len > 0 then t_blob := utl_compress.lz_compress(p_content); t_clen := dbms_lob.getlength(t_blob) - 18; t_compressed : var cpro_id = "u6292429"; 
oracle数据库，如何实现查询结果导出到excel时，将sql语句内容一并导出，便于日后查看统计口径
第一个问题：
1.创建SQL文件a.sql，如：
select * from al;
2.创建立处理文件b.txt，内容如下：
set linesize 1000 
set term off verify off feedback off pagesize 999 
set markup html on entmap ON spool on preformat off
spool tables.xls
@a.sql
spool off
exit
3.在命令行执行：
sqlplus user/password@sid @b.txt
得到文件：tables.xls（实际上实html文件），可以用excel打开。（也可以将后缀改为html，用IE打开）
注意：最终数据行超过65535后，excel2003打开报错。
第二个问题：
建议用多个select语句并存成多个sql文件，然后再上述脚本中，重复执行spool之间的语句，将结果存到多个文件中。
怎么在oracle下写一个脚本，然后导出表数据为EXCEL格式文件？
无法直接保存到excel中，但是可以保存到csv文件，同样是excel的格式，方法如下：
譬如要把文件生成在d盘下的test目录下：
1，在d盘根目录下新建test目录 
2，sqlplus以system用sysdba登录
3，create or replace directory TMP as ’d:\test’;
4，grant read on directory TMP to user; 
5，alter system set utl_file_dir='d:\test' scope=spfile;
然后建立存储过程，表是随便建了一个，最后的文件名按要求需要按照当前日期的前一天生成，所以后边文件名的地方write_file_name处就按照要求来了
create or replace PROCEDURE SP_OUTPUT
(
on_flag OUT NUMBER,
out_reason OUT VARCHAR2)
is
v_code number;
v_text varchar2(255);
file_handle utl_file.file_type;
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
v_aaa varchar2(5);
v_bbb varchar2(5);
cursor cur_sp_out
is
select aaa,bbb
from aaa;
begin
open cur_sp_out;
loop
 fetch cur_sp_out into v_aaa,v_bbb;
 exit when cur_sp_out%notfound;
 write_file_name := to_char(SYSDATE,'YYYYMMDD')||'.xls';
 file_handle := utl_file.fopen('TMP',write_file_name,'a');
 write_content := v_aaa||' '||v_bbb;
 --write file
 IF utl_file.is_open(file_handle) THEN
 utl_file.put_line(file_handle,write_content);
 END IF;
 --close file
 utl_file.fclose(file_handle);
 end loop;
 close cur_sp_out;
 v_code := 0;
v_text := '完成';
exception
when others then
on_flag := SQLCODE;
 out_reason := SQLERRM;
 begin
 IF utl_file.is_open(file_handle) THEN
 utl_file.fclose(file_handle);
 end IF;
 end;
 rollback;
end;
这样基本就完成了，但是建立完毕后需要重启数据库，否则文件写不进指定的目录下
需要注意的是： write_content := v_aaa||' '||v_bbb; 这个位置
v_aaa|| 和v_bbb中间引号的地方是TAB符，否则输出来的XLS文件中，会把这些字符串合在一个单元格中。