Re: What are stored procedures for?

"CT" <come_try_at_yahoo.com> schrieb im Newsbeitrag news:f2ca5893.0407010953.470c9bf5_at_posting.google.com...
> 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 :)
My personal answer is partly in favour of centralized computing in general and partly in favour of PL/SQL:
- They [stored procs] can access all the data without any network roundtrips.   this typically means that they are way faster than code that has   to fetch data over a LAN. It scales better too, since zero-network-roundtrips   stays zero-network-roundtrips for any amount of clients. - If you need more power you just upgrade one server instead of   multiple clients and all clients profit from it. - You know the server configuration and performance. Do you   know this for every client? It makes sense to keep the clients as   simple as possible. (Example: Client requirements of google) - Todays client systems are typically very busy displaying animated   menus and little dogs sniffing for files, while a database has all the CPUs   allocated for the real work.
- Complex triggers are hard to implement without PL/SQL or Java,   and using a client and AQ isn't going to be generic either. - PL/SQL is very tightly integrated with SQL, although you   are free to do your triggers in Java on the server if you want to.

• There's also the OOP argument. The original idea of OOP was to put the code together with the data. Well, the data is in the database, so where should the code go?
• With server side code you can force *every* access to go through your code and therefore guarantee consistency and security of your data.
• Security is also enhanced if some data never leaves the database server except filtered or otherwise processed.

In a way, PL/SQL functions are a bit like views in that they do additional processing (or filtering) only more powerful and nobody complains about views, either.

Also, there's an ANSI standard for PL/SQL. If oracles PL/SQL is a superset of that you might be in lich and not be tied down so much. At least not when mysql comes with its own supposedly ANSI compliant PL/SQL.

As for tied down, yes, but depending on the performance you want a "tied down" solution might be the best one available.

Lots of Greetings!
Volker Received on Thu Jul 01 2004 - 13:24:14 CDT