链接:http://blog.itpub.net/28602568/viewspace-2120711/
标题:Oracle 快速将数据导出到CSV(Excel)文件的方法介绍
作者:lōττéry©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
注释:
工作中有需要将线上数据导出到excel给客户分析/查看的情况,如下是方法介绍情况:
方法 | 1分钟导出的数据量 | 适用于 |
utl_file读写文件包 | 300万 | 大量导出时 |
plsql developer->export query result | 10万 | 小量导出时 |
excel连接数据库 | 1万 | |
spool 循环打印 | 5000 | |
--excel 最大行数1048576
方案一、利用utl_file导出.csv文件. --.csv逗号分隔值格式文件,可用excel工具打开,显示格式和excel一样..
- DECLARE
- VSFILE UTL_FILE.FILE_TYPE; --定义用于接收文件句柄的类型
- V_CNT NUMBER; --统计每个文件加载行数
- --字段列表
- P_CONTENT_DATE VARCHAR2(4000);
- P_LOCNO VARCHAR2(4000);
- P_CELL_NO VARCHAR2(4000);
- P_ITEM_NO VARCHAR2(4000);
- P_SIZE_NO VARCHAR2(4000);
- P_QTY VARCHAR2(4000);
- BEGIN
- --DBMS_OUTPUT.ENABLE(1000000); -->避免报错ORA-20000: ORU-10027: BUFFER OVERFLOW, LIMIT OF 10000 BYTES
-
- --文件命名规则..把表数据时间当做文件命名...
- FOR FILE_NAME IN (SELECT DISTINCT TO_NUMBER(TO_CHAR(CONTENT_DATE, 'YYYYMMDD')) DATE_
- FROM T_1 ORDER BY DATE_ /*日期的话需要TO_NUMBER转换后排序*/) LOOP
- --开始打开文件
- VSFILE := UTL_FILE.FOPEN('EXPDP', FILE_NAME.DATE_ || '.CSV', 'W');
- /*参数介绍:
- UTL_FILE.FOPEN(LOCATION IN VARCHAR2, FILENAME IN VARCHAR2, OPEN_MODE IN VARCHAR2) RETURN FILE_TYPE;
- LOCATION 是文件存放的DB目录名称,-------执行用户要有对DIR目录的读写权限
- FILENAME 是文件名,
- OPEN_MODE是打开模式('R'是读文本,'W'是写文本,'A'是附加文本,参数不分大小写,如果指定'A'但是文件不存在,它会用'W'先创建出来,'W'有覆盖的功能)*/
-
- --文件字段标头打印
- UTL_FILE.PUT_LINE(VSFILE,'CONTENT_DATE, LOCNO, CELL_NO, ITEM_NO, SIZE_NO, QTY');
-
- --每个文件加载行数[每次进入循环都赋值为0].排除标头部分
- V_CNT := 0;
- --将FOR循环查询的内容
- FOR SQL_ IN (SELECT CONTENT_DATE, LOCNO, CELL_NO, ITEM_NO, SIZE_NO, QTY
- FROM T_1 WHERE CONTENT_DATE >= TO_DATE(FILE_NAME.DATE_, 'YYYYMMDD')
- AND TRUNC(CONTENT_DATE) <TO_DATE(FILE_NAME.DATE_, 'YYYYMMDD') + 1 ) LOOP
- --字段列表
- P_CONTENT_DATE := SQL_.CONTENT_DATE;
- P_LOCNO := SQL_.LOCNO;
- P_CELL_NO := SQL_.CELL_NO;
- P_ITEM_NO := SQL_.ITEM_NO;
- P_SIZE_NO := SQL_.SIZE_NO;
- P_QTY := SQL_.QTY;
-
- /*UTL_FILE.PUT_LINE 若需要EXCEL格式,需要每字段用逗号隔开,,WINDOWS EXCEL工具打开默认就是EXCEL格式啦*/
- UTL_FILE.PUT_LINE(VSFILE,
- P_CONTENT_DATE || ',' || P_LOCNO || ',' ||
- P_CELL_NO || ',' || P_ITEM_NO || ',' || P_SIZE_NO || ',' ||
- P_QTY);
-
- --计数器,每一条数据都循环+1
- V_CNT := V_CNT + 1;
- END LOOP;
-
- --打印每个文件 LOAD ROWS
- DBMS_OUTPUT.PUT_LINE(FILE_NAME.DATE_ || '.CSV文件LOAD ROWS:' || V_CNT);
-
- --放在LOOP 后,否则报错 ORA-29282: 文件 ID 无效/ORA-06512: 在 "SYS.UTL_FILE", LINE 878
- --若不写如下 强制输出缓冲/关闭句柄,可能存在导出数据少于查询条目
- UTL_FILE.FFLUSH(VSFILE);
- UTL_FILE.FCLOSE(VSFILE);
-
- END LOOP;
- END;
方案二、plsql developer->export query result;
查询要导出的数据(只查出部分数据即可,无需全部查询出来) 再export query result ..导出csv文件(是全量).
方案三、excel连接数据库导出
(步骤:打开excel->数据->导入数据->第一步选择数据源->ODBC DSN->根据情况输入连接信息-->选表字段等)
方案四、spool 循环打印
- set linesize 200
- col 字段 format a10
- set term off verify off feedback off pagesize 5000
- set markup html on entmap off spool on preformat off
- spool &tarpath/{& table }.xls
- lottery
- /opt/
- t_2
- 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 可能会和如下代码(每一行的代码)有关...
-
- </td>
- </tr>
- <tr>
- <td>
- SYS
- </td>
- <td>
- UTL_RECOMP_SEQ
- </td>
- <td>
- ;
- </td>
- <td align="right">
- 75571
- </td>
- <td align="right">
- ;
- </td>
- <td>
- SEQUENCE
- </td>
- <td>
- 20-JAN-15
- </td>
- <td>
- 20-JAN-15
- </td>
- <td>
- 2015-01 -20: 14:17 :45
- </td>
- <td>
- VALID
- </td>
- <td>
- N
- </td>
- <td>
- N
- </td>
- <td>
- N
- </td>
- <td align="right">
- 1
- </td>
- <td>
-
- </td>
- </tr>
- </table>
- <p>
- SQL>
- 一条结束.....
扩展:
Oracle如何将.dbf/.xls/.csv文件导入oracle数据库参考博客:http://blog.itpub.net/28602568/viewspace-759777/
【源于本人笔记】 若有书写错误,表达错误,请指正...
此条目发表在 Oracle 分类目录。将固定连接加入收藏夹。