如何编写批处理导出导入数据

<一>、批处理部分：

01_导出垃圾附件.bat

@echo **********************************************************************************************************
@echo           导出垃圾附件(须知)
@echo   1.该批处理是备份垃圾附件，先备份，然后才执行清理。
@echo   2.注意
@echo        输入盘符参数p_path的值应与批处理脚本的盘符一样,如输入E,批处理脚本的盘符也应在E盘,如e:\atm_script\
@echo        盘符:\atm_script\back        (存放导出垃圾附件的脚本)
@echo        盘符:\atm_script\data        (存放导出的垃圾附件及所产生的日志文件)
@echo        盘符:\atm_script\delete      (存放清理的垃圾附件的脚本)
@echo        盘符:\atm_script\01_导出垃圾附件.bat  (备份垃圾附件的批处理文件)
@echo        盘符:\atm_script\02_清理垃圾附件数据.bat (清理垃圾附件的批处理文件)
@echo        盘符:\atm_script\03_恢复垃圾附件.bat  (恢复垃圾附件的批处理文件,需要时,才执行此批处理)
@echo   3.执行批处理文件时,需要输入数据库用户密码(如用启\密码@SID)，才能连接上数据库
@echo        数据库连接串举例:atm01/HondaHsu2011@dw
@echo **********************************************************************************************************

@echo off
set /p p_user="请输入数据库用户名:"
set /p p_password="请输入数据库密码:"
set /p p_sid="请输入数据库连接串(TNS名):"
set /p p_path="请输入脚本所在的盘符(如e:\atm_script,则输入e):"

exp %p_user%/%p_password%@%p_sid% parfile=%p_path%:\atm_script\back\gd01.txt file=%p_path%:\atm_script\data\top_attachment_comtent_gd0930.dmp log=%p_path%:\atm_script\data\exp_top_att_comtent.log
exp %p_user%/%p_password%@%p_sid% parfile=%p_path%:\atm_script\back\gd02.txt file=%p_path%:\atm_script\data\top_attachment_extend_gd0930.dmp log=%p_path%:\atm_script\data\exp_top_att_extend.log
exp %p_user%/%p_password%@%p_sid% parfile=%p_path%:\atm_script\back\gd03.txt file=%p_path%:\atm_script\data\top_attachment_obj_rel_gd0930.dmp log=%p_path%:\atm_script\data\exp_top_att_ojb_rel.log
exp %p_user%/%p_password%@%p_sid% parfile=%p_path%:\atm_script\back\gd04.txt file=%p_path%:\atm_script\data\top_attachment_pro_rel_gd0930.dmp log=%p_path%:\atm_script\data\exp_top_att_pro_rel.log

pause

 

 

02_清理垃圾附件数据.bat

@echo **********************************************************************************************************
@echo                    清理垃圾附件(须知)
@echo      1.必须先执行"01_导出垃圾附件.bat"后，才能执行该清理垃圾附件的批处理
@echo      2.注意
@echo        输入盘符参数p_path的值应与批处理脚本的盘符一样,如输入E,批处理脚本的盘符也应在E盘,如e:\atm_script\
@echo        盘符:\atm_script\back                    (存放导出垃圾附件的脚本)
@echo        盘符:\atm_script\data                    (存放导出的垃圾附件及所产生的日志文件)
@echo        盘符:\atm_script\delete                  (存放清理的垃圾附件的脚本)
@echo        盘符:\atm_script\01_导出垃圾附件.bat        (备份垃圾附件的批处理文件)
@echo        盘符:\atm_script\02_清理垃圾附件数据.bat    (清理垃圾附件的批处理文件)
@echo        盘符:\atm_script\03_恢复垃圾附件.bat        (恢复垃圾附件的批处理文件,需要时,才执行此批处理)
@echo      3.执行批处理文件时,需要输入数据库用户密码(如用启\密码@SID)，才能连接上数据库
@echo        数据库连接串举例:atm01/HondaHsu2011@dw
@echo **********************************************************************************************************
@echo off
set /p p_user="请输入数据库用户名:"
set /p p_password="请输入数据库密码:"
set /p p_sid="请输入数据库连接串(TNS名):"
set /p p_path="请输入脚本所在的盘符(如e:\atm_script,则输入e):"
if exist %p_path%:\atm_script\data\top_attachment_comtent_gd0930.dmp (
  sqlplus %p_user%/%p_password%@%p_sid% @"%p_path%:\atm_script\delete\del_top_attachment_data.sql" > "%p_path%:\atm_script\data\delete_attachment_data.log"
  echo ****************************************************************************
  echo 已经完成清理,可查看日志 %p_path%:\atm_script\data\delete_attachment_data.log
) else (
  echo ****************************************************************************
  echo 没有找到备份的垃圾附件数据，请先备份垃圾附件，然后再清理垃圾附件
)
pause>nul

 

03_恢复垃圾附件.bat

 

