[#MDEV-12457] Cursors with parameters

Under terms of this task we'll port a similar task ~~MDEV-10597~~ (that was previously done for sql_mode=ORACLE) to the default sql_mode.

As of version 10.2, the cursor related statements look like:

DECLARE cursor_name CURSOR FOR select_statement;

OPEN cursor_name;

We'll extend this syntax to allow parameters.

DECLARE cursor_name [cursor_formal_parameter[,...]] CURSOR FOR select_statement;

OPEN cursor_name [expression[,...]];

cursor_formal_parameter:

    name type [collate clause]

This is a non-standard SQL extension.

Cursor parameters can appear in any parts of select_statement where a stored procedure variable is allowed (select list, WHERE, HAVING, LIMIT etc)

Example:

DROP PROCEDURE IF EXISTS p1;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT, b VARCHAR(10));

INSERT INTO t1 VALUES (1,'old'),(2,'old'),(3,'old'),(4,'old'),(5,'old');

DELIMITER $$

CREATE PROCEDURE p1(min INT,max INT)

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE va INT;

  DECLARE cur CURSOR(pmin INT, pmax INT) FOR SELECT a FROM t1 WHERE a BETWEEN pmin AND pmax;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

  OPEN cur(min,max);

  read_loop: LOOP

    FETCH cur INTO va;

    IF done THEN

      LEAVE read_loop;

    END IF;

    INSERT INTO t1 VALUES (va,'new');

  END LOOP;

  CLOSE cur;

END;

$$

DELIMITER ;

CALL p1(2,4);

SELECT * FROM t1;

+------+------+

| a    | b    |

+------+------+

|    1 | old  |

|    2 | old  |

|    3 | old  |

|    4 | old  |

|    5 | old  |

|    2 | new  |

|    3 | new  |

|    4 | new  |

+------+------+

[MDEV-12457] Cursors with parameters Created: 2017-04-06 Updated: 2017-04-09 Resolved: 2017-04-06
Status:	Closed
Project:	MariaDB Server
Component/s:	Parser, Stored routines
Fix Version/s:	10.3.0

[MDEV-12457] Cursors with parameters Created: 2017-04-06 Updated: 2017-04-09 Resolved: 2017-04-06