【DB笔试面试490】 如何导出csv和html文件？

在Oracle中，如何导出csv和html文件？

答案部分

1、 导出csv文件格式

保存以下内容为spoolcsv.sql:

set echo on
set trimspool on
set trimout on
set linesize 4000
set pagesize 0
set sqlblanklines on
set feedback off
set serveroutput off
set term off
set echo off


define data_path=E:\data


col ymd new_value v_ymd

select to_char(sysdate,'YYYYMMDDHH24MISS') ymd FROM dual;

spool &data_path\result_&&v_ymd..csv
SELECT substr(t.lie, 3) FROM   (SELECT 0||','||'DEPTNO'||','||'DNAME'||','||'LOC' lie FROM dual a UNION SELECT replace(replace( 1||',' || REPLACE(DEPTNO,',','、')||',' || REPLACE(DNAME,',','、')||',' || REPLACE(LOC,',','、'),chr(10),''),chr(13),'')  from SCOTT.DEPT) t;
spool off


exit

---命令行
--sqlplus lhr/lhr@orcl @f:\sql\spool\spool_csv.sql


--SELECT fun_get_spool_string_lhr('SCOTT','EMP') FROM DUAL;

主要是使用：主要是使用 set markup html on

SYS@PROD1> set markup
SP2-0281: markup missing set option
Usage: SET MARKUP HTML [ON|OFF] [HEAD text] [BODY text]
 [TABLE text] [ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]

简单：

set feedback off
set markup html on;
spool e:\data\salgrade.html
select * from scott.emp; 
spool off
set markup html off
exit

漂亮的表格：

set feedback off
set markup html on;
set markup html on spool on preformat off entmap on -
head ' -
   -
  ' -
body   'BGCOLOR="#C0C0C0"'


SET MARKUP html TABLE  'WIDTH="100%" border="1" summary="Script output"  cellspacing="0px" style="border-collapse:collapse;" ' 

define data_path=E:\data

col ymd new_value v_ymd

select to_char(sysdate,'YYYYMMDDHH24MISS') ymd FROM dual;

spool &data_path\result_html_&&v_ymd..html
select * from scott.emp; 
spool off
set markup html off
exit

再例如我的健康检查脚本也是通过这种方式导出的：