SCROLL

Example

DECLARE cursor_scroll CURSOR SCROLL FOR SELECT * FROM employees;
FETCH NEXT FROM cursor_scroll;
FETCH PRIOR FROM cursor_scroll;

Output

For FETCH NEXT command:

id | name    | department
---+---------+------------
2  | Peter   | HR

For FETCH PRIOR command:

id | name    | department
---+---------+------------
1  | Alice   | Sales

Explanation

In the example code, a SCROLL cursor named “cursor_scroll” is declared. The SQL SELECT statement retrieves all records from the “employees” table. Firstly, the FETCH NEXT command is used to retrieve the next row from the cursor which in result, returns the second row from the “employees” table. Then, the FETCH PRIOR command is used to retrieve the prior row from the cursor, which returns back to the first row of the “employees” table.

Example

DECLARE
  CURSOR scroll_cur SCROLL CURSOR FOR
    SELECT * FROM employees;
  rec scroll_cur%ROWTYPE;
BEGIN
  OPEN scroll_cur;
  FETCH NEXT FROM scroll_cur INTO rec;
  DBMS_OUTPUT.PUT_LINE('First record: ' || rec.employee_name);
  FETCH LAST FROM scroll_cur INTO rec;
  DBMS_OUTPUT.PUT_LINE('Last record: ' || rec.employee_name);
  CLOSE scroll_cur;
END;

Output

First record: Alice
Last record: Bob

Explanation

The given SQL code declares a scrollable cursor scroll_cur for a SELECT statement. When the cursor is opened, the FETCH statement is used with NEXT and LAST keywords to navigate to different rows in the result set. The DBMS_OUTPUT.PUT_LINE is used to display the employee_name from the first and last row retrieved from the employees table.

Example

DECLARE @Cursor CURSOR;
DECLARE @Name VARCHAR(200);

SET @Cursor = CURSOR SCROLL FOR
SELECT name FROM sys.databases

OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @Name

WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @Name
  FETCH NEXT FROM @Cursor INTO @Name
END

CLOSE @Cursor;
DEALLOCATE @Cursor;

Output

master
tempdb
model
msdb

Explanation

The provided SQL example declares a SCROLL CURSOR that moves through each name in the sys.databases system view. It fetches each name and prints it until all names have been printed. The SCROLL option allows the cursor to fetch rows in any order. After fetching all the records, the cursor is closed and deallocated.