CURSOR

Programmability
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

Filter by Database
SQL CURSOR Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native 5.0 Cursors are supported inside stored procedures and functions only. Must be declared before handlers and other variables.
PostgreSQL Native 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 Native 1992 Supports STATIC, KEYSET, DYNAMIC, and FAST_FORWARD cursor types. FAST_FORWARD is the most performant for forward-only read-only use.
Oracle Native 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: Native since 5.0 PostgreSQL: Native since 7.4 SQL Server: Native since 1992 Oracle: Native since 7 SQLite: Not supported

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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;