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

Cursors with parameters

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

          relates to

          Technical task - A technical task. MDEV-10597 sql_mode=ORACLE: Cursors with parameters

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            Description As of version 10.2, the cursor related statements look like:

            {code:sql}
            DECLARE cursor_name CURSOR FOR select_statement;
            OPEN cursor_name;
            {code}

            We'll extend this syntax to allow parameters.

            {code:sql}
            DECLARE cursor_name [cursor_formal_parameter[,...]] CURSOR FOR select_statement;

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

            cursor_formal_parameter:
                name type [collate clause]
            {code}

            This is a non-standard SQL extension.


            Example:

            {code:sql}
            DROP PROCEDURE IF EXISTS p1;
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a INT, b VARCHAR(10)) ENGINE=MyISAM;
            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;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 1 | old |
            | 2 | old |
            | 3 | old |
            | 4 | old |
            | 5 | old |
            | 2 | new |
            | 3 | new |
            | 4 | new |
            +------+------+
            {noformat}
            		As of version 10.2, the cursor related statements look like:

            {code:sql}
            DECLARE cursor_name CURSOR FOR select_statement;
            OPEN cursor_name;
            {code}

            We'll extend this syntax to allow parameters.

            {code:sql}
            DECLARE cursor_name [cursor_formal_parameter[,...]] CURSOR FOR select_statement;

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

            cursor_formal_parameter:
                name type [collate clause]
            {code}

            This is a non-standard SQL extension.


            Example:

            {code:sql}
            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;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 1 | old |
            | 2 | old |
            | 3 | old |
            | 4 | old |
            | 5 | old |
            | 2 | new |
            | 3 | new |
            | 4 | new |
            +------+------+
            {noformat}
            bar Alexander Barkov made changes -
            Description As of version 10.2, the cursor related statements look like:

            {code:sql}
            DECLARE cursor_name CURSOR FOR select_statement;
            OPEN cursor_name;
            {code}

            We'll extend this syntax to allow parameters.

            {code:sql}
            DECLARE cursor_name [cursor_formal_parameter[,...]] CURSOR FOR select_statement;

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

            cursor_formal_parameter:
                name type [collate clause]
            {code}

            This is a non-standard SQL extension.


            Example:

            {code:sql}
            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;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 1 | old |
            | 2 | old |
            | 3 | old |
            | 4 | old |
            | 5 | old |
            | 2 | new |
            | 3 | new |
            | 4 | new |
            +------+------+
            {noformat}
            		As of version 10.2, the cursor related statements look like:

            {code:sql}
            DECLARE cursor_name CURSOR FOR select_statement;
            OPEN cursor_name;
            {code}

            We'll extend this syntax to allow parameters.

            {code:sql}
            DECLARE cursor_name [cursor_formal_parameter[,...]] CURSOR FOR select_statement;

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

            cursor_formal_parameter:
                name type [collate clause]
            {code}

            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}}, etc)

            Example:

            {code:sql}
            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;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 1 | old |
            | 2 | old |
            | 3 | old |
            | 4 | old |
            | 5 | old |
            | 2 | new |
            | 3 | new |
            | 4 | new |
            +------+------+
            {noformat}
            bar Alexander Barkov made changes -
            Description As of version 10.2, the cursor related statements look like:

            {code:sql}
            DECLARE cursor_name CURSOR FOR select_statement;
            OPEN cursor_name;
            {code}

            We'll extend this syntax to allow parameters.

            {code:sql}
            DECLARE cursor_name [cursor_formal_parameter[,...]] CURSOR FOR select_statement;

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

            cursor_formal_parameter:
                name type [collate clause]
            {code}

            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}}, etc)

            Example:

            {code:sql}
            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;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 1 | old |
            | 2 | old |
            | 3 | old |
            | 4 | old |
            | 5 | old |
            | 2 | new |
            | 3 | new |
            | 4 | new |
            +------+------+
            {noformat}
            		As of version 10.2, the cursor related statements look like:

            {code:sql}
            DECLARE cursor_name CURSOR FOR select_statement;
            OPEN cursor_name;
            {code}

            We'll extend this syntax to allow parameters.

            {code:sql}
            DECLARE cursor_name [cursor_formal_parameter[,...]] CURSOR FOR select_statement;

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

            cursor_formal_parameter:
                name type [collate clause]
            {code}

            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:

            {code:sql}
            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;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 1 | old |
            | 2 | old |
            | 3 | old |
            | 4 | old |
            | 5 | old |
            | 2 | new |
            | 3 | new |
            | 4 | new |
            +------+------+
            {noformat}
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2017-04-06 15:35:55.0 2017-04-06 15:35:55.652
            bar Alexander Barkov made changes -
            Fix Version/s 10.3.0 [ 22127 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            Component/s Parser [ 10201 ]
            bar Alexander Barkov made changes -
            Description As of version 10.2, the cursor related statements look like:

            {code:sql}
            DECLARE cursor_name CURSOR FOR select_statement;
            OPEN cursor_name;
            {code}

            We'll extend this syntax to allow parameters.

            {code:sql}
            DECLARE cursor_name [cursor_formal_parameter[,...]] CURSOR FOR select_statement;

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

            cursor_formal_parameter:
                name type [collate clause]
            {code}

            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:

            {code:sql}
            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;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 1 | old |
            | 2 | old |
            | 3 | old |
            | 4 | old |
            | 5 | old |
            | 2 | new |
            | 3 | new |
            | 4 | new |
            +------+------+
            {noformat}
            		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:

            {code:sql}
            DECLARE cursor_name CURSOR FOR select_statement;
            OPEN cursor_name;
            {code}

            We'll extend this syntax to allow parameters.

            {code:sql}
            DECLARE cursor_name [cursor_formal_parameter[,...]] CURSOR FOR select_statement;

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

            cursor_formal_parameter:
                name type [collate clause]
            {code}

            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:

            {code:sql}
            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;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 1 | old |
            | 2 | old |
            | 3 | old |
            | 4 | old |
            | 5 | old |
            | 2 | new |
            | 3 | new |
            | 4 | new |
            +------+------+
            {noformat}
            bar Alexander Barkov made changes -
            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:

            {code:sql}
            DECLARE cursor_name CURSOR FOR select_statement;
            OPEN cursor_name;
            {code}

            We'll extend this syntax to allow parameters.

            {code:sql}
            DECLARE cursor_name [cursor_formal_parameter[,...]] CURSOR FOR select_statement;

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

            cursor_formal_parameter:
                name type [collate clause]
            {code}

            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:

            {code:sql}
            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;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 1 | old |
            | 2 | old |
            | 3 | old |
            | 4 | old |
            | 5 | old |
            | 2 | new |
            | 3 | new |
            | 4 | new |
            +------+------+
            {noformat}
            		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:

            {code:sql}
            DECLARE cursor_name CURSOR FOR select_statement;
            OPEN cursor_name;
            {code}

            We'll extend this syntax to allow parameters.

            {code:sql}
            DECLARE cursor_name [cursor_formal_parameter[,...]] CURSOR FOR select_statement;

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

            cursor_formal_parameter:
                name type [collate clause]
            {code}

            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:

            {code:sql}
            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;
            {code}
            {noformat}
            +------+------+
            | a | b |
            +------+------+
            | 1 | old |
            | 2 | old |
            | 3 | old |
            | 4 | old |
            | 5 | old |
            | 2 | new |
            | 3 | new |
            | 4 | new |
            +------+------+
            {noformat}
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 80260 ] MariaDB v4 [ 133200 ]

            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.