Comparison in Procedures and Functions

[amolso]

I have just started using procedures and functions
In comparison I have seen that
--Proedures can return values but a function must
--Procedures used in a format like function are
more convinient to use than functions
--Procedures can return multiple values but a function
cannot.

If my observations are correct can anyone please tell me
why to use function itself?
Do they have any advantage over procedures?

Thanking you in advance
amol [sig][/sig]

 

[carp]

Functions are very useful because they add custom functionality to your SQL. Functions can be called from within a SQL statement; procedures cannot.
If you have a particular format you like to see dates displayed in, you could create a function called timestamp. Then, instead of having to do a to_char(...) every time you display a date, you can do a simple

SELECT ..., timestamp(the_date_field)
FROM.... [sig][/sig]

 

[amolso]

Thank you carp
I hope your information will be of great use to me
Amol [sig][/sig]

 

[carp]

Amol -
Perhaps this would be a more helpful example of the utility of a function.
Suppose you are building an application to support order transactions. Let's assume you have several procedures that need to factor in sales tax, which is currently 2%. One way to do this is to code the logic to calculate this tax in each procedure. Another approach is to create a function called sales_tax that takes in the price of goods sold and returns the applicable sales tax. Each procedure can then just call this function.

Now let us assume that Al Gore wins the election and you now have to increase the tax rate to 15%. If you took the first approach, you have to make changes to all of the procedures that calculate sales tax. If you miss one, you now have different business rules being applied, depending on which procedure is being executed. On the other hand, if you use one function to calculate sales tax, you only have to modify this one function and all of the procedures will use the same, new tax rate. [sig][/sig]

 

[MikeLacey]

<grin> no political bias *there* carp [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. &lt;grin&gt;[/sig]

 

[THoey]

I just want to know where he lives that has a 2% sales tax... [sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[carp]

Mike -
No matter how subtle I try to be, I just can't slide ANYTHING past you! [sig][/sig]

 

[amolso]

Thanks carp
seems you are of Mr Bush

you can inform this to our prime minister Mr. Vajpayee who's currently in US for discussions with both again

Amol [sig][/sig]

 

[carp]

Not particularly. Once again, I'm faced with the lesser of two evils! But we digress!! [sig][/sig]