在foreach循环中调用存储过程-仅首先执行 [英] Calling Stored Procedure in foreach loop - only first executed

问题描述

这实际上是我之前的衍生产品今天的问题.

我在数据库中创建了一个存储过程，想从PHP中连续调用多次.
假设这是我的程序:

I created a Stored Procedure in my database that I wanted to call several times in a row from PHP.
Let's say this is my procedure:

CREATE PROCEDURE PROC_1(
  IN param1 VARCHAR(255),
  IN param2 VARCHAR(255))
BEGIN
  DECLARE ok INT;
  DECLARE success, failure VARCHAR(255);

  /* several SELECT, IF ... THEN, INSERT and UPDATE operations which then SET ok var to 0 or 1 */
  IF ok = 1 THEN
    SET success = 'Everything went well';
    SELECT success;
    LEAVE;
  ELSE
    SET failure = 'Problem description';
    SELECT failure;
    LEAVE;    
  END IF;
END

我是这样做的(简短版):

I did it this way (short version):

$calls = array(
    "CALL PROC_1('param1', 'param2')",
    "CALL PROC_1('param3', 'param4')",
    "CALL PROC_1('param5', 'param6')",
);

// assuming I'm already connected to DB with $link
foreach ($calls as $i => $call)
{
    echo $i . ': ';
    $result = mysql_query($call);
    $ok = ($result === FALSE) ? FALSE : TRUE;
    var_dump($ok);

    if ($result !== FALSE)
        mysql_free_result($result);
}

第一次迭代按预期工作，但返回FALSE后将执行任何操作.
为什么会这样?

The first iteration works as expected, but anything after returns FALSE.
Why is that?

尝试mysqli以防万一，但输出完全相同:

Tried mysqli just in case, but getting exactly same output:

0: bool(true)
1: bool(false)
2: bool(false)

有趣的是，我检查了MySQL日志(设置为记录所有查询的日志记录)，并且只有第一个查询到达了服务器.接下来的查询永远不会到达服务器.

What's interesting, I've checked MySQL logs (logging set to log all queries) and only first query ever gets to the server. Next queries never get to the server.

PS.我正在运行PHP 5.3.2和Apache 2.2.17.

PS. I'm running PHP 5.3.2 and Apache 2.2.17.

更新

根据Shakti Singh的建议，在查询数据库之前，我已经检查了$link状态.我注意到自第二次迭代以来存在一个错误，因此这是带有错误的输出:

As per Shakti Singh's suggestion I've checked $link state before querying the database. I noticed there's an error since the second iteration so here's the output with the error:

ERROR: (0) 
0: bool(true)
ERROR: (0) 

ERROR: (0) 
1: bool(false)
ERROR: (2014) Commands out of sync; you can't run this command now

ERROR: (2014) Commands out of sync; you can't run this command now
2: bool(false)
ERROR: (2014) Commands out of sync; you can't run this command now

此外，这会出现在MySQL错误日志中:

Also, this appears in MySQL error log:

101126 15:46:28 [Warning] Aborted connection 129 to db: 'db1' user: 'root' host: 'localhost' (Got an error reading communication packets)

推荐答案

在php中，当我们在循环中调用存储过程时，它只执行一次.当存储过程返回任何结果集时，就会发生这种情况. 我遇到了同样的问题.我有一个用于更新表记录的存储过程.

In php, when we call a stored procedure in a loop, it just execute it once. It happens when the stored procedure returns any result set. I faced the same issue. I had a stored procedure for updating table records.

DELIMITER $$

DROP PROCEDURE IF EXISTS `espritkm`.`update_notification`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_notification`(item_id_val VARCHAR(11),item_source_val VARCHAR(50),item_type_id_val INT(50),item_type_val VARCHAR(50),created_at_val BIGINT(11),pivot_user_id_val VARCHAR(256),pivot_item_type_val VARCHAR(64),pivot_owner_type_val VARCHAR(64),pivot_owner_id_val INT(11),user_id_val VARCHAR(64),  OUT row_effect VARCHAR(11))
Begin
  Declare item_count INT(10);
   SET @SQL1 = CONCAT('select count(*) into @item_count from item_notifications where item_id = ''', item_id_val, '''');                                                                                    PREPARE S1 FROM @SQL1;                                                                                EXECUTE S1;                                                                                    DEALLOCATE PREPARE S1;     
    IF @item_count = 0 THEN
       SET @SQL2 = CONCAT('INSERT INTO item_notifications (item_id,item_source,item_type_id,item_type,created_at,pivot_user_id,pivot_item_type,pivot_owner_type,pivot_owner_id,user_id) value(''',item_id_val,''',''',item_source_val,''',''',item_type_id_val,''',''',item_type_val,''',''',created_at_val,''',''',pivot_user_id_val,''',''',pivot_item_type_val,''',''',pivot_owner_type_val,''',''',pivot_owner_id_val,''',''',user_id_val,''')');    
 PREPARE S2 FROM @SQL2;                                                                                  EXECUTE S2;                                                                                     DEALLOCATE PREPARE S2;      
      SET row_effect= "Insert";    
    ELSE             
        SET row_effect= "Update";    

    SET @SQL3 = CONCAT('UPDATE item_notifications SET viewer_id = ''',user_id_val,''' WHERE item_id = ''' ,item_id_val,'''') ;     
       PREPARE S3 FROM @SQL3;                                                                                 EXECUTE S3;                                                                                    DEALLOCATE PREPARE S3;      

    END IF;
    SELECT row_effect;
END$$

DELIMITER ;

这应该执行1000条以上，但只执行一条记录.

And this was supposed to be executed for 1000+lines, but executed for just one record.

当您的SP返回任何数据集时，它不支持.只需消除OUT var或任何select语句(仅用于任何结果引用)，它就可以正常工作.

It does not support in case of when your SP return any dataset. Just eliminate the OUT var, or any select statement(just for any result reference), and it'll work fine.

这篇关于在foreach循环中调用存储过程-仅首先执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！