Oracle通过SPOOL导出数据Excel、CSV、TXT格式

山与先生

已于 2024-06-20 14:53:13 修改

阅读量1w

点赞数 5

分类专栏： ORACLE 文章标签： oracle spool excel

于 2020-07-14 16:42:59 首次发布

本文链接：https://blog.csdn.net/u010257584/article/details/107318909

版权

ORACLE 专栏收录该内容

35 篇文章

订阅专栏

文章目录

<从来不需要解释那些所谓奇怪的事你是上天的恩赐因为珍惜学会坚持……>

1. SPOOL基础

SPOOL用的频率蛮高的一个命令工具，主要是配合SQLPLUS的SET命令使用，常用的如下：

SET ECHO {ON | OFF}：控制在执行命令时，START命令是否在脚本中列出每个命令；
SET TIMI [NG] {ON | OFF}：控制返回执行时间；
SET TI [ME] {ON | OFF}：控制当前时间的显示；
SET HEA [DING] {ON | OFF}：控制列名显示；
SET FEED[BACK] {6 | n | ON | OFF | ONLY}] [SQL_ID]：当查询结果数据行数大于等于N时，返回本次SQL结果集数据行数；
SET LIN[ESIZE] {80 | n | WINDOW}：控制每行字符的个数（1个汉字两个字符）；
SET PAGES[IZE] {14 | n}：每页显示的数据行数；
SET NEWP[AGE] {1 | n | NONE}：设置从每页顶部到顶部标题要打印的空白行数；
SET TRIMS [POOL] {ON | OFF}：去掉spool输出时每行的拖尾空格；
SET TRIM [OUT] {ON | OFF}：去除标准输出每行的拖尾空格；
SET TERM[OUT] {ON | OFF}：控制由脚本执行的命令生成的输出的显示；
SET COLSEP { | text}：列分隔符设置；
SET MARK[UP]：输出CSV格式数据或HTML标记文本;
SET PRE[FORMAT] {ON|OFF}：默认值是 OFF，即默认输出数据到HTML 表格中，要使用 <PRE>标签生成输出报表必须设置 PREFORMAT ON。

部分参数的默认值如下：

