oracle数据多表多文件导出与压缩

最新推荐文章于 2025-05-29 11:19:37 发布

原创最新推荐文章于 2025-05-29 11:19:37 发布 · 1.8k 阅读

2 ·

CC 4.0 BY-SA版权

批处理流程

进行基本文件操作，比如删除旧文件，导出处理日志等
调用main.sql生成一个数据库表导出的sql脚本export.sql
调用步骤2中生成的sql脚本export.sql，输出oracle数据到文件系统
使用7z压缩文件

批处理文件内容

::-------------------------
:: by tangtao 2018.03.05
::-------------------------
::
::for语法  http://blog.youkuaiyun.com/xhhjin/article/details/7373524
::FOR [参数] %变量名 IN (相关文件或命令) DO 执行的命令
::[参数] 		可以是/d /l /r /f，此处未使用
::%变量名		表示每次循环的变量，在cmd下一个百分号，在bat下必须为两个百分号
::(相关文件或命令) 	要循环的内容
::DO 执行的命令		对于每次循环执行的命令
::%%~ni			表示获取变量i的值，该值只包含到文件名，比如%%i为d:\test.txt，则%%~ni为test
::%%~pi			表示获取变量i的值，该值只包含到路径，比如%%i为d:\test.txt，则%%~pi为d:\
::
::7z语法  http://blog.youkuaiyun.com/earbao/article/details/51382534
::7za <命令> [<参数>...] <压缩文件> [<原文件>...]
::命令 a	添加文件到压缩文件
::参数 sdel	压缩后删除原文件
::
echo *******************By TangTao*************************
::导出地址
set exp_path=D:\car_inst\loan_exp_path\
::基础地址
set base_path=D:\car_inst\
::删除旧文件
rd /s /q %exp_path%
mkdir %exp_path%
::导出csv
(sqlplus car_inst/password @%base_path%main.sql) > %base_path%car_inst_exp.log
::压缩成zip文件
(FOR %%i IN (%exp_path%*.csv) DO %base_path%7za.exe a -sdel "%exp_path%%%~ni.zip" "%%i") > %base_path%car_inst_7z.log

main.sql文件

sql文件中带有中文，因为要在cmd环境下执行，因此该sql字符集要与cmd的字符集保持一致，才能正确识别中文。

main.sql主要作用是，select查询数据表，将查询到的结果填充到字符串中，再使用spool将这些字符串输出到export.sql中

注意spool中的设置，比如

echo off 关闭反显；linessize 32767 设置行大小，即每行可以输出的字符数；其他内容请查询spool 设置。

在下面sql中使用了隐式游标i来输出多个文件。

set echo off
set feedback off
set heading off
set linesize 32767
set serveroutput on
set termout off
set trimout off
set trimspool on
set timing off
set verify off
set preformat off
spool D:\car_inst\export.sql
DECLARE
  v_today VARCHAR2(10);
BEGIN
  SELECT to_char(SYSDATE, 'yyyymmdd') INTO v_today FROM dual;  
  dbms_output.enable(1000000);
  dbms_output.put_line('set colsep , echo off embedded on feedback off heading on headsep off linesize 32767 pagesize 0 serveroutput off term off termout off timing off trimout on trimspool on verify off');
  FOR i IN (SELECT DISTINCT br.br_id FROM ctp_br_view br WHERE br.br_id <> 02680) LOOP  
	dbms_output.put_line('spool D:\car_inst\loan_exp_path\' || i.br_id || '.csv'); 
	dbms_output.put_line('SELECT * FROM CAR_INST_LOAN_INFO t WHERE t.所属支行号 = ''' || i.br_id || ''';');  
	dbms_output.put_line('spool off');
  END LOOP;
  dbms_output.put_line('/');
  dbms_output.put_line('exit');
END;
/
spool off
@D:\car_inst\export.sql
/
exit

export.sql

export.sql是根据数据库结果生成的，下面是个输出的样例：

set colsep , echo off embedded on feedback off heading on headsep off linesize 32767 pagesize 0 serveroutput off term off termout off timing off trimout on trimspool on verify off
spool D:\car_inst\loan_exp_path\02020.csv
SELECT * FROM CAR_INST_LOAN_INFO t WHERE t.所属支行号 = '02020';
spool off
spool D:\car_inst\loan_exp_path\02100.csv
SELECT * FROM CAR_INST_LOAN_INFO t WHERE t.所属支行号 = '02100';
spool off
/
exit

每一个spool d:\car_inst\loan_exp_path\*.csv就对应输出一个csv文件。

注意：这个地方输出的csv文件是采用空白补齐的，并不是严格意义上的csv文件，其输出格式为：

col1 col2 col3

-----------------------

1, 2, 3

真正意义上的csv格式为

col1,col2,col3

1,2,3

要实现这种格式就需要拼接每一列，将所有列合并成一列用逗号分隔，但这样做输出效率变低，输出时间更长，举例如下：

set colsep ,
set feedback off
set heading off
set trimout on
spool D:\DBoracle\lfc.csv
select '"' || user_name || '","' || user_age || '","' || user_card || '","' || user_sex || '","' || user_addres || '","' || user_tel || '"'  from lfc_xinxi_tbl;
spool off
exit