Data Warehousing Community Forum
February 07, 2012, 07:32:00 am

Pages: [1]   Go Down
« previous next »
  Print  
Author Topic: Procedure and Function?  (Read 901 times)
Sipra
Founder
*****

Reputation: +59/-0
Offline Offline

Posts: 323


Am the King...


View Profile WWW
« on: July 10, 2007, 12:17:40 pm »

What is the difference between a Procedure and Function?

Is Package consists of all PL/SQL units? What is the advantage of Package when compared to a Procedure and Function?

How Procedures and Functions are called in a PL/SQL block?
Logged
Sipra
Founder

www.dwforum.net
Whoever
Administrator
*****

Reputation: +34/-0
Offline Offline

Posts: 124



View Profile WWW
« Reply #1 on: July 17, 2007, 11:50:22 am »
A Function is always returns a value using the return statement.

A Procedure may return one or more values through parameters or maynot return at all.

A Package is a PL/SQL block which contains set of Functions and Procedures.

How Procedures and Functions are called in a PL/SQL block?

Function is called as a part of expression.
for Eg - sal:=calulate_sal('sp207')

Procedure is called as a PL/SQL statement
for Eg - calculate_bonus('sp033')
Logged
If most people said what’s on their minds, they’d be speechless.
unknown
DW Apprentice
**

Reputation: +8/-0
Offline Offline

Posts: 44



View Profile
« Reply #2 on: January 19, 2008, 11:23:37 pm »
Just adding a small missed out point to Whoever's statement -

Functions are used for computations where as procedures are used for performing business logic.
Thanks  Grin
Logged
Thanks
Unknown Smiley
samishta
DW Fresher
*

Reputation: +2/-0
Offline Offline

Posts: 17



View Profile
« Reply #3 on: May 28, 2008, 11:56:30 pm »
I agree with unknown,... functions are widely used for computations, while procedures and functions both might be used for implementing complex business logic.

We can easily use functions in sql statements, ... say select queries for that matter since they are capable of returning values and thus can very easily be equated. Even the inbuilt functions like decode, nvl etc exemplify this.
Procedures dont find much use in sql statements but are used in pl blocks. They support multiple in out parameters.
Logged
Pages: [1]   Go Up
  Print  
« previous next »
 
Jump to: