mysql - 两个游标在mysql中不起作用

Question

我有两个练习表Table1和Table2。在 Table1 中插入一条记录后，我的触发器被触发，这将在 Table2 中插入记录。我在触发器中写入了 2 个游标，它们需要同时工作。因此，当我的触发器执行时，只有第一个游标(C_NEW_VERSION_RECORDS)起作用，而第二个游标(C2_NEW_VERSION_RECORDS)不起作用，如果我先写入第二个游标，那么第一个写入的游标也起作用。

下面只是练习代码。尝试以最简单的方式描述问题。实际上，我的要求非常相似，并且面临相同的光标问题。

表 1 - 具有“ID”“名称”列

表 2 - 具有“ID”“PANEL_ID”“SIMILAR_TO”“VERSION”列

 CREATE TRIGGER SAMPLE_CURSOR 
 AFTER INSERT ON TABLE1 
 FOR EACH ROW 

 BEGIN

 DECLARE C_PANEL_ID,C_SIMILAR_TO, C_VERSION INT;
 DECLARE C2_PANEL_ID,C2_SIMILAR_TO, C2_VERSION INT;

 DECLARE C_NEW_VERSION_RECORDS CURSOR FOR
    SELECT PANEL_ID, SIMILAR_TO, VERSION
    FROM PANELS_SIMILARS_VERSION
    WHERE VERSION
    IN (SELECT MAX( VERSION ) 
    FROM PANELS_SIMILARS_VERSION
    WHERE PANEL_ID =5
    OR SIMILAR_TO =5
    );

   DECLARE C2_NEW_VERSION_RECORDS CURSOR FOR
    SELECT PANEL_ID, SIMILAR_TO, VERSION
    FROM PANELS_SIMILARS_VERSION
    WHERE VERSION
    IN (SELECT MAX( VERSION ) 
    FROM PANELS_SIMILARS_VERSION
    WHERE PANEL_ID =5
    OR SIMILAR_TO =5
    );

    // Cursor 1
OPEN C_NEW_VERSION_RECORDS;

// This insert is happenning and the cursor works as required
INSERT INTO log SELECT now(), "Yes","Inside FIRST cursor"; 

read_loop: LOOP
FETCH C_NEW_VERSION_RECORDS INTO C_PANEL_ID,C_SIMILAR_TO,C_VERSION;

INSERT INTO TABLE2 (ID,PANEL_ID,SIMILAR_TO, VERSION)
VALUES
(NULL, C_PANEL_ID, C_SIMILAR_TO,C_VERSION);

 END LOOP read_loop;
CLOSE C_NEW_VERSION_RECORDS;

//Cursor 2
OPEN C2_NEW_VERSION_RECORDS;

// Statement not getting fired.
INSERT INTO log SELECT now(), "Yes","Inside SECOND cursor"; 

GET_loop: LOOP
FETCH C2_NEW_VERSION_RECORDS INTO C2_PANEL_ID,C2_SIMILAR_TO,C2_VERSION;

INSERT INTO TABLE2 (ID,PANEL_ID,SIMILAR_TO, VERSION)
VALUES
(NULL, C2_PANEL_ID, C2_SIMILAR_TO,C2_VERSION);

 END LOOP GET_loop;
CLOSE C2_NEW_VERSION_RECORDS;

END;

即使当我交换游标时，顺序中的第一个游标也会打开，而不是第二个游标，并且还忘记提及它会成功执行而不会出现错误。

Answer 1

您没有定义延续处理程序，也没有退出循环的机制。尝试添加下面以 #***** 结尾的行。

 CREATE TRIGGER SAMPLE_CURSOR 
 AFTER INSERT ON TABLE1 
 FOR EACH ROW 

 BEGIN

 DECLARE C_PANEL_ID,C_SIMILAR_TO, C_VERSION INT;
 DECLARE C2_PANEL_ID,C2_SIMILAR_TO, C2_VERSION INT;

 DECLARE C_NEW_VERSION_RECORDS CURSOR FOR
    SELECT PANEL_ID, SIMILAR_TO, VERSION
    FROM PANELS_SIMILARS_VERSION
    WHERE VERSION
    IN (SELECT MAX( VERSION ) 
    FROM PANELS_SIMILARS_VERSION
    WHERE PANEL_ID =5
    OR SIMILAR_TO =5
    );

   DECLARE C2_NEW_VERSION_RECORDS CURSOR FOR
    SELECT PANEL_ID, SIMILAR_TO, VERSION
    FROM PANELS_SIMILARS_VERSION
    WHERE VERSION
    IN (SELECT MAX( VERSION ) 
    FROM PANELS_SIMILARS_VERSION
    WHERE PANEL_ID =5
    OR SIMILAR_TO =5
    );

DECLARE done INT DEFAULT FALSE;                                 #*****
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  #*****

    // Cursor 1
OPEN C_NEW_VERSION_RECORDS;

// This insert is happenning and the cursor works as required
INSERT INTO log SELECT now(), "Yes","Inside FIRST cursor"; 

read_loop: LOOP

if done then leave read_loop; end if;                           #*****

FETCH C_NEW_VERSION_RECORDS INTO C_PANEL_ID,C_SIMILAR_TO,C_VERSION;

INSERT INTO TABLE2 (ID,PANEL_ID,SIMILAR_TO, VERSION)
VALUES
(NULL, C_PANEL_ID, C_SIMILAR_TO,C_VERSION);

 END LOOP read_loop;
CLOSE C_NEW_VERSION_RECORDS;

//Cursor 2

set done = false;                                                       #*****

OPEN C2_NEW_VERSION_RECORDS;

// Statement not getting fired.
INSERT INTO log SELECT now(), "Yes","Inside SECOND cursor"; 

GET_loop: LOOP

if done then leave get_loop; end if;                          #*****

FETCH C2_NEW_VERSION_RECORDS INTO C2_PANEL_ID,C2_SIMILAR_TO,C2_VERSION;

INSERT INTO TABLE2 (ID,PANEL_ID,SIMILAR_TO, VERSION)
VALUES
(NULL, C2_PANEL_ID, C2_SIMILAR_TO,C2_VERSION);

 END LOOP GET_loop;
CLOSE C2_NEW_VERSION_RECORDS;

END;