MS SQL Basics Tutorial: Reading data from a cursor

When a cursor is successfully opened, data can be read from the cursor line by row for related processing. Reading data from a cursor mainly uses the FETCH command. Its syntax rules are:

The meaning of each parameter is as follows:

Next

Returns the next row of the current row in the result set and increases the number of rows that are returned. If fetch next is the first time the data is read from the cursor, the first row instead of the second row is returned in the result set.

Prior

Returns the first row of the current row in the result set and decreases the number of rows that are returned. If the fetch prior is the first time the data in the cursor is read, no data records are returned and the cursor position is set to the first row.

First

Returns the first row in the cursor.

Last

Returns the last row in the cursor.

Absolute {n | @nvar}

If n or @nvar is positive, the number of rows of data returned from the cursor is represented. If n or @nvar is negative, returns the nth or @nvar row of data from the last row of data within the cursor. If n or @nvar exceeds the range of data subsets of the cursor, the @ @FETCH_STARS returns 1, in which case if n or @nvar is negative, the Fetch NEXT command gets the first row of data, and if n or @nvar is positive, the fetch PRIOR command Get the last row of data. N or @nvar can be a fixed value or a variable of a smallint, tinyint, or int type.

Relative {n | @nvar}

If n or @nvar is a positive number, read the nth or @nvar row data at the current position of the cursor, or read the nth or @nvar row of data from the current position of the cursor, if n or @nvar is negative. If n or @nvar exceeds the range of data subsets of the cursor, the @ @FETCH_STARS returns-1, in which case the fetch next command gets the first row of data if n or @nvar is negative, and if N or @nvar is positive, the Fetch PRIOR command is executed Will get the last line of data. N or @nvar can be a fixed value or a variable of a smallint, tinyint, or int type.

into @variable_name [,... N]

Allows data read using the FETCH command to be stored in multiple variables. Each variable in the variable row must correspond to the corresponding column in the cursor result set, and the data type of each variable also matches the data type of the data column in the cursor.

@ @FETCH_STATUS global variable returns the state of the last FETCH command executed. When you read data from a cursor every time you use fetch, you should examine the variable to determine whether the last fetch operation was successful and decide how to proceed next. @ @FETCH_STATUS variable has three different return values, such as table 13-2.

When reading data from a cursor using the FETCH command, you should be aware of the following:

When you use the SQL-92 syntax to declare a cursor, when you do not select the scroll option, you can only use the Fetch NEXT command to read the data from the cursor, that is, you can only read one row at a time from the first row of the result set, because you cannot use primary, last, PRIOR, Therefore, the previous data cannot be rolled back. If the scroll option is selected, all fetch operations may be used.

When you use the extended syntax for MS SQL SERVER, you must be aware of the following conventions:

• If the forward-only or Fast_forward option is defined, you can only use the fetch next command;

• All fetch operations can be used if no dynamic, forward_only, or Fast_forward option is defined, and any of the keyset, STATIC, or scroll are defined;

The dynamic SCROLL cursor supports all fetch options but disables the absolute option.

See the full set of "MS SQL Basics Tutorials"