Хранимая процедура MySQL с переменным аргументом

Я сделал хранимую процедуру. Я хочу, чтобы он отфильтровывал данные по различным параметрам. Если я передаю один параметр, он должен быть отфильтрован одним; если я передаю два, он должен быть отфильтрован двумя и так далее, но он не работает.

Кто-нибудь может мне помочь?

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;

Ответы

Ответ 1

AFAIK, вы не можете иметь такой список переменных аргументов. Вы можете сделать одну из двух вещей:

  • Возьмите фиксированное максимальное количество параметров и проверите их для null-ness перед конкатенацией:

    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.

    Возможный вызов:

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

  • Возьмите один параметр, который намного больше, чем всего 45 символов, и просто добавьте его в запрос (если он не является нулевым).

    Ясно, что это ставит бремя ответственности пользователя за предоставление правильного кода SQL.

    Возможный вызов:

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

Нельзя выбирать между ними. Либо можно заставить работать; ни один из них не является полностью удовлетворительным.

Вы могли заметить, что необходимо было защитить строки в аргументах с помощью дополнительных кавычек; это то, что делает эту проблему проблематичной.

Ответ 2

Я нашел подход, основанный на JSON, который работает с новейшими системами MySQL/MariaDB. Проверьте ссылку ниже (оригинальный автор Федерико Раззоли): https://federico-razzoli.com/variable-number-of-parameters-and-optional-parameters-in-mysql-mariadb-procedures

По сути, вы берете параметр BLOB, который на самом деле является объектом JSON, а затем выполняете JSON_UNQUOTE (JSON_EXTRACT (json object, key)) в зависимости от ситуации.

Снял выписку здесь:

CREATE FUNCTION table_exists(params BLOB)
    RETURNS BOOL
    NOT DETERMINISTIC
    READS SQL DATA
    COMMENT '
Return whether a table exists.
Parameters must be passed in a JSON document:
* schema (optional). : Schema that could contain the table.
                       By default, the schema containing this procedure.
* table              : Name of the table to check.
'
BEGIN
    DECLARE v_table VARCHAR(64)
        DEFAULT JSON_UNQUOTE(JSON_EXTRACT(params, '$.table'));
    DECLARE v_schema VARCHAR(64)
        DEFAULT JSON_UNQUOTE(JSON_EXTRACT(params, '$.schema'));

    IF v_schema IS NULL THEN
        RETURN EXISTS (
            SELECT TABLE_NAME
                FROM information_schema.TABLES
                WHERE
                    TABLE_SCHEMA = SCHEMA()
                    AND TABLE_NAME = v_table
        );
    ELSE
        RETURN EXISTS (
            SELECT TABLE_NAME
                FROM information_schema.TABLES
                WHERE
                    TABLE_SCHEMA = v_schema
                    AND TABLE_NAME = v_table
        );
    END IF;
END;