mysql - 如何在插入触发器后的条件基础上声明游标(Mysql)

Question

我的触发器就在这里。

mysql> CREATE TRIGGER `trg_after_insert` AFTER INSERT ON `tbl_demo`
    -> FOR EACH ROW
    -> BEGIN
    ->   DECLARE `done` BOOL DEFAULT FALSE;
    ->   DECLARE `_id` INT;
    ->   DECLARE `_value` VARCHAR(50);
    -> 
    ->   DECLARE `demo_cursor` CURSOR FOR
    ->   SELECT * -- <- SELECT `id`, `value` Recommendation for use
    ->   FROM `tbl_demo`
    ->   WHERE `id` = NEW.`id`;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` := TRUE;
    -> 
    ->   OPEN `demo_cursor`;
    -> 
    ->   `read_loop`: LOOP
    ->     FETCH `demo_cursor` INTO `_id`, `_value`;
    -> 
    ->     IF `done` THEN
    ->       CLOSE `demo_cursor`;
    ->       LEAVE `read_loop`;
    ->     END IF;
    -> 
    ->     INSERT INTO `result_demo`
    ->       (`id`, `value`)
    ->     VALUES
    ->       (`_id`, `_value`);
    ->   END LOOP;
    -> END//

我希望根据新字段值声明demo_cusror，例如

if NEW.`id`=NULL THEN
   DECLARE `demo_cursor` CURSOR FOR SELECT * tbl_demo ;
ELSE
   DECLARE `demo_cursor` CURSOR FOR SELECT * tbl_demo2;
END IF

我尝试过，但无法将 if 条件放在声明部分。

Answer 1

您不能在 mysql 中有条件地声明任何变量。时期。作为 mysql 文档 declare说:

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

声明 2 个游标变量并在应用程序逻辑中决定使用哪一个。