Results 1 to 3 of 3

Thread: PL/SQL Exit

  1. #1
    Join Date
    Feb 2004
    Posts
    86

    Unanswered: PL/SQL Exit

    Is there are method in PL/SQL to exit from the procedure if a condition is met?

    I need to change my procedure to check the existance of a particular value, however I have several hundred lines of code. Is there is a simple exit statement or goto statement that will jump to or exit based on a condition?

    Example

    select count(*) into x_var from table where field=x;
    if x_var = 1 then
    exit procedure
    end if;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "EXIT" statement can be used only within a loop. Do you use it? Cursor loop, for example? Then you can use EXIT WHEN cursor%NOTFOUND. Simple IF condition would also work - IF your_condition THEN EXIT; END IF;

    If you don't have a loop, you could use a GOTO statement, such as
    Code:
    DECLARE
      x NUMBER;
    BEGIN
      SELECT COUNT(*) INTO x FROM EMP;
      IF x > 100 THEN
         dbms_output.put_line(x);
      ELSE
         dbms_output.put_line('exit');
         GOTO the_end;
      END IF;
    
      <<the_end>>
      NULL;
    END;
    /
    I *think* there's no way to exit a PL/SQL procedure with a single statement. Or is there? Perhaps someone else will know.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    RETURN does that:
    Code:
    SQL> create or replace procedure p is
      2  begin
      3    dbms_output.put_line('one');
      4    return;
      5    dbms_output.put_line('two');
      6* end;
    SQL> /
    
    Procedure created.
    
    SQL> exec p
    one
    
    PL/SQL procedure successfully completed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •