mysql - 将不同记录从一个表复制到另一个表的游标

Question

我想将所有 recoreds 从 temp1 表复制到我为此使用游标的另外两个表。

 DELIMITER //
    CREATE PROCEDURE cpyQ()
    BEGIN
        DECLARE g_id INT DEFAULT 0;
        DECLARE v_fn varchar(100);
        DECLARE v_ln varchar(100);
        DECLARE v_email varchar(100);

        declare tcursor for select distinct mailid,fname,lname  from temp1;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
        OPEN tcursor;
    REPEAT 
          FETCH cursor into v_fn,v_ln, v_email;
            insert into atom(type) values('Person');
            SET g_id = LAST_INSERT_ID();
            insert into user(id,fname,lname,mailid) values(g_id,v_fname,v_lname,v_email);
    END REPEAT;
        CLOSE tcursor;
      END//

    DELIMITER

这段代码显示错误

 MySQL said: Documentation
    #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 'for select distinct mailid,fname,lname  from temp1;
        DECLARE CONTINUE HANDLE' at line 8 

如何解决这个问题

Answer 1

您的语法中有多个错误，请不要退出循环。试试这个？

CREATE PROCEDURE cpyQ()
BEGIN
  DECLARE g_id INT DEFAULT 0;
  DECLARE v_fn varchar(100);
  DECLARE v_ln varchar(100);
  DECLARE v_email varchar(100);
  DECLARE done INT DEFAULT FALSE;

  declare tcursor cursor for select distinct mailid,fname,lname  from temp1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN tcursor;
  read_loop: LOOP
    FETCH tcursor into v_fn,v_ln, v_email;
    if done then
      LEAVE read_loop;
    END IF;
    insert into atom(type) values('Person');
    SET g_id = LAST_INSERT_ID();
    insert into user(id,fname,lname,mailid) values(g_id,v_fn,v_ln,v_email);

  END LOOP;
  CLOSE tcursor;
END