/*The table1 structure is as follows ID intname varchar (a)*/Declare @id intDeclare @name varchar( -)DeclareCursor1cursor for --Defining Cursors Cursor1Select * fromTable1--objects using cursors (fill in select text as required)OpenCursor1--Open CursorFetch Next fromCursor1 into @id,@name --move the cursor down by 1 rows to get the data into the previously defined variable @id, @name while @ @fetch_status=0 --determine if the data was successfully obtainedbeginUpdateTable1SetName=Name+'1'whereId=@id --Handle it accordingly (fill in the SQL text as needed)Fetch Next fromCursor1 into @id,@name --move the cursor down by 1 rowsEndCloseCursor1--Close CursorsdeallocateCursor1
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)
/*function: Database table tbl_users Data deptid userid username1 A1 101 B2 102 C requires an SQL statement to output the following results DeptID UserName1 AB2 c[requires a cursor to implement the design: ok_008 time: 2006-05 Note: no*/Create Table#Temp1 (DeptIDintUseridint, usernamevarchar( -))--data tables to be testedCreate Table#Temp2 (DeptIDint, usernamevarchar( -))--Results Table--Insert some of the data to be tested into the table #temp1 to be tested firstInsert 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 All Select 2,202,'a' Union AllSelect 2,221,'e' Union AllSelect 3,102,'y' Union All Select 3,302,'e' Union AllSelect 3,121,'T' --Declare @deptid int,@username varchar( -)--Defining CursorsDeclareSelect_cursorcursor for SelectDeptid,username from#Temp1OpenSelect_cursorFetch 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 DeptID if(exists(Select * from#Temp2whereDeptID=@deptid )) Update#Temp2SetUsername=Username+@username whereDeptID=@deptid Else --inserting new data Insert into#Temp2Select @deptid,@username Fetch Next fromSelect_cursor into @deptid,@username EndCloseSelect_cursordeallocateSelect_cursorSelect * from#Temp2--Test ResultsDrop Table#Temp1, #Temp2
SQL Cursors Cursor Basic usage