文章首页 Web开发 Windows开发 编程语言 数据库技术 移动平台 系统服务 微信 设计 布布扣 其他

Oracle 快速将数据导出到CSV(Excel)文件的方法介绍

时间：2016-06-24 02:05:53   收藏：0   阅读：1472

作者：ｌōττéｒｙ©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

注释： 
    工作中有需要将线上数据导出到excel给客户分析/查看的情况，如下是方法介绍情况：
方法 1分钟导出的数据量 适用于
utl_file读写文件包 300万 大量导出时
plsql developer->export query result 10万 小量导出时
excel连接数据库 1万
spool 循环打印 5000
--excel 最大行数1048576 

方案一、利用utl_file导出.csv文件.  --.csv逗号分隔值格式文件，可用excel工具打开，显示格式和excel一样..

点击(此处)折叠或打开

  1. DECLARE
  2.   VSFILE UTL_FILE.FILE_TYPE; --定义用于接收文件句柄的类型
  3.   V_CNT NUMBER; --统计每个文件加载行数
  4.   --字段列表
  5.   P_CONTENT_DATE VARCHAR2(4000);
  6.   P_LOCNO VARCHAR2(4000);
  7.   P_CELL_NO VARCHAR2(4000);
  8.   P_ITEM_NO VARCHAR2(4000);
  9.   P_SIZE_NO VARCHAR2(4000);
  10.   P_QTY VARCHAR2(4000);
  11. BEGIN
  12.   --DBMS_OUTPUT.ENABLE(1000000); -->避免报错ORA-20000: ORU-10027: BUFFER OVERFLOW, LIMIT OF 10000 BYTES
  13.  
  14.   --文件命名规则..把表数据时间当做文件命名...
  15.   FOR FILE_NAME IN (SELECT DISTINCT TO_NUMBER(TO_CHAR(CONTENT_DATE, ‘YYYYMMDD‘)) DATE_
  16.                       FROM T_1 ORDER BY DATE_ /*日期的话需要TO_NUMBER转换后排序*/) LOOP
  17.    --开始打开文件
  18.     VSFILE := UTL_FILE.FOPEN(‘EXPDP‘, FILE_NAME.DATE_ || ‘.CSV‘, ‘W‘);
  19.     /*参数介绍：
  20.    UTL_FILE.FOPEN(LOCATION IN VARCHAR2, FILENAME IN VARCHAR2, OPEN_MODE IN VARCHAR2) RETURN FILE_TYPE;
  21.    LOCATION 是文件存放的DB目录名称，-------执行用户要有对DIR目录的读写权限
  22.    FILENAME 是文件名，
  23.    OPEN_MODE是打开模式（‘R‘是读文本，‘W‘是写文本，‘A‘是附加文本，参数不分大小写，如果指定‘A‘但是文件不存在，它会用‘W‘先创建出来，‘W‘有覆盖的功能）*/
  24.   
  25.     --文件字段标头打印
  26.     UTL_FILE.PUT_LINE(VSFILE,‘CONTENT_DATE, LOCNO, CELL_NO, ITEM_NO, SIZE_NO, QTY‘);
  27.   
  28.     --每个文件加载行数[每次进入循环都赋值为0].排除标头部分
  29.     V_CNT := 0;
  30.     --将FOR循环查询的内容
  31.     FOR SQL_ IN (SELECT CONTENT_DATE, LOCNO, CELL_NO, ITEM_NO, SIZE_NO, QTY
  32.                    FROM T_1 WHERE CONTENT_DATE >= TO_DATE(FILE_NAME.DATE_, ‘YYYYMMDD‘)
  33.                     AND TRUNC(CONTENT_DATE) <TO_DATE(FILE_NAME.DATE_, ‘YYYYMMDD‘) + 1 ) LOOP
  34.       --字段列表
  35.       P_CONTENT_DATE := SQL_.CONTENT_DATE;
  36.       P_LOCNO := SQL_.LOCNO;
  37.       P_CELL_NO := SQL_.CELL_NO;
  38.       P_ITEM_NO := SQL_.ITEM_NO;
  39.       P_SIZE_NO := SQL_.SIZE_NO;
  40.       P_QTY := SQL_.QTY;
  41.       
  42.       /*UTL_FILE.PUT_LINE 若需要EXCEL格式，需要每字段用逗号隔开,,WINDOWS EXCEL工具打开默认就是EXCEL格式啦*/
  43.       UTL_FILE.PUT_LINE(VSFILE,
  44.                         P_CONTENT_DATE || ‘,‘ || P_LOCNO || ‘,‘ ||
  45.                         P_CELL_NO || ‘,‘ || P_ITEM_NO || ‘,‘ || P_SIZE_NO || ‘,‘ ||
  46.                         P_QTY);
  47.                         
  48.       --计数器，每一条数据都循环+1
  49.       V_CNT := V_CNT + 1;
  50.     END LOOP;
  51.     
  52.     --打印每个文件 LOAD ROWS
  53.     DBMS_OUTPUT.PUT_LINE(FILE_NAME.DATE_ || ‘.CSV文件LOAD ROWS：‘ || V_CNT);
  54.     
  55.     --放在LOOP 后，否则报错 ORA-29282: 文件 ID 无效/ORA-06512: 在 "SYS.UTL_FILE", LINE 878
  56.     --若不写如下 强制输出缓冲/关闭句柄，可能存在导出数据少于查询条目
  57.     UTL_FILE.FFLUSH(VSFILE);
  58.     UTL_FILE.FCLOSE(VSFILE);
  59.     
  60.   END LOOP;
  61. END;



