Talk:Cursor (databases)

Presume

I presume that there must be some pros with cursors as well? Senappp 21:50, 1 January 2006 (UTC)

Yeah, there are. This article is in pretty poor shape, and I mean to add to it. Time is the limiter, as usual. -- Mikeblas 21:59, 1 January 2006 (UTC)

I'm finally getting 'round to this, so please be patient in the interim. -- Mikeblas 00:35, 7 June 2006 (UTC)

Great! -- Patrickdepinguin 20:37, 9 June 2006 (UTC)

Of course there are, or they wouldn't have been invented in the first place. There are (limited) situations where they make the difference between something being possible or impossible. Which is a pretty compelling advantage in my book. I've already begun improving the article, but will attempt to address this and include a real-world example or two. DigitalEnthusiast 00:54, 15 December 2006 (UTC)

I believe that the "disadvantages" section is partly wrong and partly very imprecise. First, cursors are the only way to transfer data from a relational DBMS to a procedural application (with the exception being a SELECT INTO statement). Next, a FETCH does not imply a network round-trip at all - the DBMS client can use block fetch. Also, what are those "restrictions on SELECT statements" and what is the complex syntax? Examples would be important! After all, declaring a cursor is just prefixing a SELECT statement with DECLARE cursor-name CURSOR FOR statement. Finally, I believe that system-specific things like SQL Server's implementation based on temp tables should go into a separate (sub)section. --Stolze 13:46, 20 December 2006 (UTC)

I'm actually not aware of any restrictions on the SELECT statement; however cursors are not the only way to get data from the db engine to the client app - the DataReader class in .net parses a TDS or Oracle network stream directly as it's sent up. As for the "complex syntax," I think that's showing up because most people who've used SQL are used to set-based operations, and not procedural coding. DigitalEnthusiast 20:58, 20 December 2006 (UTC)

A description of the differences between the five types of cursors would be helpful. --Sapphire Wyvern 03:13, 6 September 2006 (UTC)

Multi-row fetch

The most current SQL standard SQL:2003 does not know the concept of multi-row fetch. Subclause 14.3, "<fetch statement>" has this BNF:

 <fetch statement> ::=
   FETCH [ [ <fetch orientation> ] FROM ] <cursor name> INTO <fetch target list>
 
 <fetch orientation> ::=
     NEXT
   | PRIOR
   | FIRST
   | LAST
   | { ABSOLUTE | RELATIVE } <simple value specification>
 
 <fetch target list> ::=
   <target specification> [ { <comma> <target specification> }... ]

No multi-row stuff here. So I removed this again. --Stolze 15:23, 26 March 2007 (UTC)

How to add parameters in a cursor, take is an example:

CURSOR c_employee (p_dept VARACHAR2) IS

 SELECT   name,salary  
 FROM   t_employee
 WHERE   deptno=p_dept;

--Billbinshi (talk) 06:15, 19 August 2008 (UTC)Bill.Shi

Cursors make you curse

Probably not worthy of trivia, but I had a boss who said this once, and he wasn't very creative, leading me to think someone told him ... DigitalEnthusiast 19:55, 21 December 2006 (UTC)

Nice one

Nice one —Preceding unsigned comment added by

An example and guideline

syntax: declare

cursor c1 is
  select statement
  where condition;
variables datatype;

begin

open c1;
fetch c1 into variables;
exit when c1%notfound;
  do something with the data;
end loop;
close c1;

end;

guideline: Always, use 'exit when c1%notfound' soon after the "fetch cursor" statement to avoid the duplication of the last record of the cursor.

Abhijithdev (talk) 05:56, 8 July 2011 (UTC)