使用sqlplus/mysql 导出数据为excel或html

最新推荐文章于 2024-09-06 10:55:11 发布

iteye_4537

最新推荐文章于 2024-09-06 10:55:11 发布

阅读量370

点赞数

文章标签：数据库 shell php

通过SQL*PLUS我们可以构建友好的输出，满足多样化用户需求。
本例通过简单示例，介绍通过sql*plus输出xls，html两种格式文件.
首先创建两个脚本:
1.main.sql 用以设置环境，主要调用具体脚本
2.get_tables.sql 为获取具体数据之脚本

get_table.sql脚本内容：

select owner,table_name,tablespace_name,blocks,last_analyzed
from all_tables order by 1,2;

main.sql脚本内容：

set linesize 200
set term off verify off feedback off pagesize 999
set markup html on entmap ON spool on preformat off
spool tables.xls
@get_tables.sql
spool off
exit

输出效果图：

全文引用这里:http://www.eygle.com/archives/2005/04/eoasqlplusieaae.html
#main ... 后辍名.xls即excel，后辍名.html即html文件

#生成HTML报表样式:http://download.oracle.com/docs/cd/B14117_01/server.101/b12170/ch8.htm#i1043351

#set markup HTML [ON|OFF] 指定是否生成HTML格式，ON生成，OFF不生成，默认是OFF。

#用SHOW MARKUP命令查看MARKUP操作当前状态：

#HEAD text 指定HTML中HEAD标签中的内容，默认情况下，文本包括级联样式和标题。
#
#BODY text 指定HTML中BODY标签属性
#
#TABLE text
#设置<TABLE>标签的属性，如BORDER, CELLPADDING, CELLSPACING和WIDTH.。默认情况下，<TABLE> 的WIDTH属性设置为90%，BORDER属性设置为1。
#
#ENTMAP {ON|OFF}
#指定在SQL * Plus中是否用HTML字符实体如<, >, " and &等替换特殊字符<, >, " and & 。默认设置是ON。
#
#SPOOL {ON|OFF}
#指定是否在SQL*Plus生成HTML标签<HTML> 和<BODY>, </BODY> 和</HTML>。默认是OFF。
#注：这是一个后台打印操作，只有在生成SPOOL文件生效，在屏幕上并不生效。
#
#PRE[FORMAT] {ON|OFF}
#指定SQL*Plus生成HTML时输出<PRE>标签还是HTML表格，默认是OFF，因此默认输出是写HTML表格。
#
#在生成HTML格式时要设置一些其他操作，列出如下：
#
#SET ECHO {ON|OFF}
#是否显示脚本中正在执行的SQL语句。
#
#SET FEED[BACK] {6|n|ON|OFF}
#是否显示当前sql语句查询或修改的行数。默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数。
#
#SET NEWP[AGE] {1|n|NONE}
#设置页与页之间的分隔。
#当SET NEWPAGE 0 时，会在每页的开头有一个小的黑方框。
#当SET NEWPAGE n 时，会在页和页之间隔着n个空行。
#当SET NEWPAGE NONE 时，会在页和页之间没有任何间隔。
#
#SET NULL text
#显示时，用text值代替NULL值
#
#SET PAGES[IZE] {24|n}
#设置一页有多少行数，如果设为0，则所有的输出内容为一页并且不显示列标题
#
#SET WRA[P] {ON|OFF}
#当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句，设置SET WRAP ON时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。
#
#SET TERM[OUT] {ON|OFF}
#是否在屏幕上显示输出的内容, 主要用与SPOOL结合使用。设置SET TERMOUT OFF，输出的内容只保存在输出文件中，不会显示在屏幕上，提高了SPOOL输出速度。
#
#SET TRIMS[OUT] {ON|OFF} 将SPOOL输出中每行后面多余的空格去掉

-----------------------------------------------------------------------------------------------------------------------------

Mysql导出xls

这里不讲如何用php到处，只是用mysql的命令和shell结合来导出。。。

方法一：进入到mysql的控制台，输入：

 
    SELECT*INTOOUTFILE‘./test.xls‘FROMtb1WHERE1ORDERBYidDESCLIMIT0,50;

这样，应该会在你的mysql的var/目录下产生一个test.xls的文件。。。
但是如果你的shell的登录帐户和mysql的运行账户不同，很可能这个方法就不适用了，因为你可以通过mysql控制台来产生这个文件，但是你却没有办法通过shell来对这个文件进行操作，因为用户不同，没有访问权限。

这个时候，同事告诉偶滴办法就重装登录了！！！

 
    echo "SELECT * FROM tb1 WHERE 1 ORDER BY id DESC LIMIT 0, 50;"| /usr/local/mysql/bin/mysql -hlocalhost -uroot -pxxxxx > /tmp/test.xls

ok，把你的test.xls文件从tmp下载到本地打开来看看吧。。。
什么？中文乱码？
别着急。。。
因为office默认的是gb2312编码，服务器端生成的很有可能是utf-8编码，这个时候你有两种选择，
1.在服务器端使用iconv来进行编码转换，

 
    iconv -futf8 -tgb2312 -otest2.xls test.xls

如果转换顺利，那么从server上下载下来就可以使用了。
转换如果不顺利，则会提示：iconv: illegal input sequence at position 1841 类似于这样的错误，
先把test.xls下载下来，这个时候文件是utf-8编码的，用excel打开，乱码。
把test.xls以文本方式打开，然后另存为，在编码选择ANSI编码，保存。

ok，再用excel打开，乱码问题，烟消云散。。。