代码之家 › 专栏 › 技术社区 › James Pitt

MySQL存储过程-无法重新打开表(不是临时表)

stored-procedures mysql

1

James Pitt · 技术社区 · 13 年前

我正在将一个存储过程从MS-SQL转换为MySQL。它是基于有向无环图的。

以下页面上的清单2中显示了原始MS-SQL脚本: http://www.codeproject.com/Articles/22824/A-Model-to-Represent-Directed-Acyclic-Graphs-DAG-o

存储过程保存良好,但不会从数据库中删除任何行。我已经尝试过运行Debugger for MySQL,它将第一次通过,似乎会更新我用于临时数据的表,但不会更新“edges”表。它也不会把桌子放在最后。

第二次运行,如果我手动删除purgelist表,它将运行到REPEAT语句,然后我得到错误代码:1137 SQLState:HY000,消息:无法重新打开表:“purgelist”。最初我想使用临时表,但我知道我不能像SELECT那样多次引用临时表,然而,我很惊讶这个错误发生在非临时表上。此外,我认识到在多会话环境中,我需要对purgelist进行动态命名。

以下是MySQL代码:

DELIMITER //
CREATE PROCEDURE RemoveEdge(
IN iId int(11)
)

MAIN_BLOCK: BEGIN

DECLARE counter int default 0;
DECLARE rcount int default 0;
SET counter = ( SELECT id FROM edges WHERE id = iId AND hops = 0 );
IF counter = 0 THEN
   BEGIN
      LEAVE MAIN_BLOCK;
   END;
END IF;

CREATE TABLE purgeList (id int);

-- step 1: rows that were originally inserted with the first
-- AddEdge call for this direct edge
INSERT INTO purgeList
    SELECT id
      FROM edges
      WHERE directEdgeId = iId;

-- step 2: scan and find all dependent rows that are inserted afterwards
REPEAT
    INSERT INTO purgeList
        SELECT id    
            FROM edges
            WHERE hops > 0
                AND ( entryEdgeId IN ( SELECT id FROM purgeList ) 
                    OR exitEdgeId IN ( SELECT id FROM purgeList ) )
            AND id NOT IN (SELECT id FROM purgeList );
    SET rcount = ROW_COUNT();
UNTIL rcount = 0
END REPEAT;

DELETE FROM edges
   WHERE id IN ( SELECT id FROM purgeList);
DROP TABLE purgeList;
END //
DELIMITER ;

我已经仔细检查了我的语法,但我认为其中有一个错误。非常感谢您的帮助。

1 回复 | 直到 13 年前

1

2

joocer 13 年前

可能有一种更干净的方法,但我认为您需要在Repeat中创建一个临时表

SELECT id FROM purgeList

然后引用临时表,而不是purgeList,类似这样:

REPEAT

    CREATE TABLE innerPurgeList (id int);

    INSERT INTO innerPurgeList
       SELECT id
            FROM purgeList;

    INSERT INTO purgeList
       SELECT id    
            FROM edges
            WHERE hops > 0
                AND ( entryEdgeId IN ( innerPurgeList ) 
                    OR exitEdgeId IN ( innerPurgeList ) )
            AND id NOT IN ( innerPurgeList );
    SET rcount = ROW_COUNT();

    DROP TABLE innerPurgeList;

UNTIL rcount = 0
END REPEAT;