By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,292 Members | 1,721 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,292 IT Pros & Developers. It's quick & easy.

what are cursors in sql???

P: n/a
I am new to sql and require some help on cursors?
what are they and how and why are they used for???
it will be kind enough if anyone helps me in this regards..
regards
vishal jain.

Feb 17 '06 #1
Share this Question
Share on Google+
17 Replies


P: n/a
vishal wrote:
I am new to sql and require some help on cursors?
what are they and how and why are they used for???
it will be kind enough if anyone helps me in this regards..
regards
vishal jain.


Cursors are data structures that allow you to manipulate the results of
a query one row at a time. Typically we only do that in client
application code not in SQL. That's because it is usually much faster,
easier, more concise and more maintainable to use "set based" SQL code
- code that manipulates entire sets of data rather than one row at a
time.

If you are new to SQL then my advice is that you ignore cursors until
you get a lot more expert with set based SQL code. Although there are
certainly legitimate uses for them, cursors are too often badly misused
by the inexperienced who don't know any better. Only when you have lots
and lots of experience in "real" SQL will you have the sense to
recognise when a cursor is an appropriate solution.

That may not sound like a very helpful answer but if you consider that
probably at least 99% of what the average SQL developer does can be
done without a cursor you will realise that there are lots more
important things to learn first.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Feb 17 '06 #2

P: n/a
interesting take on cursors.
I have never used cursors in client code. seemed pretty inefficient to
pass all that crud across the net, and leave the cursor open until the
clients cpu can process it.

i've used cursors extensively in stored procedures.

The cursor is the last resort. Whenever possible use set based
solutions. But if you have to process each record step by step,
sometimes it is the only way.

Feb 26 '06 #3

P: n/a
Doug wrote:
interesting take on cursors.
I have never used cursors in client code. seemed pretty inefficient to
pass all that crud across the net, and leave the cursor open until the
clients cpu can process it.

That's true. Usually most processing should happen at the server. I
didn't mean to imply otherwise. A client recordset is a cursor of sorts
and that's what I was referring to.
i've used cursors extensively in stored procedures.


Really? For most production tasks I'd say that more than 99% of data
manipulation can best be done without a cursor. Certainly if you make
"extensive" use of cursors then I'd suggest you should be looking
harder for better solutions. That's unless you are writing lots of
metadata management or admin processes. For example I think I've
written maybe 3 or 4 cursors in the last 6 years and all were for
DBA-type tasks.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Feb 27 '06 #4

P: n/a
Hello,
I would agree that you do not need cursors in 99 percent of the cases.
In fact, as an example, I bet for most apps there are at least 300
selects for every cursor.

But sometimes you just can't get around it. Sometimes the business
logic just forces you to examine each "record" once, and do weird logic
on it. And sometimes, the logic is such that one result can change the
way you process the rest.

I had an app once that took money, and split it into diffferent
buckets. The calculations were very complex, and the rules were
actually stored in tables. As an example, sometimes there were
percentages, sometimes hard amounts, and the number of buckets you
dumped the money into changed radically. Further, you had to look in a
table to figure out whether to charge sales tax on the whole thing,
parts of it, or none of it.

I dumped all this into a stored procedure, and used a cursor to load
teh rules for splitting the money.
It was not going to work on set based logic. And, I really didn't want
the rules and calculations completed on the client level. And if
something went REALLY wrong, I wanted to back the whole thing out.
For this, SQL server, stored procedures, and cursors were really cool.

It can be "proven" using a mathematic proof that cursors ARE required
in certain circumstances. Like we both said, cursors are by far the
exception, adn like you said, cursors are very often implemented too
frequently.

regards,
doug

Mar 1 '06 #5

P: n/a
Doug wrote:
Hello,
I would agree that you do not need cursors in 99 percent of the cases.
In fact, as an example, I bet for most apps there are at least 300
selects for every cursor.

But sometimes you just can't get around it. Sometimes the business
logic just forces you to examine each "record" once, and do weird logic
on it. And sometimes, the logic is such that one result can change the
way you process the rest.

I had an app once that took money, and split it into diffferent
buckets. The calculations were very complex, and the rules were
actually stored in tables. As an example, sometimes there were
percentages, sometimes hard amounts, and the number of buckets you
dumped the money into changed radically. Further, you had to look in a
table to figure out whether to charge sales tax on the whole thing,
parts of it, or none of it.

I dumped all this into a stored procedure, and used a cursor to load
teh rules for splitting the money.
It was not going to work on set based logic. And, I really didn't want
the rules and calculations completed on the client level. And if
something went REALLY wrong, I wanted to back the whole thing out.
For this, SQL server, stored procedures, and cursors were really cool.

Fine example. Exceptions prove the rule.
It can be "proven" using a mathematic proof that cursors ARE required
in certain circumstances.


I'm not so sure what you are referring to. Do you have a reference?
Mathematical proofs are usually more concerned with the relational
model rather than with the illegitimate child we call SQL. As regards
RM, given that only relational operations are possible I'm pretty
certain your statement goes against conventional knowledge and proofs
of the completeness of relational languages. Here I'm dimly recalling
the chapters on recursion and completeness from Abiteboul et al
("Database Foundations"). I don't have that book with me at the moment
but I can post a better reference when I get home.

