Cursor in PL/SQL

Question

How can we check the status of Cursor whether it is closed or open.

Tulasi · Answer

HI, sry for my previous rply You can use the CURSOR_STATUS function to determine its state. IF CURSOR_STATUS ( 'global' , 'myCursor' )>= -1 BEGIN DEALLOCATE myCursor END

Pankaj Rana · Answer

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

Hemant Kumar · Answer

You can use the CURSOR_STATUS function to determine its state. IF CURSOR_STATUS('global','myCursor')>=-1 BEGIN DEALLOCATE myCursor END One solution using a Dynamic Management function : DECLARE crsA CURSOR LOCAL FAST_FORWARD FOR SELECT * FROM dbo.Factura ; OPEN crsA; DECLARE crsB CURSOR LOCAL FAST_FORWARD FOR SELECT * FROM dbo.Employee; DECLARE @CursorName SYSNAME = 'crsA' --or 'crsB' SELECT f.is_open, f.* FROM sys.dm_exec_cursors(@@SPID) f WHERE f.name = @CursorName CLOSE crsA; DEALLOCATE crsA; DEALLOCATE crsB;

Prabhu Raja · Answer

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

Kunal Naik · Answer

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

Tulasi · Answer

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.

Cursor in PL/SQL

Answers (6)