Data Warehousing Community Forum
October 24, 2019, 01:14:46 am

Pages: [1]   Go Down
Author Topic: Procedure and Function?  (Read 4331 times)

Reputation: +56/-0
Offline Offline

Posts: 354

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?


Reputation: +55/-0
Offline Offline

Posts: 145

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')

Wenn ist das Nunstück git und Slotermeyer? Ja! … Beiherhund das Oder die Flipperwaldt gersput.
DW Apprentice

Reputation: +9/-0
Offline Offline

Posts: 61

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

Unknown Smiley
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.
Pages: [1]   Go Up
Jump to: