Recognize SQL cursors from a small example

In the original: A small example of understanding SQL cursors

1 What is a cursor:

Operations in a relational database work on the entire rowset. For example, the rowset returned by the SELECT statement includes all rows that satisfy the criteria in the WHERE clause of the statement. The complete rowset returned by the statement is called the result set. Applications, especially interactive online applications, do not always effectively handle the entire result set as a unit. These applications require a mechanism to process one or a subset of rows at a time. Cursors are an extension of the result set that provides this mechanism.

Cursors extend the processing of results in the following ways:

• Allows locating specific rows in the result set.
• Retrieves a row or part of a row from the current position of the result set.
• Supports data modification of rows at the current position in the result set.
• Provides different levels of visibility support for changes made by other users to the database data that is displayed in the result set.
• Provides Transact-SQL statements that are used in scripts, stored procedures, and triggers to access data in a result set.  

--msdn

It is not difficult to understand that the biggest difference between a cursor and other database operations is that the object is a single record rather than a result set, and is typically used for SQL statements embedded in a procedural program. The automatic implicit creation of cursors is used in the database service program.

2 Basic usage:

2.1 Declaring Cursors

DECLARE Wiper name Cursor

For SELECT statement

2.2 Opening Cursors

OPEN cursor name

2.3 Fetching data from cursors

FETCH NEXT from cursor name [into Fetch_list]

Getting data from a cursor requires attention to the possibility of reaching the end of the cursor, which resolves this problem to prevent the user from generating an error when the cursor is closed

1 BEGIN2  　　 DECLARE @custname VARCHAR( -)3  　　 DECLARENamecursorCURSOR  for SELECTCust_name fromTbl_customerOPENNamecursor4  　　 FETCH NEXT  fromNamecursor into @custname5  　　  while(@ @FETCH_STATUS <> -1)6  　　 BEGIN7  　　　　 IF(@ @FETCH_STATUS <> -2)8  　　　　　　 BEGIN9  　　　　　　　　 --Manipulating cursor VariablesTen  　　　　　　 END One  　　　　 FETCH NEXT  fromNamecursor into @custname A  　　 END -  　　 CLOSENamecursor -  　　 deallocateNamecursor the END

2.4 Closing Cursors

CLOSE cursor name

The cursor cannot be read after closing, but can be opened again using the Open statement

2.5 Releasing Cursors

deallocate cursor name

Delete cursor, no longer available

31 Interesting Little Examples:

Although the concept and basic usage of cursors are known, it is unclear when a cursor is used, or even a cursor can be replaced by a quilt query. Until I met this interesting little example:

The table structure is as follows:

The topic requirements are: list different combinations of employees who work in the same job but belong to different departments

This is the result:

After thinking about the sub-queries, table joins, temporary tables, and so on, I found myself encountering an insurmountable hurdle: the uniqueness of the two-name combination cannot be ruled out. That is: The result I get may be the following

ANAME	Bname
Adams	James
James	Adams

Finally I thought about the cursor I just learned, the code is as follows

1 SELECTA.ename asANAME, B.ename asbname2  into#t3  fromEMP A4 JOINEMP B5  onA.job=B.job andA.deptno<>B.deptno andA.ename<>B.ename6 ORDER  byANAME7 8 DECLARETest_cursorCURSOR  for9 SELECTANAME, Bname from#tTen  One OPENTest_cursor A DECLARE @ANAME VARCHAR( -) - DECLARE @BNAME VARCHAR( -) -  the FETCH NEXT  fromTest_cursor into @ANAME,@BNAME - DELETE  from#tWHEREANAME=@BNAME  andBname=@ANAME -  while @ @FETCH_STATUS = 0 - BEGIN  + FETCH NEXT  fromTest_cursor into @ANAME,@BNAME - DELETE  from#tWHEREANAME=@BNAME  andBname=@ANAME + END A  at CLOSETest_cursor - deallocateTest_cursor -  - SELECT *  from#t

Finally got the expected results, but I think this problem may be more than this one solution, I hope there are other solutions to Daniel can point twos ^^

Recognize SQL cursors from a small example