Wednesday, 23 March 2011

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;

/

2 comments :


  1. I like the way on how you put up your blogs. Wonderful and awesome. Hope to read more post from you in the future. Goodluck. Happy blogging!

    Bubble
    www.gofastek.com

    ReplyDelete
  2. Mistake does not only teach you how to be strong but also how to stand up again and to do good next time. Visit my site for more information. Have a good day always and thank you for sharing your knowledge and wonderful article to the public. I do hope that you can inspire more readers.

    n8fan.net

    www.n8fan.net

    ReplyDelete