作者：
ｌōττéｒｙ©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
注释：
工作中有需要将线上数据导出到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 >
-
一条结束.....
扩展：
【源于本人
笔记】 若有书写错误，表达错误，请指正...