MySQL Cursors

MySQL Cursor क्या है?

Cursor stored program को SELECT result one row at a time पढ़ने देता है। Normal SQL set-oriented है। Cursor तभी useful है जब हर row का procedural work safe और clear set-based statement में express न हो।

DECLARE cur_overdue CURSOR FOR
  SELECT order_id, customer_id, total_amount
  FROM orders_cursor_lab
  WHERE status='PENDING' AND order_date < p_cutoff
  ORDER BY order_id;

Properties और Declaration Order

• Asensitive: server result copy कर भी सकता, नहीं भी; concurrent change visibility assume न करें।
• Read-only: current cursor row update नहीं करता।
• Nonscrollable: केवल forward FETCH; backward/jump नहीं।

Required order: local variables, named conditions, cursors, handlers; फिर executable statements।

DECLARE v_done BOOLEAN DEFAULT FALSE;
DECLARE no_more_rows CONDITION FOR SQLSTATE '02000';
DECLARE cur_orders CURSOR FOR
  SELECT order_id FROM orders_cursor_lab;
DECLARE CONTINUE HANDLER FOR no_more_rows
  SET v_done = TRUE;

Handler को cursor से पहले या executable statement के बाद declaration रखने पर syntax error होगा।

Reminder Queue Lab

DROP TABLE IF EXISTS reminder_queue_lab;
DROP TABLE IF EXISTS orders_cursor_lab;
CREATE TABLE orders_cursor_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE reminder_queue_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  reminder_text VARCHAR(200) NOT NULL,
  queued_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
INSERT INTO orders_cursor_lab VALUES
(1,101,'PAID','2026-08-01',1200.00),
(2,101,'PENDING','2026-08-05',500.00),
(3,102,'PAID','2026-08-03',750.00),
(4,103,'CANCELLED','2026-08-04',300.00),
(5,101,'PAID','2026-08-10',1500.00),
(6,102,'PENDING','2026-08-11',900.00),
(7,104,'PENDING','2026-08-14',2200.00);

Cutoff 2026-08-12 पर orders 2 और 6 qualify, 7 नहीं। Queue primary key repeat call को duplicate से बचाती है।

Complete Cursor Procedure

DELIMITER //
CREATE PROCEDURE queue_overdue_orders(IN p_cutoff DATE)
MODIFIES SQL DATA
BEGIN
  DECLARE v_done BOOLEAN DEFAULT FALSE;
  DECLARE v_order_id INT;
  DECLARE v_customer_id INT;
  DECLARE v_total DECIMAL(10,2);
  DECLARE cur_overdue CURSOR FOR
    SELECT order_id, customer_id, total_amount
    FROM orders_cursor_lab
    WHERE status='PENDING' AND order_date < p_cutoff
    ORDER BY order_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET v_done=TRUE;
  IF p_cutoff IS NULL THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='cutoff date is required';
  END IF;
  OPEN cur_overdue;
  read_loop: LOOP
    FETCH cur_overdue INTO
      v_order_id, v_customer_id, v_total;
    IF v_done THEN LEAVE read_loop; END IF;
    INSERT IGNORE INTO reminder_queue_lab
      (order_id,customer_id,reminder_text)
    VALUES
      (v_order_id,v_customer_id,
       CONCAT('Pending order ',v_order_id,
              ' amount ',FORMAT(v_total,2)));
  END LOOP;
  CLOSE cur_overdue;
END //
DELIMITER ;

FETCH targets SELECT count/types से match हैं। Last attempted FETCH पर NOT FOUND flag set करता है; immediate check stale values process होने से रोकता है।

Procedure Call और Execution Trace

CALL queue_overdue_orders('2026-08-12');
SELECT order_id,customer_id,reminder_text
FROM reminder_queue_lab ORDER BY order_id;

1. OPEN result prepare करता है।
2. FETCH order 2 load, reminder insert।
3. Next FETCH order 6, reminder insert।
4. Next FETCH no row, v_done true।
5. Loop stale values use किए बिना leaves; CLOSE।

Repeat call duplicates नहीं डालती। Production में existing row ignore करना required policy है या नहीं, explicitly decide करें।

Handlers, Nested Blocks और Safety

NOT FOUND SQLSTATE class '02' है। Same scope में no-row SELECT...INTO भी handler activate कर सकता है। Unrelated lookups avoid या nested block/own handler use करें।

• OPEN से पहले parameters validate करें।
• Order important हो तो ORDER BY रखें।
• Intentional batch design के बिना per-row COMMIT न करें।
• Transactions short और writes idempotent रखें।
• SQLEXCEPTION EXIT handler तभी जब cleanup/rollback/RESIGNAL policy defined हो।
• Progress logging workload distort न करे।

Set-Based Alternative Prefer करें

INSERT IGNORE INTO reminder_queue_lab
  (order_id,customer_id,reminder_text)
SELECT order_id,customer_id,
       CONCAT('Pending order ',order_id,
              ' amount ',FORMAT(total_amount,2))
FROM orders_cursor_lab
WHERE status='PENDING'
  AND order_date < '2026-08-12';

CREATE INDEX ix_orders_status_date
ON orders_cursor_lab(status,order_date);

यह shorter है और optimizer whole work plan कर सकता है। Genuine row-specific branching/sequential dependency में cursor; same transformation, aggregation, window function या joined DML में set-based SQL।

Cursor Use और Test Checklist

1. Prove करें कि set-based SQL insufficient है।
2. Rows bound और cursor SELECT index करें।
3. Declaration order follow करें।
4. FETCH columns/types match करें।
5. FETCH के तुरंत बाद done check करें।
6. Sequence matter करे तो ORDER BY।
7. Zero, one, many rows test करें।
8. Duplicate, retry, rollback test करें।
9. SELECT INTO handler collision avoid करें।
10. Duration, locks, temp space, queue growth measure करें।

procedures और handlers, optimization और Event Scheduler पढ़ें।

Official संदर्भ

• MySQL 8.4: Cursors
• MySQL 8.4: DECLARE CURSOR
• MySQL 8.4: DECLARE HANDLER

Properties, declaration order, lifecycle और NOT FOUND handling official MySQL 8.4 manual से verify किए गए हैं।