共享利用Python脚本解决Oracle的SQL脚本@@用法

Windows批处理脚本

set ForkPath=%~dp0

echo %ForkPath%

cd %ForkPath%

sqlplus xxx/xxx@xxx @1/demo1.sql

pause

1/demo1.sql

spool demo1.log

@@demo1_1.sql

@@demo1_2.sql

spool off

exit

1/demo1_1.sql

select 'demo1_1' from dual;

1/demo1_2.sql

select 'demo1_2' from dual;

rewrite_o2y.py

import os

def get_all_files(root_path):

    """

    递归获取指定路径下所有文件的绝对路径

    :param root_path: 需要递归的根目录路径

    :return: 文件绝对路径列表

    """

    # 初始化文件列表

    all_files = []

    # 获取根目录下的所有文件和目录

    for dir_path, dir_names, file_names in os.walk(root_path):

        # 遍历文件

        for file_name in file_names:

            # 获取文件的绝对路径并添加到列表中

            file_path = os.path.join(dir_path, file_name)

            all_files.append(file_path)

    return all_files

if __name__ == '__main__':

    # 获取脚本所在路径

    current_path = os.path.dirname(os.path.abspath(__file__))

    print("当前目录是:", current_path)

    filenames = get_all_files(current_path)

    for filename in filenames:

        if filename.endswith(".sql"):

            filename_dirname = os.path.dirname(filename)

            filename_basename = os.path.basename(filename)

            filename_orig = filename + ".orig"

            print("\n当前处理: "+filename)

            # 读取所有行

            with open(filename, 'r') as file:

                lines = file.readlines()

            need_rename_file = False

            newlines = []

            for line in lines:

                # 替换以@@为@+绝对路径

                if line.startswith("@@"):

                    newlines.append(line.replace("@@","@" + filename_dirname + "\\", 1))

                    need_rename_file = True

                else:

                    newlines.append(line)

            for newline in newlines:

                print(newline, end="")

            if need_rename_file:

                # 重命名文件

                os.rename(filename, filename_orig)

                # 写回文件

                with open(filename, 'w') as file:

                    file.writelines(newlines)

            print("\n处理完毕: " + filename)

cd %Oracle示例所在的目录%

python rewrite_o2y.py

D:\Workspace\Demo\python_demo>python rewrite_o2y.py

当前目录是: D:\Workspace\Demo\python_demo

当前处理: D:\Workspace\Demo\python_demo\demo2\1\demo1.sql

spool demo1.log

@D:\Workspace\Demo\python_demo\demo2\1\demo1_1.sql

@D:\Workspace\Demo\python_demo\demo2\1\demo1_2.sql

spool off

exit

处理完毕: D:\Workspace\Demo\python_demo\demo2\1\demo1.sql

当前处理: D:\Workspace\Demo\python_demo\demo2\1\demo1_1.sql

select 'demo1_1' from dual;

处理完毕: D:\Workspace\Demo\python_demo\demo2\1\demo1_1.sql

当前处理: D:\Workspace\Demo\python_demo\demo2\1\demo1_2.sql

select 'demo1_2' from dual;

处理完毕: D:\Workspace\Demo\python_demo\demo2\1\demo1_2.sql

1/demo1.sql

spool demo1.log

@D:\Workspace\Demo\python_demo\demo2\1\demo1_1.sql

@D:\Workspace\Demo\python_demo\demo2\1\demo1_2.sql

spool off

exit

1/demo1.sql.orig

spool demo1.log

@@demo1_1.sql

@@demo1_2.sql

spool off

exit

Windows批处理脚本

set ForkPath=%~dp0

echo %ForkPath%

cd %ForkPath%

yasql xxx/xxx@xx.xx.x.x:xxxx -f 1/demo1.sql

pause