SQL 想在SQLPLUS中一次性运行多个SQL脚本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22680829/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
want to run multiple SQL script file in one go with in SQLPLUS
提问by rahul jain
I have to run multiple SQL script file in one go.
我必须一次运行多个 SQL 脚本文件。
Like every time i have to write command in SQLPLUS
就像每次我必须在 SQLPLUS 中编写命令一样
SQL>@d:\a.txt SQL>@d:\a2.txt SQL>@d:\a3.txt SQL>@d:\a4.txt
is there any way put all file in one folder & run all script file in one go without missing any single file like @d:\final.txtor @d\final.bat
有没有办法把所有的文件一个文件夹中运行和一气呵成的所有脚本文件不丢失任何单一文件中像 @d:\final.txt或@d\final.bat
回答by a_horse_with_no_name
There is no single SQL*Plus command to do that, but you can create a single script that calls all the others:
没有单个 SQL*Plus 命令可以做到这一点，但您可以创建一个调用所有其他脚本的脚本：
Put the following into a batch file
将以下内容放入批处理文件中
@echo off
echo.>"%~dp0all.sql"
for %%i in ("%~dp0"*.sql) do echo @"%%~fi" >> "%~dp0all.sql"
When you run that batch file it will create a new script named all.sqlin the same directory where the batch file is located. It will look for all files with the extension .sqlin the same directory where the batch file is located.
当您运行该批处理文件时，它将all.sql在批处理文件所在的同一目录中创建一个名为的新脚本。它将.sql在批处理文件所在的同一目录中查找具有扩展名的所有文件。
You can then run all scripts by using sqlplus user/pwd @all.sql(or extend the batch file to call sqlplusafter creating the all.sqlscript)
然后您可以通过使用运行所有脚本sqlplus user/pwd @all.sql（或扩展批处理文件sqlplus在创建all.sql脚本后调用）
回答by Eric
It might be worth the time to write a shell script that runs multiple files.
花时间编写一个运行多个文件的 shell 脚本可能是值得的。
#!/bin/ksh
sqlplus user/password@instance <<EOF
@a.txt
@a1.txt
exit
EOF
For more on the syntax, look into Here Document
有关语法的更多信息，请查看Here Document
回答by Manoj
Some tricks and command can help you to generate master.sql file and you can run from that location.
一些技巧和命令可以帮助您生成 master.sql 文件，您可以从该位置运行。
c:\direcotory_location\dir *.sql /-t /b >master.sql
Go to the parent directory open master.sql open using notepad++ remove master.sql line and use regular expression to replace
进入父目录 open master.sql open using notepad++ 去掉 master.sql 行，用正则表达式替换
\n with \n @
go to cmd From cmd
从 cmd 转到 cmd
C:\root_directory\sqlplus user/password @master.sql
I find this process very convenient if i have 30 to 40 scripts placed in a single directory.
如果我将 30 到 40 个脚本放在一个目录中，我发现这个过程非常方便。
回答by carmellose
If you're using gnu linux, you could use process substitution:
如果您使用的是 gnu linux，则可以使用进程替换：
sqlplus USERNAME/PASSWORD@DOMAIN < <(cat a.txt a2.txt a3.txt a4.txt)
# ... or a for loop on input files, inside the process substitution
Alternatively, you can create a .pdcfile and list your sql scripts:
或者，您可以创建一个.pdc文件并列出您的 sql 脚本：
-- pdc file
@a.txt;
@a2.txt;
@a3.txt;
@a4.txt;
and call sql plus:
并调用 sql plus：
sqlplus USERNAME/PASSWORD@DOMAIN < my_scripts.pdc
回答by AmolG
For Windows try copy /b *.sql +x final.sql
对于 Windows 尝试复制 /b *.sql +x final.sql
sqlplus user/password @final.sql
sqlplus 用户/密码@final.sql
回答by Cyva
here is similar solution but you do not have to iterate and to have special formated an sql file names. You compose an one sql file and run it once.
这是类似的解决方案，但您不必迭代并具有特殊格式的 sql 文件名。您编写一个 sql 文件并运行一次。
cat table_animal.sql > /tmp/temp.sql
cat table_horse.sql >> /tmp/temp.sql
cat table_fish.sql >> /tmp/temp.sql
sqlplus USERNAME/PASSWORD@DOMAIN @/tmp/temp.sql
回答by jiboOne
Use *.PDCextension file like this
*.PDC像这样使用扩展文件
install.pdcfile content
install.pdc文件内容
whenever sqlerror exit sql.sqlcode
prompt started!
prompt 1.executing script 1
@@install/01.script_1.sql
prompt 2.executing script 2
@@install/02.script_2.sql
prompt 3.executing script 3
@@install/03.script_3.sql
prompt finished!
where @@install/points in which directory is the SQL script located
where@@install/指向 SQL 脚本所在的目录
回答by vinay kumar reddy
Special Thanks to Joseph Torre
特别感谢Joseph Torre
sqlplus login/password@server @filename
