PL/SQL - Can function have OUT, IN OUT parameter ?

[richasaraf]

Hi guys,
Can someone help me...
I want to know... can we have OUT, IN OUT parameters in function? :confused:
And can we use it in select statement ?

Thanks a lot !
Take Care

Richa :)

[rsankpal]

Yes, we have in-out parameter in function. we can use it in select statement.
we can have more than one OUT parameter in function.But to execute this unction all this function in anonomous block and use dbms_output.put_line :)

[Mahesh_Bote]

 
Create Function Addition
(
@Num1 Int,
@Num2 Int
)
Returns Int
As
Begin
 Return(@Num1 + @Num2)
End
 
Select DBO.Addition(3, 5) 
 
O/P
------------
8
 
 

we have create one function which accepts two parameters and will return one value in place of its name. thus, the fucntions which accepts parameters, these are inputs and what fucntion return, is output.

Mahesh
:) :) :)

[vijil]

Hai richasaraf ,
U can create function only in in mode.but u can create procedure in 3 ways(in,out,in out)

[sreepathi]

hellow man
u cant create a function that have morethan one out variable
a function is that it gets any number of inputs and returns only one value
A procedure can help u to satisfy ur needs .A procedure can have any number of in (input) variables and any number of out(return variable)variables.
A procedure can have variables that acts as both in and out variables

 
create or replace procedure pro_name(a in number,b ijn number,c out number,d out number,e in out number) is
begin
    c:=a+b;
    d:=a-b;
    e:=a+b-e;
end pro_name;
 
 
this procedure can be called like this
 
declare
sum number;
minus number;
var1 number:=2;
begin
------------
pro_name(10,5,sum,minus,var1);
-----------
---------
-------------
----------
end;
 
 

here sum will be 15(10+5)
minus will be 5(10-5)
var1 will be 13(10+5-2)
bye
see u

[Alixandro Florian Cuevas]

Dear Richa
a function only return one value, only accep parameters IN not IN OUT or OUT.
eg. Create or Replace Function Richa (var1 number, var2 in varchar2) Return Date, Varchar2 or number.

example.

 
 
Create Or Replacr Function Telephone(PersonID number) Return Varchar2 Is
Cursor tel is
 Select tel_telefono telefono
   from tcli_telefono
  where tel_codcli = PersonID;
--
vTelefonos  varchar2(500) Default null;
vComa       varchar2(1)   := ',';
--
Begin
 For reg in tel loop
  vTelefonos := Reg.Telefono||vComa||vTelefonos;
  --
 End Loop;
  Return( Rtrim(vTelefonos,','));
End Telephone;
 
 

[dkarthick]

a Function returns Exactly only one value. we canot use the OUT parameter in function.
and also we can call a function from SQL prompt.

for Example

 
 
CREATE OR REPLACE FUNCTION ADD(I NUMBER, J NUMBER)IS
K NUMBER;
BEGIN
K:=I+J;
RETURN(K);
END ADD;
 
 
--CALLING A FUNCTION:
 
DECLARE
N NUMBER;
BEGIN
N:=ADD(10,20);
DBMS_OUTPUT.PUT_LINE('THE ADDITION OF TWO NUMBERIS :'||N);
END;
 
 
--OR
 
 
SELECT ADD(10,20) FROM DUAL;
 

[saravanan120480]

Hi guys,
Can someone help me...
I want to know... can we have OUT, IN OUT parameters in function? :confused:
And can we use it in select statement ?

Thanks a lot !
Take Care

Richa :)

hi
function can have out arguments also. if the function has an out arguments then we can't use it in select or dml statments

[saravanan120480]

Hi guys,
Can someone help me...
I want to know... can we have OUT, IN OUT parameters in function? :confused:
And can we use it in select statement ?

Thanks a lot !
Take Care

Richa :)

hi
function can have out arguments also. if the function has an out arguments then we can't use it in select or dml statments

Thanks and Regards
saravanan p

[debasisdas]

Hi guys

We can use all the three parameter modes (IN,OUT, IN OUT ) in a function.

Please find the details regarding PL/SQL Function.

[Saii]

yes, a function can have all three modes for parameters but underlying fact is that function should return only one value. So it is better to restrict the function parameter mode to IN, in case you want to have a functionality wherein you have an OUT parameter, use a procedure instead.

[rohitsharma1977]

Hi guys,
Can someone help me...
I want to know... can we have OUT, IN OUT parameters in function? :confused:
And can we use it in select statement ?

Thanks a lot !
Take Care

Richa :)

You can not use OUT parameters in function.

-Rohit

[amitpatel66]

You can not use OUT parameters in function.

-Rohit

We can use OUT MODE in function as well. CHeck this:

 
SQL> create or replace function add_num1(a IN NUMBER,b OUT NUMBER) RETURN NUMBER IS
  2  BEGIN
  3  dbms_output.put_line(a);
  4  b:= a;
  5  RETURN a;
  6  END add_num1;
  7  /
 
Function created.
 
SQL> var num1 number;
 
SQL> ed
Wrote file afiedt.buf
 
  1  declare
  2  ab number;
  3  begin
  4  ab:=add_num1(10,:num1);
  5  dbms_output.put_line(:num1||','||ab);
  6* end;
SQL> /
10
10,10
 
PL/SQL procedure successfully completed.
 
 

We cannot use this funciton from select statement:

 
SQL> select add_num1(10,:num1) from dual;
select add_num1(10,:num1) from dual
       *
ERROR at line 1:
ORA-06572: Function ADD_NUM1 has out arguments
 
 

[debasisdas]

You can use all the three modes in function .

find a related discussion here regarding function with IN OUT parameter.