Oracle通過Sqlplus結合Shell指令碼方式生成Excel檔案

yhdmy發表於2017-11-29
OracleSQL指令碼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.

  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.

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

  18. SAM@dzwj > select * from test1;

  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

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

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


  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

  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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章