Stored procedures or stored functions in any open source software? [closed]

开发者_StackOverflow This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. Closed 11 years ago.

I've been reading about Mysql stored procedures lately. and... after awhile I see very little use for them personally. ...well...Maybe for security reasons if you only want to give access to part of your data.

Also - I've never seen them being used in the wild. AFAIK - I checked magento, wordpress, joomla, drupal - non of them used stored procs, functions or views.

Can someone suggest any open source piece of software that actually uses stored procedures. I'm eager to see what problems are solved by the authors that can't be solved using standard dynamic sql.

Thanks.

UPDATE:

While I definitely agree with points made by Suroot and duffymo - I'm still looking for an example usage of stored procedures in any real world open source software. If anyone could point me to such a software - plz let me know. :)

---

There are instances where stored procedures work much more efficiently than performing multiple queries over the wire. I.E. if you had 10 or 100 SQL lines would best be done by compiling it into a single procedure that you only have to call once rather than running query by query and doing the processing in your memory. I would say that for the most part stored procedures are a good way to perform maintenance functionality (especially with function scheduling).

Also it provides a centralized point for performing some functionality on the SQL data; let's say that you had 3-5 different applications that are part of a statemachine. Each one of them needs to perform a set of queries in a specific order and give a specific output. It is much simpler to add these as stored procedures as opposed to having to update the functionality in the shared library.

Think of this one as well, what happens if your function in code fails during part of the 10 different SQL queries and you end up in a partial state? I'm talking about a connection failure or some other type of exception that would cause the connection to fail (maybe you tried to parse something and forgot to catch NumberFormatException). In this case a stored procedure would be more likely to succeed as it cannot fail due to a connection failure.

The main thing that you'll get with stored procedures I believe is more of a side effect of implementing things like triggers. Although this is just an assumption that I have.

---

I've been reading about Mysql stored procedures lately. and... after awhile I see very little use for them personally. ...well...Maybe for security reasons if you only want to give access to part of your data.

I believe you're incorrect. Stored procedures are a good way to provide an abstraction layer between applications and the table structure.

Also - I've never seen them being used in the wild. AFAIK - I checked magento, wordpress, joomla, drupal - non of them used stored procs, functions or views.

They aren't required, as your citations prove, but people who like that style would swear by them. People like objects tend to frown on stored procedures; DBAs like them.

One argument against them is that stored procedure languages are generally proprietary. Procedures written for one RDBMS don't port to others without a rewrite. So unless your system is willing to mandate a particular RDBMS, you'll have to steer clear of them.