无法为游标编译 mysql 过程 [英] couldnt compile mysql procedure for cursors

问题描述

我正在尝试使用以下过程来更新表，但我无法编译该过程.

I am trying to use the below procedure to update a table but i could not compile the procedure.

CREATE
    PROCEDURE `propmanage2016`.`test`()
    DECLARE CURSOR cur1 FOR
         SELECT unit_id, unit_code FROM t_units WHERE unit_projectid = 1;
    DECLARE done INT DEFAULT FALSE;
    DECLARE a INT;
    DECLARE b VARCHAR(200);
    DECLARE done INT DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    BEGIN
        OPEN cur1;    
        read_loop: LOOP
            FETCH cur1 INTO  b , a ;       
            IF done THEN
                LEAVE read_loop;
            END IF;
            UPDATE t_owner_resident SET or_unit = a WHERE unit_name = b;
        END LOOP;
        CLOSE cur1;
    END;

请帮忙

推荐答案

如果没有存储过程，你确定不能这样做吗?

Sure you can't do it like this without the stored procedure?

   UPDATE t_owner_resident ow INNER JOIN t_units t set ow.unit_name = t.unit_code 
   WHERE o.unit_name = t.unit_id and t.unit_projectid = 1

注意:我认为 a,b 在你的 SP 中混淆了，所以请原谅我的列名也稍微混淆了

Note: I think a,b is mixed up in your SP, so please excuse if i've got the column names slightly mixed up too

这篇关于无法为游标编译 mysql 过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！