Function Example

As Salamo alaikum wa rahmatullah

-- Function 

Create or Replace function Fun_Test
return varchar2 
is
nm varchar(20);
begin
select ename into nm from emp where empno=7788;
return nm;
end Fun_Test;

Select Fun_Test() from dual;

-- Function with In Parameter
Create or Replace Function Fun_Test_In_Param(eno IN Number)
return varchar2
is
enm varchar2(100);
begin
select ename into enm from emp where empno=eno;
return enm;
end Fun_Test_In_Param;

Select Fun_Test_In_Param(7788) from dual;

-- Function with IN OUT Parameter

Create or Replace Function Fun_Test_In_Out_Param(eno IN Number,nm OUT varchar)
return varchar2
is
jb varchar2(20);
begin
select ename into nm from emp where empno=eno;
select job into jb from emp where ename=nm;
return jb;
end  Fun_Test_In_Out_Param;

Declare
rt varchar2(100);
nm1 varchar(100);
begin
rt:=Fun_Test_In_Out_Param(7788,nm1);
dbms_output.put_line('return value'||rt);
dbms_output.put_line('output parameter'||nm1);
end;



--
Ma Aslaam
Passion 4 Oracle