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

时间：2016-06-24 02:05:53 阅读：1469 评论：0 收藏：0 [点我收藏+]

链接：http://blog.itpub.net/28602568/viewspace-2120711/

注释：

工作中有需要将线上数据导出到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