如何在 Windows 命令脚本中使用 sql*plus 来控制流程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10485443/
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
How to use sql*plus in Windows command script to control flow?
提问by Cade Roux
I'm trying to use sql*plus to control a small Windows command script.
我正在尝试使用 sql*plus 来控制一个小的 Windows 命令脚本。
Basically, I want to execute some PL/SQL (perhaps select from a view or table or execute a function) which shows me the status of some rows in the database, and then depending upon the state of the rows, perform some Windows commands.
基本上，我想执行一些 PL/SQL（可能从视图或表中选择或执行一个函数），它向我显示数据库中某些行的状态，然后根据行的状态，执行一些 Windows 命令。
My problem is how to get the results back into the command script.
我的问题是如何将结果返回到命令脚本中。
sqlplus user/password@server @script.sql
IF <CONDITIONAL HERE BASED on script.sql results> GOTO :runprocess
REM log and email that process had to be skipped
EXIT
:runprocess
REM run various Windows service commands
采纳答案by Cade Roux
This is what I ended up using.
这就是我最终使用的。
My .cmd script:
我的 .cmd 脚本：
@ECHO OFF
ECHO Checking Oracle...
for /f %%i in ('sqlplus -s user/password@database @script.sql') do @set count=%%i
echo %count%
IF %count% GTR 0 GOTO :skipped
GOTO :runprocess
Where script.sql:
其中script.sql：
SELECT COUNT(*)
FROM table
WHERE criteria = 1;
exit
回答by René Nyffenegger
I'd probably write the script (or the conditional, depending on the requirements) from the called script.sqlitself.
我可能会从被调用者script.sql本身编写脚本（或条件，取决于要求）。
For example, the following script.sqlcreates a .bat filewindows_commands.bat:
例如，以下script.sql创建一个.bat 文件windows_commands.bat：
set feedback off
set echo off
set trimspool on
set termout off
set serveroutput on size 100000 format wrapped
set lines 500
set pages 0
-- create the bat file to be executed later:
spool windows_commands.bat
declare
c number;
begin
select count(*) into c from dual;
-- depending on a conditional, write the stuff to be executed into the
-- bat file (windows_commands.bat)
if c = 1 then
dbms_output.put_line('@echo everthing ok with dual');
else
dbms_output.put_line('@echo something terribly wrong with dual');
end if;
end;
/
spool off
exit
You can then call script.sqlfrom yet another .bat filelike so:
然后，您可以script.sql从另一个.bat 文件调用，如下所示：
@rem create oracle session, call script.sql
sqlplus %user%/%password%@%db% @script.sql
@rem script.sql has created windows_commands.bat.
@rem call this newly created bat file:
call windows_commands.bat
回答by paulsm4
I would strongly encourage you to notuse .bat files. You've got lots of other alternatives: C/C++ or VB, Windows scriptingor Powershell, or even free downloads like Perlor Bash.
我强烈建议您不要使用 .bat 文件。您还有很多其他选择：C/C++ 或 VB、Windows 脚本或Powershell，甚至是Perl或Bash 之类的免费下载。
But here's one example of returning error codes in .bat files:
但这是在 .bat 文件中返回错误代码的一个示例：
But please do look at some of the links I gave above. Avoiding .bat files will make it easier for you, and make it easier to maintain in the future.
但是请务必查看我上面给出的一些链接。避免使用 .bat 文件将使您更轻松，并且将来更易于维护。
IMHO ...
恕我直言 ...
回答by kevinsky
I do something like this by creating a .bat file which does the windows stuff and calling sql scripts as needed. Use SQL to spool your results to a text file which you can read.
我通过创建一个 .bat 文件来执行类似的操作，该文件执行 Windows 操作并根据需要调用 sql 脚本。使用 SQL 将您的结果假脱机到您可以阅读的文本文件中。
...dos commands here
sqlplus /nolog @C:\Dump\DropRecreateUsers.sql
sqlplus /nolog @C:\Dump\Cleanup.sql
...dos commands
...这里的dos命令
sqlplus /nolog @C:\Dump\DropRecreateUsers.sql
sqlplus /nolog @C:\Dump\Cleanup.sql
...dos 命令
In the sql use this command spool C:\yourResults.txt or for more sophisticated usages create a procedure, which, when called, writes the results to a text file using UTL_FILE
在 sql 中使用此命令 spool C:\yourResults.txt 或更复杂的用法创建一个过程，该过程在调用时使用 UTL_FILE 将结果写入文本文件
回答by Bjarte Brandt
I encourage you to take a look at the two scripts included in the Oracle XE for backup and restore. These scripts have taught me a lot how to handle batch-scripting and Oracle on the Windows platform.
我鼓励您查看 Oracle XE 中包含的用于备份和恢复的两个脚本。这些脚本教会了我很多如何在 Windows 平台上处理批处理脚本和 Oracle。
- C:\oraclexe\app\oracle\product\11.2.0\server\bin\Backup.bat
- C:\oraclexe\app\oracle\product\11.2.0\server\bin\Restore.bat
- C:\oraclex\app\oracle\product\11.2.0\server\bin\Backup.bat
- C:\oraclex\app\oracle\product\11.2.0\server\bin\Restore.bat
回答by Rizwan Basheer
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
连接到：Oracle 数据库 11g 企业版 11.2.0.1.0 版 - 具有分区、OLAP、数据挖掘和实际应用程序测试选项的 64 位生产
SQL> @f:\testa.txt
