简介
Oracle客户端sql*plus正在执行某SQL脚本，当该SQL脚本调用同级目录的SQL脚本，会使用@@，请看详情的Oracle示例。崖山数据库23.2.x.100已支持@@用法，但是对于以前的版本，可以用Python脚本进行批量重写，对于存在@@调用的SQL文件，用SQL文件所在的绝对路径进行替换。
详情
Oracle示例
Oracle示例是在Windows上通过批处理脚本执行。
Windows批处理脚本
set ForkPath=%~dp0
echo %ForkPath%
cd %ForkPath%
sqlplus xxx/xxx@xxx @1/demo1.sql
pause
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
入口SQL脚本(1/demo1.sql)
1/demo1.sql
spool demo1.log
@@demo1_1.sql
@@demo1_2.sql
spool off
exit
1
2
3
4
5
6
7
8
9
10
11
入口SQL脚本(1/demo1.sql)调用同级目录的SQL脚本(1/demo1_1.sql和demo1_2)
1/demo1_1.sql
select 'demo1_1' from dual;
1
2
3
1/demo1_2.sql
select 'demo1_2' from dual;
1
2
3
Python脚本
Windows的Python脚本如下，如果运行在Linux上，请调整这一行newlines.append(line.replace(“@@”,“@” + filename_dirname + “\”, 1))的’\‘为’/'即可
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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
用法
cd %Oracle示例所在的目录%
python rewrite_o2y.py
1
2
3
运行
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
结果
入口SQL脚本(1/demo1.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
2
3
4
5
6
7
8
9
10
11
原入口SQL脚本(1/demo1.sql)被重命名为(1/demo1.sql.orig)
1/demo1.sql.orig
spool demo1.log
@@demo1_1.sql
@@demo1_2.sql
spool off
exit
1
2
3
4
5
6
7
8
9
10
11
其他SQL文件都保持不变，然后调整批处理脚本即可在YashanDB运行
Windows批处理脚本
set ForkPath=%~dp0
echo %ForkPath%
cd %ForkPath%
yasql xxx/xxx@xx.xx.x.x:xxxx -f 1/demo1.sql
pause
————————————————
