这件事把我弄糊涂了,我希望解决办法是“你知道为什么这不管用,是不是?”式的回答。
下面是:通过php我调用了一个mysql存储过程,因此:
$sql = "CALL sp_test;";
$mysqli->query($sql);
没问题。我知道存储过程会被执行。
这是我为这个例子划分的存储过程,它仍然产生同样的问题。
DELIMITER \\
DROP PROCEDURE IF EXISTS sp_test\\
CREATE PROCEDURE sp_test()
DETERMINISTIC
BEGIN
DECLARE exit_loop TINYINT(1) DEFAULT 0;
DECLARE v_TableName VARCHAR(50); DECLARE v_ScanID INT(11); DECLARE v_ScanType TINYINT(4);
DECLARE v_TitleID INT(11); DECLARE v_VolumeNo INT(11); DECLARE v_IssueNo VARCHAR(10); DECLARE v_IssueDate INT(11); DECLARE v_FIDateUnknown TINYINT(4); DECLARE v_PureHavocScan TINYINT(4);
-- declare cursor for employee email
DECLARE storeroom_cursor CURSOR FOR SELECT TableName, ScanID, ScanType, FIDateUnknown, TitleID, VolumeNo, REPLACE(IssueNo, "\'", "") AS IssueNo, IssueDate FROM t_storeroom ts JOIN t_issueguide tig ON ts.TitleID = tig.ComicTitleID;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
OPEN storeroom_cursor;
get_purehavoc: LOOP
FETCH storeroom_cursor INTO v_TableName, v_ScanID, v_ScanType, v_FIDateUnknown, v_TitleID, v_VolumeNo, v_IssueNo, v_IssueDate;
IF exit_loop = TRUE THEN
LEAVE get_purehavoc;
END IF;
CASE v_ScanType
WHEN '1' AND v_FIDateUnknown = 0 THEN
SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND IssueDate = ", v_IssueDate, " AND DiggerExclusive = 1;");
WHEN '1' AND v_FIDateUnknown = 1 THEN
SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND DiggerExclusive = 1;");
-- WHEN '2' THEN
-- Do something
END CASE;
PREPARE statement FROM @sql_text;
EXECUTE statement;
-- Setting the PureHavocScan variable to enter into the PREPARE STATEMENT.
IF @RowCount = 0 THEN
SET @PureHavocScan = 1;
ELSE
SET @PureHavocScan = 0;
END IF;
SET @ScanID = v_ScanID;
SET @TableName = v_TableName;
-- SET @sql_text = concat("UPDATE t_storeroom SET PureHavocScan = ? WHERE TableName = ? AND ScanID = ? ");
SET @sql_text = concat("UPDATE t_storeroom SET PureHavocScan = ", @PureHavocScan, " WHERE TableName = '", @TableName, "' AND ScanID = ", @ScanID, ";");
PREPARE statement FROM @sql_text;
EXECUTE statement;
END LOOP get_purehavoc;
DEALLOCATE PREPARE stmt;
CLOSE storeroom_cursor;
END//
DELIMITER ;
当我在mysql数据库管理工具(在我的例子中是navicat)中运行这个存储过程时,存储过程100%按预期工作,所有应该更新的行都会得到更新。
但是,当我通过php运行这个存储过程时,只有1行(有时2行)得到更新。
以完全相同的方式调用的其他存储过程工作正常。就是这个。与失败的命令的唯一区别是mysql命令是一个更新,而selects和insert命令工作正常。
我已经做了两天了,已经用尽了我所有的知识。所以如果有人能帮我摆脱痛苦,我会非常感激的。
1条答案
按热度按时间8i9zcol21#
这个
CASE
陈述不正确:CASE