本地机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 1980/12/17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30
 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10
 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20
 7839 KING       PRESIDENT       1981/11/17    5000.00               10
 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30
 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30
 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20
 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

14 rows selected

使用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(); -- 写入csv行
  end loop;

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

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

SQL> @D:\SpoolFile\test.sql
SQL>

结果如下

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

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

导出单个文件或者多个文件

1、存储过程封装脚本

create or replace procedure SCOTT.sql_to_data
(
    l_sqltext in varchar2 -- plsql查询sql语句
)
is
  l_curid   INTEGER;
  l_cnt     NUMBER;
  l_separator varchar2(1);
  l_desctab dbms_sql.desc_tab;
  l_status integer;
  l_columnvalue varchar2(4000);
BEGIN
  DBMS_OUTPUT.ENABLE(buffer_size => null);  -- 表示输出buffer不受限制，如果数据量大，最好加上这句
  execute immediate 'alter session set nls_date_format=''yyyy-mm-dd'''; -- 修改日期格式
  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); -- 执行动态SQL语句。

  -- 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 || ''); -- 输出表对应的表数据
          dbms_output.put(l_separator || '' ||trim(both ' ' from replace(l_columnvalue, '"', '""')) || ''); -- 部分字段前会有多余的字符'"'，会导致数据导出异常
          l_separator := ',';
      end loop;
      dbms_output.new_line(); -- 写入csv行
  end loop;

  dbms_sql.close_cursor(l_curid);
END;
/

2、导出单个文件

导出EMP表所有的数据

脚本内容如下

SET FEEDBACK off
SET TERMOUT OFF
Spool D:\SpoolFile\TEST.csv
EXEC sql_to_data('SELECT * FROM EMP');
Spool off

3、导出多个文件

按照部门导出到不同的CSV文件

脚本内容如下

-- 1、定义包

CREATE OR REPLACE package p_view_param  is
-- 部门编号 --
   function set_deptno(deptno NUMBER) return NUMBER;
   function get_deptno return NUMBER;
   
end p_view_param;
/


-- 2、定义包体

create or replace package body p_view_param is  
    paramdeptno NUMBER(2);  
       -- 部门编号  
       function set_deptno(deptno NUMBER) return NUMBER is  
       begin  
         paramdeptno:=deptno;  
         return deptno;  
        end;  
  
       function get_deptno return NUMBER is  
       begin  
         return paramdeptno;  
       end;  
    
   end p_view_param;
   /


-- 3、创建视图

CREATE OR REPLACE VIEW EMP_VIEW AS
SELECT 
EMPNO
, ENAME
, JOB
, MGR
, HIREDATE
, SAL
, COMM
, DEPTNO
FROM EMP
WHERE DEPTNO = p_view_param.get_deptno()
;

-- 4、导出多个文件

set serveroutput on
set termout off
spool D:\SpoolFile\tmp_script.sql
DECLARE
    TYPE TYPE_ARRAY IS VARRAY(3) OF VARCHAR2(100);
    TYPE NUM_ARRAY IS VARRAY(3) OF NUMBER(2);
    name_array TYPE_ARRAY := TYPE_ARRAY('DEPTNO_10','DEPTNO_20','DEPTNO_30');  -- 导出数据的CSV文件命名数组
    var_array NUM_ARRAY := NUM_ARRAY(10,20,30);
    v_sql varchar2(4000);
BEGIN
    DBMS_OUTPUT.ENABLE(buffer_size => null);  -- 表示输出buffer不受限制
    -- 按照部门导出到不同的CSV文件
    for i IN 1..var_array.count loop
        v_sql := 'SELECT * FROM EMP_VIEW WHERE p_view_param.set_deptno('||var_array(i)||')='||var_array(i)||'';
        dbms_output.put_line('SET FEEDBACK off');
        dbms_output.put_line('spool D:\SpoolFile\' ||name_array(i)|| '.csv');
        DBMS_OUTPUT.put_line('EXEC sql_to_data('''|| v_sql ||''');');
        dbms_output.put_line('spool off');
        dbms_output.put_line('SET FEEDBACK ON');
    end loop;

end;
/
spool off

@D:\SpoolFile\tmp_script.sql
HOST del D:\SpoolFile\tmp_script.sql

把以上内容，放在一个sql脚本里，在命令行执行即可，如下