PL/SQL:是否有一条指令可以完全停止脚本执行? [英] PL/SQL: is there an instruction to completely stop the script execution?

问题描述

我试图在PL/SQL脚本的开头对数据库模式进行一些检查.

I'm trying to do some checkings on a DB schema at the beginning of a PL/SQL script.

如果检查结果不成功，我想停止脚本，以防止执行下一条指令.

If the checkings give unsuccessful results, I want to stop the script, to prevent the next instructions to be executed.

我有这样的东西

-- 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 ...

我正在寻找允许执行"STOP EXECUTION HERE"的指令.

I'm looking for the instruction(s) allowing to do the "STOP EXECUTION HERE".

推荐答案

问题显示了多语句批处理脚本. RAISE_APPLICATION_ERROR()仅退出PL/SQL块(子程序)，而不退出整个脚本(由Justin指出)，因此它将继续执行随后的语句.

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.

对于批处理脚本，最好使用WHENEVER SQLERROR EXIT.是的，它是一个SQL Plus指令，而不是标准SQL，但具有可移植性.支持脚本的大多数流行的Oracle工具至少部分支持该指令.下面的示例可在SQL Plus，SQL * Developer，Toad，SQLsmith以及其他可能的版本中使用，并且如果将行注释掉，则会演示该问题.

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.

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;
/

如果删除WHEN SQLERROR，脚本将继续执行第二个代码块，依此类推.这正是问题所要避免的.

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.

在这种情况下，模拟sqlplus的图形工具的优点是它们确实停止了脚本，并且不将其余脚本作为外壳程序命令提交给命令外壳，如果粘贴脚本会发生这种情况在控制台窗口中运行的SQL Plus中. SQL Plus可能会因错误而退出，但是其余的缓冲命令将由OS Shell处理，如果注释中包含Shell命令(这是闻所未闻的)，则这会有些混乱并且可能存在风险. .使用SQLPlus，总是最好先连接，然后执行脚本，或在<中传递它.开始>命令行参数(sqlplus scott/tiger @ foo.sql)可以避免这种情况.

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.

这篇关于PL/SQL:是否有一条指令可以完全停止脚本执行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！