Cursor in PL/SQL

Check the status of Cursor

 

--Create a cursor.
DECLARE cur CURSOR

 

Display the status of the cursor after declare
SELECT CURSOR_STATUS('global','cur') return -1

 

 OPEN cur
Display the status of the cursor after open
SELECT CURSOR_STATUS('global','cur') return 1

 

CLOSE cur
Display the status of the cursor after close

 

SELECT CURSOR_STATUS('global','cur') return -1

 

--Remove the cursor.
DEALLOCATE cur
For more visit:
http://queriesinsql.blogspot.com/2011/12/cursor-in-sql-with-example.html

Hi,

We can use Cursor for example :

DECLARE C CURSOR FOR       -- Declaring Cursor 
       
       Select getDate()            -- Stores Current System Date to Cursor


OPEN C                               -- Opening Cursor


DECLARE @CurrentDate smallDatetime      -- Local Varialbes to store date time value in cursor

FETCH NEXT FROM C INTO @CurrentDate -- Fetch Value from Cursor


CLOSE C -- Close Cursor
DEALLOCATE C  -- free resources used by Cursor

If you want to check the state of cursor, try this one.

DECLARE cur CURSOR


SELECT CURSOR_STATUS('global','cur') AS 'After declare'
OPEN cur
SELECT CURSOR_STATUS('global','cur') AS 'After Open'
CLOSE cur
SELECT CURSOR_STATUS('global','cur') AS 'After Close'


--Remove the cursor.
DEALLOCATE cur

and refer MSDN Documentation for more Details : http://msdn.microsoft.com/en-us/library/ms177609.aspx

Hi,

Status of Cursor when it is Open:-

Cursors are a database object that allows programmers to loop through records in tables. A cursor stays open until the SQL Server programmer deallocates the memory on the server. Leaving cursors open in the database can hurt performance on the server. There is a special function in transact SQL that gives the programmer the ability to check for any open cursors. The programmer checks the cursor's status, and if it is returned as open, the programmer can deallocate the memory.
1.Open the Microsoft SQL Server Management console. The icon to open the program is in the Windows Start menu in the "SQL Server" program directory.
2.Open a cursor. The code below creates a new cursor to test the status function.
declare my_cur cursor
3.Get the status of a cursor to detect if it's open. Step 2 created the cursor, but it's not opened yet. Therefore, the return status value is "-1." The code below checks the status:
select cursor_status('global','my_cur')
4.Open the cursor and evaluate the status again. A return value of 1 means the cursor is open. This function can be used for any cursor you've created on the database server. The following code detects an open cursor:
select cursor_status('global','my_cur')
Although the syntax is the same as Step 3, the return value is different.
5.Deallocate and remove the cursor from database memory. Too many opened cursors reduce performance on the machine. The code below frees the database memory and closes the cursor:
deallocate my_cur
Also refer following link of MSDN for status of cursor whether it is open or closed,

Link

Hi,

After the FETCH command, you should always control the value of the @@FETCH_STATUS. This variable returns the status of the last cursor FETCH command in the current connection.

The possible return values of @@FETCH_STATUS are;

0	FETCH statement was successful
-1	FETCH statement failed or the row was beyond the result set
-2	Row fetched is missing

WHILE @@FETCH_STATUS = 0
BEGIN
   --code to  fetch 
END

Please mark the answer as Accepted if it helps you.