1 table1 structure as follows
2 ID int
3 name varchar (50)
4
5 declare @id int
6 DECLARE @name varchar (50)
7 declare CURSOR1 cursor for--defines a cursor Cursor1
8 SELECT * FROM Table1-object with cursor (fill in select text as required)
9 Open Cursor1--opening cursors
10
FETCH NEXT from the Cursor1 into @id, @name--Move the cursor down by 1 rows, get the data into the previously defined variable @id, @name
12
While @ @fetch_status = 0--Determine if the data was successfully obtained
Begin
Update table1 set name=name+ ' 1 '
where [email protected]--handle it (fill in the SQL text as needed)
17
FETCH NEXT from Cursor1 into @id, @name--Move cursor down 1 rows
End
20
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 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 results
DeptID username
1 AB
2 C
[requires a cursor to implement the design: ok_008
Time: 2006-05
Remark: None
*/
CREATE TABLE #Temp1 (deptid int,userid int,username varchar (20))-data table to be tested
CREATE TABLE #Temp2 (deptid int,username varchar (20))--Results table
--First insert some of the data to be tested into the table under test #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 to @deptid, @username--The column data of 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 succeeded
@ @FETCH_STATUS =-1 FETCH statement failed or this row is not in the result set
@ @FETCH_STATUS =-2 The fetched row does not exist
*/
Begin
--Append @username values directly to the column username when the same data exists in the table #temp2 column DeptID
if (exists (SELECT * from #Temp2 where [email protected]))
Update #Temp2 set username=username [email protected] where [email protected]
Else
--Insert new data
Insert INTO #Temp2 select @deptid, @username
FETCH NEXT from Select_cursor to @deptid, @username
End
Close Select_cursor
Deallocate select_cursor
SELECT * FROM #Temp2-test results
Drop table #Temp1, #Temp2
SQL Cursor Basic Usage
