SQL Reference - CURSOR

Example

DECLARE db_cursor CURSOR FOR
SELECT name
FROM requirements
OPEN db_cursor
FETCH NEXT FROM db_cursor
WHILE @@FETCH_STATUS = 0
   BEGIN
      FETCH NEXT FROM db_cursor
   END;
CLOSE db_cursor;
DEALLOCATE db_cursor;

Output

requirement1
requirement2
requirement3

Explanation

In the above example, a cursor named db_cursor is declared and associated with a SELECT statement that fetches the names from the requirements table. The cursor is then opened, and a WHILE loop is used to iterate through the rows returned by the query. For each iteration, FETCH NEXT is called to move the cursor to the next row. When all rows have been processed, the cursor is closed and deallocated.

Example

DECLARE cur CURSOR FOR SELECT * FROM employees;
FETCH NEXT FROM cur;
CLOSE cur;

Output

 row_number | first_name | last_name | employee_id
------------+------------+-----------+-------------
          1 | John       | Doe       | E1234

Explanation

In the provided example, a cursor cur is declared for a SELECT query which gets all data from the employees table. FETCH NEXT FROM cur; is used to fetch the next row of from the result set of the cursor cur. CLOSE cur; is used to close the cursor once all rows have been fetched and processed. The output simulates a scenario where the employees table has at least one row of data, and is designed to show that the cursor fetches this data successfully.

Example

DECLARE @Name VARCHAR(50)
DECLARE cur CURSOR FOR
SELECT name FROM sys.databases
OPEN cur

FETCH NEXT FROM cur INTO @Name

WHILE @@FETCH_STATUS = 0
BEGIN

    PRINT(@Name)

    FETCH NEXT FROM cur INTO @Name

END

CLOSE cur
DEALLOCATE cur

Output

master
tempdb
model
msdb

Explanation

In this example, a CURSOR named cur is declared and a SELECT query is provided as source of the cursor. The cursor is then opened and a column (name) from SQL Server’s built-in databases data is fetched row by row into the variable @Name until there are no more rows left to fetch. For each fetch, the database name is printed. After fetching all rows from the cursor, the cursor is closed and deallocated.

Example

DECLARE
  CURSOR cur_employees IS
    SELECT first_name, last_name FROM employees;
  rec_employee cur_employees%ROWTYPE;
BEGIN
  OPEN cur_employees;
  LOOP
    FETCH cur_employees INTO rec_employee;
    EXIT WHEN cur_employees%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || rec_employee.first_name || ' ' || rec_employee.last_name);
  END LOOP;
  CLOSE cur_employees;
END;

Output

Employee Name: Steven King
Employee Name: Neena Kochhar
Employee Name: Lex De Haan
...

Explanation

The aforementioned SQL declares a CURSOR cur_employees that retrieves first and last names from the employees table. The LOOP fetches each record from the CURSOR and outputs the ‘first_name’ and ‘last_name’ before moving to the next record. If no more records are available, it stops fetching and closes the cursor.