MySQL游标循环仅运行一次

Question

我正在使用mysql存储过程，我花了两个小时试图弄清楚为什么这个游标只运行一次。（我假设它只运行了一次，因为我运行这个存储过程后只看到一条记录——最后有一个select*from resultado命令，我从前面创建的临时表中选择了所有内容）

注意，我已经尝试过在游标中单独运行查询，它运行正常（返回了多条记录，这是应该的）。

我认为这一定与我从循环中插入数据有关（我希望在每个循环过程中向临时表中插入一行数据）。

-- cursors
DECLARE done INT DEFAULT FALSE;

-- this query, qhen run on its own, returns more than one record
DECLARE assessments_cur CURSOR FOR 
    SELECT a.id assessment_id, 
        a.name assessment_name, 
        a.create_date data_entrada,
        a.open_date data_inicio,
        a.close_date data_fim

    FROM assessments a

    WHERE a.create_date > '2013-02-28 00:00:00'
    AND a.create_date < '2014-02-28 00:00:00'
    AND a.client_id = 13
    AND a.open_date IS NOT NULL
    AND a.close_date IS NOT NULL
;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- tabelas temporárias
DROP TEMPORARY TABLE IF EXISTS resultado;

CREATE TEMPORARY TABLE resultado (
    nome_assessment VARCHAR(511) NOT NULL,
    segmento VARCHAR(511) NOT NULL,
    sequencia_assessment VARCHAR(511) NOT NULL,
    demanda VARCHAR(511) NULL,
    data_entrada DATETIME NOT NULL,
    data_inicio DATETIME NOT NULL,
    data_fim DATETIME NOT NULL,
    quantidade_vulnerabilidades int(11) NOT NULL,

);

OPEN assessments_cur;

REPEAT

    FETCH assessments_cur INTO 
        assessment_id_n,
        assessment_name_vr,
        data_entrada_dt,
        data_inicio_dt,
        data_fim_dt;

    SET demanda_vr = get_demanda(assessment_id_n);
    SET segmento_vr = get_segmento(assessment_id_n);

    SET quantidade_vulnerabilidades_n = get_quantidade_vulnerabilidades(assessment_id_n);

    INSERT INTO resultado(
            nome_assessment,
            segmento,
            sequencia_assessment,
            demanda,
            data_entrada,
            data_inicio,
            data_fim,
            quantidade_vulnerabilidades
        ) VALUES(
            assessment_name_vr,
            segmento_vr,
            'Foo',
            demanda_vr,
            data_entrada_dt,
            data_inicio_dt,
            data_fim_dt,
            quantidade_vulnerabilidades_n
    );


UNTIL done END REPEAT;

CLOSE assessments_cur; 

select * from resultado;

DROP TEMPORARY TABLE resultado;

Answer 1

尝试从选择查询中删除别名，如下所示：

SELECT a.id, 
    a.name, 
    a.create_date,
    a.open_date,
    a.close_date

FROM assessments a

WHERE a.create_date > '2013-02-28 00:00:00'
AND a.create_date < '2014-02-28 00:00:00'
AND a.client_id = 13
AND a.open_date IS NOT NULL
AND a.close_date IS NOT NULL

Answer 2

没有以下信息：

• 表结构：评估
• 功能：get_demanda，get_segmento和get_quantidade_vulnerabilidades

然而，在一个小例子中，你可以看到光标是如何工作的。将存储过程示例与代码进行比较，并根据需要进行调整。

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_test`$$

CREATE PROCEDURE `sp_test`()
BEGIN
-- cursors
DECLARE `done` TINYINT(1) DEFAULT 0;
DECLARE `assessment_name_vr`, `segmento_vr`, `demanda_vr` VARCHAR(511);
DECLARE `data_entrada_dt`, `data_inicio_dt`, `data_fim_dt` DATETIME;
DECLARE `assessment_id_n`, `quantidade_vulnerabilidades_n` INT(11);

-- this query, qhen run on its own, returns more than one record
DECLARE `assessments_cur` CURSOR FOR 
    SELECT `a`.`id` `assessment_id`,
    `a`.`name` `assessment_name`,
    `a`.`create_date` `data_entrada`,
    `a`.`open_date` `data_inicio`,
    `a`.`close_date` `data_fim`
    FROM `assessments` `a`
    /*WHERE `a`.`create_date` > '2013-02-28 00:00:00'
    AND `a`.`create_date` < '2014-02-28 00:00:00'
    AND `a`.`client_id` = 13
    AND `a`.`open_date` IS NOT NULL
    AND `a`.`close_date` IS NOT NULL*/;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` := 1;

-- tabelas temporárias
DROP TEMPORARY TABLE IF EXISTS `resultado`;

CREATE TEMPORARY TABLE `resultado` (
    `nome_assessment` VARCHAR(511) NOT NULL,
    `segmento` VARCHAR(511) NOT NULL,
    `sequencia_assessment` VARCHAR(511) NOT NULL,
    `demanda` VARCHAR(511) NULL,
    `data_entrada` DATETIME NOT NULL,
    `data_inicio` DATETIME NOT NULL,
    `data_fim` DATETIME NOT NULL,
    `quantidade_vulnerabilidades` INT(11) NOT NULL
);

OPEN `assessments_cur`;

REPEAT
    FETCH `assessments_cur` INTO 
        `assessment_id_n`,
        `assessment_name_vr`,
        `data_entrada_dt`,
        `data_inicio_dt`,
        `data_fim_dt`;

    IF (NOT `done`) THEN
        /*SET `demanda_vr` := `get_demanda`(`assessment_id_n`);*/
        SET `demanda_vr` := CONCAT('demanda_vr', CONVERT(`assessment_id_n`, CHAR));

        /*SET `segmento_vr` := `get_segmento`(`assessment_id_n`);*/
        SET `segmento_vr` := CONCAT('segmento_vr', CONVERT(`assessment_id_n`, CHAR));

        /*SET `quantidade_vulnerabilidades_n` := `get_quantidade_vulnerabilidades`(`assessment_id_n`);*/
        SET `quantidade_vulnerabilidades_n` := `assessment_id_n`;

        INSERT INTO `resultado` (
            `nome_assessment`,
            `segmento`,
            `sequencia_assessment`,
            `demanda`,
            `data_entrada`,
            `data_inicio`,
            `data_fim`,
            `quantidade_vulnerabilidades`
        )
        VALUES
        (
            `assessment_name_vr`,
            `segmento_vr`,
            'Foo',
            `demanda_vr`,
            `data_entrada_dt`,
            `data_inicio_dt`,
            `data_fim_dt`,
            `quantidade_vulnerabilidades_n`
        );
    END IF;
UNTIL `done` END REPEAT;

CLOSE `assessments_cur`;

SELECT * FROM `resultado`;

DROP TEMPORARY TABLE `resultado`;
END$$

DELIMITER ;

SQL小提琴演示