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


    <一>、批处理部分:

    01_导出垃圾附件.bat

    @echo **********************************************************************************************************
    @echo           导出垃圾附件(须知)
    @echo   1.该批处理是备份垃圾附件,先备份,然后才执行清理。
    @echo   2.注意
    @echo        输入盘符参数p_path的值应与批处理脚本的盘符一样,如输入E,批处理脚本的盘符也应在E盘,如e:atm_script
    @echo        盘符:atm_scriptack        (存放导出垃圾附件的脚本)
    @echo        盘符:atm_scriptdata        (存放导出的垃圾附件及所产生的日志文件)
    @echo        盘符:atm_scriptdelete      (存放清理的垃圾附件的脚本)
    @echo        盘符:atm_script1_导出垃圾附件.bat  (备份垃圾附件的批处理文件)
    @echo        盘符:atm_script2_清理垃圾附件数据.bat (清理垃圾附件的批处理文件)
    @echo        盘符:atm_script3_恢复垃圾附件.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_scriptackgd01.txt file=%p_path%:atm_scriptdata	op_attachment_comtent_gd0930.dmp log=%p_path%:atm_scriptdataexp_top_att_comtent.log
    exp %p_user%/%p_password%@%p_sid% parfile=%p_path%:atm_scriptackgd02.txt file=%p_path%:atm_scriptdata	op_attachment_extend_gd0930.dmp log=%p_path%:atm_scriptdataexp_top_att_extend.log
    exp %p_user%/%p_password%@%p_sid% parfile=%p_path%:atm_scriptackgd03.txt file=%p_path%:atm_scriptdata	op_attachment_obj_rel_gd0930.dmp log=%p_path%:atm_scriptdataexp_top_att_ojb_rel.log
    exp %p_user%/%p_password%@%p_sid% parfile=%p_path%:atm_scriptackgd04.txt file=%p_path%:atm_scriptdata	op_attachment_pro_rel_gd0930.dmp log=%p_path%:atm_scriptdataexp_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_scriptack                    (存放导出垃圾附件的脚本)
    @echo        盘符:atm_scriptdata                    (存放导出的垃圾附件及所产生的日志文件)
    @echo        盘符:atm_scriptdelete                  (存放清理的垃圾附件的脚本)
    @echo        盘符:atm_script1_导出垃圾附件.bat        (备份垃圾附件的批处理文件)
    @echo        盘符:atm_script2_清理垃圾附件数据.bat    (清理垃圾附件的批处理文件)
    @echo        盘符:atm_script3_恢复垃圾附件.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_scriptdata	op_attachment_comtent_gd0930.dmp (
      sqlplus %p_user%/%p_password%@%p_sid% @"%p_path%:atm_scriptdeletedel_top_attachment_data.sql" > "%p_path%:atm_scriptdatadelete_attachment_data.log"
      echo ****************************************************************************
      echo 已经完成清理,可查看日志 %p_path%:atm_scriptdatadelete_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_scriptack                    (存放导出垃圾附件的脚本)
    @echo        盘符:atm_scriptdata                    (存放导出的垃圾附件)
    @echo        盘符:atm_scriptdelete                  (存放清理的垃圾附件的脚本及所产生的日志文件)
    @echo        盘符:atm_script1_导出垃圾附件.bat        (备份垃圾附件的批处理文件)
    @echo        盘符:atm_script2_清理垃圾附件数据.bat    (清理垃圾附件的批处理文件)
    @echo        盘符:atm_script3_恢复垃圾附件.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_scriptdata	op_attachment_comtent_gd0930.dmp (
      imp %p_user%/%p_password%@%p_sid% file=%p_path%:atm_scriptdata	op_attachment_comtent_gd0930.dmp log=%p_path%:atm_scriptdataimp_top_att_comtent.log grants=n rows=y ignore=y full=y
    ) else (
      echo ****************************************************************************
      echo 没有找到备份的垃圾附件数据:   %p_path%:atm_scriptdata	op_attachment_comtent_gd0930.dmp
    )
    
    if exist %p_path%:atm_scriptdata	op_attachment_extend_gd0930.dmp (
      imp %p_user%/%p_password%@%p_sid% file=%p_path%:atm_scriptdata	op_attachment_extend_gd0930.dmp log=%p_path%:atm_scriptdataimp_top_att_extend.log grants=n rows=y ignore=y full=y
    ) else (
      echo ****************************************************************************
      echo 没有找到备份的垃圾附件数据:   %p_path%:atm_scriptdata	op_attachment_extend_gd0930.dmp
    )
    
    if exist %p_path%:atm_scriptdata	op_attachment_obj_rel_gd0930.dmp (
      imp %p_user%/%p_password%@%p_sid% file=%p_path%:atm_scriptdata	op_attachment_obj_rel_gd0930.dmp log=%p_path%:atm_scriptdataimp_top_att_obj_rel.log grants=n rows=y ignore=y full=y
    ) else (
      echo ****************************************************************************
      echo 没有找到备份的垃圾附件数据:   %p_path%:atm_scriptdata	op_attachment_obj_rel_gd0930.dmp
    )
    
    if exist %p_path%:atm_scriptdata	op_attachment_pro_rel_gd0930.dmp (
      imp %p_user%/%p_password%@%p_sid% file=%p_path%:atm_scriptdata	op_attachment_pro_rel_gd0930.dmp log=%p_path%:atm_scriptdataimp_top_att_pro_rel.log grants=n rows=y ignore=y full=y
      echo ****************************************************************************
      echo 已经完成导入,可查看日志 %p_path%:atm_scriptdataimp_top_att_********.log
    ) else (
      echo ****************************************************************************
      echo 没有找到备份的垃圾附件数据:   %p_path%:atm_scriptdata	op_attachment_pro_rel_gd0930.dmp
    )
    
    pause

    <二>、SQL脚本组装

    • 备份脚本:
    atm_scriptackgd01.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_scriptackgd02.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_scriptackgd03.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_scriptackgd04.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_scriptdeletedel_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;
  • 相关阅读:
    17. 偏函数
    16. 装饰器
    vim详解
    linux用户管理sudo 磁盘分区
    linux用户管理
    linux文件与目录(四)
    linux特殊权限
    linux文件和目录(二)
    linux文件和目录
    配置网络
  • 原文地址:https://www.cnblogs.com/HondaHsu/p/3345970.html
Copyright © 2020-2023  润新知