Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12457

Cursors with parameters

    XMLWordPrintable

Details

    Description

      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  |
      +------+------+
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.