Tuesday 6 December 2011

Cursor in SQL with example



A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.
To use cursors in SQL procedures, you need to do the following:
  1. Declare a cursor that defines a result set.
  2. Open the cursor to establish the result set.
  3. Fetch the data into local variables as needed from the cursor, one row at a time.
  4. Close the cursor when done
  5. Deallocate  the cursor
To work with cursors you must use the following SQL statements:
  • DECLARE CURSOR
  • OPEN
  • FETCH
  • CLOSE
  • DEALLOCATE
Declare @Firstname varchar(50)
Declare @Lastname varchar(50)
DECLARE @Email varchar(20)

DECLARE Student_cursor CURSOR FOR

Select Firstname,Lastname,Email from from student

OPEN Student_cursor;

FETCH NEXT FROM Student_cursor
INTO @Firstname, @Lastname, @Email;

WHILE @@FETCH_STATUS = 0
BEGIN
print ‘---------------‘
print @Firstname
print @Lastname
print @Email
print ‘---------------‘
FETCH NEXT FROM Student_cursor
    INTO @Firstname, @Lastname, @Email;
END

CLOSE Student_cursor;
DEALLOCATE Student_cursor;




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

No comments:

Post a Comment