oracle 发生错误时退出执行 PL/SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21786763/
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
Exit execution when error occurs PL/SQL
提问by user3311382
I would like to know, how can I exit the execution when an error occurs. In Microsoft SQL Server there is a RETURNclause, which does the work. But I would like to know similar functionality in Oracle. I am using Oracle Sql Developer. Here is the script I am using:
我想知道，发生错误时如何退出执行。在 Microsoft SQL Server 中，有一个RETURN子句可以完成这项工作。但我想知道 Oracle 中的类似功能。我正在使用 Oracle Sql Developer。这是我正在使用的脚本：
First block throws error due to Unique Key Violation, even though it throws error the execution goes to next block and executes the insert statement. I want to end the execution or exit at first block of code itself. Please help me to write the code.
由于唯一键违规，第一个块抛出错误，即使它抛出错误，执行也会转到下一个块并执行插入语句。我想在第一个代码块本身结束执行或退出。请帮我写代码。
First anonymous PL/SQL block:
第一个匿名 PL/SQL 块：
set serveroutput on;
BEGIN
insert into test values(1);
insert into test values(1);
COMMIT;
dbms_output.put_line('PRINT SOMETHING 1');
EXCEPTION
WHEN OTHERS THEN
if sqlcode <> 0
then
dbms_output.put_line(SQLCODE || ' ' || SQLERRM);
RAISE;
end if;
return;
END;
/
Second anonymous PL/SQL block:
第二个匿名 PL/SQL 块：
set serveroutput on;
BEGIN
insert into test values(6);
COMMIT;
dbms_output.put_line('PRINT SOMETHING');
EXCEPTION
WHEN OTHERS THEN
if sqlcode <> 0
then
dbms_output.put_line(SQLCODE || ' ' || SQLERRM);
RAISE;
end if;
return;
END;
/
回答by Brian McGinity
If you create a stored procedure, you have more control and can exit whenever you like with a return statement.
如果您创建存储过程，您将拥有更多控制权，并且可以随时使用 return 语句退出。
So create a stored proc:
所以创建一个存储过程：
create or replace procedure myProc as
begin
dbms_ouput.put_line('i am here');
return;
dbms_ouput.put_line('and not here');
end;
Then in sqlplus or developer:
然后在 sqlplus 或开发人员中：
exec myProc();
回答by Gary Myers
You can nest the blocks into a single 'program unit'. In this way an exception in the first block will stop the whole program unit from executing, rather than just being limited in scope to the first block.
您可以将块嵌套到单个“程序单元”中。这样，第一个块中的异常将停止整个程序单元的执行，而不仅仅是将范围限制在第一个块中。
set serveroutput on;
BEGIN
BEGIN
insert into test values(1);
insert into test values(1);
COMMIT;
dbms_output.put_line('PRINT SOMETHING 1');
EXCEPTION
WHEN OTHERS THEN
if sqlcode <> 0
then
dbms_output.put_line(SQLCODE || ' ' || SQLERRM);
RAISE;
end if;
return;
END;
BEGIN
insert into test values(6);
COMMIT;
dbms_output.put_line('PRINT SOMETHING');
EXCEPTION
WHEN OTHERS THEN
if sqlcode <> 0
then
dbms_output.put_line(SQLCODE || ' ' || SQLERRM);
RAISE;
end if;
return;
END;
END;
/
回答by JoshL
You should be able to use "exit" - see the Oracle documentation here: http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12023.htm
您应该能够使用“退出” - 请参阅此处的 Oracle 文档：http: //docs.oracle.com/cd/B19306_01/server.102/b14357/ch12023.htm
Note that this will end your SqlPlus session, but I don't know of another way of doing it aside from using a single block or stored procedure.
请注意，这将结束您的 SqlPlus 会话，但除了使用单个块或存储过程之外，我不知道其他方法。
Another useful statement is:
另一个有用的声明是：
WHENEVER SQLERROR EXIT SQL.SQLCODE
Oracle documentation: http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12052.htm
Oracle 文档：http: //docs.oracle.com/cd/B19306_01/server.102/b14357/ch12052.htm
回答by user3311382
Thanks for your valuable comments. JoshL, i tried using EXIT but i am ending up with error. Please correct my code( I am new to PL/SQL). "WHENEVER SQLERROR EXIT" is good to use but my issue is that I use these sql scriptsd in InstallShield, so InstallShield installers does not recognize these statements and throws error.
感谢您提出宝贵意见。JoshL，我尝试使用 EXIT，但最终出现错误。请更正我的代码（我是 PL/SQL 的新手）。“WHENEVER SQLERROR EXIT”很好用，但我的问题是我在 InstallShield 中使用了这些 sql 脚本，因此 InstallShield 安装程序无法识别这些语句并引发错误。
set serveroutput on;
BEGIN
insert into test values(1);
insert into test values(1);
COMMIT;
dbms_output.put_line('PRINT SOMETHING 1');
EXCEPTION
WHEN OTHERS THEN
if sqlcode <> 0
then
dbms_output.put_line(SQLCODE || ' ' || SQLERRM);
RAISE;
exit;
end if;
END;
/
回答by TonyK
The EXIT command is only for use within a loop in PL/SQL. The EXIT command leaves the loop at that point. If you use the EXIT command outside a loop in PL/SQL the compiler throws an error.
EXIT 命令仅用于 PL/SQL 的循环内。EXIT 命令在该点离开循环。如果在 PL/SQL 的循环外使用 EXIT 命令，编译器会抛出错误。
The EXIT command in SQLPlus exits the SQLPlus session.
SQL Plus 中的 EXIT 命令退出 SQLPlus 会话。
This is confusing, because they are two different Oracle products. SQL*Plus can run PL/SQL and the EXIT statement is a valid statement in both products, but with different contexts.
这令人困惑，因为它们是两种不同的 Oracle 产品。SQL*Plus 可以运行 PL/SQL，并且 EXIT 语句在两种产品中都是有效的语句，但是上下文不同。