[oracle@chengyu ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jul 10 11:36:15 2020
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
Session altered.
11:36:15 SQL> show arraysize;
arraysize 15
11:36:29 SQL> show autocommit;
autocommit OFF
11:36:40 SQL> show echo
echo OFF
11:37:15 SQL> show feedback
FEEDBACK ON for 6 or more rows SQL_ID OFF
11:37:28 SQL> show heading
heading ON
11:37:37 SQL> show linesize
linesize 80
11:37:42 SQL> show pagesize
pagesize 14
11:37:49 SQL> show serveroutput
serveroutput OFF
11:37:56 SQL> show sqlprompt
sqlprompt "_DATE SQL> "
11:38:03 SQL> show time
time OFF
11:38:09 SQL> show timing
timing OFF
11:38:15 SQL> show newpage
newpage 1
16:38:13 SQL> show trimspool 
trimspool OFF
16:42:10 SQL> show termout
termout ON

更详尽的SET选项参考：https://docs.oracle.com/en/database/oracle/oracle-database/20/sqpug/SET-system-variable-summary.html#GUID-A6A5ADFF-4119-4BA4-A13E-BC8D29166FAE

2. SPOOL使用示例

本节2个简单示例是我平时用的最多的，实际使用中可以根据需要进行set个性化数据输出。

导出数据到TXT或者CSV格式，一般数据量比较大的情况都是采用CSV或者TXT导出，不受Excel表的数据行数极限限制，当然如必要，也可以拆分成多个Excel来导出。

set linesize 1000
set pagesize 0
set echo off
set heading off
set feedback off
set colsep "|" --数据分隔符设置
set trims ON --去掉空字符，等同于set trimspool on
set term off --查询结果仅仅显示在输出文件中，不会显示在屏幕上
set trimout on
spool '/home/oracle/test1_2020071301.txt'
select * from test1;
spool '/home/oracle/test2_2020071302.txt'
select * from test2;
spool off;
exit;

导出数据到Excel格式，在这部分内容之前可以先阅读一下Oracle官文—关于CSV和HTML数据输出的介绍：https://docs.oracle.com/en/database/oracle/oracle-database/20/sqpug/generating-HTML-reports-from-SQL-Plus.html#GUID-BCC83F09-A432-4A6F-A8BA-1A099A5830BF，除了TXT输出外，SQL*Plus命令行界面支持生成完整的HTML网页输出或CSV格式的数据输出，使用SQLPLUS -MARKUP“ HTML ON”或SET MARKUP HTML ON SPOOL ON来生成完整的HTML页面，这些页面自动用和标记封装，也可以使用SQLPLUS -MARKUP“ CSV ON”或SET MARKUP CSV ON生成CSV格式的报告。

set linesize 300
set heading on
set term off verify off feedback off pagesize 100
--生成完整的HTML页面格式化输出成Excel，或者使用SET MARKUP CSV ON生成CSV格式的报告
set markup html on entmap ON spool on preformat off 
spool /home/oracle/test_20200713.xls
select * from test;
spool off;
exit;

3. 简化SPOOL导出的一个小（偷懒）脚本

每次导出的工作内容大同小异，小小减少重复性的工作（脚本以Excel为例，欢迎指正、优化）：

#!/bin/bash
# Script:export.sh
# Author:ChengYu
# Date:2014-03-21
# PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/home/oracle/product/bin:/root/bin
# export PATH

dir=/home/oracle
source ~/.bash_profile
echo "    this shell is help you to manage *** Server,it can startup or shutdown http service,DB service,remove cache,log files,input/output DB data and so on."
echo "    the options are:"
echo "          1 restart *** tomcat and remove cache;"
echo "          2 restart *** tomcat and remove cache,log files;"
echo "          3 input or output some user data(For Oracle database);"
echo "          4 export table for excel;"
printf "please enter option what you need:"
read option
until [[ $option =~ ^[1-4]+$ ]]
do
	printf "[error]: $option is invalid option,please enter the effective option:"
	read option
done

exportExcel(){
    echo "please enter user_name/password:"
    read pass
	echo "please enter export excel name:"
	read name
	echo "please enter the sql what you want to export:"
	read sql
    echo "$sql" >> $dir/get_tables.sql
	echo "set linesize 200" >> $dir/main.sql
    echo "set heading on" >> $dir/main.sql
	echo "set term off verify off feedback off pagesize 999" >> $dir/main.sql
	echo "set markup html on entmap ON spool on preformat off" >> $dir/main.sql
	echo "spool $dir/$name.xls" >> $dir/main.sql
	echo "start $dir/get_tables.sql" >> $dir/main.sql
	echo "spool off" >> $dir/main.sql
	echo "exit" >> $dir/main.sql
	chown oracle $dir/main.sql
	chown oracle $dir/get_tables.sql
    chmod 644 $dir/main.sql
    chmod 644 $dir/get_tables.sql
    su - oracle -c "sqlplus $pass @$dir/main.sql"
	yes|rm $dir/main.sql
	yes|rm $dir/get_tables.sql
}

case $option in
    1)
	echo "this is the 1 options"
	;;
    2)
	echo "this is the 2 options"
	;;
    3)
	echo "this is the 3 options"
	;;
    4)
	echo "this is the 4 options"
	exportExcel
	;;
    *)
	echo "other options"
	;;
esac

2020年07月12日

因为微信列表里一个陌生号，我们克制着进行了一场无畏的争论，一个不听解释，换上衣服出去跑步，一个不再解释，站在阳台上大喘气。

介意男性的靠近无可厚非，女的也这么警惕，难不成我还能把自己掰弯了？

很多话缠绕在喉头，却一个字都多余，过分在乎又无法建立对等的信任，彼此都会很疲累。

冷静，24点翻过这一篇。