如何使用sqlplus将oracle中的任何给定表输出到csv

SET MARKUP HTML ON SPOOL ON
HEAD "<title>Data Extract</title> - <meta http-equiv='Content-Type' content='application/vnd.ms-excel;'> 
<style type='text/css'>
</style>"
SET ECHO OFF
SPOOL output.xls
select * from &tablename ;
spool off
exit

阿比

您可以使用SET Colsep选项。

SET COLSEP "," 
SET PAGES 0
SET FEEDBACK OFF
SPOOL output.csv
select * from HR.employees;
spool off
exit

在搜索堆栈溢出后,我找不到这个问题的确切答案,因此我开发了自己的解决方案。

SET echo off
SET verify off 
SET heading off 
SET pages 50000 
SET feedback off
SET newpage none 
SET termout off
SET linesize 900
SET trimspool on
SET serveroutput on

define table_name = &1
define spool_path = &2
var rc refcursor
column qry new_val capture

SELECT  'select ''"'' || ' || listagg(column_name,' || ''","'' || ') within group (order by column_id) || ' || ''"'' as rec from &table_name' qry 
  FROM user_tab_cols 
 WHERE table_name = '&table_name';



spool &spool_path

SELECT listagg(column_name,',') WITHIN GROUP (ORDER BY column_id) 
  FROM user_tab_cols 
 WHERE table_name = '&table_name';

BEGIN

FOR v_rec IN (&capture) LOOP

dbms_output.put_line(v_rec.rec);

END LOOP;

END;
/

spool off

EXIT

该脚本需要两个参数——第一个参数是表名,第二个是假脱机路径。

基本上是第一个查询:

SELECT  'select ''"'' || ' || listagg(column_name,' || ''","'' || ') within group (order by column_id) || ' || ''"'' as rec from &table_name' qry 
  FROM user_tab_cols
 WHERE table_name = '&table_name';

标题是由类似的查询生成的(这是我们开始后台处理后的第一个操作),然而,为此,我们可以直接将列标题名称列表聚合在一起。

在您选择的表上测试这两个查询以查看结果,这更容易理解!