Unlike actual programming language code, they:
- not portable (every db has its own version of PL/SQL. Sometimes different versions of the same database are incompatible – I’ve seen it!)
- not easily testable – you need a real (dev) database instance to test them and thus unit testing their code as part of a build is virtually impossible
- not easily updatable/releasable – you must drop/create them, ie modify the production db to release them
- do not have library support (why write code when someone else has)
- are not easily integratable with other technologies (try calling a web service from them)
- they use a language about as primitive as Fortran and thus are inelegant and laborious to get useful coding done, so it is difficult to express business logic, even though typically that is what their primary purpose is
- do not offer debugging/tracing/message-logging etc (some dbs may support this – I haven’t seen it though)
- lack a decent IDE to help with syntax and linking to other existing procedures (eg like Eclipse does for java)
- people skilled in coding them are rarer and more expensive than app coders
- their “high performance” is a myth, because they execute on the database server they usually increase the db server load, so using them will usually reduce your maximum transaction throughput
- inability to efficiently share constants (normally solved by creating a table and questing it from within your procedure – very inefficient)
- etc.
If you have a very database-specific action (eg an in-transaction action to maintain db integrity), or keep your procedures very atomic and simple, perhaps you might consider them.
Caution is advised when specifying “high performance” up front. It often leads to poor choices at the expense of good design and it will bite you much sooner than you think.
Use stored procedures at your own peril (from someone who’s been there and never wants to go back). My recommendation is to avoid them like the plague.