利用存储过程将Oracle远程数据库数据以.csv的格式批量导出 |
您所在的位置：网站首页 › oracle数据库导出csv › 利用存储过程将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; |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |