SQL · Theory

Cursor

Theory

289/439

Cursor

A cursor walks a SELECT row by row inside a stored program. DECLARE cur CURSOR FOR SELECT …; OPEN; FETCH; CLOSE. Use it when set-based SQL cannot express the loop. Most student problems should stay set-based: UPDATE … WHERE, not a cursor.

Handlers needed for NOT FOUND. Easy to write slow row-by-row logic.

Trap — cursor as the first answer for ‘print all students’ — that’s just SELECT.

On the example next to this theory: Cursor: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

Cursors are for procedures. Prefer UPDATE/SELECT sets.

Cursor — sample query

-- Cursor
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Cursor: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefCursor = row-by-row loop in a procedure.
RulePrefer set-based SQL. FETCH until NOT FOUND.
TrapCursor for a plain SELECT.

SQL · Theory

Cursor

← All stacks

Theory

289/439

Cursor

Handlers needed for NOT FOUND. Easy to write slow row-by-row logic.

Trap — cursor as the first answer for ‘print all students’ — that’s just SELECT.

On the example next to this theory: Cursor: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Exam tip

Cursors are for procedures. Prefer UPDATE/SELECT sets.

Cursor — sample query

-- Cursor
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Cursor: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefCursor = row-by-row loop in a procedure.
RulePrefer set-based SQL. FETCH until NOT FOUND.
TrapCursor for a plain SELECT.

Cursor

Cursor — sample query

Short notes

Questions

Cursor

Cursor — sample query

Short notes

Questions