Docstoc

DML Statements contd

Document Sample
DML Statements contd Powered By Docstoc
					DML Statements contd..


   SQL Server 2005
CURSORS

   Cursor is used in handling results of select
    query for data calculations
   Cursors are used as buffered storage of table
    information
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.
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
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
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
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.
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.
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.
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 ]
Indicators

   @@FETCH_STATUS
       Returns the status of the last cursor FETCH
        statement issued against any cursor currently
        opened by the connection.
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.
FETCH
[ NEXT | PRIOR | FIRST | LAST
|ABSOLUTE/ RELATIVE {n | @nvar ]
FROM
{ [ GLOBAL ] cursor_name } | @cursor_variable_name
INTO @variable_name [ ,...n ]
Example

   Demo
Operations

   Batch
   Row
Select in Insert

Eg:

insert into inv_temp select itemid,itemname
  from inventory
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

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:5/12/2013
language:English
pages:17