利用存储过程将Oracle远程数据库数据以.csv的格式批量导出

之前经常用plsql执行脚本，将查出来的数据以.csv的格式导出然后提交给客户。由于近期的清单数量增加，每份清单的数据量也比较大，所以再用plsql提取就非常耗时。因为大部分清单的脚本是固定的，所以想着是否可以使用存储过程定时执行脚本直接导出清单。于是，就开始各种百度查阅资料，终于成功实现了不用手动执行脚本就可以导出清单的好方法。

实现方法都在下面存储过程的注释里，其中第二部分：PRC_DATA_TO_CSV存储过程会调用第一部分：PRC_SQL_TO_CSV 存储过程实现数据以.csv格式导出的导出。

第一部分：

CREATE OR REPLACE PROCEDURE PRC_SQL_TO_CSV
                                      (P_QUERY IN VARCHAR2,    -- 查询语句
                                       P_DIR IN VARCHAR2,      -- 导出的文件放置目录/只能是应用服务器上的路径
                                       P_FILENAME IN VARCHAR2  -- 文件名.csv格式/输出的csv文件
                                       )
/******************************************************************************
  * 功能名称：以CSV的格式导出表数据
  * 功能描述：
  * @Description:
  * @Copyright: FFCS（C） 2017
  * @Company: FFCS
  * @author Wang Yiren
  * @Version 0.1.1 date:2018年02月27日
  *******************************************************************************/
/*
第一步：修改数据库utl_file_dir参数，命令如下：
             show parameter utl_file_dir;--查看参数
             alter system set utl_file_dir = '/oradata/ftp/' scope = spfile;  --修改参数，参数为linux服务器相关路径
第二步：重启数据库，使修改的参数生效
第三步：定义一个directory,命令如下：
             create or replace directory MYDIR as '/oradata/ftp/';
第四部：测试
 declare
begin
  PRC_SQL_TO_CSV('select * from view_LDAPM_MPW_BD_PSNDOC t where t.MONTH_ID = 201712','MYDIR','LDAPM_MPW_BD_PSNDOC.csv');
end;
 */
 IS
  L_OUTPUT UTL_FILE.FILE_TYPE;
  L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
  L_COLUMNVALUE VARCHAR2(4000);
  L_STATUS INTEGER;
  L_COLCNT NUMBER := 0;
  L_SEPARATOR VARCHAR2(1);
  L_DESCTBL DBMS_SQL.DESC_TAB;
  P_MAX_LINESIZE NUMBER := 32000;
BEGIN
  --OPEN FILE
  L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);
  --DEFINE DATE FORMAT
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
  --OPEN CURSOR
  DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR, L_COLCNT, L_DESCTBL);
  --DUMP TABLE COLUMN NAME
  FOR I IN 1 .. L_COLCNT LOOP
    UTL_FILE.PUT(L_OUTPUT,L_SEPARATOR || '"' || L_DESCTBL(I).COL_NAME || '"'); --输出表字段
    DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 4000);
    L_SEPARATOR := ',';
  END LOOP;
  UTL_FILE.NEW_LINE(L_OUTPUT); --输出表字段
  --EXECUTE THE QUERY STATEMENT
  L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);

  --DUMP TABLE COLUMN VALUE
  WHILE (DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0) LOOP
    L_SEPARATOR := '';
    FOR I IN 1 .. L_COLCNT LOOP
      DBMS_SQL.COLUMN_VALUE(L_THECURSOR, I, L_COLUMNVALUE);
      UTL_FILE.PUT(L_OUTPUT,
                  L_SEPARATOR || '"' ||
                  TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE, '"', '""')) || '"');
      L_SEPARATOR := ',';
    END LOOP;
    UTL_FILE.NEW_LINE(L_OUTPUT);
  END LOOP;
  --CLOSE CURSOR
  DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
  --CLOSE FILE
  UTL_FILE.FCLOSE(L_OUTPUT);
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;

第二部分：

CREATE OR REPLACE PROCEDURE PRC_DATA_TO_CSV AUTHID CURRENT_USER IS
/******************************************************************************
  * 功能名称：多张表数据以CSV格式导出
  * 功能描述：
  * @Description:
  * @Copyright: FFCS（C） 2017
  * @Company: FFCS
  * @author Wang Yiren
  * @Version 0.0.1 date:2018年02月27日
  *******************************************************************************/
/*
 * 使用方法：此存储过程与PRC_SQL_TO_CSV配合使用，
 * 其中要在 MYDIR 代表的/oradata/ftp/ 目录下
 * 新建一个tablelist.tex文件，此文件是需要导出数据的表名
 */
v_tablename VARCHAR2(100);   --用来存表名
v_sql VARCHAR2(1000);        --用来存组合起来查询的sql语句
v_filename VARCHAR2(100);    --用来存放构造的文件名

--v_realName  VARCHAR2(100);    -- 用来存放汉字的表名

--V_DAY_MONTH_ID VARCHAR2(10); --日账期
V_MONTH_MONTH_ID VARCHAR2(8);--月账期

fhandle utl_file.file_type;   -- 文件句柄

BEGIN
  --获取日账期
   V_SQL := 'select to_char(sysdate,''yyyymm'') from dual';
  EXECUTE IMMEDIATE V_SQL
    INTO V_MONTH_MONTH_ID;
    
  V_MONTH_MONTH_ID := 201802; 
    
    --DBMS_OUTPUT.PUT_LINE(V_DAY_MONTH_ID);
  --获得tablelist的文件句柄---这里tablelist.txt 就存放中将要查询的表的表名，txt文件放在MYDIR代表的/oradata/ftp/目录下  
  fhandle := utl_file.fopen('MYDIR','tablelist.txt','r');
  LOOP
    BEGIN
      utl_file.get_line(fhandle,v_tablename);---从文件中一条一条读取表名，并存放在v_tablename中
      dbms_output.put_line(v_tablename);
      v_sql := 'select * from '||v_tablename||' t where t.month_id = '||V_MONTH_MONTH_ID; ---拼接查询语句
      dbms_output.put_line(v_sql);
      
      
      --select table_desc into v_realName from tab2bean_corresref  where table_view = v_tablename;
      
      v_filename := V_MONTH_MONTH_ID||'_'||v_tablename||'.csv';---构造导出的csv文件名:201803_VIEW_DATALIST_BD_PSNDOC.csv
      
      dbms_output.put_line(v_filename);
      EXCEPTION
      WHEN no_data_found then ----直到文件末尾，即找不到数据了再停止
        EXIT;
        END;
     PRC_SQL_TO_CSV(v_sql,'MYDIR',v_filename);----调用存储过程，将查询结果导出。这里核心部分
     END LOOP;
END;