As far as SQL is concerned, SQL99 supports recursive queries and a
procedural language that is Turing-complete so intuitively I'd say you
would be wrong to claim that SQL is incomplete without cursors. Of
course I entirely accept arguments about "practicability" in certainly
DBMSs but that's a long way from a mathematical proof that cursors are
"required".

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Mar 1 '06 #6

P: n/a
Interesting challenge. Can we "prove" that cursors are "REQUIRED" in a
relational model?

Someone somewhere is having epileptic fits on that statement.

All I have to do is come up with a single example of data selection
that cannot be solved without cursors. Right?

Hmmmm. I'm sure open for suggestions!

Mar 2 '06 #7

P: n/a
Doug wrote:
Interesting challenge. Can we "prove" that cursors are "REQUIRED" in a
relational model?

Someone somewhere is having epileptic fits on that statement.

Abiteboul, Hull and Vianu p469 describe a language they call "WHILEnew"
and show that it can solve any deterministic query. Of course you can
add non-determinism to the picture but then we aren't discussing
relational problems any more.
All I have to do is come up with a single example of data selection
that cannot be solved without cursors. Right?


Not really, no. The relational model forbids explicit "tuple-at-a-time"
operations in any case. It is still perfectly possible to show that
some practical result cannot be obtained in some particular relational
*language*. For example, Codd's original relational algebra doesn't
support aggregation or transitive closure.

For SQL however, the problems are a bit more murky because even
standard SQL allows you to do lots of non-relational and
non-deterministic things in queries. So problems that are hard or
unsolvable with set-based SQL are interesting to those of us who use
the language (especially on usenet) but probably less attractive from a
theory perspective.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Mar 2 '06 #8

P: n/a
Stu
Sorry to butt in, but I think the answer to the question of "to curse,
or not to curse" (what else would you call stepping through a cursor?)
lies in David's statement of what the "average SQL programmer" does.
Some of us are outliers.

I think that if you do a lot of integration work where you're
retriveing data from databases where you have little control over the
design (ie, third-party applications, legacy databases, etc) then
you'll find yourself using more of the specialty tools of SQL Server
than standard SQL. If you're working in data warehousing where data
denormalization is a standard practice, then flattening your data
during the import process using a cursor may be a viable option (and
possibly your only viable option).

However, I do agree that 99% of the time for MOST developers, cursors
are not the optimal choice, and if you work with them a lot, it's a
struggle to think in set-based terms. In my shop, we alternate between
OLAP and OLTP a lot, and I have to mentally switch gears to keep from
applying procedural logic to my relational designs and relational logic
to my procedural operations.

To answer the OP, I think David is correct; ignore cursors for now.
Learn how to design and think in sets, and save row-based operations
until much later in your career.

Stu

Mar 2 '06 #9

P: n/a
>> Interesting challenge. Can we "prove" that cursors are "REQUIRED" in a
relational model? <<

There is a proof that a finite automata with a push-down stack can
solve the same set of problems as primitive recursive functions, so
declarative and procedural languages can do the same things.

Mar 2 '06 #10

P: n/a
the jump where sql can be categorized as "primitive recursive
functions" lost me.

Is there a practical limit on how levels of recursion SQL supports?
Does the SQL spec guarantee that recursion has to be available for say
1000 layers?

As I understand your logic, you are stating you can use declarative SQL
recursively to fulfill the looping role. I am questioning whether the
SQL spec guarantees say a million levels of recursion.

Am I understanding your premise correctly?

Mar 2 '06 #11

P: n/a
Doug wrote:
the jump where sql can be categorized as "primitive recursive
functions" lost me.

Is there a practical limit on how levels of recursion SQL supports?
Does the SQL spec guarantee that recursion has to be available for say
1000 layers?

As I understand your logic, you are stating you can use declarative SQL
recursively to fulfill the looping role. I am questioning whether the
SQL spec guarantees say a million levels of recursion.

Am I understanding your premise correctly?


Of course there are practical limits to everything in finite state
machines and real hardware. What is your point? That the resource
constraints on cursors are inherently less onerous than on recursive
queries? If we are talking at the logical level then that is
irrelevant. If we are talking about real implementations then I think
any of the major SQL databases demonstrate otherwise. Those systems are
designed and optimized to perform with set-based operations and more
often than not the set-based operations perform best.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Mar 2 '06 #12

P: n/a
>> the jump where sql can be categorized as "primitive recursive functions" lost me.<<

Just about any functional or declarative language like APL, LISP, FP,
et al can be derived from primitive recursive functions. Of course,
the whole of integer math and number theory can be derived from Peano's
Postules. Nobody in their right mind would do it, of course.
Is there a practical limit on how levels of recursion SQL supports? <<
No; language standards never have limits. Implementations have limits.

Does the SQL spec guarantee that recursion has to be available for say
1000 layers?
As I understand your logic, you are stating you can use declarative SQL recursively to fulfill the looping role.<<