方案二、plsql developer->export query result；
    查询要导出的数据(只查出部分数据即可,无需全部查询出来) 再export query result ..导出csv文件(是全量).
方案三、excel连接数据库导出
 （步骤：打开excel->数据->导入数据->第一步选择数据源->ODBC DSN->根据情况输入连接信息-->选表字段等）
bubuko.com,布布扣
 bubuko.com,布布扣

方案四、spool 循环打印

点击(此处)折叠或打开

  1. set linesize 200
  2. col 字段 format a10
  3. set term off verify off feedback off pagesize 5000
  4. set markup html on entmap off spool on preformat off
  5. spool &tarpath/{& table }.xls
  6. lottery
  7. /opt/
  8. t_2
  9. SQL > select * from t_2

***6.5w数据导出excel是101M（1是会浪费空间，2是打开也慢）
[root@sinosoft lottery]#  du -sh \{t_2\}.xls
101M    {t_2}.xls
[root@sinosoft lottery ]#
         至于为啥6.5w就占101M 可能会和如下代码（每一行的代码）有关...
        

点击(此处)折叠或打开

  1.  
  2.           </td>
  3.           </tr>
  4.           <tr>
  5.           <td>
  6.           SYS
  7.           </td>
  8.           <td>
  9.           UTL_RECOMP_SEQ
  10.           </td>
  11.           <td>
  12.            ;
  13.           </td>
  14.           <td align="right">
  15.                75571
  16.           </td>
  17.           <td align="right">
  18.            ;
  19.           </td>
  20.           <td>
  21.           SEQUENCE
  22.           </td>
  23.           <td>
  24.           20-JAN-15
  25.           </td>
  26.           <td>
  27.           20-JAN-15
  28.           </td>
  29.           <td>
  30.           2015-01 -20: 14:17 :45
  31.           </td>
  32.           <td>
  33.           VALID
  34.           </td>
  35.           <td>
  36.           N
  37.           </td>
  38.           <td>
  39.           N
  40.           </td>
  41.           <td>
  42.           N
  43.           </td>
  44.           <td align="right">
  45.           1
  46.           </td>
  47.           <td>
  48.            
  49.           </td>
  50.           </tr>
  51.           </table>
  52.           <p>
  53.           SQL>
  54. 一条结束.....
          
扩展：

  【源于本人笔记】 若有书写错误，表达错误，请指正...

此条目发表在 Oracle 分类目录。将固定连接加入收藏夹。

原文：http://blog.itpub.net/28602568/viewspace-2120711/

评论（0）
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣，分享程序人生！