ORACLE导出到EXCEL

示例是每1000条记录spool 到一个 excel 文件中

测试数据创建

代码:
    
    


SQL> CREATE TABLE EXP_EXCEL (ID NUMBER, COL VARCHAR2(10));



Table created



SQL> INSERT INTO EXP_EXCEL SELECT LEVEL, 'COL_'||TO_CHAR(LEVEL,'FM000000') FROM DUAL CONNECT BY LEVEL<=3000;



3000 rows inserted



SQL> COMMIT;



Commit complete



---------------------------

   
   

创建一个脚本, 比如 名称为spool_xls.sql
脚本内容如下:

代码:
    
    


set echo off

set linesize 1000 

set term off 

set verify off 

set feedback off 

set pagesize 50000

set heading off 

set trimspool on



spool exp_xls.sql

select 'SPOOL C:EXP_EXCEL_'||LEVEL||'.XLS'||CHR(10)||

       'SELECT * FROM (SELECT t.*, row_number() over(order by ID) as rn FROM exp_excel t) WHERE rn BETWEEN 1000 * ('||level||' -1) + 1 AND 1000 * '||level||';'||chr(10)||

       'SPOOL OFF'

  from dual

 connect by level <=(select ceil(count(*)/1000) from exp_excel);

spool off



set heading on

set markup html on entmap off spool on preformat off

@exp_xls.sql

set markup html off entmap off preformat on

set term on 

set verify on 

set feedback on 

set pagesize 14 

set linesize 80

set trimspool off

set echo off

prompt spool over .



----------------

   
   

脚本保存, 在SQL*Plus下执行创建的脚本, 就会在c盘的根目录下生成名为EXP_EXCEL*.XLS的文件, 用excel打开看看吧
(应该会有3个文件)