ITPub博客

首页 > 数据库 > Oracle > Oracle通过Sqlplus结合Shell脚本方式生成Excel文件

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

原创 Oracle 作者:yhdmy 时间:2017-11-29 12:08:50 0 删除 编辑
一、问题描述
       今天接到一个任务,是要求每天通过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. SAM@dzwj > insert into test1 values (1,'sam1','beijing',sysdate,'sam1@oracle.com','it');
  4. 1 row created.
  5. SAM@dzwj > insert into test1 values (2,'sam2','beijing',sysdate,'sam2@oracle.com','it');
  6. 1 row created.
  7. SAM@dzwj > insert into test1 values (3,'sam3','beijing',sysdate,'sam3@oracle.com','it');
  8. 1 row created.
  9. SAM@dzwj > insert into test1 values (4,'sam4','beijing',sysdate,'sam4@oracle.com','it');
  10. 1 row created.
  11. SAM@dzwj > insert into test1 values (5,'sam5','beijing',sysdate,'sam5@oracle.com','it');
  12. 1 row created.

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

  15. SAM@dzwj > select * from test1;

  16.         ID NAME LOC HIRE_DATE EMAIL DEPARTMENT
  17. ---------- ---------- ------------------------------ ------------------ -------------------- --------------------
  18.          1 sam1 beijing 28-NOV-17 sam1@oracle.com it
  19.          2 sam2 beijing 28-NOV-17 sam2@oracle.com it
  20.          3 sam3 beijing 28-NOV-17 sam3@oracle.com it
  21.          4 sam4 beijing 28-NOV-17 sam4@oracle.com it
  22.          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. SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 29 11:00:19 2017

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


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

  7. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  8. 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
曾就职于铁道科学研究院,太极计算机公司,合力中税。负责过国家电网数据库部署、迁移、升级、灾备等实施规划工作。大唐发电厂、北京市公安局数据库运维及优化工作。现任金融工场高级DBA岗位,负责oracle、mysql数据库相关工作。拥有Oracle技术10g/11g的 OCP与OCM认证,Oracle YEP成员,OCM联盟成员。

注册时间:2013-04-22

  • 博文量
    106
  • 访问量
    1565057