oracle 如何在发生错误时退出 SQLPlus 中的脚本并返回 SQLPlus 提示，而无需断开连接或退出 SQLPlus？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11197088/
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 do I exit a script in SQLPlus when an error occurs and return to the SQLPlus prompt, without disconnecting or exiting SQLPlus?
提问by Thought
I have some scripts that get run often, always from within a connected SQLPlus session.
我有一些经常从连接的 SQLPlus 会话中运行的脚本。
I need a way to exit the script when an error occurs, withoutdisconnecting or exiting SQLPlus itself. 100% of the time, when an error occurs, the connected DBA will need to issue one or more commands into the session. 100% of the time, when an error occurs, there are other SQLPlus statements (and thus mustbe outsideof a BEGIN..END;) later on in the script that must notbe executed or serious problems could arise.
我需要一种在发生错误时退出脚本的方法，而无需断开连接或退出 SQLPlus 本身。在 100% 的情况下，当发生错误时，连接的 DBA 需要向会话发出一个或多个命令。的100％的时间，当发生错误时，也有其他的SQLPlus语句（并因此必须是外部的BEGIN..END;）以后，所述脚本不能被执行或可能出现严重问题。
NOTE: If you suggest WHENEVER SQLERROR EXITthen you didn't read the above text. That will disconnect and exit SQLPlus in addition tothe script, which is not acceptable behavior.
注意：如果您建议，WHENEVER SQLERROR EXIT那么您没有阅读上述文字。除了脚本之外，这将断开连接并退出 SQLPlus ，这是不可接受的行为。
采纳答案by Thought
I've found an interesting idea herewhich, when combined with spencer7593's answer, will get me selective sub-script calling, to which I can pass the PL/SQL output values. To wit:
我在这里发现了一个有趣的想法，当结合spencer7593的答案时，我将获得选择性子脚本调用，我可以将 PL/SQL 输出值传递给它。以机智：
VAR continue number;
EXEC :continue := 1;
BEGIN
SELECT some_bool_test() INTO :continue FROM dual;
END;
SET termout OFF
COLUMN script_name NEW_VALUE v_script_name
SELECT decode(:continue, 1, 'run_stuff.sql', 'skip.sql') script_name FROM dual;
SET termout ON
@&v_script_name :some_other_values
Where skip.sqlis an empty text file.
哪里skip.sql是空文本文件。
UPDATE:I've moved most of this into a RUN.SQLfile, where I pass in the boolean (0 or 1) as &1, the script name to call on success as &2, and then any other expected parameters to pass to the called script. Thus, it ends up looking something like this:
更新：我已经将大部分内容移到一个RUN.SQL文件中，在那里我传入布尔值（0 或 1） as &1，成功时调用的脚本名称 as &2，然后任何其他预期参数传递给被调用的脚本。因此，它最终看起来像这样：
VAR continue number;
EXEC :continue := 1;
BEGIN
SELECT some_bool_test() INTO :continue FROM dual;
END;
@run.sql :continue 'run_stuff.sql' :some_other_values
回答by spencer7593
It's not possible.
这是不可能的。
SQLPlus doesn't provide that level of control over the execution of a script.
SQLPlus 不提供对脚本执行的那种级别的控制。
Obviously, you'd need to AVOID using the WHENEVER SQLERROR EXIT ...command.
显然，您需要避免使用该WHENEVER SQLERROR EXIT ...命令。
It's possible to gain conditional control over which SQL statements do or do not get executed as a result of raised exceptions (errors) using PL/SQL. But that doesn't address SQLPlus commands (which cannot be executed from within a PL/SQL block.)
使用 PL/SQL 可以有条件地控制由于引发异常（错误）而执行或不执行哪些 SQL 语句。但这并没有解决 SQLPlus 命令（不能从 PL/SQL 块内执行。）
DECLARE
lb_continue BOOLEAN;
BEGIN
lb_continue := TRUE;
BEGIN
sql statement
EXCEPTION
WHEN OTHERS THEN
lb_continue = FALSE;
END;
IF lb_continue THEN
BEGIN
sql statements
EXCEPTION
WHEN OTHERS THEN
lb_continue := FALSE;
END;
END;
Of course, that approach has it's own limitations and issues. Any DDL statements would need to be called dynamically; the easiest way to do that is an EXECUTE IMMEDIATE statement.
当然，这种方法有其自身的局限性和问题。任何 DDL 语句都需要动态调用；最简单的方法是使用EXECUTE IMMEDIATE statement.
The biggest issue (in your case) is that it's not possible to execute SQLPlus commands from inside a PL/SQL block.
最大的问题（就您而言）是不可能从 PL/SQL 块内部执行 SQLPlus 命令。
回答by Alex Poole
You can't exit the script and stay in SQL*Plus, but you can stop executing things. It isn't pretty, but assuming you can modify the script to add the control flow then you can just about do this with bind variable.
您不能退出脚本并停留在 SQL*Plus 中，但可以停止执行操作。这并不漂亮，但假设您可以修改脚本以添加控制流，那么您可以使用绑定变量来执行此操作。
set serveroutput on
var flag char;
exec :flag := 'Y';
begin
if :flag != 'Y' then
raise program_error;
end if;
dbms_output.put_line('Doing some work');
/* Check for some error condition */
if 0 != 1 then
raise program_error;
end if;
/* Only reach this if earlier statements didn't fail
* but could wrap in another flag check if needed */
dbms_output.put_line('Doing some more work');
exception
when program_error then
dbms_output.put_line(sqlerrm);
:flag := 'N';
when others then
/* Real exception handling, obviously */
dbms_output.put_line(sqlerrm);
:flag := 'N';
end;
/
-- DML only does anything if flag stayed Y
select sysdate from dual
where :flag = 'Y';
-- Optional status message at the end of the script, for DBA info
set feedback off
set head off
select 'Something went wrong' from dual where :flag != 'Y';
set feedback on
set head on
When executed:
执行时：
SQL> @script
PL/SQL procedure successfully completed.
Doing some work
ORA-06501: PL/SQL: program error
PL/SQL procedure successfully completed.
no rows selected
Something went wrong
SQL>
Any PL/SQL blocks in the script can check the flag status at the start, and raise program_error(just as a handy pre-defined exception) to jump back out. Anything that errors inside a PL/SQL block can update the bind variable flag, either directly or in an exception handler. And any non-PL/SQL DML can have an additional whereclause to check the flag status, so if it's been set to Nby the time that statement is reached, no work is done. (For an insertI guess that would mean not using the valuesform).
脚本中的任何 PL/SQL 块都可以在开始时检查标志状态，并引发program_error（就像一个方便的预定义异常）跳回。PL/SQL 块内的任何错误都可以直接或在异常处理程序中更新绑定变量标志。并且任何非 PL/SQL DML 都可以有一个额外的where子句来检查标志状态，因此如果N在到达该语句时已将其设置为，则不会进行任何工作。（对于insert我想这意味着不使用values表单）。
What this can't do is deal with any errors fromplain SQL statements, but I'm not sure if that's an issue. If it is then those might need to be changed to dynamic SQL inside a PL/SQL block.
这不能处理来自普通 SQL 语句的任何错误，但我不确定这是否是一个问题。如果是，则可能需要将它们更改为 PL/SQL 块内的动态 SQL。
回答by Anselme
I know its old, but these two instructions at the very begining of the SQL script do the work:
我知道它很旧，但是 SQL 脚本开头的这两条指令可以完成工作：
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
当 SQLERROR 退出失败回滚时
WHENEVER OSERROR EXIT FAILURE ROLLBACK
每当 OSERROR 退出失败回滚时
