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

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;

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 ' -
  <title> SCOTT.EMP表的数据</title> -
  <style type="text/css"> -
    body              {font:11px Courier New,Helvetica,sans-serif; color:black; background:White;} -
    p                 {font:11px Courier New,Helvetica,sans-serif; color:black; background:White;} -
    table,tr,td       {font:11px Courier New,Helvetica,sans-serif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} -
    th                {font:bold 11px Courier New,Helvetica,sans-serif; color:White; background:#0066cc; padding:0px 0px 0px 0px;} -
    h1                {font:bold 12pt Courier New,Helvetica,Geneva,sans-serif; color:White; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
    h2                {font:bold 11pt Courier New,Helvetica,Geneva,sans-serif; color:White; background-color:White; margin-top:4pt; margin-bottom:0pt;} -
    a                 {font:11px Courier New,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.link            {font:11px Courier New,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLink          {font:11px Courier New,Helvetica,sans-serif; color:#663300; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkBlue      {font:11px Courier New,Helvetica,sans-serif; color:#0000ff; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkDarkBlue  {font:11px Courier New,Helvetica,sans-serif; color:#000099; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkRed       {font:11px Courier New,Helvetica,sans-serif; color:#ff0000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    a.noLinkDarkRed   {font:11px Courier New,Helvetica,sans-serif; color:#990000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -  
    a.info:hover {background:#eee;color:#000000; position:relative;} -
    a.info span {display: none; } -
    a.info:hover span {font-size:11px!important; color:#000000; display:block;position:absolute;top:30px;left:40px;width:150px;border:1px solid #ff0000; background:#FFFF00; padding:1px 1px;text-align:left;word-wrap: break-word; white-space: pre-wrap; white-space: -moz-pre-wrap} -
  </style>' -
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