Because this cursor is executed it is equivalent to a select its efficiency is not flattering nor doing in-depth research.
Copy Code code as follows:
The Table1 structure is as follows
ID int
Name varchar (50)
DECLARE @id int
DECLARE @name varchar (50)
Declare cursor1 cursor for--Define cursors Cursor1
SELECT * FROM table1-objects that use cursors (fill in the Select text as needed)
Open Cursor1--opening cursors
FETCH NEXT from Cursor1 into @id, @name--Moves the cursor down 1 rows, gets the data into the previously defined variable @id, @name
While @ @fetch_status = 0--To determine whether the data was successfully fetched
Begin
Update table1 set name=name+ ' 1 '
where id= @id--to be processed appropriately (SQL text is required)
FETCH NEXT from Cursor1 into @id @name--Move the cursor down 1 lines
End
Close Cursor1--closing cursors
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 Procedure ...
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 Code code as follows:
Example:
/*
Function: Database table tbl_users Data
DeptID userid Username
1 A
1 b
2 102 C
requires an SQL statement to output the following results
DeptID username
1 AB
2 C
[Design required for cursor implementation: 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 of the data to be tested into the test table #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)
--Defining cursors
DECLARE select_cursor cursor FOR
Select Deptid,username from #Temp1
Open Select_cursor
FETCH NEXT from Select_cursor into @deptid, @username--The column data of the fetch operation is placed in a local variable
While @ @fetch_status = 0-Returns the state of the last cursor executed by the FETCH statement
/*
@ @FETCH_STATUS =0 FETCH statement succeeded
@ @FETCH_STATUS =-1 FETCH statement failed or the row is not in the result set
@ @FETCH_STATUS =-2 The fetched row does not exist
*/
Begin
--Append @username value directly to the column username when the same data exists in the table #temp2 column DeptID
if (exists (SELECT * from #Temp2 where deptid= @deptid))
Update #Temp2 set Username=username + @username where deptid= @deptid
Else
--Inserting 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 results
Drop table #Temp1, #Temp2
