Sql-server Declaration cursor

server| Cursor Sql-server Declaration cursor
Each cursor must have four components, these four key parts must conform to the following order;
1.DECLARE Cursors
2.OPEN Cursors
3. Fetch information from a cursor
4.CLOSE or deallocate cursors
Usually we use declare to declare a cursor declaring a cursor that consists mainly of the following main elements: Cursor name data source (table and column) Select condition attributes (read-only or modifiable)
The syntax format is as follows:
DECLARE cursor_name [insensitive] [SCROLL] Cursor
For select_statement
[For {READ only | UPDATE [of column_name [,... N]]}]
of which: cursor_name
Refers to the name of the cursor. Insensitive
Indicates that MS SQL SERVER stores the data records selected by the cursor definition in a temporary table (built under the tempdb database). The read operation on the cursor is answered by a temporary table. As a result, modifications to the base table do not affect the data that is extracted by the cursor, that is, the cursor does not change as the contents of the base table change, nor does it pass
Cursor to update the base table. If you do not use the reserved word, updates and deletions to the base table will be reflected in the cursor.

It should also be noted that the cursor automatically sets the INSENSITIVE option when the following conditions occur.
Use a distinct, GROUP by, having UNION statement in a SELECT statement;
Use outer JOIN;
Any table selected has no index;
Treats a real value as a selected column. SCROLL
Indicates that all extraction operations (such as the PRIOR, NEXT, relative, and absolute) are available. If you do not use the reserved word, you can only perform a next fetch. Thus, SCROLL greatly increases the flexibility of extracting data, and can read any row of data records in the result set without having to close
Re-open the cursor. Select_statement
Is the SELECT statement that defines the result set. It should be noted that compute, compu-te by, a for BROWSE, and into statements cannot be used in cursors. READ only
Indicates that the data within the cursor is not allowed to be updated although in the default state the downstream subscript is allowed to be updated. And in the WHERE CURRENT OF clause of an UPDATE or DELETE statement, the cursor is not allowed to be referenced. UPDATE [of Column_name[,... N]]
Defines the columns that can be modified in the cursor, and if the column to be updated is not indicated, all columns are updated. When a cursor is successfully created, the cursor name becomes the unique identifier of the cursor, and if the cursor is used in a later stored procedure, trigger, or Transact_sql script, the name of the cursor must be specified.
The above is the sql_92 of the rules of the banner law. The extended Cursor declaration syntax provided by MS SQL SERVER is described below, and the functionality of the cursor is further enhanced by adding additional reserved words;
Local
The scope of the definition cursor is limited to the stored procedure, trigger, or batch in which it resides. After the execution of the stored procedure that created the cursor is completed, the cursor is automatically freed. Therefore, we often use the output reserved word in the stored procedure, passes the cursor to the caller of the stored procedure, so that after the execution of the stored procedure is completed, the cursor variable can be referenced, in which case the cursor is automatically freed until the last one referencing the cursor is released. GLOBAL
The scope of the cursor is defined as the entire session-level session layer refers to the user's connection time, which includes the entire period of time from the user logging in to SQL Server to the database. Selecting global indicates that the cursor can be used in any stored procedure, trigger, or batch process throughout the session layer, and that the cursor is automatically freed only when the user is detached from the database.
Note: If neither global nor local is used, SQL Server will use the default local cursor database option, which is often set to false for the purpose of apologizing to an outstanding version. Forward_only
option indicates that only the fetch NEXT action can be selected when data records are extracted from the cursor only in the order from the first row to the last line. Unless you use the static, keyset, and dynamic keywords, if you do not specify whether to use Forward_only or scroll, then forward_only becomes the default option because if you use the static keyset and dynamic keyword, it becomes a scroll cursor. In addition, if you use the forward_only, you cannot use Fast_forward. STATIC
The meaning of an option is the same as the insensitive option, where MS SQL SERVER stores the data records selected by the cursor definition in a temporary table (built under the tempdb database). The read operation on the cursor is answered by a temporary table. Therefore, the modification of the base table does not affect the data in the cursor, that is, the cursor does not follow the underlying table contents
Changes, and you cannot update the base table with cursors. Keyset
Indicates that when the cursor is opened, the order of the columns in the cursor is fixed, and MS SQL SERVER creates a table within tempdb that is the key value of the keyset keyset that uniquely identifies a row of data in the cursor. This change is reflected in the cursor when the cursor owner or other user modifies the Non-key value data in the base table, so the cursor user or owner can limit the data by scrolling the cursor.

The data cannot be read by this cursor when other users add a new data that conforms to the defined cursor range. Because the Transact-SQL Server cursor does not support INSERT statements.
If a row in the cursor is deleted, when the delete row is fetched through the cursor, the @ @FETCH_STATUS return value is-2. @ @FETCH_STATUS is a system global variable used to determine whether a read cursor is successful.
Because the update operation consists of two parts: deleting the original data and inserting new data, the @ @FETCH_STATUS returns a value of 2 if the original data is read, and the newly inserted data cannot be read through the cursor. However, if you use the WHERE CURRENT OF clause, the newly inserted row data is visible.
Note: If the underlying table does not contain a unique index or primary key, a keyset cursor will revert to a static cursor. DYNAMIC
Indicates that changes to the underlying table will be reflected in the cursor, using this option to maximize data consistency. However, this type of cursor requires a large number of cursor resources compared to the keyset and static type cursors. Fast_forward
Indicates a forward_only, read_only-type cursor. This option has been optimized for execution. If the scroll or for_update option is defined, the FAST_FORWARD option cannot be defined. Scroll_locks
Indicates that the lock was placed in the data used by the cursor result set fooled. Locks appear when data is read into the cursor. This option ensures that update and delete operations on one cursor can always be executed successfully. You cannot select this option if the FAST_FORWARD option is defined. In addition, because data is locked by a cursor, you should avoid using this option when considering concurrent processing of data. Optimistic
Indicates that if a row of data in a cursor has changed since the data was read into the cursor, updating or deleting the cursor data may cause failure. This option is not available if the Fast_forward option is used. Type_warning
Indicates that a warning message is sent to the client if the cursor type is modified to be different from the user-defined type.
Note: It is not possible to mix the sql_92 's cursor rules with that of MS SQL Server. Here we will summarize some of the issues that should be noted when declaring cursors.
If you use scroll or insensitive reserved words before cursor, you cannot use any reserved words between cursor and for select_statement. Vice versa.
If the read_only, optimistic, or scroll_locks option is not selected when the cursor is declared with declare CURSOR, the default for the cursor is:
If the SELECT statement does not support updating, the cursor is read_only;
The STATIC and Fast_forward types of cursors default to Read_Only;
The DYNAMIC and keyset cursors default to optimistic.
We can reference cursors only in Transact-SQL statements, not in database API functions.
After a cursor is declared, its attributes can be set through a system procedure.
It also has default permissions to use cursors for users who have permission to execute a SELECT statement on a view, table, or some column.