@echo **********************************************************************************************************
@echo                    恢复垃圾附件(须知)
@echo      1.在需要恢复被清理掉的附件时，使用该批处理
@echo      2.注意
@echo        输入盘符参数p_path的值应与批处理脚本的盘符一样,如输入E,批处理脚本的盘符也应在E盘,如e:\atm_script\
@echo        盘符:\atm_script\back                    (存放导出垃圾附件的脚本)
@echo        盘符:\atm_script\data                    (存放导出的垃圾附件)
@echo        盘符:\atm_script\delete                  (存放清理的垃圾附件的脚本及所产生的日志文件)
@echo        盘符:\atm_script\01_导出垃圾附件.bat        (备份垃圾附件的批处理文件)
@echo        盘符:\atm_script\02_清理垃圾附件数据.bat    (清理垃圾附件的批处理文件)
@echo        盘符:\atm_script\03_恢复垃圾附件.bat        (恢复垃圾附件的批处理文件,需要时,才执行此批处理)
@echo      3.执行批处理文件时,需要输入数据库用户密码(如用启\密码@SID)，才能连接上数据库
@echo        数据库连接串举例:atm01/HondaHsu2011@dw
@echo **********************************************************************************************************

@echo off
set /p p_user="请输入数据库用户名:"
set /p p_password="请输入数据库密码:"
set /p p_sid="请输入数据库连接串(TNS名):"
set /p p_path="请输入脚本所在的盘符(如e:\atm_script,则输入e):"

if exist %p_path%:\atm_script\data\top_attachment_comtent_gd0930.dmp (
  imp %p_user%/%p_password%@%p_sid% file=%p_path%:\atm_script\data\top_attachment_comtent_gd0930.dmp log=%p_path%:\atm_script\data\imp_top_att_comtent.log grants=n rows=y ignore=y full=y
) else (
  echo ****************************************************************************
  echo 没有找到备份的垃圾附件数据:   %p_path%:\atm_script\data\top_attachment_comtent_gd0930.dmp
)

if exist %p_path%:\atm_script\data\top_attachment_extend_gd0930.dmp (
  imp %p_user%/%p_password%@%p_sid% file=%p_path%:\atm_script\data\top_attachment_extend_gd0930.dmp log=%p_path%:\atm_script\data\imp_top_att_extend.log grants=n rows=y ignore=y full=y
) else (
  echo ****************************************************************************
  echo 没有找到备份的垃圾附件数据:   %p_path%:\atm_script\data\top_attachment_extend_gd0930.dmp
)

if exist %p_path%:\atm_script\data\top_attachment_obj_rel_gd0930.dmp (
  imp %p_user%/%p_password%@%p_sid% file=%p_path%:\atm_script\data\top_attachment_obj_rel_gd0930.dmp log=%p_path%:\atm_script\data\imp_top_att_obj_rel.log grants=n rows=y ignore=y full=y
) else (
  echo ****************************************************************************
  echo 没有找到备份的垃圾附件数据:   %p_path%:\atm_script\data\top_attachment_obj_rel_gd0930.dmp
)

if exist %p_path%:\atm_script\data\top_attachment_pro_rel_gd0930.dmp (
  imp %p_user%/%p_password%@%p_sid% file=%p_path%:\atm_script\data\top_attachment_pro_rel_gd0930.dmp log=%p_path%:\atm_script\data\imp_top_att_pro_rel.log grants=n rows=y ignore=y full=y
  echo ****************************************************************************
  echo 已经完成导入,可查看日志 %p_path%:\atm_script\data\imp_top_att_********.log
) else (
  echo ****************************************************************************
  echo 没有找到备份的垃圾附件数据:   %p_path%:\atm_script\data\top_attachment_pro_rel_gd0930.dmp
)

pause

 

<二>、SQL脚本组装

• 备份脚本:

\atm_script\back\gd01.txt
#导出附件内容表中垃圾数据(不存在于附件信息表中的附件)
tables=(top_attachment_content) feedback=1000 buffer=102400000 TRIGGERS=N  query="where attachment_id  in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id))"

\atm_script\back\gd02.txt
#导出垃圾附件的扩展属性条件数据
tables=(top_attachment_extend) TRIGGERS=N query=" where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id))"

\atm_script\back\gd03.txt
#导出垃圾附件的关联对象条件
tables=(top_attachment_object_relation) TRIGGERS=N query="where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id))"


\atm_script\back\gd04.txt
#导出垃圾附件的关联属性条件
tables=(top_attachment_pro_relation) TRIGGERS=N query="where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id))"

• 清理脚本:

 

\atm_script\delete\del_top_attachment_data.sql
delete from top_attachment_extend nologging where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id));
delete from top_attachment_object_relation nologging where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id));
delete from top_attachment_pro_relation nologging where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id));
delete from top_attachment_content nologging where attachment_id in (select a.attachment_id from top_attachment_content a where not exists (select 1 from top_attachment b where a.attachment_id=b.attachment_id) );
commit;
exit;