PL/SQL: is there an instruction to completely stop

问题:

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.

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

回答1:

Based on the question, I disagree with the accepted answer. 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.

For batch scripts, it is best to use WHENEVER SQLERROR EXIT. Yes, it is a SQL*Plus 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 SQL*Plus, 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;
/

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.

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 SQL*Plus running in a console window. SQL*Plus 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.

回答2:

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.

回答3:

A few more seconds of googling gave me the answer: the function 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.

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)

回答4:

Rather than throw an application error, it's much simpler to just use the RETURN keyword which exits the current PL/SQL block very smoothly.

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!