FUNCTIONS

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;
/