Cursor works on single record at a time.Performance hit:It works slowly than the SET based operation.
*********************************************************************************
Simple Cursor statements
*********************************************************************************
********
Declaring a Cursor
********
--Declaring a cursor
DECLARE FilmCursor CURSOR
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
********
Opening and Closing Cursors
********
OPEN FilmCursor
--Do something useful here
FETCH NEXT FROM FilmCursor
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM FilmCursor
CLOSE FilmCursor
DEALLOCATE FilmCursor
*********************************************************************************
Calling a Stored Procedure
*********************************************************************************
******** Executing a Stored Procedure with a Cursor: ********
---Below Cursor definition
******** Reading Values into Variables: ******** DECLARE @ID INT DECLARE @Name VARCHAR(MAX) DECLARE @Date DATETIME
--Declaring a cursor
DECLARE FilmCursor CURSOR
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
OPEN FilmCursor
--Do something useful here
FETCH NEXT FROM FilmCursor INTO @ID,@Name,@Date
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC spListCharacters @ID,@Name,@Date
FETCH NEXT FROM FilmCursor INTO @ID,@Name,@Date
END
CLOSE FilmCursor
DEALLOCATE FilmCursor
*********************************************************************************
Types of Cursors
SQL Server Different Types of Cursors
Understanding SQL Server Fast_Forward Server Cursors
*********************************************************************************
********
OTHER FETCH OPTIONS for Cursors: DECLARE FilmCursor CURSOR SCROLL/FORWARD_ONLY
*******
--Do something useful here
FETCH FIRST/LAST FROM FilmCursor
~~~~~~~~
FETCH ABSOLUTE 10 FROM FilmCursor
--ABSOLUTE - 10 (for cursor to move backward).
WHILE @@FETCH_STATUS = 0
FETCH RELATIVE 10 FROM FilmCursor
--RELATIVE - 10
********
Setting the Scope of a Cursor: DECLARE FilmCursor CURSOR LOCAL/GLOBAL
********
Note:Right Click->Database->Properties->Default Cursor(LOCAL or GLOBAL)
********
Record Set Types for Cursors:
DECLARE FilmCursor CURSOR STATIC/DYNAMIC/CURSOR_FAST_FORWARD
********
Note: CURSOR_FAST_FORWARD( readonly),STATIC( copy of data in tempdb),KetSET(Key values in tempdb),DYNAMIC( change key or nonkey values)
********
Record Locking Options for Cursors:
DECLARE FilmCursor CURSOR READ_ONLY/SCROLL_LOCKS/OPTIMISTIC
Note:OPTIMISTIC(updates when you change ,if inbetween someone changes it keeps its update)
********
********Combining Cursor Options:
DECLARE FilmCursor CURSOR GLOBAL FORWARD_ONLY STATIC READ_ONLY
********
********
Declaring Cursors for Updating Records:
--Declaring a cursor
DECLARE FilmCursor CURSOR
FOR SELECT FilmID, FilmName, FilmReleaseDate FROM tblFilm
FOR UPDATE OF FilmCumulativeOscars
********
Running Total:
********
DECLARE @filmOscars INT
DECLARE @TotalOscars INT
SET @TotalOscars = 0
********
Declaring a Cursor
********
--Declaring a cursor
...Refer Video
FETCH NEXT FROM FilmCursor INTO @FilmOscars
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotalOscars += @FilmOscars
PRINT @TotalOscars --comment it out
UPDATE tblFilms SET FilmCumulativeOscars = @TotalOscars WHERE CURRENT OF FilmCursor
FETCH NEXT FROM FilmCursor INTO @FilmOscars
END
.....Refer Video
*********************************************************************************
No comments:
Post a Comment