自定义脚本读取文本文件信息导出dmp

 通过自定义文本及条件导出表

::自动导出数据脚本
::此脚本用来导出一个数据表为一个dmp文件

::获取当天日期，格式YYYYMMDD
set "Today=%date:~,4%%date:~5,2%%date:~8,2%"

::如果存在该目录先删除该目录，以支持重跑
if exist %Today% rmdir /s /q %Today%

::数据库连接字符串
set Connect_String=brhfr/brhfr@oraORSS

md %Today%

set TableList=

if exist createTable.sql del createTable.sql

if exist dropTable.sql del dropTable.sql

setlocal enabledelayedexpansion
echo 1
for /F "tokens=1-3 delims=#" %%a IN ('type tablelst.txt') do (
    echo %%a
	if "%%a"=="table" echo create table temp_%%b as select * from %%b;>%Today%/create%%bTable.sql
	if "%%a"=="sql"   echo create table temp_%%b as %%c;>%Today%/create%%bTable.sql
     echo quit>>%Today%/create%%bTable.sql
	 sqlplus !Connect_String! @%Today%/create%%bTable.sql
	 
	 exp !Connect_String! tables=temp_%%b direct=Y buffer=40960 file=%Today%\temp_%%b.dmp log=%Today%\temp_%%b.log
 
	 echo drop table temp_%%b;>%Today%/drop%%bTable.sql
	 echo quit>>%Today%/drop%%bTable.sql
	 sqlplus !Connect_String! @%Today%/drop%%bTable.sql
)

配置文件tablelst.txt

第一列sql或者table

第二列为表明

第三列为导出条件

sql#ZSLP_CTIS_ITC_HIS#SELECT * FROM ZSLP_CTIS_ITC_HIS WHERE APPG_DATE = 20150630

table#BOCS_CUSM#
table#BOCS_CUSVAA#
table#BOCS_CUSVCC#
table#BOCS_INVE#
table#BOCS_XREF#
table#BOCS_BORM#
table#BOCS_ACSN#
table#BOCS_INVM#
table#BOCS_INVS#
sql#CTIS_IMC#SELECT * FROM CTIS_IMC WHERE APPG_DATE >= 20150630
sql#ZSLP_CTIS_IMC_HIS#SELECT * FROM ZSLP_CTIS_IMC_HIS WHERE APPG_DATE >= 20150630
table#CTIS_ISC#
sql#ZSLP_CTIS_ISC_HIS#SELECT * FROM ZSLP_CTIS_ISC_HIS WHERE APPG_DATE >= 20150630
sql#CTIS_ITC#SELECT * FROM CTIS_ITC WHERE APPG_DATE >= 20150630
sql#ZSLP_CTIS_ITC_HIS#SELECT * FROM ZSLP_CTIS_ITC_HIS WHERE APPG_DATE >= 20150630
table#ZSLP_CUSINFO#
table#ZSLP_CUS_EMAIL_MAP#


sql#CTIS_IMC#SELECT * FROM CTIS_IMC WHERE APPG_DATE <= 20150331 and appg_date>=20150101
sql#ZSLP_CTIS_IMC_HIS#SELECT * FROM ZSLP_CTIS_IMC_HIS WHERE APPG_DATE <= 20150331 and appg_date>=20150101
table#CTIS_ISC#
sql#ZSLP_CTIS_ISC_HIS#SELECT * FROM ZSLP_CTIS_ISC_HIS WHERE APPG_DATE <= 20150331 and appg_date>=20150101
sql#CTIS_ITC#SELECT * FROM CTIS_ITC WHERE APPG_DATE <= 20150331 and appg_date>=20150101
sql#ZSLP_CTIS_ITC_HIS#SELECT * FROM ZSLP_CTIS_ITC_HIS WHERE APPG_DATE <= 20150331 and appg_date>=20150101