DML Statements contd..

1. DML Statements contd.. SQL Server 2005

2. CURSORS • Cursor is used in handling results of select query for data calculations • Cursors are used as buffered storage of table information

3. Types of CURSORS [based on accessibility] • Updatable • Cursors which are used as resultset in SP and are updated in functional process lines. • Readonly • Cursors which are only used to read and this doesn’t support updation in functional process lines.

4. Types of CURSORS [based on functionality] • Based on scope • LOCAL • GLOBAL • Based on navigation • FORWARD_ONLY • SCROLL • STATIC • KEYSET • DYNAMIC • FAST_FORWARD • Based on Locks • READ_ONLY • SCROLL_LOCKS • OPTIMISTIC

5. Cursors with scope • 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. • GLOBAL • Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect. Note: Cursor names can be used without @ symbol in declaration statements

6. LOCAL Cursor example Procedure XYZ start Local cursor declaration …………………. …………………. …………………. Procedure XYZ end In the above example the local cursor declared within XYZ procedure is accessible only within the procedure and will not have accessibility outside the procedure

7. GLOBAL Cursor example Procedure XYZ start Global cursor declaration …………………. Call to an external procedure ABC Procedure XYZ end Procedure ABC start use of Global cursor Procedure ABC end In the above example the global cursor declared within XYZ procedure and is accessible in procedure ABC.

8. Cursors with navigation • FORWARD_ONLY • Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option • SCROLL • Specifies that all fetch options (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) are available • STATIC • Defines a cursor that 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, 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. • KEYSET • Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. • DYNAMIC • Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors. • FAST_FORWARD • Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.

9. Cursors with locks • READ_ONLY • Prevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated. • SCROLL_LOCKS • Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. Microsoft SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified. • OPTIMISTIC • Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.

10. General Syntax DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ][ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ]

11. Indicators • @@FETCH_STATUS • Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

12. Functions OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name } Opens a Transact-SQL server cursor and populates the cursor by executing the Transact-SQL statement specified on the DECLARE CURSOR or SET cursor_variable statement. CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name } Closes an open cursor by releasing the current result set and freeing any cursor locks held on the rows on which the cursor is positioned DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name } Removes a cursor reference. When the last cursor reference is deallocated, the data structures comprising the cursor are released by Microsoft SQL Server.

13. FETCH           [ NEXT | PRIOR | FIRST | LAST |ABSOLUTE/ RELATIVE {n | @nvar ]                 FROM            { [ GLOBAL ] cursor_name } | @cursor_variable_name INTO @variable_name [ ,...n ]

14. Example • Demo

15. Operations • Batch • Row

16. Select in Insert Eg: insert into inv_temp select itemid,itemname from inventory

17. For XML Clause • <select qry> for XML [AUTO | RAW | PATH | EXPLICIT] • AUTO – Table name as Tag • RAW - <Row> as Tag • PATH – Hierarchical view • EXPLICT – Functional hierarchical view