I heard it isn't good to make use of cursors, because they "abnormal" to DBMS plus they provide bad perfomance.
But think of the following situation: I've got a saved procedure and I have to refer to this as saved technique of every customer from France (for instance). I've got a couple of options, for example using cursor, write all stuff in a single query and call saved technique of every customer in the client-side-application.
Basically write all stuff in a single query - it will in all probability duplicate code/logic/whole query from already established saved procedure. Also it appears like a "smelly approach" (should you read "Refactoring" book) in my experience. The logic isn't incapsulated in one location any longer.
What is your opinion?
PS. Links to the documents explaining why cursors can be harmful or pretty good are welcome.
If you are devoted to getting business logic as saved methods around the database a cursor is pretty good.
Presuming you've got a pretty standard client-server-database architecture it's most likely a much better idea to maneuver the logic from the database and in to the application server. It has a few benefits:
- Better scalability. Its simpler/cheaper to include application-servers than database servers.
- Centralizing business logic. A credit card applicatoin with business logic spread throughout is harder to keep.
Also keep in mind that cursor performance will be different from RDBMS to RDBMS.
However, I believe that the situation can be created that cursors are abnormal to some SQL database (and you will find some experts about them who'd argue this). Should you consider it, a cursor could be regarded as an iterator (or perhaps a pointer if you want to become nasty). Even though iterators work nicely in procedural languages, they do not easily fit in well having a declarative language like SQL.
Now, I've not really used cursors enough to agree or disagree with this type of thought. However I will state that after i consider it, I can not really think about questions that I have written which were simplified using a cursor (not saying they do not exist).
You will find occasions whenever a cursor may be the right tool to make use of. You will find in other cases when it is easier to retrieve a whole query, and work on it as being a collection.
SQL provides extensive set oriented procedures included in it. For instance, an UPDATE can work on a whole group of rows from the table. If there is a WHERE clause, individuals would be the rows which get up-to-date. The update may use context sensitive subqueries and Situation constructs use a large amount of versatility when it comes to upgrading different rows in apparently various ways.
Indicating a monumental data transformation like a single UPDATE can appear just like a daunting task to some programmer who's just approaching to hurry on SQL. It is so much simpler to declare a cursor, loop with the rows came back, treat each row like a record, and revert to 1 record at any given time processing. As lengthy as the participation with databases remains "lite", that might be adequate for you personally.
But when you anticipate to construct industrial strength databases, it behooves you to definitely learn to manipulate data when it comes to teams of rows, and not simply one row at any given time. You will get better performance. Possibly more to the point, you will get better clearness concerning the relastionship between your underlying business rules and also the code you've written.
It's much simpler to function on teams of data inside a smartly designed database compared to a poorly designed database. If you are just approaching to hurry on database design, and merely approaching to hurry on SQL queries simultaneously, you might like to obtain a mentor to advise for your database design. If you do not do this, you might have a difficult time learning the energy and simplicity of set oriented procedures.
And, you will find still occasions when you will employ cursors.
For the way your store procedure was written, you may have the ability to convert it right into a function if you work with SQL Server. With functions that you can do something alone the lines of:
SELECT uf_MyFunction(customer_id, customer_name, customer_address) FROM Customer
to use it to each single customer record
Cursors aren't always bad, it is only that generally where your instincts tell you to employ them, there's a far more performant method of doing exactly the same factor utilizing a declarative or set based approach. Should you publish the more knowledge about your proc, I wager you're going to get good quality recommendations for how you can do the thing you need having a single saved proc call with no cursor.