Re: What are stored procedures for?

CT wrote:

> Stored procedures and functions are coded on the database side. They
> are stored on the database. Also, doing this they are tied down to the
> database that you are coding on.
>
> What are the advantages of using these as compared to plain jdbc/odbc
> statemetn.
>
> Might be a controversial question but needs an answer :)
>
> Thanks.
>
> CT.

Not controversial at all. My opinion as follows:

Pro-stored procedure:

Use stored procedures when:

1. you want to keep network traffic lower by resolving the response at the database server instead of possibly needing several data sets sent to the 'client'
2. you want to ensure the same routine is run regardless of the client. (It seems like everyone and his dog has access to Excel or some other ODBC-capable client. Once they figure it out, users tend to bypass the application.)
3. you want to minimze maintenance cost by not distributing software to end-useer machines. (Less of a consideration in 3-tier).
4. you want to use the packaged capabilities that are included free with Oracle. (such as workflow, message queueing, smtp mail, PSP (PLSQL Server Pages), job scheduling, and so on)

Remember that with Oracle, Stored PRocedures could be written in Java and stored in the database as well.

Also remember that writing 'generic, database independant' Java code avoids takig advantage of database capabilities. It's also a sign the developer does not understand the database and is attempting to work-around that liitation using the tools [s]he knows. This always ends up in increased cost to the customer.

A decent discussion is in chapter 1 of 'Expert One-on-One Oracle' and chapter 1 of 'Effective Oracle by Design'.

Pro-distributed:

Easier for some developers to understand.

Apparently generic. Therefore allows for making a quick buck, when reselling the application.

De-centralized. Some shops mandate this.

Supports load balancing (somewhat bogus, because the technique & pattern generally used is usually anti-scalable in the first place.)

/Hans Received on Thu Jul 01 2004 - 13:17:43 CDT