Cursors are server side database objects that are used by applications to apply operations on the database table data on a row-by-row basis. The operations may vary from one row to another row dynamically based on the requirements (business logic) and also multiple operations can be performed on the same row. Typical SQL commands apply the same updates/changes to all the selected rows at once where cursors apply updates/changes one by one row. Cursors must be declared in the database before they can be used/called from the applications [front end]. Afterwards, you can open cursors to fetch data using them, you can fetchrow by row and make multiple operations on the currently active row in the cursor. You should close the cursors and deallocate them after you are done with the cursors.This is the Transact-SQL Extended Syntax:DECLARE cursor_name CURSOR[LOCAL | GLOBAL][FORWARD_ONLY | SCROLL][STATIC | KEYSET | DYNAMIC | FAST_FORWARD][READ_ONLY | SCROLL_LOCKS | OPTIMISTIC][TYPE_WARNING]FOR select_statement[FOR UPDATE [OF column_name [,...n]]]An example cursor:DECLARE AuthorsCursor CURSOR FORSELECT authors.id, au_lname, au_fnameFROM authorsORDER BY authors.idAnother ExampleDECLARE @AuthorID char(11) DECLARE Cursor1 CURSOR READ_ONLYFORSELECT au_idFROM authorsOPEN Cursor1FETCH NEXT FROM Cursor1INTO @AuthorIDWHILE @@FETCH_STATUS = 0BEGIN PRINT @AuthorID FETCH NEXT FROM Cursor1 INTO @AuthorIDENDCLOSE Cursor1DEALLOCATE Cursor1wherecursor_name: The name of the server side cursorLOCAL: The cursor will be available only to the batch, stored procedure, or trigger in which the cursor was created GLOBAL: The cursor is global to the connection FORWARD_ONLY: The cursor can only fetch data sequentially STATIC: The cursor will use a temporary copy of the data instead of base tables KEYSET: Specifies keysets so that the membership and order of rows in the cursor are fixed when the cursor is opened DYNAMIC: The cursor reflects all data changes made to the base tables as you scroll around the cursor FAST_FORWARD: The cursor is FORWARD_ONLY and READ_ONLY READ ONLY: The cursor [data] cannot be updatedSCROLL_LOCKS: The fetched data will be locked OPTIMISTIC: The cursor does not lock rows as they are read into the cursor select_statement: The standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywordsUPDATE [OF column_name [,...n]]: Specifies which columns can be updated
From: http://sitestree.com/?p=4795
Categories:38
Tags:
Post Data:2011-07-27 22:47:54
Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
(Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a>
In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
<a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>
