-1

I have a confusion regarding the OUT parameter and RETURN type of a function in oracle PL SQL. I have few questions regarding this.

  1. Can someone please explain the difference between the two?
  2. Can a function have both IN and OUT parameter like a procedure?
  3. Is it possible to return more than one value using functions?

I have searched over the internet but I am getting different answers which are confusing me further.

abdul shajahan
  • 11
  • 1
  • 2

2 Answers2

1

1) If function have only IN parameters than you can use that function in queries.

create or replace function my_func( i in integer) 
return integer as
begin
    return i+1; 
end;

select my_func(1) from dual;

2) Yes. In functions are allowed using IN and OUT parameters.

 create or replace function  my_func( i in integer, j out integer, k in out integer) return integer as
 begin
    j:= i + 2; 
    k:= k + i;
    return i+1;
 end;

3) Yes, you can use many OUT parameters, but only one RETURN

 create or replace function my_func( i in integer, j out integer, k out integer) 
 return integer as
 begin
    j:= i + 2; 
    k:= i + 3;
    return i+1;
 end;
AlexSmet
  • 2,141
  • 1
  • 13
  • 18
  • thanks for the response. I have executed the code given for my 3rd questions and I have understood what happens if we give more than one out parameter in functions. – abdul shajahan Oct 11 '16 at 06:22
  • I have another request, in many places it was mentioned that it is not advisable to use OUT parameter in a function and it is better to use a procedure in such a scenario. What is the difference. Can you please explain me why a procedure with OUT parameter is better than a function? ... Thanks in advance – abdul shajahan Oct 11 '16 at 06:26
  • 2
    I think it is mean that your code will be more clear if you will not mixing `return` and `out` parameters in functions. If you need return only one value, then function it is a better way. If you need to use many `out` parametrs, then use procedures with `out` parameters. But in PL/SQL we don't have any restriction for using `out ` parameters in functions. – AlexSmet Oct 11 '16 at 07:03
0
  1. If you want to return more than one value from function you can return previously defined user type that contains many fields. However this is a bit dirty solution.
Kacper
  • 4,798
  • 2
  • 19
  • 34