本地机Spool出某个表的字段名和数据到csv

之前是通过sqlcl提供的SET SQLFORMAT csv导出数据到csv文件，这次是想尝试仅仅是使用SQL plus 中的spool导出数据

数据准备

SQL> SELECT * FROM EMP ORDER BY EMPNO;

   EMPNO ENAME     JOB              MGR HIREDATE         SAL    COMM    DEPTNO
________ _________ ____________ _______ ____________ _______ _______ _________
    7369 SMITH     CLERK           7902 17-12月-80        800                20
    7499 ALLEN     SALESMAN        7698 20-2月 -81       1600     300        30
    7521 WARD      SALESMAN        7698 22-2月 -81       1250     500        30
    7566 JONES     MANAGER         7839 02-4月 -81       2975                20
    7654 MARTIN    SALESMAN        7698 28-9月 -81       1250    1400        30
    7698 BLAKE     MANAGER         7839 01-5月 -81       2850                30
    7782 CLARK     MANAGER         7839 09-6月 -81       2450                10
    7788 SCOTT     ANALYST         7566 19-4月 -87       3000                20
    7839 KING      PRESIDENT            17-11月-81       5000                10
    7844 TURNER    SALESMAN        7698 08-9月 -81       1500       0        30
    7876 ADAMS     CLERK           7788 23-5月 -87       1100                20
    7900 JAMES     CLERK           7698 03-12月-81        950                30
    7902 FORD      ANALYST         7566 03-12月-81       3000                20
    7934 MILLER    CLERK           7782 23-1月 -82       1300                10

使用Oracle中SCOTT用户自己提供的数据

脚本

脚本内容如下：

-- 导出CSV文件
-- set serveroutput on  
SET FEEDBACK off
set termout off
Spool D:\SpoolFile\TEST.csv
DECLARE
  l_curid   INTEGER;
  l_cnt     NUMBER;
  l_separator varchar2(1);
  l_desctab dbms_sql.desc_tab;
  l_sqltext VARCHAR2(2000);
  l_status integer;
  l_columnvalue varchar2(4000);
BEGIN
  l_sqltext := 'SELECT * FROM EMP'; -- 可以是任意有效的查询sql文本 
  l_curid   := dbms_sql.open_cursor();
  dbms_sql.parse(l_curid, l_sqltext, dbms_sql.native);
  dbms_sql.describe_columns(l_curid, l_cnt, l_desctab);

   -- dump table column name
    for i in 1 .. l_cnt loop
        dbms_output.put(l_separator || '' || l_desctab(i).col_name || ''); -- 输出表头部字段名
        dbms_sql.define_column(l_curid, i, l_columnvalue, 4000);
        l_separator := ',';
    end loop;
    dbms_output.new_line();

  l_status := dbms_sql.EXECUTE(l_curid); 

  -- dump table column value
  while (dbms_sql.fetch_rows(l_curid) > 0) loop
      l_separator := '';
      for i in 1 .. l_cnt loop
          dbms_sql.column_value(l_curid, i, l_columnvalue);
          dbms_output.put(l_separator || '' || l_columnvalue || ''); -- 输出表对应的表数据
          l_separator := ',';
      end loop;
      dbms_output.new_line(); -- 写入cvs行
  end loop;

  dbms_sql.close_cursor(l_curid);
END;
/
Spool off

把以上脚本放在一个sql文件里，然后直接执行脚本文件就好

@D:\SpoolFile\test.sql

结果如下

l_sqltext := 'SELECT * FROM EMP'; -- 可以是任意有效的查询sql文本 

该段sql还是比较简单的查询，如果是比较复杂的业务逻辑代码，可以先使用视图进行封装后，再放进这里。需要导出多个文件，可以使用数组遍历的方式导出csv文件。

参考资料

DBMS_SQL使用 - 雪山上的蒲公英 - 博客园 (cnblogs.com)

dbms_output.put_line的用法（Oracle）_dbmsoutputputline-CSDN博客

ORACLE中使用DBMS_SQL获取动态SQL执行结果中的列名和值 - 红无酒伤 - 博客园 (cnblogs.com)

Oracle批量导出表数据到CSV文件 - 天之痕羽翼 - 博客园 (cnblogs.com)