Maintenance of the SQL Server database (four) __ cursor (CURSOR)

--Maintenance Database--

-- cursors (cursor)--

-- Overview :

Note: The result set for querying results using a SELECT statement is a whole, and cursors can provide this processing mechanism if you want to work with one row or branch of data at a time. cursors can be understood as pointers . Which record the pointer points to, and which record is the action record .

How the cursor handles the result set:

1) allows locating rows at the specified location in the result set .

2) retrieves a row or a branch record from the current position of the result set .

3) Support for data modification, deletion and other operations on the current position of the result set .

-- using Cursors

Note: the general steps for locating and manipulating data records using cursors are: Declaring cursors, opening cursors, extracting data, closing cursors, and releasing cursors .

-- declaring a cursor (declare ...cursor)

Note: declaring a cursor is similar to declaring a variable with the DECLARE command .

Declare cursor name cursor--declare represents the cursor name, cursor indicates the cursor meaning

[ forward_only] [scroll] [read_only] [dynamic] --optional forward_only indicates that the cursor is a forward-only cursor. Optional read_only defines the cursor as a read-only cursor. The optional scroll represents the option to use all the extracted data. The optional dynamic indicates that the data in the cursor result set can be modified.

For Select statement [For Update [of field name [, ...] ] The--select statement refers to a query statement that can query to a result set, which cannot contain compute, compute by, and into statements. To make update modifications to the result set, you can use the of Word by to indicate which field names are allowed to be modified, and all fields are modified if you do not specify the field name with.

-- Open cursor

Open The cursor name - The Open command indicates opening. The cursor name must be a defined cursor that is not in use . Just opened the cursor, and the pointer points to the first record in the result set.

The global scalar @ @error can determine if the cursor is open successfully or fails if 0 is returned.

The global variable @ @cursor_rows can return the number of records in the cursor being opened.

-- extracting cursors

Fetch [First | prior | next | last | absolute {n|@ variable} | Relative {n|@ variable}] --Thefetch command means extracting the data. Only one record can be extracted at a time .

[from] cursor name [into @ variable name [, ...]] --[email protected] Variable name , ... Assigns the extracted field value to the specified variable and then uses it.

- -The first command is used to extract a record from the result set and position the pointer.

--Theprior command is used to extract the previous record of the current record and position the pointer. If the current record is the first record in the result set, the prior command moves the pointer up above the first record and does not extract the results.

Thenext command is used to extract the last record of the current record and position the pointer. The newly opened database uses next to extract the first row of records from the result set. If the last row in the result set is recorded as the current record, next causes the pointer to point to the bottom of the last record and does not extract the results.

The last command is used to extract the final record in the result set and position the pointer.

--absolute {n|@ variable}, when n (or @ variable) is positive, returns the nth (or @) bar record from the beginning of the result set, and positions the pointer ; when n (or @ variable) is negative, the nth (or @ variable) bar that starts at the end of the result set is returned and the pointer is positioned.

---relative {n|@ variable}, when n (or @ variable) is positive, returns the nth (or @ variable) bar record from the current row and positions the pointer, and when n (or @ variable) is negative, Returns the nth (or @ variable) bar record from the end of the result set , and positions the pointer.

--You can use the value of the global variable @ @fetch_status to determine whether the fetch command is extracting data . A value of 0 means the extraction to the record , and a value of 1 means that the pointer points to the last record in the result set , and a value of 2 indicates a problem with the extraction operation .

-- close cursor

Close cursor name --After the cursor is closed, you cannot use the FETCH command to extract the records in the cursor unless you reopen it again by using the Open command (open).

-- releasing cursors

deallocate cursor Name --the released cursor can no longer be opened using the Open command unless redefined with the DECLARE command (declaration).

Example: (***Here is a case to help understand***) (see Sales information for December 20, 2012 in the sales information sheet of the Product Management database) UseCommodity Management DatabaseGoSetNocount on--declaring CursorsDeclareC_ Sales Table _ Datecursor forSelectCommodity information sheets-commodity numbers, commodity information sheets-commodity names, sales amounts fromProduct Information Sheet, sales information FormwhereCommodity information Sheets-Product code=Sales Information sheet. Product Code andSales Date='2012-12-20'--Open CursorOpenC_ Sales Table _ Date--Extracting DatabeginPrint '=====2012 Year December 20 sales information ====='Declare @pdno nchar(8),@pdname nvarchar(Ten),@salemoney decimal( -,2)--extracts the first record and assigns the extracted variable to the corresponding variableFetch Next  fromC_ Sales Table _ Date into @pdno,@pdname,@salemoney--use the global variable @ @fetch_status whether the value is 0 as a loop to determine whether to extract records, such as extracted to the output extracted in the loop body value,--then continue to extract the data using the FETCH command until the @ @FETCH_STATUS value is not 0 while(@ @FETCH_STATUS=0)beginPrint 'Product Code:'+@pdno+'Product Name:'+@pdname+'Sales Amount:'+Convert(nchar( -),@salemoney)Fetch Next  fromC_ Sales Table _ Date into @pdno,@pdname,@salemoneyEndEnd--Close CursorsCloseC_ Sales Table _ Date--Releasing CursorsdeallocateC_ Sales Table _ DateSetNocountoffGo

Understanding Examples 

Note: "--" can be seen as a description or comment text

Maintenance of the SQL Server database (four) __ cursor (CURSOR)