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

一、问题描述

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

二、实验

1.建立测试表

1. SAM@dzwj > create table test1(id int,name varchar2(10),loc varchar2(30),hire_date date,email varchar2(20),department varchar2(20));
   
2. Table created.
   
3. 
   
4. SAM@dzwj > insert into test1 values (1,'sam1','beijing',sysdate,'sam1@oracle.com','it');
   
5. 1 row created.
   
6. SAM@dzwj > insert into test1 values (2,'sam2','beijing',sysdate,'sam2@oracle.com','it');
   
7. 1 row created.
   
8. SAM@dzwj > insert into test1 values (3,'sam3','beijing',sysdate,'sam3@oracle.com','it');
   
9. 1 row created.
   
10. SAM@dzwj > insert into test1 values (4,'sam4','beijing',sysdate,'sam4@oracle.com','it');
    
11. 1 row created.
    
12. SAM@dzwj > insert into test1 values (5,'sam5','beijing',sysdate,'sam5@oracle.com','it');
    
13. 1 row created.
    
14. 
    
15. SAM@dzwj > SAM@dzwj > commit;
    
16. Commit complete.
    
17. 
    
18. SAM@dzwj > select * from test1;
    
19. 
    
20.         ID NAME LOC HIRE_DATE EMAIL DEPARTMENT
    
21. ---------- ---------- ------------------------------ ------------------ -------------------- --------------------
    
22.          1 sam1 beijing 28-NOV-17 sam1@oracle.com it
    
23.          2 sam2 beijing 28-NOV-17 sam2@oracle.com it
    
24.          3 sam3 beijing 28-NOV-17 sam3@oracle.com it
    
25.          4 sam4 beijing 28-NOV-17 sam4@oracle.com it
    
26.          5 sam5 beijing 28-NOV-17 sam5@oracle.com it

2.编辑 main.sql

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

3.编辑 get_tables.sql

1. [oracle@testdb ~]$ cat get_tables.sql
   
2. select * from test1;

4.编辑执行文件 collect.sh

1. [oracle@testdb ~]$ cat collect.sh
   
2. #!/bin/bash
   
3. . /home/oracle/.bash_profile
   
4. DATE=`date +%Y%m%d`
   
5. sqlplus sam/oracle@dzwj @/home/oracle/main
   
6. mv /home/oracle/test1.xls /home/oracle/test1_${DATE}.xls

5.给collect.sh 执行权限

1. [oracle@testdb ~]$ chmod u+x collect.sh

6.执行

1. [oracle@testdb ~]$ ./collect.sh
   
2. 
   
3. SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 29 11:00:19 2017
   
4. 
   
5. Copyright (c) 1982, 2013, Oracle. All rights reserved.
   
6. 
   
7. 
   
8. Connected to:
   
9. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
11. 
    
12. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    
13. 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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26148431/viewspace-2147929/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26148431/viewspace-2147929/