Oracle使用SQL*Plus生成html文件参考: http://blog.itpub.net/26736162/viewspace-2149543/
Oracle如何使用spool导出utf8字符集的文本文件: http://blog.itpub.net/26736162/viewspace-2639287/
导出 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;
其中函数fun_get_spool_string_lhr如下所示:
CREATE OR REPLACE FUNCTION FUN_GET_SPOOL_STRING_LHR(P_OWNER VARCHAR2,
P_TABLE VARCHAR2)
RETURN VARCHAR2 AS
-----------------------------------------------------------------------------------
-- Created on 2012/11/07 11:33:07 by lhr
--Changed on 2012/11/07 11:33:07 by lhr
-- function:
/* drop table XB_COLS_LHR;
Create Table XB_COLS_LHR(
id number,
lie varchar2(4000)
);*/
-----------------------------------------------------------------------------------
V_RETURN VARCHAR2(32767);
V_RETURN1 VARCHAR2(32767);
V_RETURN2 VARCHAR2(32767);
V_MAX VARCHAR2(255);
V_TABLE VARCHAR2(255) := UPPER(P_TABLE);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT COUNT(1)
INTO V_MAX
FROM DBA_TAB_COLS C
WHERE C.TABLE_NAME = V_TABLE
AND C.OWNER = P_OWNER;
---列
SELECT 'SELECT ' || 0 || '||'',' ||
TO_CHAR(WMSYS.WM_CONCAT(DECODE(T.COLUMN_ID,
V_MAX,
'''||''' || T.COLUMN_NAME || '''',
'''||''' || T.COLUMN_NAME ||
'''||''')
)) || ' lie FROM dual a '
INTO V_RETURN1
FROM (SELECT C.COLUMN_NAME, C.COLUMN_ID
FROM DBA_TAB_COLS C
WHERE C.TABLE_NAME = V_TABLE
AND C.OWNER = P_OWNER
ORDER BY C.COLUMN_ID) T;
--表
SELECT 'UNION SELECT replace(replace( ' || 1 || '||'',' ||
TO_CHAR(WMSYS.WM_CONCAT(DECODE(T.COLUMN_ID,
V_MAX,
''' || REPLACE(' || T.COLUMN_NAME ||
','','',''、'')',
''' || REPLACE(' || T.COLUMN_NAME ||
','','',''、'')||'''))) ||
',chr(10),''''),chr(13),'''') from '|| P_OWNER||'.'|| V_TABLE
INTO V_RETURN2
FROM (SELECT C.COLUMN_NAME, C.COLUMN_ID
FROM DBA_TAB_COLS C
WHERE C.TABLE_NAME = V_TABLE
AND C.OWNER = P_OWNER
ORDER BY C.COLUMN_ID) T;
IF LENGTH(V_RETURN2) > 2000 THEN
EXECUTE IMMEDIATE 'truncate table XB_COLS_LHR';
INSERT INTO XB_COLS_LHR
VALUES
(1, 'SELECT substr(t.lie, 3) FROM (' || V_RETURN1);
INSERT INTO XB_COLS_LHR VALUES (2, V_RETURN2 || ') t;');
COMMIT;
V_RETURN := '见表 select * from XB_COLS_LHR; ';
ELSE
V_RETURN := 'SELECT substr(t.lie, 3) FROM (' || V_RETURN1 ||
V_RETURN2 || ') t;';
END IF;
RETURN V_RETURN;
END FUN_GET_SPOOL_STRING_LHR;
/
About Me
........................................................................................................................ ● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除 ● 本文在itpub( http://blog.itpub.net/26736162 )、博客园( http://www.cnblogs.com/lhrbest )和个人weixin公众号( xiaomaimiaolhr )上有同步更新 ● 本文itpub地址: http://blog.itpub.net/26736162 ● 本文博客园地址: http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址: http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答: http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址: http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群号: 230161599 (满) 、618766405 ● weixin群:可加我weixin,我拉大家进群,非诚勿扰 ● 联系我请加QQ好友 ( 646634621 ) ,注明添加缘由 ● 于 2019-04-01 06:00 ~ 2019-04-30 24:00 在魔都完成 ● 最新修改时间:2019-04-01 06:00 ~ 2019-04-30 24:00 ● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解 ● 版权所有,欢迎分享本文,转载请保留出处 ........................................................................................................................ ● 小麦苗的微店 : https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书 : http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班 : http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麦苗腾讯课堂主页 : https://lhr.ke.qq.com/ ........................................................................................................................ 使用 weixin客户端 扫描下面的二维码来关注小麦苗的weixin公众号( xiaomaimiaolhr )及QQ群(DBA宝典)、添加小麦苗weixin, 学习最实用的数据库技术。
........................................................................................................................ |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2641522/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26736162/viewspace-2641522/