mysql - 一次调用多个 mysql 存储过程

Question

我有一个像这样的存储过程，它工作正常:

$drop = $mysqli->query("DROP PROCEDURE IF EXISTS changegroup");
$initiate = $mysqli->query("
    Create Procedure changegroup(IN param1 int(10),IN param2 int(10))
    BEGIN
       UPDATE t_parts SET part_group_id = param2 WHERE part_id = param1;
    END;
");
$result = $mysqli->query("CALL changegroup($p1,$p2);");

我的问题是，我们是否可以将两个 SQL 语句放在一个过程中，并基于第一个过程执行第二个过程，如下所示:

BEGIN
     SELECT * FROM ........
     /**fetch the result of this mysql statment and if matches certain conditions,then execute the update statment***/

     UPDATE t_parts SET part_group_id = param2 WHERE part_id = param1;
END;

Answer 1

在你的存储过程中写入

if <condition> then 
  your code
end if;

所以你的代码应该是这样的

Create Procedure changegroup(IN param1 int(10),IN param2 int(10))
BEGIN
     DECLARE totalcount Integer default 0;
     SELECT count(*) into totalcount  FROM yourtable where <condition>;
     /**fetch the result of this mysql statment and if matches certain conditions,then execute the update statment***/
    if(totalcount > 0) then
     UPDATE t_parts SET part_group_id = param2 WHERE part_id = param1;
    end if;
END;