Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates.
All requests to the cursor are answered from this temporary table in tempdb ; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications. This option overrides the default capability of a cursor to be updated. LOCAL Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created.
The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter.
If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect. All insert, update, and delete statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched.
Because the cursor cannot be scrolled backward, however, changes made to rows in the database after the row was fetched are not visible through the cursor. Forward-only cursors are dynamic by default, meaning that all changes are detected as the current row is processed. This provides faster cursor opening and enables the result set to display updates made to the underlying tables. While forward-only cursors do not support backward scrolling, applications can return to the beginning of the result set by closing and reopening the cursor.
STATIC Specifies that the cursor always displays the result set as it was when the cursor was first opened, and makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb.
Therefore inserts, updates, and deletes made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not detect changes made to the membership, order, or values of the result set after the cursor is opened. Static cursors may detect their own updates, deletes, and inserts, although they are not required to do so.
For example, suppose a static cursor fetches a row, and another application then updates that row. If the application refetches the row from the static cursor, the values it sees are unchanged, despite the changes made by the other application.
Deallocate: In this part, we delete the cursor definition and release all the system resources associated with the cursor. A static cursor can move forward and backward. It is identical to the static except that you can only scroll forward. If another user deletes a record, it is inaccessible from our recordset. When a row or column is exclusively locked, other users are not permitted to access the locked data until the lock is released. It is used for data integrity. This ensures that two users cannot simultaneously update the same column in a row.
It specifies that the cursor will lock the rows as they are read into the cursor to ensure that updates or deletes made using the cursor will succeed. So, the updates or deletes made using the cursor will not succeed if the row has been updated outside the cursor.
The statement has been terminated. When executing this cursor, we will get an error because the static cursor does not allow modifications in data. View All. As far as the buyer is concerned, the products appear one at a time, but the application uses a scrollable cursor to browse up and down through the result set.
Sensitive to underlying table changes caused by other applications such as membership, sort, inserts, updates, and deletes. Complex cursors can be defined with keysets that point back to base table rows. Although some cursors are read-only in a forward direction, others can move back and forth and provide a dynamic refresh of the result set based on changes that other applications are making to the database.
Not all applications need to use cursors to access or update data. Some queries simply do not require direct row updating by using a cursor. Cursors should be one of the last techniques you choose to retrieve data-and then you should choose the lowest impact cursor possible. When you create a result set by using a stored procedure, the result set is not updateable using cursor edit or update methods.
In some multiuser applications it is very important for the data presented to the end user to be as current as possible. A classic example of such a system is an airline reservation system, where many users might be contending for the same seat on a given flight and therefore, a single record.
In a case like this, the application design must handle concurrent operations on a single record. In other applications, concurrency is not as important.
In such cases, the expense involved in keeping the data current at all times cannot be justified. A cursor also keeps track of the current position in a result set. Think of the cursor position as a pointer to the current record, similar to the way an array index points to the value at that particular location in the array.
The type of cursor employed by your application also affects the ability to move forward and backward through the rows in a result set; this is sometimes referred to as scrollability. The ability to move forward and backward through a result set adds to the complexity of the cursor, and is therefore more expensive to implement.
For this reason, you should ask for a cursor with this functionality only when necessary. Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services.
0コメント