mysql - 将游标变量获取到局部变量中

Question

是否可以将游标变量提取到局部变量中？这样我就可以将它们用于准备好的语句中。

OPEN curseur2;
            LOOP2: LOOP
                SET no_more_rows = FALSE;
                FETCH curseur2 INTO @a, @b, @c;
                IF no_more_rows THEN
                    CLOSE curseur2;
                    LEAVE LOOP2;
                END IF;

                EXECUTE stmtCountBrut USING @a,@b,@c;

这只会给我一个 SQL 语法错误。

编辑 1

这是一个程序示例，除非我从“FETCHcurseur1 INTO @my_name;”中删除“@”，否则该示例不起作用。 :

    DROP PROCEDURE IF EXISTS `01_input_brut`.test_cursor;
CREATE DEFINER=`root`@`%` PROCEDURE `test_cursor`()
BLOCK1: BEGIN
    DECLARE no_more_rows1 INT;
    DECLARE my_name VARCHAR(255);
    DECLARE civility VARCHAR(255);

    DECLARE curseur1 CURSOR FOR
        SELECT `name`
        FROM source;

    DECLARE CONTINUE handler FOR NOT FOUND SET no_more_rows1 = TRUE;

    OPEN curseur1;

    LOOP1: LOOP
                set no_more_rows1 = false;
        FETCH curseur1 INTO @my_name;
        IF no_more_rows1 THEN
            CLOSE curseur1;
            LEAVE LOOP1;
        END IF;

INSERT INTO log (id, message, date) VALUES (NULL, @my_name, NOW());


    END LOOP LOOP1;     
END BLOCK1;

错误:

[2016-11-17 16:19:34] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@my_name; IF no_more_rows1 THEN CLOSE curseur1; ' at line 17

Answer 1

尝试:

DROP TABLE IF EXISTS `source`;
DROP TABLE IF EXISTS `log`;
DROP PROCEDURE IF EXISTS `test_cursor`;

CREATE TABLE IF NOT EXISTS `source` (
  `name` VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS `log` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `message` VARCHAR(255),
  `date` DATETIME
);

INSERT INTO `source` (`name`)
VALUES ('name 1');

DELIMITER //

CREATE PROCEDURE `test_cursor`()
`BLOCK1`: BEGIN
  -- DECLARE `no_more_rows1` INT;
  DECLARE `no_more_rows1` BOOL DEFAULT FALSE;
  DECLARE `my_name` VARCHAR(255);
  -- DECLARE `civility` VARCHAR(255);

  DECLARE `curseur1` CURSOR FOR
    SELECT `name`
    FROM `source`;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET `no_more_rows1` := TRUE;

  OPEN `curseur1`;

  `LOOP1`: LOOP
    -- set no_more_rows1 = false;
    -- FETCH curseur1 INTO @my_name; <- User-Defined Variable. See http://dev.mysql.com/doc/refman/5.7/en/user-variables.html
    FETCH `curseur1` INTO `my_name`;
    IF `no_more_rows1` THEN
      CLOSE `curseur1`;
      LEAVE `LOOP1`;
    END IF;

    INSERT INTO `log` (`id`, `message`, `date`)
    -- VALUES (NULL, @my_name, NOW());
    VALUES (NULL, `my_name`, NOW());
  END LOOP `LOOP1`;
END `BLOCK1`//

DELIMITER ;

CALL `test_cursor`;

SELECT `name`
FROM `source`;

SELECT `id`, `message`, `date`
FROM `log`;

参见SQL Fiddle demo