Getting Started with SQL Server 7.0 (7)

i. Declaring the cursor
In this step, you need to specify the properties of the cursor and the result set generated according to the requirements. There are two ways to specify a cursor.
Form 1 (ANSI 92)
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE][OF column_list]}]
Form 2
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
INSENSITIVE keyword indicates that a temporary copy is to be created for the retrieved result set, and future data will be obtained from this temporary copy. If the data in the original base table changes during subsequent cursor processing, they will not be visible to the cursor. This insensitive cursor does not allow data changes. The
SCROLL keyword indicates that the cursor can scroll in any direction. All fetch options (first, last, next, relative, absolute) can be used in cursors. If this option is omitted, the cursor can only scroll forward (next).
Select_statement specifies the result set created by the SQL statement. The Transact SQL statements COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed in the select statement of a cursor declaration.
READ ONLY indicates that data modification is not allowed in the cursor result set. The
UPDATE keyword indicates that the cursor's result set can be modified.
OF column_list specifies the columns in the result set that can be modified. By default (using the UPDATE keyword), all columns can be modified. The
LOCAL keyword indicates that the cursor is local and can only be used within the process in which it is declared. The
GLOBAL keyword makes the cursor globally visible to the entire connection. Global cursors are available anytime the connection is active. Only when the connection ends is the cursor no longer available.
FORWARD_ONLY specifies that the cursor can only scroll forward.
STATIC cursors are the same as INSENSITIVE cursors.
KEYSET specifies the order of selected rows. SQL Server will create a temporary keyword set from the result set. If modifications are made to non-keyword columns of the database, they are visible to the cursor. Because it is a fixed set of keywords, modifications to the keyword column or newly inserted columns are not visible.
DYNAMIC indicates that the cursor will reflect all modifications to the result set.
SCROLL_LOCK is to lock modification or deletion in order to ensure the success of cursor operations.
OPTIMISTIC indicates which modifications or deletions through the cursor will not succeed.
Note:
· If DISTINCT, UNION, and GROUP BY statements are used in the SELECT statement, and the selection contains an aggregate expression, the cursor will automatically be an INSENSITIVE cursor.
· If the base table does not have a unique index, the cursor is created as an INSENSITIVE cursor.
· If the SELECT statement contains ORDER BY, and the column being ORDER BY is not a unique row identifier, the DYNAMIC cursor will be converted into a KEYSET cursor. If the KEYSET cursor cannot be opened, it will be converted to an INSENSITIVE cursor. The same is true for cursors defined using SQL ANSI-92 syntax, but without the INSENSITIVE keyword.
II. Open the cursor
open the cursor is to create a result set. The cursor is defined through the DECLARE statement, but its actual execution is through the OPEN statement. The syntax is as follows:
OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
GLOBAL specifies a global cursor.
Cursor_name is the name of the open cursor.
Cursor_variable_name is the variable name of the referenced cursor. The variable should be of cursor type.
After the cursor is opened, the system variable @@cursor_rows can be used to detect the number of rows in the result set. When @@cursor_rows is a negative number, it means that the cursor is being migrated asynchronously, and its absolute value (if @@cursor_rows is -5, the absolute value is 5) is the number of rows in the current result set. Asynchronous cursors enable users to still access the results of a cursor while the cursor is completely migrated.
          iii.                                                                                                                                                                                                                                                                                   but ii. If the cursor is defined as scrollable (using the SCROLL keyword when declaring it), any row in the result set can be retrieved at any time. For non-scrolling cursors, fetch operations can only be performed on the row next to the current row. The result set can be retrieved from local variables. The syntax of the Fetch command is as follows:
FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
FROM [GLOBAL] cursor_name} | cursor_variable_name}
[INTO @variable_name ][,...n]]
NEXT specifies to get the value from the next row of the current row.
PRIOR specifies taking the value from the previous row of the current row.
FIRST is the first row of the result set.
LAST is the last row of the result set.
ABSOLUTE n represents the nth row in the result set. The row number can also be propagated through a local variable. Line numbers start from 0, so you can't get any lines when n is 0.
RELATIVE n means that the row to be fetched is n rows before or n rows after the current row. If the value is a positive number, the row to be fetched is located n rows before the current row. If the value is negative, the row after the current row is returned.
INTO @cursor_variable_name represents the variable list where the cursor column value is stored. The number of variables in this list should be the same as the number of variables used by the select statement in the DECLARE statement. The data type of the variable should also be the same as the data type of the selected column. The value in the variable will be retained until the next time the FETCH statement is used.
Each FETCH execution is stored in the system variable @@fetch_status. If the FETCH is successful, @@fetch_status is set to 0. @@fetch_status of -1 indicates that part of the result set has been reached (for example, a row in the base table was deleted after the cursor was opened). @@fetch_status can be used to construct a cursor processing loop.
For example:
DECLARE @iname char(20), @fname char(20)
OPEN author_cur
FETCH FIRST FROM author_cur INTO @iname, @fname
WHILE @@fetch_status = 0
BEGIN
IF @fname = 'Albert'
PRINT “Found Albert Ringer”
ELSE
Print “Other Ringer”
FETCH NEXT FROM author_cur INTO @iname, @fname
END
                     iv. Close the cursor
CLOSE statement is used to close the cursor and release the result set. After the cursor is closed, no FETCH operation can be performed. If you still need to use the FETCH statement, you need to reopen the cursor. The grammar is as follows:
Close [Global] Cursor_name | Cursor_variable_name
V. Release the cursor
the cursor is no longer needed, to release the cursor. The DEALLOCATE statement releases locks on data structures and cursors. The syntax is as follows:
DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name

A complete example of a cursor is given below:
USE master
GO
CREATE PROCEDURE sp_BuildIndexes
AS
DEC LARE @TableName sysname, @msg varchar(100), @cmd varchar(100)

DECLARE table_cur CURSOR FOR
SELECT name FROM sysobjects WHERE type='u'

OPEN table_cur
FETCH NEXT FROM table_cur INTO @TableName

WHILE @@fetch_status = 0
BEGIN
IF @@fetch_status = -2
CONTINUE
S ELECT @msg = “Building indexes for table”+@TableName+”…”
PRINT @msg
SELECT @cmd = "DBCC DBREINDEX ('"+@TableName+"')"
EXEC (@cmd)
PRINT " "
FETCH NEXT FROM table_cur INTO @TableName
END
DEALLOCATE table_cur
GO
The following script will execute sp_BuildIndexes
USE pubs
GO
EXEC ap_BuildIndexes
for the PUBS database. NOTE: The above is also an example of creating a user-defined system stored procedure.

Use temporary table
Temporary tables are tables created in TempDB. The names of temporary tables all start with "#". The scope of a temporary table is the connection that created the temporary table. Because temporary tables cannot be shared between two connections, the temporary table will be discarded once the connection is closed. If the temporary table is created within a stored procedure, the scope of the temporary table is within the stored procedure, or any stored procedure called by the stored procedure. If you need to share temporary tables between connections, you need to use global temporary tables. The global temporary table starts with the "##" symbol and will exist in the database until SQL Server is restarted. Once such a temporary table is created, all users can access it. Permissions cannot be specified explicitly on temporary tables. Temporary tables provide the ability to store intermediate results. Sometimes, temporary tables can also improve performance by breaking a complex query into two queries. This can be achieved by first storing the results of the first query in a temporary table and then using the temporary table in the second query. Temporary tables are recommended when a subset of a large table is used multiple times during an existing procedure. In this case, keeping a subset of the data in a temporary table for use in subsequent joins can greatly improve performance. Indexes can also be created on temporary tables.

The above is the content of Getting Started with SQL Server 7.0 (7). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!