No, for a lot of "looping things", I use a Sequence auxiliary table and
an inner join. I use a nested set table. And I sometimes I convert
generator functions into a closed form, on those rare occasions when
the full power of an MS in math are required.


Am I understanding your premise correctly?

Mar 3 '06 #13

P: n/a
David Portas wrote:
That the resource

constraints on cursors are inherently less onerous than on recursive
queries?

There is a proof, genearally accepted as valid, that any mathematical
construct or programming problem that can be solved using a loop can
also be solved by using recursion.
However, in most of the languages I have found looked at, there is a
wall of some sorts limiting how many times you can recurse. As an
example, LISP is designed to recurse, and there is no practical limit.
On the other hand, SQL has almost no practical limit for looping in
most implementations.
My question was...... Does the SQL spec have any hints as to a minimum
recursion required? Is there any real limit in SQL server? I've done
almost no recursion in SQL. Kind of a different mind set, and I just
don't think that way in SQL.
Recursion for the right problem can be a truly elegant solution.

Mar 3 '06 #14

P: n/a
>I use a Sequence auxiliary table and
an inner join. I use a nested set table. And I sometimes I convert
generator functions into a closed form,

I'd be very interested in reading more about this. Never done it, never
seen it. I would like to learn.
o MS in math are required.


Interesting. We think differently, but in some ways similarly. My
undergrad was MS in Math, minors fizzicks and CS. for grad, did
business.
I love to pick nits, but I am trying to pick nits that matter. Often
my loves overcome my attempts.

Mar 3 '06 #15

P: n/a
Dave ---

Please use English for us po' folk.
"David Portas" <RE****************************@acm.org> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
Doug wrote:
the jump where sql can be categorized as "primitive recursive
functions" lost me.

Is there a practical limit on how levels of recursion SQL supports?
Does the SQL spec guarantee that recursion has to be available for say
1000 layers?

As I understand your logic, you are stating you can use declarative SQL
recursively to fulfill the looping role. I am questioning whether the
SQL spec guarantees say a million levels of recursion.

Am I understanding your premise correctly?


Of course there are practical limits to everything in finite state
machines and real hardware. What is your point? That the resource
constraints on cursors are inherently less onerous than on recursive
queries? If we are talking at the logical level then that is
irrelevant. If we are talking about real implementations then I think
any of the major SQL databases demonstrate otherwise. Those systems are
designed and optimized to perform with set-based operations and more
often than not the set-based operations perform best.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Mar 4 '06 #16

P: n/a
Try this as an example, with one of my cut & paste for cutting up a CSV
list

This is a common Newbie question. This shows that you don't know SQL
uses only scalar parameters and has only one data structure, the table.
This is a fundamental programming concept that you should learn in the
first week of any SQL language class. SQL is not your original
procedural programming language.

1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter; it
is not code. Again, this is not just an SQL problem; this is a basic
misunderstanding of programming principles.

2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.

This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.

CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);

It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):

INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;

The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma. You can add a computation for the
relative postion of each element in the list (left as a exercise for
the student)

You can then write:a query like this:

SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE parm IS NOT NULL);

Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice altho they do not know it.

3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array" made from a repeated
gorup, like this in SQL/PSM (translate into your local dialect):

CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm -- kill redundant dups
FROM (VALUES (p1), (p2), .., (pN)) AS ParmList(parm)
WHERE parm IS NOT NULL -- ignore empty aparameters
AND <other conditions>)
AND <more predicates>;
<more code>;
END;

The idea is that creating a derived table will perform better .You can
also add functions to the parameters like UPPER(pi), apply CASE
expressions like in T-SQL

(CASE WHEN @p1 = 'usa' THEN @p2 ELSE 2.2 * @p2 END)

or use scalar subqueries like this on subsets of the parameters:

(SELECT L.address_code
FROM Locations AS L
WHERE @p1 = L.longitude
AND @p2 = L.latitude
AND @p3 = 'Paris');

SQL Server can have up to 1,024 parameters in a stored procedure and
that is usually good enough. If not, make two calls to the procedure
but have you ever seen a procedure in any language with over 1,024
parameters?

Mar 4 '06 #17

P: n/a
Hmmmm.

Perhaps I'm missing something. Rather then use recursion, or use a
loop, you are suggesting that we somehow load all possible instances
into parameters, then pass them as parameters.
Option 1 requires simple memory manipulation without any accessing of
the disk. My experience has indicated that we are ALWAYS better off to
crunch in memory without resorting to accessing the disk drive.
For option 2,
I'd be very curious as to the logic where an index could be used to
prevent a complete table scan would not be required of FOOBAR. In
other words, I can't imagine this being nearly as fast as a simple join
against a temp table.
For option 3.
Ok. I'll bite. How do you load all of those fine data values into
those fine parameters without loops and without recursion? And, any
suggestions as to how you would go about dealing with, say, 10,000
iterations, or at least, the possiblity of 10,000 iterations?
Would you REALLY create 10,000 variables, or would you just give up,
and use a loop or recursion?
I'd be very interested in learning of the application where your
solution 3 was actually used.
Thanks in advance,
-doug

Mar 6 '06 #18

This discussion thread is closed

Replies have been disabled for this discussion.