本地机spool导出多个查询结果到多个CSV文件

问题

需要在PL/SQL developer 上多次手动重复查询导出大量数据。

解决

使用sqlcl中提供的SET SQLFORMAT csv和SQL plus提供的Spool导出数据到csv文件。

代码

set serveroutput on
set termout off
spool D:\SpoolFile\tmp_script.sql
DECLARE
    TYPE TYPE_ARRAY IS VARRAY(10) OF VARCHAR2(100);
    name_array TYPE_ARRAY := TYPE_ARRAY('01_XXX','02_XXX','03_XXX','04_XXX','05_XXX','06_XXX','07_XXX','08_XXX','09_XXX','10_XXX');  -- 导出数据的CSV文件命名数组
    var_array TYPE_ARRAY := TYPE_ARRAY('01','02','03','04','05','06','07','08','09','10'); -- 定义前2位编码的数组
    cityId VARCHAR2(2);
    v_sql varchar2(4000);
BEGIN
    -- 创建临时表
   for i IN 1..var_array.count
        LOOP
            cityId:=var_array(i);
            v_sql := 'CREATE GLOBAL TEMPORARY TABLE TMP_MX_'||cityId||' ON COMMIT PRESERVE ROWS  AS SELECT * FROM MX_M_VIEW WHERE p_view_param.set_cityId('''||cityId||''')='''||cityId||'''';  -- 把复杂的业务代码逻辑使用视图封装起来。这里是一个带参视图，放在循环里，然后循环传参，创建临时表，然后使用临时表导出数据。当然也可以直接使用视图导出数据，但是试了几次，导数会比较久。
            execute immediate v_sql;
        end loop;
		commit;

    -- 创建导出数据到文件的sql脚本
    for i IN 1..var_array.count loop
		cityId:=var_array(i);
        dbms_output.put_line('SET SQLFORMAT csv');
        dbms_output.put_line('SET FEEDBACK off');
        dbms_output.put_line('spool D:\SpoolFile\' ||name_array(i)|| '.csv'); -- 存放文件的路径不要带中文名
        DBMS_OUTPUT.put_line('SELECT * FROM TMP_MX_'||cityId||' ;');
        dbms_output.put_line('spool off');
        dbms_output.put_line('SET SQLFORMAT ansiconsole');
        dbms_output.put_line('SET FEEDBACK ON');
    end loop;
end;
/
spool off

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

环境

Oracle Instant Client 版本

instantclient_11_2,下载了Basic, JDBC supplement, SQL *Plus, SDK, ODBC

sqlcl 版本

sqlcl-24.2.0.180.1721

java 版本

Oracle JDK11

关于使用UTL_FILE包

之前有尝试使用UTL_FILE包，但是一直在打开文件的步骤操作不成功。需要在数据库Oracle在的服务器创建文件夹，并不是在本地机上创建文件夹，但我目前只能远程操作。

参考资料

plsql - 假脱机多个文件 - 堆栈溢出 (stackoverflow.com)

oracle - SQL*Plus - Spool into multiple files - Stack Overflow

SPOOL sql语句实现LOOP循环转储多文件_oracle spool 生成多个文件-CSDN博客