Use of Cursors

 1The table1 structure is as follows2Idint    3Name varchar ( -)    4--------------------------5DECLARE @idint    6DECLARE @name varchar ( -)   7DECLARE cursor1 cursor for--Defining Cursors Cursor18 Select* fromTable1--objects using cursors (fill in select text as required)9Open Cursor1--Open CursorTen    OneFETCH NEXT fromCursor1 into @id, @name--The cursor is migrated down, and the obtained data is placed in the previously defined variable @id, @name A     -  while@ @fetch_status =0--determine if the data was successfully obtained -begin theUpdate table1Setname=name+'1'   - where[Email protected]--Handle it accordingly (fill in the SQL text as needed) -     -FETCH NEXT fromCursor1 into @id, @name--Migrate cursors down +End -    +Close Cursor1--Close Cursors Adeallocate 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=0BEGIN 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) Example:/*function: Database table tbl_users data deptid userid username 1 a 1 101 B 2 102 C requires an SQL statement to output the following result D Eptid username 1 ab 2 c*/CREATE TABLE #Temp1 (DeptIDintUseridint, username varchar ( -)) --data table to be tested CREATE TABLE #Temp2 (DeptIDint, username varchar ( -))            --Results Table--Insert some of the data to be tested into the test table #temp1 insert INTO #Temp1Select 1, -,'a'Union AllSelect 1,101,'b'Union AllSelect 1,131,'D'Union AllSelect 1,201,'F'Union AllSelect 2,302,'C'Union AllSelect 2,202,'a'Union AllSelect 2,221,'e'Union AllSelect 3,102,'y'Union AllSelect 3,302,'e'Union AllSelect 3,121,'T'Declare @deptidint, @username varchar ( -) --defining cursors declare select_cursor cursor for          SelectDeptid,username from#Temp1 Open select_cursor fetch next fromSelect_cursor into @deptid, @username--The column data for the fetch operation is placed in the local variable while@ @fetch_status =0--returns the state of the last cursor executed by the FETCH statement/*@ @FETCH_STATUS =0 Fetch statement succeeds @ @FETCH_STATUS =-1 FETCH statement fails or the row is not in the result set @ @FETCH_STATUS =-2 fetched rows do not exist*/         
Begin--append @username values directly to the column username when the same data exists for the table #temp2 column DeptIDif(Exists (Select* from#Temp2whereDeptid=@deptid)) Update #Temp2Setusername=username [email protected]where[Email protected]Else--Insert new data insert INTO #Temp2Select@deptid, @username fetch next fromselect_cursor into @deptid, @username end close Select_cursor deallocate select_cursorSelect* from#Temp2--Test Results Drop table #Temp1, #Temp2

Use of Cursors