具有可变参数列表的MySQL存储过程 [英] MySQL stored procedure with variable argument list

问题描述

我做了一个存储过程.我希望它通过不同的参数过滤数据.如果我传递了一个参数，则应将其过滤掉一个参数；如果我通过了两个，则应将其过滤为两个，依此类推，但无法正常工作.

I have made a stored procedure. I want it to filter the data by different parameters. If I pass one parameter, it should be filtered by one; if I pass two, it should be filtered by two, and so on, but it is not working.

有人可以帮我吗?

DROP PROCEDURE IF EXISTS medatabase.SP_rptProvince2;
CREATE PROCEDURE medatabase.`SP_rptProvince2`(
 IN e_Region VARCHAR(45)
  )
BEGIN

 DECLARE strQuery VARCHAR(1024);
 DECLARE stmtp   VARCHAR(1024);
  SET @strQuery = CONCAT('SELECT * FROM alldata where 1=1');
  IF e_region IS NOT NULL THEN
   SET @strQuery = CONCAT(@strQuery, ' AND (regionName)'=e_Region);
  END IF;

 PREPARE  stmtp FROM  @strQuery;
 EXECUTE  stmtp;
END;

推荐答案

AFAIK，您不能拥有像这样的可变参数列表.您可以执行以下其中一项操作:

AFAIK, you can't have a variable argument list like that. You can do one of a couple of things:

1. 采用固定的最大数量的参数，并在连接之前检查它们是否为空:

1. Take a fixed maximum number of parameters, and check them for null-ness before concatenating:

CREATE PROCEDURE SP_rptProvince2(a1 VARCHAR(45), a2 VARCHAR(45), ...)

...

  IF a1 IS NOT NULL THEN
    SET @strQuery = CONCAT(@strQuery, ' AND ', a2);
  END IF;

如果需要在参数中应用条件的预定字段(例如现有代码中的e_Region参数)，则可以适当地修改CONCAT操作.

If you need predetermined fields to which the criteria in the argument apply (like the e_Region parameter in your existing code), then you modify the CONCAT operation appropriately.

可能的调用:

CALL SP_rptProvince2('''North''', 'column3 = ''South''')

采用一个仅比45个字符大得多的参数，然后将其简单地附加到查询中(假设它不为null).

Take a single parameter that is much bigger than just 45 characters, and simply append it to the query (assuming it is not null).

很显然，这使用户有责任提供正确的SQL代码.

Clearly, this places the onus on the user to provide the correct SQL code.

可能的调用:

CALL SP_rptProvince2('RegionName = ''North'' AND column3 = ''South''')

两者之间没有太多选择.任一种都可以工作；都不是完全令人满意的.

There's not a lot to choose between the two. Either can be made to work; neither is entirely satisfactory.

您可能会注意到，有必要用引号将引数中的字符串加以保护；那就是使这个问题成问题的原因.

You might note that there was a need to protect the strings in the arguments with extra quotes; that is the sort of thing that makes this problematic.

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