--LETS TALK
ABOUT BRIEFLY ABOUT CURSOR--
--1)WHAT IS
CURSOR ?
--ANSWER->A
CURSOR JUST POINT ONE SINGLE RECORD AT A POINT. THEN IT MOVES THROUGH THE
RECORDS ONE ROW AT A TIME UNTIL YOU REACH THE END.
--2)ADV-->IT
GIVES YOU MUCH FINER LEVEL OF CONTROL OVER THE INDIVIDUAL RECORDS IN A DATASET.
--3)DISADVANTAGE-->
IT WORKS SLOWLY..INEVITABLY SLOWER THAN SET BASED OPERATION
--HOW TO WRITE
CURSOR?
CREATE TABLE
CREATE TABLE #TEMP
(
ID INT
IDENTITY(1,1) PRIMARY KEY NOT NULL,
[FILM NAME] VARCHAR(100)
)
INSERT INTO #TEMP
SELECT 'SHOLAY'
UNION ALL SELECT 'DDLJ'
UNION ALL SELECT 'KKHH'
UNION ALL SELECT 'SOLDIER'
UNION ALL SELECT 'KAHO NA PYAR HAI'
UNION ALL SELECT 'KOI MIL GAYA'
UNION ALL SELECT 'KRISH'
UNION ALL SELECT 'DHOOM 2'
UNION ALL SELECT 'KRISH 2'
UNION ALL SELECT 'REFUGEE'
UNION ALL SELECT 'ABCD'
UNION ALL SELECT 'CHENNAI EXPRESS'
UNION ALL SELECT 'HOW MANY TIMES IN MUMBAI'
SELECT * FROM #TEMP
CURSOR SYNTAX
--NOW OUR TABLE
IS CREATED...NOW IF WE DO SELECT STATEMENT WE GET A SET BASED OPERATION...WE
GET ALL THE RECORDS..NOW LETS DO THAT WITH A CURSOR
--SYNTAX
-- DECLARE
-- FOR SELECT
-- OPEN CURSOR
--
PROCESS[DO THE THINGS HERE]
--
CLOSE CURSOR
-- DEALLOCATE CURSOR
--EXECUTE THIS
EXAMPLE 1
DECLARE HINDIFILM_CURSOR CURSOR
FOR
SELECT [FILM
NAME] FROM #TEMP
OPEN
HINDIFILM_CURSOR
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--O/P-- COMMAND
EXECUTED SUCCESSFULLY
EXAMPLE 2
DECLARE HINDIFILM_CURSOR CURSOR
FOR
SELECT [FILM
NAME] FROM #TEMP
OPEN
HINDIFILM_CURSOR
FETCH NEXT FROM
HINDIFILM_CURSOR
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--O/P
SHOLAY--[THIS IS WORKING LIKE A TOP FUNCTION ISN'T IT :) FETCHING THE TOP ROW
THATS IT]
EXAMPLE 3
--EXECUTE
THIS[INTRODUCED ORDER BY]
DECLARE HINDIFILM_CURSOR CURSOR
FOR
SELECT [FILM
NAME] FROM #TEMP ORDER BY ID DESC
OPEN
HINDIFILM_CURSOR
FETCH NEXT FROM
HINDIFILM_CURSOR
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--O/P HOW MANY TIMES IN MUMBAI
FETCH STATUS
--EXECUTE
THIS[INTRODUCED @@FETCH_STATUS]
DECLARE HINDIFILM_CURSOR CURSOR
FOR
SELECT [FILM
NAME] FROM #TEMP ORDER
BY ID DESC
OPEN
HINDIFILM_CURSOR
FETCH NEXT FROM
HINDIFILM_CURSOR
WHILE @@FETCH_STATUS=0--IT MEANS IF IT IS ZERO ..THEN
OUR CURSOR IS ABLE TO FETCH THE NEXT ROW...SO WHEN IT REACHES THE END
FETCH NEXT FROM
HINDIFILM_CURSOR
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--O/P ALL THE
THIRTEEN RECORDS...TOOK 2 SECONDS...HERE ONLY 13 RECORDS...IF THE COUNT
INCREASES THEN THE TIME WILL ALSO INCREASE
SELECT * FROM #TEMP--TOOK NOT EVEN 1 SECOND ...SO YOU GOT IT THAT CURSOR IS
LITTLE SLOW ...
SCROLL
--------------------------------SCROLL
CURSOR------------------------------------
DECLARE HINDIFILM_CURSOR CURSOR SCROLL
FOR
SELECT [FILM
NAME] FROM #TEMP
OPEN
HINDIFILM_CURSOR
FETCH FIRST FROM HINDIFILM_CURSOR
WHILE @@FETCH_STATUS=0
FETCH NEXT FROM
HINDIFILM_CURSOR
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--IT WORKS..
--O/P IS ALL THE
THIRTEEN RECORDS FROM FIRST TO LAST...YOU CAN USE FIRST/LAST/PRIOR WITH SCROLL CURSOR ONLY
SCROLL CONTINUED
DECLARE HINDIFILM_CURSOR CURSOR
SCROLL
FOR
SELECT [FILM
NAME] FROM #TEMP
OPEN
HINDIFILM_CURSOR
FETCH LAST FROM
HINDIFILM_CURSOR--LAST IS INTRODUCED
WHILE @@FETCH_STATUS=0
FETCH PRIOR FROM
HINDIFILM_CURSOR--PRIOR IS INTRODUCED
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--O/P IT GIVES
ALL THE RECORDS ,,,BUT FROM LAST TO FIRST..
ABSOLUTE Ã RELATIVE
--------------------------ABSOLUTE-----------------------------------------------
DECLARE HINDIFILM_CURSOR CURSOR
SCROLL
FOR
SELECT ID,[FILM NAME] FROM
#TEMP
OPEN
HINDIFILM_CURSOR
FETCH ABSOLUTE 3 FROM HINDIFILM_CURSOR--ABSOLUTE IS INTRODUCED
WHILE @@FETCH_STATUS=0
FETCH RELATIVE 3 FROM HINDIFILM_CURSOR--RELATIVE IS INTRODUCED
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--ITS NOT
NECESSARILY YOU HAVE TO WRITE RELATIVE. IF YOU WANT TO MOVE FROM 3RD RECORD ONE
BY ONE..MEANS 3RD,4TH,5TH AND SO
ON..THEN YOU CAN WRITE IN TWO WAYS
--1)SIMPLY MAKE
IT AS : FETCH NEXT FROM HINDIFILM_CURSOR
--2)MAKE IT AS :
FETCH RELATIVE 1 FROM HINDIFILM_CURSOR
ABSOLUTEÃ RELATIVE
EXAMPLE 2
--------------------NEGATIVE
----------------------------------------------------
DECLARE HINDIFILM_CURSOR CURSOR
SCROLL
FOR
SELECT ID,[FILM NAME] FROM
#TEMP
OPEN
HINDIFILM_CURSOR
FETCH ABSOLUTE -4 FROM
HINDIFILM_CURSOR--ABSOLUTE IS INTRODUCED
WHILE @@FETCH_STATUS=0
FETCH RELATIVE -3 FROM
HINDIFILM_CURSOR--RELATIVE IS INTRODUCED
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--EXPLANATION:
IT STARTS CALCULATION LIKE 13,12,11,10 ,,,IT PICKS 10.
VALUES TO VARIABLE IN CURSOR
--------------------------------READING
THE VALUES INTO VARIABLE-----------------
DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)
DECLARE HINDIFILM_CURSOR CURSOR
SCROLL
FOR
SELECT ID,[FILM NAME] FROM
#TEMP
OPEN
HINDIFILM_CURSOR
FETCH NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME
WHILE @@FETCH_STATUS=0
FETCH NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--O/P COMMAND
COMPLETED SUCCESSFULLY
EXECUTING THE
VARIABLES INSIDE CURSOR AND HOW PRINT WORKS
-----------------------------------------EXECUTING
THE VARIABLES-----------------
DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)
DECLARE HINDIFILM_CURSOR CURSOR
SCROLL
FOR
SELECT ID,[FILM NAME] FROM
#TEMP
OPEN
HINDIFILM_CURSOR
FETCH NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME
PRINT '================================================='
PRINT 'FILMNAME :' + CAST(@FILMNAME AS VARCHAR(100))
PRINT 'FILM ID :' + CAST(@ID AS VARCHAR(100))
PRINT '<=================================================>'
WHILE @@FETCH_STATUS=0
BEGIN--ADDED
IF (@FILMNAME='SHOLAY')
BEGIN
UPDATE #TEMP SET
[FILM NAME]=@FILMNAME +' ' +'==>OLD IS GOLD' WHERE
[FILM NAME]=@FILMNAME
END
ELSE IF(@ID=5)
BEGIN
UPDATE
#TEMP SET [FILM NAME]=@FILMNAME
+ ' '+'==> THE FIVTH MOVIE'
WHERE ID=@ID
END
ELSE
BEGIN
UPDATE
#TEMP SET [FILM NAME]=@FILMNAME
+' ' +'==>NORMAL HIT MOVIE'
WHERE [FILM NAME]=@FILMNAME
END
FETCH NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME
END--ADDED
SELECT * FROM #TEMP
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--O/P
--CLICK ON
MESSAGE JUST ADJACENT TO THE RESULT.--ONE THING WE NOTICE IS THAT WE GET THIS
RESULT BELOW ,,,AND THEN WE GET 1 ROW(S) AFFECTED FOR ALL RECORDS
--SO I JUST MEAN
TO TELL YOU HOW CURSOR WORKS... NEXT EXAMPLE WE WILL PUT THE PRINT INSIDE WHILE
CONDITION ...YOU WILL NOTICE A DIFFERENCE
PRINT INSIDE WHILE
LOOP
DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)
DECLARE HINDIFILM_CURSOR CURSOR
SCROLL
FOR
SELECT ID,[FILM NAME] FROM
#TEMP
OPEN
HINDIFILM_CURSOR
FETCH NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '================================================='
PRINT 'FILMNAME :' + CAST(@FILMNAME AS VARCHAR(100))
PRINT 'FILM ID :' + CAST(@ID AS VARCHAR(100))
PRINT '<=================================================>'
IF (@FILMNAME='SHOLAY')
BEGIN
UPDATE #TEMP SET
[FILM NAME]=@FILMNAME +' ' +'==>OLD IS GOLD' WHERE
[FILM NAME]=@FILMNAME
END
ELSE IF(@ID=5)
BEGIN
UPDATE
#TEMP SET [FILM NAME]=@FILMNAME
+ ' '+'==> THE FIVTH MOVIE'
WHERE ID=@ID
END
ELSE
BEGIN
UPDATE
#TEMP SET [FILM NAME]=@FILMNAME
+' ' +'==>NORMAL HIT MOVIE'
WHERE [FILM NAME]=@FILMNAME
END
FETCH NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME
END
SELECT * FROM #TEMP
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--EXECUTE AND SEE THE MESSAGE JUST NEAR THE RESULT TAB
--HOPE YOU GOT
IT....
ENCAPSULATING CURSOR
INSIDE A STORED PROCEDURE
LAST
BUT NOT THE LEAST HOW WE WILL ENCAPSULATE IT IN A STORED PROCDEDURE
IT'S
VERY SIMPLE----
CREATE PROCEDURE CURSOR_PROC
AS
BEGIN
DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)
DECLARE HINDIFILM_CURSOR CURSOR
SCROLL
FOR
SELECT ID,[FILM NAME] FROM
#TEMP
OPEN
HINDIFILM_CURSOR
FETCH NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '================================================='
PRINT 'FILMNAME :' + CAST(@FILMNAME AS VARCHAR(100))
PRINT 'FILM ID :' + CAST(@ID AS VARCHAR(100))
PRINT '<=================================================>'
IF (@FILMNAME='SHOLAY')
BEGIN
UPDATE #TEMP SET
[FILM NAME]=@FILMNAME +' ' +'==>OLD IS GOLD' WHERE
[FILM NAME]=@FILMNAME
END
ELSE IF(@ID=5)
BEGIN
UPDATE
#TEMP SET [FILM NAME]=@FILMNAME
+ ' '+'==> THE FIVTH MOVIE'
WHERE ID=@ID
END
FETCH
NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME
END
SELECT * FROM #TEMP
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
END
--EXEC
CURSOR_PROC
--SO OUR CURSOR
IS WRAPPED INSIDE STORED PROCEDURE..THERE ARE TWO WAYS TO EXECUTE THE STORED
PRODEDURE
--1) EXECUTE
PRODUDURENAME/ EXEC PROCEDURENAME
--2) SELECT THE
PROCEDURE NAME AND HIT F5.[MEANS SELECT THIS AND HIT F5 -->CURSOR_PROC ]
CALLING A STORED
PROCEDURE INSIDE THE CURSOR
--LETS CALL A
STORED PROCEDURE INSIDE A CURSOR
CURSOR
DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)
DECLARE HINDIFILM_CURSOR CURSOR
SCROLL
FOR
SELECT ID,[FILM NAME] FROM
#TEMP
OPEN
HINDIFILM_CURSOR
FETCH NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '================================================='
PRINT 'FILMNAME :' + CAST(@FILMNAME AS VARCHAR(100))
PRINT 'FILM ID :' + CAST(@ID AS VARCHAR(100))
PRINT '<=================================================>'
EXEC PROC_INSIDECURSOR @ID,@FILMNAME
–THE STORED PROCEDURE IS WRITTEN
BELOW..WE ARE PASSING THE CURSOR PARAMETERS TO THE STORED PROCEDURE
FETCH
NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME
END
SELECT * FROM #TEMP
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--I M CREATING
SIMPLE STORED PROCEDURE TO HAVE A BETTER UNDERSTANDING.
PROCEDURE 2
CREATE PROCEDURE PROC_INSIDECURSOR
(
@ID INT,
@FILMNAME VARCHAR(MAX)
)
AS
BEGIN
IF(SUBSTRING(@FILMNAME,1,1)='S')
BEGIN
PRINT 'THE FILM STARTS WITH S'
END
ELSE
PRINT 'THE FILM DOESNT START WITH S'
END
--O/P CLICK ON
MESSAGE JUST ADJACENT TO THE RESULT. YOU WILL GET TO KNOW.
SELECT THE CURSOR AND EXECUTE IT.
=================================================================================
SCROLL TYPE IN CURSOR [FORWARD_ONLY]
--AS WE HAVE
EARLIER NOTICED THAT
SCROLL AFTER CURSOR ALLOWS US
1)FETCH FIRST
2)FETCH LAST
3)FETCH PRIOR
--BUT FORWARD_ONLY ALLOWS ONLY FETCH NEXT.
DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)
DECLARE HINDIFILM_CURSOR1 CURSOR
FORWARD_ONLY
FOR
SELECT ID,[FILM NAME] FROM
#TEMP
OPEN
HINDIFILM_CURSOR1
FETCH FIRST FROM
HINDIFILM_CURSOR1 INTO @ID,@FILMNAME
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '================================================='
PRINT 'FILMNAME :' + CAST(@FILMNAME AS VARCHAR(100))
PRINT 'FILM ID :' + CAST(@ID AS VARCHAR(100))
PRINT '<=================================================>'
EXEC PROC_INSIDECURSOR @ID,@FILMNAME
FETCH
NEXT FROM
HINDIFILM_CURSOR1 INTO @ID,@FILMNAME
END
SELECT * FROM #TEMP
CLOSE
HINDIFILM_CURSOR1
DEALLOCATE
HINDIFILM_CURSOR1
--I M CREATING
SIMPLE STORED PROCEDURE TO HAVE A BETTER UNDERSTANDING.
CREATE PROCEDURE PROC_INSIDECURSOR
(
@ID INT,
@FILMNAME VARCHAR(MAX)
)
AS
BEGIN
IF(SUBSTRING(@FILMNAME,1,1)='S')
BEGIN
PRINT 'THE FILM STARTS WITH S'
END
ELSE
PRINT 'THE FILM DOESNT START WITH S'
END
----ERROR -->
Msg 16911, Level 16, State 1, Line 9
--fetch: The
fetch type first cannot be used with forward only cursors.
FETCH FIRST >< FORWARD ONLY
One scenario i faced in the real time project where i needed cummulative . Then how I tackled using cursor is explained below using a simple example.
CUMMULATIVE IN CURSOR
CONTINUED
DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)
DECLARE @DIR VARCHAR(100)
DECLARE @RELEASE DATETIME,@AWARD INT ,@TOTALAWARD
INT
SET
@TOTALAWARD=0
DECLARE HINDIFILM_CURSOR CURSOR
SCROLL
FOR SELECT ID,[FILM NAME],DIRECTOR,[RELEASE
DATE],[NATIONAL AWARD] FROM
#TEMP1
FOR UPDATE OF
[CUMMULATIVE NATIONAL AWARD]
OPEN
HINDIFILM_CURSOR
FETCH NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME,@DIR,@RELEASE,@AWARD
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '================================================='
SET @TOTALAWARD+=@AWARD
PRINT 'TOTAL AWARDS: ' + CAST(@TOTALAWARD AS VARCHAR(100))
UPDATE #TEMP1 SET [CUMMULATIVE NATIONAL
AWARD]=@TOTALAWARD
FETCH
NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME,@DIR,@RELEASE,@AWARD
END
SELECT * FROM #TEMP1
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--IF I EXECUTE
THIS WHAT HAPPENS....OOPS WE GET A STRANGE O/P
--O/P
--156
--156
--156
--156
--156
--156
--156
--156
--156
--156
--156
--156
--156
--Now this is logical. Let me explain you what exactly is happening.
--first time the column gets updated to 12.
--second time the column gets updated to 24.
--it continues till thirteen times. And we are updating the column of the table.
--LETS SET NULL
TO OUR CUMMULATIVE NATIONAL AWARD AGAIN AND TRY ADDING WHERE CLAUSE
UPDATE #TEMP1 SET [CUMMULATIVE NATIONAL AWARD]=NULL
===============================================================================
CUMMULATIVE IN CURSOR
CONTINUED [WHERE CLUASE TRICK]
-------------------------------CHANGE
THE CURSOR A LITTLE BIT--------------------
DECLARE @ID INT
DECLARE @FILMNAME VARCHAR(100)
DECLARE @DIR VARCHAR(100)
DECLARE @RELEASE DATETIME,@AWARD INT ,@TOTALAWARD INT
SET
@TOTALAWARD=0
DECLARE HINDIFILM_CURSOR CURSOR
SCROLL
FOR SELECT ID,[FILM NAME],DIRECTOR,[RELEASE
DATE],[NATIONAL AWARD] FROM
#TEMP1
FOR UPDATE OF
[CUMMULATIVE NATIONAL AWARD]
OPEN
HINDIFILM_CURSOR
FETCH NEXT FROM HINDIFILM_CURSOR
INTO @ID,@FILMNAME,@DIR,@RELEASE,@AWARD
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '================================================='
SET
@TOTALAWARD+=@AWARD
PRINT 'TOTAL AWARDS: ' + CAST(@TOTALAWARD AS VARCHAR(100))
UPDATE #TEMP1 SET
[CUMMULATIVE NATIONAL AWARD]=@TOTALAWARD
WHERE CURRENT
OF HINDIFILM_CURSOR
FETCH
NEXT FROM
HINDIFILM_CURSOR INTO @ID,@FILMNAME,@DIR,@RELEASE,@AWARD
END
SELECT * FROM #TEMP1
CLOSE
HINDIFILM_CURSOR
DEALLOCATE
HINDIFILM_CURSOR
--THIS WORKS
COOL
--O/P
--CUMMULATIVE
NATIONAL AWARD
--12
--24
--36
--48
--60
--72
--84
--96
--108
--120
--132
--144
--156
J J J J SO THIS ENDS OUR CURSOR TUTORIAL..HOPE YOU ENJOYED IT...KEEP LEARNING THE
THINGS..GOOD BYE J J J J
Hello, my name is Jack Sparrow. I'm a 50 year old self-employed Pirate from the Caribbean.
No comments:
Post a Comment