5 Replies Latest reply: Dec 10, 2011 6:37 AM by Solomon Yakobson RSS

    Can we use return statement in procedure?

    901451
      Can we use return statement in procedure or we can use more than one return statement in procedure?
        • 1. Re: Can we use return statement in procedure?
          sb92075
          Satyam_898448 wrote:
          Can we use return statement in procedure or we can use more than one return statement in procedure?
          when all else fails Read The Fine Manual

          http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/return_statement.htm#LNPLS01340
          • 2. Re: Can we use return statement in procedure?
            Billy~Verreynne
            Why ask the same question again? What did you not understand in {message:id=10013442}?
            • 4. Re: Can we use return statement in procedure?
              Solomon Yakobson
              HamidHelal wrote:
              NO
              Really? Did you at least test it? You can use RETURN in procedure or in anonymous PL/SQL block. The only restriction is you can't specify return value:
              SQL> begin
                2      dbms_output.put_line('Before return');
                3      return;
                4      dbms_output.put_line('After return');
                5  end;
                6  /
              Before return
              
              PL/SQL procedure successfully completed.
              
              SQL> create or replace
                2    procedure p1
                3      is
                4      begin
                5          dbms_output.put_line('Before return');
                6          return;
                7          dbms_output.put_line('After return');
                8  end;
                9  /
              Procedure created.
              
              SQL> exec p1;
              Before return
              
              PL/SQL procedure successfully completed.
              
              SQL> begin
                2      dbms_output.put_line('Before return');
                3      return 99;
                4          dbms_output.put_line('After return');
                5  end;
                6  /
                  return 99;
                  *
              ERROR at line 3:
              ORA-06550: line 3, column 5:
              PLS-00372: In a procedure, RETURN statement cannot contain an expression
              ORA-06550: line 3, column 5:
              PL/SQL: Statement ignored
              
              
              SQL> create or replace
                2    procedure p1
                3      is
                4      begin
                5          dbms_output.put_line('Before return');
                6          return 99;
                7          dbms_output.put_line('After return');
                8  end;
                9  /
              Warning: Procedure created with compilation errors.
              
              SQL> show err
              Errors for PROCEDURE P1:
              
              LINE/COL ERROR
              -------- -----------------------------------------------------------------
              5/9      PL/SQL: Statement ignored
              5/9      PLS-00372: In a procedure, RETURN statement cannot contain an
                       expression
              
              SQL> 
              SY.
              • 5. Re: Can we use return statement in procedure?
                Mini
                Great Example SY.

                Thanks for your detailed explanation.

                Brgds,
                Mini