Out
A reader, December 09, 2016 - 10:51 am UTC
(1) Use a function.
(2) You can of course use an out parameter in conjunction with the above.
create or replace procedure p(p1 int, p2 int, p3 int, res out nocopy int) is
begin
dbms_output.put_line('started');
if coalesce(p1, p2, p3) is null then
res := -1;
return;
end if;
dbms_output.put_line('stopped');
end p;
/
declare
res int;
begin
p(null,null,null, res);
dbms_output.put_line('Valid of res is ' || res);
end;
started
Valid of res is -1
December 09, 2016 - 2:40 pm UTC
Excellent point. Out parameters do allow you to return values from procedures.
Exit from stored procedure
Oleg, December 09, 2016 - 12:03 pm UTC
I have to not just return. I have to return certain number, for example return -1 from stored procedure. Is it possible?
December 09, 2016 - 2:42 pm UTC
I'm not sure what the issue with functions is. But if you must use a procedure, out parameters can "return" a value from a procedure. See the above comment for an example.
Additonal Comments to what has been mentioned already.
David Pulliam, December 09, 2016 - 3:26 pm UTC
What about using exception handling instead? What I will do many times is use user defined exceptions if there is a limited number of of outcomes when I want the program to come to a halt and I need to send the results back up several levels. I believe, correct me if I am wrong, that the default action on an exception is to raise it if not handled. I agree with Chris here that in your case, a function would be the way to go. You actually have to tell the function what to return and it always has to return a value. If you hit your bad case, return -1 and your done. If you cannot use a function, then you might need to reconsider how your process is designed. Out Parameters are a good option as already mentioned especially for cases where you need to return multiple values as a function can only return either a single variable value or a static hard coded value.
December 09, 2016 - 3:40 pm UTC
Yes, exception handling is another way to deal with this.
In this case I think it's better than returning -1 as it represents an invalid scenario. You can only have customer errors in the range -20000 to -20999 though.