Function

A function is a sub program that executes a set of statements and returns a value to the main program. The basic difference between Functions and procedures is, function returns a value to the calling program or main program where as a procedure does not return any value

Syntax of A Function

Create [ Or Replace ] Function < Function Name > ( Argument1 [Mode] <Data type>, Argument2 [Mode] <data type>,---) return data type

Is / As

Local Variables Declarations;

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

Begin

Executable Statements;

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

[Exception

Exception Handling; ]

End;

/

Example : 1

Write a program to find sum of two numbers

Sol :

ed function1

Create or replace function addition(x integer, y integer) return integer

is

Begin

Return x+y;

End;

/

mainfunction1

Declare

A integer :=&a;

B integer :=&b;

C integer;

Begin

C:=addition(a,b);

Dbms_output.put_line('sum of two numbers is '|| c );

End;

/

Example : 2

wirte a function which accepts item number and quantity and then return the total amount by fetching rate from the item table

create or replace function itembill(q in number,r in number) return number

is

begin

return q*r;

end;

/

ed mainfunction2

declare

q1 number:=&quantity;

r1 number;

res number;

begin

select rate into r1 from item where itno=&itno;

res:=itembill(q1,r1);

dbms_output.put_line(' Total Amount is '||res);

exception

when no_data_found then

dbms_output.put_line(' No Such Item Exits');

end;

/