接受多个参数的存储过程 [英] Stored procedure that accepts multiple parameters

问题描述

这是我第一次使用存储过程.

It's my first time working with stored procedures.

以前的开发人员已经有一个可以工作的存储过程，但它只接受 1 个参数.

The previous developer already had a stored procedure in place that works, but it only accepts 1 parameter.

我使用PHP传递参数:

I am using PHP to pass the parameters:

 <?php
   $containers = $_POST['cntnum'];

   $shortened = array();
   foreach($containers as $short)
   {
     $shortened[] = substr($short, 0, 10);
   }
   $sans_check = preg_replace('/\n$/','',preg_replace('/^\n/','',preg_replace('/[\r\n]+/',"\n",$shortened)));
   $sans = "'" . implode("', '", $sans_check) ."'";

   // At this point, $sans looks like this: 'value1', 'value2', 'value3'... 

   // now I send $sans to the stored procedure
   $thecall = mysqli_query($dbc, "CALL SP_ContSearch_TEST($sans)");
 ?>

我可以毫无问题地发送 1 个值.我取回数据.但是当超过 1 个时，我收到以下错误:

I can send 1 value with no problem. I get back the data. But when there are more than 1, I get the following error:

Incorrect number of arguments for PROCEDURE table.storeprocedure; expected 1, got 3

这是存储过程的样子(缩短了时间):

Here is what the stored procedure looks like (shortened for time):

Begin
  DECLARE sans_check varchar(100);  // adjusted from 10, but same error message
  SET sans_check = SUBSTR(cont,1,10);
  SELECT
    `inventory`
    ,delivery_date
    ,pool
  FROM 
    inventory
  WHERE
    CONTAINER_CHECK IN (cont);
  END

参数 cont 是 varchar(11)//不确定这是否意味着什么

The parameter cont is varchar(11) // not sure if that means anything

这是我第一次尝试调用存储过程，我可以返回一个值的数据.我需要返回多个值的数据.

This is my first attempting a stored procedure call, and I can return data for one value. I need to return data for multiple values.

推荐答案

错误信息完全正确.您将 3 个参数发送到只需要一个的存储过程.

The error message is absolutely right. You are sending 3 parameters to a stores procedure which takes only one.

您所做的是修改了采用单个字符串的存储过程，以便它仍然需要单个字符串.

What you've done is you have modified the stored proc which takes a single string such that it still expects a single string.

您应该修改存储过程的定义以采用 3 个参数(您的问题中缺少该部分)

You should modify the definition of the stored procedure to take 3 parameters (that part is missing in your question)

这是一个带有 3 个参数的存储过程声明示例:

Here is an example of a stored proc declaration with 3 parameters:

 CREATE PROCEDURE SP_ContSearch_TEST
    (IN sans1 CHAR(10),
     IN sans2 CHAR(10),
     IN sans3 CHAR(10)
     -- add as many other parameters here as you need
    )
 BEGIN
     -- your stored proc logic here.. can use sans1, sans2, and sans3
 END

您还应该更改您的代码以使用参数化查询，而不是您现在的做法.请参阅:http://php.net/manual/en/pdo.prepared-statements.php 或 http://php.net/manual/en/mysqli.prepare.php

You should also change your code to use parameterized queries instead of the way you're doing right now. See: http://php.net/manual/en/pdo.prepared-statements.php or http://php.net/manual/en/mysqli.prepare.php

这篇关于接受多个参数的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！