What logic is recommended to put in Stored procedures?

I see the advantages of running code in the database engine instead of distributing this out of the database and the advantage of the opposite aswell.

But the real question for me to be answer is what logic is recommended to put in stored procedures? I think i need examples, should there be indata verification? business rules, as in should be do this? 开发者_如何转开发When is it actually a good example too use stored procedures?

Thanks in advance.

It entirely depends on what you are building, how you are building, what kind of developers you are working with and decision is lot more religious and philosophical than technical. That said following are some of my thoughts and I'm sure there will be many people who'd consider all of these are wrong :) so consider them as personal opinions, and dont really take them as guidelines in your work and do your due diligence and studies before you decide.

Against

Bunch of SQL statements, really thats what they are because even if you compile them against a specific schema, but lets say if you drop a column which is used in one of the procedures, does stored procedure give you a red error message? no, atleast I've not seen one, unless you go and compile every SP after every change in you schema (The databases I've seen generally dont, I dont know the state of the art on databases) That brings to a bigger problem does whole of your database schema can be compiled? versioned? deployed like you deploy a binary release or a website? based on my experience and knowledge there are many techniques but they hardly work or followed well.

Ok now to debugging, can a stored procedure debugged or tested without having the database it is deployed on? as far as I think or know you need the entire database to debug or test SPs. Where as your Java code can be independently debugged/tested without needing entire database as long as you can provide the abstractions of the data dependencies.(I'm a C# dev now, I've doen little java with JDK1.2)

What about all the awesomeness of object orientation like encapsulation, abstraction and other conecpts, can a stored procedure be made private to specific class of objects? (there are techniques to do that again, but how many times it is done zero times in my experience). Data Types? have you seen any complex type processing in stored procedure? I mean passing an array of student objects to stored procedure, and how easy or difficult is that? as far as I know its pretty difficult to handle complex types in SQL or make everything XML. Also how is logging, tracing done? as far as my experienc of seeing huge SPs go it is againt tied down to a database server and its not easily configurable to use other mechanisms if you want so.

Lets say you know a specific SP is doing some heavy lifting now you want to spawn off 10 threads and run some part of your code concurrently? how easy is that? I dont know, its hard again.

For

Becuase stored procedures are run inside the database servers, and not really compiled, they have distinct advantages over other code you write in Java or any highlevel language. Heavy data crunching, if you have a specific need of utilizing a lot of tables for calcuating something and then updating this calculated data to bunch of other tables etc, performance is better (not scalability) in this case and there is no datatransfer latency between between the code and data. Change - Since stored procedure change is ideally open your sp in a text editor and run the modified script on the database server to deploy this kind of provides a flexibility over other compiled languages, so to unblock a production issue you change a couple of SPs and you deploy to the database done fix is out users can use the app now, as you dont need to compile all depedencies or deploy all pieces (deployment generally is complicated in large projects and takes hours together to get it right). CRUD - anything that is simple as Create, Read, Update, Delete operations should be encapsulated in SPs. Any operation that requires you to define temp tables or table variables or cursors definitely needs to be relooked at and ask is it the right place?

There are a lot more considerations to be given before you code a peice of feature/functionality in Stored Procedures or Java(in your case), but as I said in the starting it entirely depends.

** Edit **

Therefore based on these considerations its better to avoid any logic in stored procedure unless it is really data-intensive and doesnt need any complex types. If you are having a well defined business logic which is modelled using java objects its better to code up the logic in respective business classes rather than SPs. SP should contain logic pertaining to store data, update data, delete data or retrieve data any other logic than this should be avoided in SP.

In routines flow control constructs are available. e.g. - IF, WHILE, ... which sometimes are helpful to write a logic.

And of course, sp is a code that can be used many times.

It's a good question. The short answer (as it always is) is "it depends"

I'm going to include functions and tvf in this...

For me:

1) Insert interfaces. If you have a logical concept of a "something" and you've modelled that something using many tables with tight relationships ( an employee would be a classic example ) then (imho) it's better to write a sp that will insert a new employee and have that proc deal with the right order of table insertion ( dealing with the foreign keys etc ). Than it would be to do it all inline code in your appliation

2) Output interfaces. Having an sp that returns the logical representation of something in one place is good. That means the underlying tables can change and you will have to change the sp. Otherwise you'd have to trawl through the codebase of your app.

I've never been absolutely happy with buisness rules in the database but a lot of companies have databases groaning with them.