Home » SQL & PL/SQL » SQL & PL/SQL » Quit the stored procedure (Oracle 9.2.0.1.0)
Quit the stored procedure [message #378810] Fri, 02 January 2009 00:23 Go to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Create or replace procedure test
IS
i number := 6;


begin
IF (i>6) then
------holds true then exit the procedure
ELSE
------some operation
------some operation
------some operation
	if (condition) then
	------some operation
	else
	------some operation
	end if;
end if;

----here suppose i call another stored procedure
----than some insert query
----some operation

exception
end;
\


Here i want to exit the complete procedure , or i want to stop the procedure as i>6 , what command do i need to use?
Re: Quit the stored procedure [message #378815 is a reply to message #378810] Fri, 02 January 2009 00:38 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
What about RETURN Statement?
Re: Quit the stored procedure [message #378819 is a reply to message #378810] Fri, 02 January 2009 00:49 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
hi flyboy

return will only end the subprogram, but i want to stop the whole procedure after that, nothing should be executed as soon as that command is encountered, Exit also doesnt seems to work like that.
Re: Quit the stored procedure [message #378823 is a reply to message #378819] Fri, 02 January 2009 00:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
You can move the code after end if to before the end if or you can use goto and label.

[Updated on: Fri, 02 January 2009 00:55]

Report message to a moderator

Re: Quit the stored procedure [message #378827 is a reply to message #378819] Fri, 02 January 2009 01:04 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
shaksing wrote on Fri, 02 January 2009 07:49
return will only end the subprogram, but i want to stop the whole procedure after that, nothing should be executed as soon as that command is encountered, Exit also doesnt seems to work like that.

I may be blind but I do not see any subprogram in the pseudocode nor the description you posted.

But, what about change the sub-procedure to sub-function; return specific value for each case (proceed/not proceed) and evaluate it after every its call.
Re: Quit the stored procedure [message #378830 is a reply to message #378810] Fri, 02 January 2009 01:11 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Actually i was talking about the definition of the return, as you provided me the link. Let me try this thing .will let you know , that worked or not.
Re: Quit the stored procedure [message #378919 is a reply to message #378830] Fri, 02 January 2009 11:42 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
FYI, similar to the GOTO (that is universally seen an bad coding practice), you can (mis)use exceptions to jump to the end. It has the advantage that you could do common pre-exiting functionality like logging or whatever
procedure p1
is
...
   exit_now  exception;
   v_stage varchar2(40);
begin
...
 v_stage := 'i>6?';
 if (i>6) then
 then
    raise exit_now;
 end if;

 v_stage := 'i>10?';
 if (i>10) then
 then
    raise exit_now;
 end if;

...

exception
 when exit_now then
   --null;
   log_to_table(v_stage);
end;


Re: Quit the stored procedure [message #378951 is a reply to message #378919] Sat, 03 January 2009 01:20 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I totally agree.
It even has the benefit that you can jump out of several layers in the call-stack, if you assign a number to your exception through a pragma.
Previous Topic: Toggle Sequence
Next Topic: how can i select range of numbers between two numbers
Goto Forum:
  


Current Time: Sat Dec 03 11:46:34 CST 2016

Total time taken to generate the page: 0.07087 seconds