mysql - 存储迭代过程未插入表中

Question

我使用两个过程，一个定义游标，第二个将第一个过程的结果插入临时表，然后再插入最终表。我已经测试了游标的选择查询定义，它返回了我想要的结果；但是，我没有将任何值插入到我的临时表中。

迭代过程

CREATE PROCEDURE procIteration ()
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE TaxonName varchar(170);
DECLARE RankID varchar(170);
DECLARE ParentID varchar(170);
DECLARE TaxonID varchar(170);
DECLARE cur CURSOR FOR SELECT child.SciName, RankID, parent.parentID, child.tid FROM taxa child LEFT JOIN (SELECT MAX(parenttid) as parentID, SciName, taxaenumtree.tid FROM taxaenumtree, taxa WHERE taxa.tid = taxaenumtree.tid GROUP BY taxaenumtree.tid) AS parent ON parent.tid = child.tid WHERE rankID = 10;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

OPEN cur;

testLoop: LOOP
    FETCH cur INTO TaxonName, RankID, ParentID, TaxonID;
    IF done THEN 
        LEAVE testLoop;
    END IF;
    CALL taxon_reclamation(TaxonName, RankID, ParentID, TaxonID);
END LOOP testLoop;

CLOSE cur;
END //

插入过程

CREATE PROCEDURE taxon_reclamation (IN TaxonNameIn VARCHAR(170), IN RankIDIn VARCHAR(170), IN ParentIDIn VARCHAR(11), IN TaxonIDIn VARCHAR(170)) 
BEGIN
DECLARE TaxonName varchar(170);
DECLARE RankID varchar(170);
DECLARE ParentID varchar(170); 
DECLARE TaxonID varchar(170);
SET TaxonName = TaxonNameIn;
SET RankID = RankIDIn;
SET TaxonID = TaxonIDIn;
SET ParentID = ParentIDIn;

INSERT INTO taxon_reclamation(TaxonID, FullName, Name, RankID, ParentID, TaxonTreeDefID, TaxonTreeDefItemID)
VALUES (TaxonID, TaxonName, TaxonName, RankID, ParentID, 2, 1);
END //

我应该得到一个临时表，其中包含分类单元的名称、分类单元 ID、该分类单元的亲代 ID 及其等级 ID(科、属、种等)。

Answer 1

您应该更改变量的名称，以便它们与列名称不同。