what is a stored procedure?
Posted: Tue Dec 22, 2009 10:51 pm
can someone explain to me what a stored procedure is in real plain english? i've been reading about it but i am more confused now.
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
I really can't agree with that statement ...PCSpectra wrote:Views (in case you are unaware) are similar to stored procs, but less powerful.
PCSpectra wrote:A bad practice.In all serious-ness.
Your second section highlights the most useful aspect of stored procedures. They're essentially "database functions" that should be written by someone who knows SQL and databases very well. That fact makes your initial statement very wrong.PCSpectra wrote:A stored proc, when used correctly, acts very much like a view, although with more power. I hate SQL (much like I hate recursive programming, regex, etc) anything beyond two or more JOINs and I start crying. I struggle with wrapping my head around all that relationship resolution stuff. The last place I worked had a guy who was really good with SQL so any time I needed data I would go to the DBA and say I need this data so a few minutes later he would have a stored proc for me which I would call and it would do the rest, such as JOINs, cascading deletes, updates, etc.
It does depend entirely on your skillset and application requirements, operating environment, etc.i think that whether they are a bad practice or not depends on the application. sometimes you have to do work on your database where you don't need any other language, or like you said you may need to perform many operations at once.
Sorry man I'm not sure I follow. I read through the previous thread quickly...what is it about views that I am missing? As I understand views are really only good for compartmentalizing complex SELECTS so instead of having to query 10 tables I could throw that code into a View and the View then acts as a virtual table reflecting all real tables involved???I really can't agree with that statement ...
Yes and no. You can argue until were blue in the face as to whether stored procs are bad practice. If you use them with a great deal of discipline then sure they are awesome. Much like you can use GOTO or globals very sparingly in rare occassions to solve a problem more elegantly. Most will still agree that in general GOTO and globals are bad practice because when they are abused (which is often the case with stored procs for the reasons I stated above) they result in really bad code.They're essentially "database functions" that should be written by someone who knows SQL and databases very well. That fact makes your initial statement very wrong.
What is it about that statement you don't agree with? From the list you have provided, does it not make sense to say Views are less powerful or perhaps flexible or capable would have been the better term?pcspectra wrote:Views (in case you are unaware) are similar to stored procs, but less powerful.VladSun wrote:I really can't agree with that statement ...
Code: Select all
Based on the realization that the stored procedure author need not be the same person who writes the Java code, and may not even know Java or JUnit yada yada
funIt's more like comparing arrays and functions - that's why I don't agree with you.PCSpectra wrote:I certainly didn't mean to imply that stored procs and views are technically equal or similar like a function versus procedure in some langauges.
Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable.
In such a case I would usually expose an API to the database instead of allowing direct access. Depending on the level of cohesion between the applications and the database.there are more than one applications (and maybe a developers teams) using the same DB
That is a good argument but in that case I think I would provide a web services API that Windows desktop applications and/or web applications would use.- there are more than one applications (and maybe a developers teams) using the same DB;
pytrin wrote:In such a case I would usually expose an API to the database instead of allowing direct access. Depending on the level of cohesion between the applications and the database.there are more than one applications (and maybe a developers teams) using the same DB
An SP is an abstraction in this case, IMHO ...PCSpectra wrote:Personal preference, obviously using a web services layer is going to be slower than direct access, I prefer abstraction over performance, so depending on what side of the fence your on, that will be a big factor in your decision making.