CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`()

BEGIN

DECLARE chkc INT;

DECLARE id_tmp INT;

DECLARE age_tmp INT;

DECLARE name_tmp VARCHAR(32);

DECLARE flag INT DEFAULT TRUE;//定义一个flag设置为TRUE

DECLARE cur CURSOR FOR SELECT id FROM testname;//定义游标，以testname表中的id字段为游标进行循环遍历

DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = FALSE;//如果程序有未声明的变量，flag变为FLASE循环遍历中止

OPEN cur;//打开游标

REPEAT//循环遍历

    FETCH cur INTO id_tmp;//将当前遍历到的赋值给声明的id_tmp变量

    SELECT COUNT(*) INTO chkc FROM testname_detail    WHERE id = id_tmp; 

    IF chkc = 1 THEN

    UPDATE testname_detail,testname    SET testname_detail.age = (SELECT age FROM testname WHERE id = id_tmp) WHERE testname_detail.id = id_tmp;

    ELSE

    SELECT age, name INTO age_tmp, name_tmp FROM testname WHERE id = id_tmp;

    INSERT INTO testname_detail(id,name,age) VALUES(id_tmp,name_tmp,age_tmp);

    END IF;

UNTIL flag = FALSE 

END REPEAT;

CLOSE cur;

END