Functions in Oracle

Functions in Oracle

A Function is a named PL/SQL block that returns a value.

Syntax for Creating Function

Create (or Replace) function (function name)
(parameter1 (mode1) datatype1,
parameter2 (mode2) datatype2)

RETURN datatype

AS|IS
PL/SQL BLOCK

Some Programs Using Functions

1)
create or replace function tax (p_value in number)
return number is
Begin
return (p_value * 2);
end ;

select tax(3) from dual ;

2)
create or replace function total ( a number , b number )
return number
is
begin
return (a+b) ;
end ;

select area (3,2) from dual ;

3)
Create or replace function get_sal
(mempid in emp.empno%type)
return number
IS
v_sal number;
BEGIN
select sal into v_sal from emp where empno=mempid ;
return v_sal;
end;

execution Method - 1
--------------------
select get_sal(7900) from dual ;

execution Method - 2
--------------------
Variable g_sal number
execute :g_sal := get_sal(7900)
Print :g_sal
--------------------------------

select empno,ename, sal, tax(sal) from emp where deptno=20 ;

Create or replace function get_sal
(mempid in emp.empno%type)
return number
IS
v_sal number;
x number;
begin
select sal into v_sal from emp where empno=mempid ;
x := v_sal*.5 ;
return x ;
end ;
---------------------------------------------------------
create or replace function func_name
(mempno in number)
return number
is
msal emp.sal%type;
begin
select sal into msal from emp where empno=mempno ;
if msal > 0 then
return msal ;
else
return msal*2 ;
end if ;
end ;
-----------------------------------------------------------------------------------
create or replace function func_name
(mempno in number)
return boolean
is
msal emp.sal%type;
begin
select sal into msal from emp where empno=mempno ;
if msal > 0 then
return (true) ;
else
return (false) ;
end if ;
end ;
----------------

begin
if func_name(7902) then
dbms_output.put_line('right') ;
else
dbms_output.put_line('wrong') ;
end if ;
end;

-----------------------------------------------------------------------------------

create or replace function func_name
(mempno in number)
return varchar2
as
mename emp.ename%type ;
begin
select ename into mename from emp where empno=mempno ;
if sql%rowcount > 0 then
return mename ;
end if ;
end;

select func_name(7902) from dual ;

-----------------------------------------------------------------------------------

Using Exception

create or replace function func_name
(mempno in number)
return varchar2
as
mename emp.ename%type ;
begin
select ename into mename from emp where empno=mempno ;
if sql%rowcount > 0 then
return mename ;
end if ;
exception
when no_data_found then
mename:='no data' ;
return mename ;
end ;

Dropping a Function

Drop function function_name ;

Desc user_objects ;
select * from user_objects ;

desc user_source
select text from user_source where NAME='FUNC_NAME' ;

desc user_errors
select * from user_errors ;