In-depth introduction to the basic usage of SQL Cursor

Since executing the cursor is equivalent to executing the SELECT statement, the efficiency is not flattering and has not been studied in depth. Copy codeThe Code is as follows: table1 structure:
Id int
Name varchar (50)

Declare @ id int
Declare @ name varchar (50)
Declare cursor1 cursor for -- defines the cursor cursor1
Select * from table1 -- the object using the cursor (enter select text as needed)
Open cursor1 -- open a cursor

Fetch next from cursor1 into @ id, @ name -- move the cursor down one row and put the obtained data in the variable @ id, @ name

While @ fetch_status = 0 -- determines whether data is successfully obtained
Begin
Update table1 set name = name + '1'
Where id = @ id -- perform corresponding processing (enter the SQL text as needed)

Fetch next from cursor1 into @ id, @ name -- move the cursor down one row
End

Close cursor1 -- close the cursor
Deallocate cursor1

Cursor general format:
Declare cursor name cursor for select Field 1, Field 2, Field 3,... FROM table name WHERE...
OPEN cursor name
Fetch next from cursor name INTO variable name 1, variable name 2, variable name 3 ,...
WHILE @ FETCH_STATUS = 0
BEGIN
SQL statement execution process ......
Fetch next from cursor name INTO variable name 1, variable name 2, variable name 3 ,...
END
CLOSE cursor name
DEALLOCATE cursor name (delete cursor)Copy codeThe Code is as follows:
/*
Function: Database Table tbl_users data
Deptid userid username
1 100
1 101 B
2 102 c
An SQL statement is required to output the following results.
Deptid username
1 AB
2 c
[Requires the use of cursors for design: OK _008
Time: 2006-05
Note: None
*/
Create table # Temp1 (deptid int, userid int, username varchar (20) -- data table to be tested
Create table # Temp2 (deptid int, username varchar (20) -- result table
-- Insert some data to be tested into the table to be tested # Temp1
Insert into # Temp1
Select 1,100, 'A' union all
Select 1,101, 'B' union all
Select 1,131, 'd union all
Select 1,201, 'F' union all
Select 2,302, 'C' union all
Select 2,202, 'A' union all
Select 2,221, 'E' union all
Select 3,102, 'y' union all
Select 3,302, 'E' union all
Select 3,121, 'T'
--
Declare @ deptid int, @ username varchar (20)
-- Define a cursor
Declare Select_cursor cursor
Select deptid, username from # Temp1
Open Select_cursor
Fetch next from Select_cursor into @ deptid, @ username -- put the extracted column data into a local variable
While @ fetch_status = 0 -- returns the status of the last cursor executed by the FETCH statement
/*
@ FETCH_STATUS = 0 The FETCH statement is successful.
@ FETCH_STATUS =-1 The FETCH statement fails or this row is not in the result set
@ FETCH_STATUS =-2 The extracted row does not exist.
*/
Begin
-- When the deptid in table # Temp2 has the same data, the @ username value is directly appended to the column username.
If (exists (select * from # Temp2 where deptid = @ deptid ))
Update # Temp2 set username = username + @ username where deptid = @ deptid
Else
-- Insert new data
Insert into # Temp2 select @ deptid, @ username
Fetch next from Select_cursor into @ deptid, @ username
End
Close Select_cursor
Deallocate Select_cursor
Select * from # Temp2 -- Test Result
Drop table # Temp1, # Temp2