PL / SQL

Function is a PL/SQL block which must and should return single value.

Syntax:
-----------

Create or replace function <Function_name>
( <Par_name> <mode> <datatype>,
,, ,, ,, )
return datatype
is
Begin
..........
.........
end;
/

ex1:
-----

Create a function which accepts two numbers and display its sum.

create or replace function add_num_f1 ( a number, b number)
return number
is
c number(5);
begin

c :=a+b;
return c;
end;
/

To invoke a function from a pl/Sql block:
---------------------------------------------

declare
n number(5);
begin

n := add_num_f1(20,40);

dbms_output.put_line('The sum is '||n);
end;
/

We can invoke functions from select stmt:
----------------------------------------------
select add_num_f1(30,50) from dual;

Functions can be invoked as part of an expression:
----------------------------------------------------

select 100 + add_num_f1(50,10) from dual;

Ex2:
------

create a function which accepts sal and returns tax value ( 10% of sal is tax).

create or replace function cal_tax ( a number)
is
begin

return a*10/100;
end;
/

Note: A function can return a value using return statement.

Ex 3:
----------

Have a look at the following function:

create or replace function add_num_f2 ( a number, b number)
return number
is
c number(5);
begin

insert into dept values (50,'HR','HYDERABAD')

c :=a+b;
return c;
end;
/

The above function gets created.

The above function can be invoked from the pl/SQL block

declare
n number(5);
begin

n := add_num_f2(20,40);

dbms_output.put_line('The sum is '||n);
end;
/

But, we cannot invoke the above function using select stmt.

ex:

select add_num_f2(30,50) from dual; -- will give us error.

Note: So, functions with dml commands cannot be invoked from select stmt.

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

TO see the list of all the functions

select object_name from user_objects
where object_type = 'FUNCTION';

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

To drop a function

drop function <function_name>;

ex:

drop function add_num_f2;

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

Functions are mainly used for calculation purposes.
Rest of the activities, prefer procedures.