Cursors for SQL Server

Generally speaking, we use SQL as a collection-oriented data operation, but cursors provide us with a more powerful capability to perform a selective manipulation of data sets on a row-by-line basis. Of course, cursors also have an unavoidable flaw: inefficiency and complexity. So normal operation processing does not choose to use cursors for data manipulation.

　　Use of cursors:

DECLARE testcur cursor

For select Id,gradefrom student where grade <60 or grade is NULL---declaration cursor

Open Testcur---opening cursors

DECLARE @ID decimal (10,0)
DECLARE @grade varchar---Define parameters in the cursor

FETCH NEXT from Testcur to @ID, @grade---when the cursor is opened, the row pointer will point to the 1th row of the cursor set, and if you want to read the 1th row of data in the cursor set, you must move the row pointer so that it points to line 1th

While @ @fetch_status =0---@ @fetch_status =0 description cursor activity (FETCH NEXT from test Cur) Successful,-1 indicates that the FETCH statement failed or the row is not in the result set,-2 the fetched row does not exist

If @grade is NOT NULL---join logical judgment

Begin

Update student set grade=60 where grade<60 and [email protected]--based on cursor

FETCH NEXT from Testcur to @ID, @grade

End

Else

Begin

Update student set grade= ' missing ' where [email protected]

FETCH NEXT from Testcur to @ID, @grade

End

Close Testcur---closing cursors

deallocate testcur---Delete a cursor

Cursors for SQL Server