时间戳

首页 > 技术文章 > 将oracle查询结果输出为csv,html

将oracle查询结果输出为csv,html

nanblog 2021-08-29 21:09 原文

一：将查询结果输出为csv
1）.创建表
Create Table TMP(
id number,
lie varchar2(4000)
);
2).在数据库中创建函数
CREATE OR REPLACE FUNCTION FUN_GET_SPOOL_STRING_NAN(P_OWNER VARCHAR2,
P_TABLE VARCHAR2)
RETURN VARCHAR2 AS

/* drop table TMP;
Create Table TMP(
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 TMP';
INSERT INTO TMP
VALUES
  (1, 'SELECT substr(t.lie, 3) FROM   (' || V_RETURN1);
INSERT INTO TMP VALUES (2, V_RETURN2 || ') t;');
COMMIT;

V_RETURN := '见表 select * from TMP; ';

ELSE

V_RETURN := 'SELECT substr(t.lie, 3) FROM   (' || V_RETURN1 ||
            V_RETURN2 || ') t;';

END IF;
RETURN V_RETURN;
END FUN_GET_SPOOL_STRING_NAN;
/
3).编译该函数
4).调用该函数，将查询的用户名，表名传入参数中
select FUN_GET_SPOOL_STRING_LHR('SCOTT','EMP') from dual where rownum <=10;
5).将函数执行结果返回值粘贴在improtcsv.sql文件中
importcsv.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=/tmp/scritps

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||','||'EMPNO'||','||'ENAME'||','||'JOB'||','||'MGR'||','||'HIREDATE'||','||'SAL'||','||'COMM'||','||'DEPTNO' lie FROM dual a UNION SELECT replace(replace( 1||',' || REPLACE(EMPNO,',','??')||',' || REPLACE(ENAME,',','??')||',' || REPLACE(JOB,',','??')||',' || REPLACE(MGR,',','??')||',' || REPLACE(HIREDATE,',','??')||',' || REPLACE(SAL,',','??')||',' || REPLACE(COMM,',','??')||',' || REPLACE(DEPTNO,',','??'),chr(10),''),chr(13),'') from SCOTT.EMP) t;
spool off

exit

在SQLplus中执行该SQL脚本

下载该文件即可，结果如下：

二：将查询结果输出为html格式

漂亮的表格：
set feedback off

set markup html on;

set markup html on spool on preformat off entmap on -

head ' -

SCOTT.EMP表的数据 - ' -

body 'BGCOLOR="#C0C0C0"'
SET MARKUP html TABLE 'WIDTH="100%" border="1" summary="Script output" cellspacing="0px" style="border-collapse:collapse;" '
define data_path=/tmp/scritps/
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

注意在Linux和Windows上执行命令时对路径的修改

生成结果如下：

三：生成简单的html
set feedback on;
set markup html on;
spool /tmp/scritps
select * from scott.salgrade;
spool off
set markup html off
exit
生成结果如下：

本文仅为整理网络内容
原文地址：http://blog.itpub.net/26736162/viewspace-2149543/

推荐阅读