SQL Server First-knowledge cursors

---restore content starts---

　　　

Cursor: A cursor is a mechanism for extracting one piece at a time from a result set containing multiple data

Cursors are characterized by:

• • • More flexibility in retrieving the resulting data set
    • Data can be manipulated in a targeted direction
    • Have the ability to delete and update data

Why cursors are used:

• • • Cursors provide a good solution to turn batch operations into row operations.

Steps for Cursors

1. 1. Defining cursors
   2. Open cursor
   3. Using cursors
   4. Close Cursors
   5. Delete cursor

Code Explanation:

1. Defining cursors

　　　　　　　　DECLARE  Cursor name  [][]cursor for T- SQL Statements 　　　　　　　　　　

2. Open the cursor

OPEN cursor name

　　　　　　

3. Using Cursors

FETCH       [NEXT | PRIOR | First | Last     | ABSOLUTE {n | @nvar}     | RELATIVE {n | @nvar}  　　　　　　　　　　　　]from    
into assigns values to local variables

　　　　　　　　　　　　

4. Close the cursor

CLOSE cursor name

5. Deleting cursors

           deallocate cursor name        

　　　　　　

Example:

Premise: Table Stuscore

In-table data:

　　　　　　　　　　

Purpose: To change a table into a form

　　　　　　

Code　　　　　

 　　　　　　　　 / * Define local variables for storing SQL statements * /
Declare @sql varchar(Max)Set @sql='SELECT DISTINCT (sname)' 　　　　　　　　 /*define the local variables used by the cursor*/ 　　　　　　 Declare @cname varchar( -)/*Defining Cursors*/ 　　　　　　 DeclareCur_stuscorecursorRead_Only for Select distinct(CNAME) fromStuscore/*Open Cursor*/ 　　　　　　 OpenCur_stuscore/ *using Cursors*/ 　　　　　　 Fetch Next  fromCur_stuscore into  @cname 　　　　　　　　 /*use the While loop to make the statement traverse the entire table*/ 　　　　　　  while(@ @FETCH_STATUS=0)begin    　　　　　　　　  Set @sql=@sql+', (select S.score from Stuscore s where S.cname=" "+@cname+" "and S.sname=t.sname) as" "+@cname+" '"    　　　　　　　　  Fetch Next  fromCur_stuscore into  @cname 　　　　　　 End　　 / *Update SQL statements*/ 　　　　 Set @sql=@sql+'From Stuscore T' 　　　　　　　　 /*Execute SQL statement*/ 　　　　　　 exec(@sql)/ *Close Cursors*/ 　　　　　　 CloseCur_stuscore/*Delete Cursor*/ 　　　　　　 deallocateCur_stuscore


The Ps:sql execution statement is:
 　 

SELECT DISTINCT (sname),

(select S.score from Stuscore s where s.cname= ' language ' and s.sname = t.sname) as ' language ',

(select S.score from Stuscore s where s.cname= ' mathematics ' and s.sname = t.sname) as ' mathematics ',

(select S.score from Stuscore s where s.cname= ' English ' and s.sname = t.sname) as ' English '

From Stuscore t

SQL Server First-knowledge cursors