Oracle通过Sqlplus结合Shell脚本方式生成Excel文件

2025-04-14 技术教程

一、问题描述今天接到一个任务，是要求每天通过SQL脚本生成excel文件，并且自动发送到相关人员邮箱。这个需求我还真是没有做过，之前只做过通过SQL脚本生成为HTML网页文件。于是乎，我又开始的学习过程，如今的Internet时代，解决问题就是easy，很快就找到了eygle的博客（使用SQL*PLUS,构建完美excel或html输出http://www.eygle.com/archives/2005/04/eoasqlplusieaae.html）。下面是学习过程中的实验部分。
二、实验1.建立测试表

SAM@dzwj > create table test1(id int,name varchar2(10),loc varchar2(30),hire_date date,email varchar2(20),department varchar2(20));
Table created.

SAM@dzwj > insert into test1 values (1,'sam1','beijing',sysdate,'sam1@oracle.com','it');
1 row created.
SAM@dzwj > insert into test1 values (2,'sam2','beijing',sysdate,'sam2@oracle.com','it');
1 row created.
SAM@dzwj > insert into test1 values (3,'sam3','beijing',sysdate,'sam3@oracle.com','it');
1 row created.
SAM@dzwj > insert into test1 values (4,'sam4','beijing',sysdate,'sam4@oracle.com','it');
1 row created.
SAM@dzwj > insert into test1 values (5,'sam5','beijing',sysdate,'sam5@oracle.com','it');
1 row created.

SAM@dzwj > SAM@dzwj > commit;
Commit complete.

SAM@dzwj > select * from test1;

ID NAME LOC HIRE_DATE EMAIL DEPARTMENT
---------- ---------- ------------------------------ ------------------ -------------------- --------------------
1 sam1 beijing 28-NOV-17 sam1@oracle.com it
2 sam2 beijing 28-NOV-17 sam2@oracle.com it
3 sam3 beijing 28-NOV-17 sam3@oracle.com it
4 sam4 beijing 28-NOV-17 sam4@oracle.com it
5 sam5 beijing 28-NOV-17 sam5@oracle.com it

2.编辑 main.sql

[oracle@testdb ~]$ cat main.sql
set linesize 200 pagesize 10000
set term off verify off feedback off
set markup html on entmap on spool on preformat off
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
spool /home/oracle/test1.xls
@/home/oracle/get_tables.sql
spool off
exit
3.编辑 get_tables.sql

[oracle@testdb ~]$ cat get_tables.sql
select * from test1;
4.编辑执行文件 collect.sh

[oracle@testdb ~]$ cat collect.sh
#!/bin/bash
. /home/oracle/.bash_profile
DATE=`date +%Y%m%d`
sqlplus sam/oracle@dzwj @/home/oracle/main
mv /home/oracle/test1.xls /home/oracle/test1_${DATE}.xls
5.给collect.sh 执行权限

[oracle@testdb ~]$ chmod u+x collect.sh
6.执行

[oracle@testdb ~]$ ./collect.sh

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 29 11:00:19 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
7.验证将文件传回到本地机器打开，得到想要的excel文件

三、总结生活在Internet时代真是件幸福的事，此次任务算是告一段落，但是当中还是碰到一些小problems，比如一开始没有加时间NLS_DATE_FORMAT变量的修改，导出的时间类型数据时没有时间，只有年月日。总而言之，多学习，多实践，没错的。向eygle大神致谢。 Where there is a will, there is a way.