CURSORProgrammability
Buy Me A Coffee

A database object that lets procedural code iterate over a result set one row at a time.

A server-side pointer for traversing result sets row by row, typically used in stored procedures and functions. Cursors allow row-by-row processing when set-based operations are not appropriate. Generally avoid cursors in favor of set-based operations for performance.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0 Cursors are supported inside stored procedures and functions only. Must be declared before handlers and other variables.
PostgreSQL ✓ Supported 7.4 PL/pgSQL cursors support FOR UPDATE and scrollable variants. Can also be opened with OPEN cur FOR EXECUTE for dynamic SQL.
SQL Server ✓ Supported 1992 Supports STATIC, KEYSET, DYNAMIC, and FAST_FORWARD cursor types. FAST_FORWARD is the most performant for forward-only read-only use.
Oracle ✓ Supported 7 Oracle supports implicit cursors (FOR loop shorthand) and explicit cursors. REF CURSOR allows passing cursors as parameters.
SQLite ✗ Not Supported — No server-side cursor support. Row-by-row iteration must be handled by the application via the sqlite3 step/reset API.

Details

Cursors are widely supported in stored procedural code but are generally considered an anti-pattern for bulk work — set-based operations are almost always faster. SQL Server is particularly sensitive: even FAST_FORWARD cursors introduce significant per-row overhead vs. a single UPDATE or INSERT...SELECT. Oracle's implicit cursor FOR loop (`FOR rec IN (SELECT ...) LOOP`) is idiomatic and often the most readable option when row-by-row logic is unavoidable. PostgreSQL allows cursors to be returned to the client via `refcursor`, enabling result-set streaming from a function. In MySQL, cursor scope is limited to stored routines; the NOT FOUND handler pattern is the standard termination idiom.

Standard Syntax

DECLARE cur CURSOR FOR SELECT col FROM t; OPEN cur; FETCH NEXT FROM cur INTO @val; CLOSE cur; DEALLOCATE cur;

Version Support

MySQL: Since 5.0 PostgreSQL: Since 7.4 SQL Server: Since 1992 Oracle: Since 7 SQLite: Not supported

Per-Database Syntax & Notes

MySQL

Cursors are supported inside stored procedures and functions only. Must be declared before handlers and other variables.

DECLARE cur CURSOR FOR SELECT id FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; read_loop: LOOP FETCH cur INTO v_id; IF done THEN LEAVE read_loop; END IF; END LOOP; CLOSE cur;

PostgreSQL

PL/pgSQL cursors support FOR UPDATE and scrollable variants. Can also be opened with OPEN cur FOR EXECUTE for dynamic SQL.

DECLARE cur CURSOR FOR SELECT id FROM t; v_id INT; BEGIN OPEN cur; LOOP FETCH cur INTO v_id; EXIT WHEN NOT FOUND; END LOOP; CLOSE cur; END;

SQL Server

Supports STATIC, KEYSET, DYNAMIC, and FAST_FORWARD cursor types. FAST_FORWARD is the most performant for forward-only read-only use.

DECLARE cur CURSOR FAST_FORWARD FOR SELECT id FROM t; OPEN cur; FETCH NEXT FROM cur INTO @id; WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM cur INTO @id; END; CLOSE cur; DEALLOCATE cur;

Oracle

Oracle supports implicit cursors (FOR loop shorthand) and explicit cursors. REF CURSOR allows passing cursors as parameters.

DECLARE CURSOR cur IS SELECT id FROM t; v_id t.id%TYPE; BEGIN OPEN cur; LOOP FETCH cur INTO v_id; EXIT WHEN cur%NOTFOUND; END LOOP; CLOSE cur; END;