Post #344,116
6/23/11 8:10:58 PM
|
Well, it depends
on what you're trying to do. Do you want to have your stored procedures to be nothing more than a single SELECT, UPDATE or DELETE statement? You can if you want to. They're big on that here at my current employer. But then, you might as well just execute the SQL command from your application and not have SPs at all.
The flip side is companies that want the business logic in SPs. For instance, I worked with a guy who interviewed with AIG years ago and he said that back then they had SPs that ran from 5,000 to 10,000 line long. In those SPs it absolutely makes sense to have the SQL code compiled and stored in the database.
"Chicago to my mind was the only place to be. ... I above all liked the city because it was filled with people all a-bustle, and the clatter of hooves and carriages, and with delivery wagons and drays and peddlers and the boom and clank of freight trains. And when those black clouds came sailing in from the west, pouring thunderstorms upon us so that you couldn't hear the cries or curses of humankind, I liked that best of all. Chicago could stand up to the worst God had to offer. I understood why it was built--a place for trade, of course, with railroads and ships and so on, but mostly to give all of us a magnitude of defiance that is not provided by one house on the plains. And the plains is where those storms come from."
-- E.L. Doctorow
|
Post #344,123
6/23/11 10:06:20 PM
|
5,000 to 10,000 lines long?
Amateurs.
The biggest one I've seen was 15K. Once you have that much PL/SQL you really get to experience at a visceral level all of the issues I mentioned in the other post.
Regards, -scott Welcome to Rivendell, Mr. Anderson.
|
Post #344,144
6/24/11 1:07:06 PM
|
I still win
32K lines in a single C header file.
To be included in every program compiled.
On a slowwwwww machine.
No, I didn't write it.
Left the jobs within 2 weeks of being told I had to work for the guy who wrote it.
|
Post #344,145
6/24/11 1:17:55 PM
|
Let me guess - No comments, too? ;-)
|
Post #344,177
6/24/11 8:08:50 PM
|
Don't recall
If there was, they were stupider than the code.
|
Post #344,231
6/25/11 8:12:24 PM
|
I remember looking at it...
When I did something for you... you had to show it to me...
No there were no comment and its was serious Cut and Paste.
|
Post #344,229
6/25/11 6:42:10 PM
6/25/11 6:42:35 PM
|
Hmmm.
Until you have some database code that, for every call, creates TCP sockets, makes external procedure calls (socket to process to C library, woo), builds up and tears down all of its package state (DBMS_SESSION.RESET_PACKAGE), logs megabytes of text per second, simulates synchronous calls using AQ with a 30 second timeout, stores data using comma delimited strings, XML, and UDTs, and generates HTML using string catenation in PLSQL... UNTIL then no, no you do not actually "win".
And no comments, natch.
Regards, -scott Welcome to Rivendell, Mr. Anderson.
Edited by malraux
June 25, 2011, 06:42:35 PM EDT
|
Post #344,230
6/25/11 7:37:19 PM
|
:-)
|
Post #344,217
6/25/11 4:04:44 PM
|
they were written over a dozen years ago
so they've probably grown since then ;-)
"Chicago to my mind was the only place to be. ... I above all liked the city because it was filled with people all a-bustle, and the clatter of hooves and carriages, and with delivery wagons and drays and peddlers and the boom and clank of freight trains. And when those black clouds came sailing in from the west, pouring thunderstorms upon us so that you couldn't hear the cries or curses of humankind, I liked that best of all. Chicago could stand up to the worst God had to offer. I understood why it was built--a place for trade, of course, with railroads and ships and so on, but mostly to give all of us a magnitude of defiance that is not provided by one house on the plains. And the plains is where those storms come from."
-- E.L. Doctorow
|
Post #344,148
6/24/11 1:46:34 PM
|
Amateur
Even your simple select/update/delete statements should be stored procedures because then they'll be compiled and you'll avoid the overhead of query plan calculation on each call.
Or so I've been told. :-/
--
Drew
|