oracle PL/SQL：是否有完全停止脚本执行的指令？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8908210/
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
PL/SQL: is there an instruction to completely stop the script execution?
提问by Frosty Z
I'm trying to do some checkings on a DB schema at the beginning of a PL/SQL script.
我试图在 PL/SQL 脚本的开头对 DB 模式进行一些检查。
If the checkings give unsuccessful results, I want to stop the script, to prevent the next instructions to be executed.
如果检查给出不成功的结果，我想停止脚本，以防止执行下一条指令。
I got something like this
我得到了这样的东西
-- 1st line of PL/SQL script
DECLARE
SOME_COUNT INTEGER;
BEGIN
SELECT COUNT(*) INTO SOME_COUNT FROM SOME_TABLE WHERE <SOME_CONDITIONS>;
IF (SOME_COUNT > 0) THEN
DBMS_OUTPUT.PUT_LINE('Test failed, I don''want the rest of the script'
|| ' to be executed.');
--EXIT or something like that?... <= STOP EXECUTION HERE
END IF;
END;
/
-- OTHER SQL INSTRUCTIONS...
ALTER TABLE SOME_TABLE ...
I'm looking for the instruction(s) allowing to do the "STOP EXECUTION HERE".
我正在寻找允许执行“ STOP EXECUTION HERE”的说明。
采纳答案by codenheim
The question shows a multi-statement batch script. RAISE_APPLICATION_ERROR() only exits out of a PL/SQL block (sub-program), not out of the overall script (as pointed out by Justin) so it will continue with statements that follow.
该问题显示了一个多语句批处理脚本。RAISE_APPLICATION_ERROR() 仅退出 PL/SQL 块（子程序），而不退出整个脚本（如 Justin 所指出的），因此它将继续执行后面的语句。
For batch scripts, it is best to use WHENEVER SQLERROR EXIT. Yes, it is a SQLPlus directive, not standard SQL, but is fairly portable; most popular Oracle tools that support scripts support this directive, at least partially. The following example works in SQLPlus, SQL*Developer, Toad, SQLsmith and possibly others, and demonstrates the problem, if you comment the line out.
对于批处理脚本，最好使用 WHENEVER SQLERROR EXIT。是的，它是一个 SQL Plus 指令，不是标准 SQL，但相当可移植；支持脚本的最流行的 Oracle 工具至少部分支持此指令。下面的示例适用于 SQLPlus、SQL*Developer、Toad、SQLsmith 和其他可能的应用程序，并演示了问题（如果您注释掉该行）。
set serveroutput on
-- Without this line, things keep going
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
BEGIN
IF (1 > 0) THEN
DBMS_OUTPUT.PUT_LINE('First thing');
RAISE_APPLICATION_ERROR(-20000, 'Test failed'); -- not enough
END IF;
END;
/
-- This will execute if you remove WHEN SQLERROR.., so RAISE_APPLICATION_ERROR is not enough
BEGIN
DBMS_OUTPUT.PUT_LINE('Second thing - Executes anyway');
END;
/
If you remove the WHEN SQLERROR, the script will continue and execute the 2nd block, etc. which is exactly what the question asks to avoid.
如果删除 WHEN SQLERROR，脚本将继续执行第二个块，等等，这正是问题要避免的。
The advantage, in this instance, of graphical tools that emulate sqlplus, is that they really stop the script and don't submit the remainder of the script to the command shell as shell commands, which is what happens if you paste scripts into SQLPlus running in a console window. SQLPlus may exit on error, but the remaining buffered commands will then be handled by the OS shell, which is a bit messy and potentially risky, if you had shell commands in the comments (which isn't unheard of). With SQLPlus, it is always best to connect, and then execute the script, or pass it in the < start > command line argument (sqlplus scott/tiger @foo.sql) to avoid this.
在这种情况下，模拟 sqlplus 的图形工具的优点是它们确实停止了脚本，并且不会将脚本的其余部分作为 shell 命令提交到命令 shell，如果将脚本粘贴到 SQL Plus 中会发生这种情况在控制台窗口中运行。SQLPlus 可能会在出错时退出，但剩余的缓冲命令将由 OS shell 处理，如果您在注释中有 shell 命令（这并非闻所未闻），这会有点混乱且可能存在风险。使用SQLPlus，总是最好先连接，然后执行脚本，或者在<start>命令行参数（sqlplus scott/tiger @foo.sql）中传递，以避免这种情况。
回答by tbone
If you don't want to raise an exception, you could try something like (untested):
如果你不想引发异常，你可以尝试类似（未经测试）：
declare
SOME_COUNT INTEGER;
begin
SELECT COUNT(*) INTO SOME_COUNT FROM SOME_TABLE WHERE <SOME_CONDITIONS>;
IF (SOME_COUNT > 0) THEN
DBMS_OUTPUT.PUT_LINE('Test failed, I don''want the rest of the script'
|| ' to be executed.');
goto end_proc;
END IF;
-- A bunch of great code here
<<end_proc>>
null; -- this could be a commit or other lines of code
end;
Some people hate any GOTO statements as they can lead to spaghetti code if abused, but in simple situations like this (again, assuming you don't want to raise an exception) they work well imo.
有些人讨厌任何 GOTO 语句，因为如果滥用它们会导致意大利面条式代码，但在这样的简单情况下（再次假设您不想引发异常），它们在imo中运行良好。
回答by Frosty Z
A few more seconds of googling gave me the answer: the function RAISE_APPLICATION_ERROR()
几秒钟的谷歌搜索给了我答案：函数 RAISE_APPLICATION_ERROR()
IF (SOME_COUNT > 0) THEN
RAISE_APPLICATION_ERROR(-20000, 'Test failed');
END IF;
The user-defined error code should be between -20000 and -20999.
用户定义的错误代码应介于 -20000 和 -20999 之间。
Details on Oracle doc here: http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/07_errs.htm#877(section Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR)
此处有关 Oracle 文档的详细信息：http: //docs.oracle.com/cd/B10501_01/appdev.920/a96624/07_errs.htm#877（定义您自己的错误消息部分：过程 RAISE_APPLICATION_ERROR）
回答by Jonny
Rather than throw an application error, it's much simpler to just use the RETURNkeywordwhich exits the current PL/SQL block very smoothly.
与抛出应用程序错误相比，使用RETURN关键字非常顺利地退出当前 PL/SQL 块要简单得多。
Just make sure you do a DBMS_OUTPUT.PUT_LINE('Exited because <error')before it to provide the user with a nice message of why you're exiting of course!
只要确保你DBMS_OUTPUT.PUT_LINE('Exited because <error')在它之前做一个给用户一个很好的信息，当然你为什么要退出！
