在MySQL中创建具有可选参数的函数

I want to create a function with optional arguments in MySQL. For instance, I want to create function that calculates the average of its arguments. I create a function of five arguments, but when user passes just two arguments to the function then it should still run and return the average of the two arguments.

我想在MySQL中创建一个带有可选参数的函数。例如，我想要创建一个函数来计算其参数的平均值。我创建了一个包含五个参数的函数，但是当用户只向函数传递两个参数时，它仍然应该运行并返回两个参数的平均值。

3 个解决方案

#1

You cannot set optional parameters in MySQL stored procedures.
You can however set optional parameters in a MySQL UDF.

不能在MySQL存储过程中设置可选参数。但是，您可以在MySQL UDF中设置可选参数。

You do know that MySQL has an AVG aggregate function?

你知道MySQL有AVG聚合函数吗?

Workaround If you can face the ugliness of this workaround here's samplecode that uses a comma separated string with values as input and returns the average.

解决方案如果你能面对这个解决方案的丑陋之处，这里的samplecode使用逗号分隔的字符串作为输入并返回平均值。

DELIMITER $$

CREATE FUNCTION MyAvg(valuestr varchar) RETURNS float
BEGIN
  DECLARE output float;
  DECLARE arg_count integer;
  DECLARE str_length integer;
  DECLARE arg float;
  DECLARE i integer;

  SET output = NULL;

  SET i = LENGTH(valuestr);
  IF i > 0 THEN BEGIN 

    SET arg_count = 1;
    WHILE i > 0 DO BEGIN
      IF MID(valuestr, i, 1)
      SET i = i - 1;
    END; END WHILE;

    /* calculate average */
    SET output = 0;
    SET i = arg_count;
    WHILE i > 0 DO BEGIN
      SET arg = SUBSTRING_INDEX( 
                  SUBSTRING_INDEX(valuestr, ',' , i)
                  , ',', -1 );
      SET output = output + arg;
      SET i = i - 1; 
    END; END WHILE;       
    SET output = output / arg_count;

  END; END IF;    
  RETURN output;
END $$

DELIMITER ;

Use concat_ws to feed the function.

使用concat_ws来提供函数。

SELECT MyAvg(CONCAT_WS(',',100,200,300,500)) AS test;

You can also write an UDF in C(++) or Delphi/Lazarus

您还可以用C(++)或Delphi/Lazarus编写UDF

#2

While far from an ideal solution, here's how I solved optional parameters for a concat function I needed:

虽然这远不是一个理想的解决方案，但以下是我如何为我需要的concat函数求解可选参数:

delimiter ||
create function safeConcat2(arg1 longtext, arg2 varchar(1023))
 returns longtext
 return safeConcat3(arg1, arg2, '');
||

create function safeConcat3(arg1 longtext, arg2 varchar(1023), arg3 varchar(1023))
 returns longtext
 return safeConcat4(arg1, arg2, arg3, '');
||

create function safeConcat4(arg1 longtext, arg2 varchar(1023), arg3 varchar(1023), arg4 varchar(1023))
returns longtext
  begin
      declare result longText;
      set result = concat(arg1, arg2, arg3, arg4);
      if( result is null) then
          set result=arg1;
      end if;
      return result;
  end
||

Note: This means you have to call the method that corresponds to the number of args.

注意:这意味着您必须调用对应于args数量的方法。

#3

Another approach is to pass only one 'super' parameter which is string with commas in it separating the real parameters. The mysql procedure can then parse the 'super' parameter into the separate real parameters. Example:

另一种方法是只传递一个“super”参数，该参数是字符串，其中包含逗号，用于分隔实际参数。然后，mysql过程可以将“super”参数解析为单独的实际参数。例子:

create procedure procWithOneSuperParam(param1 varchar(500))
declare param2 varchar(100);
begin
 if LOCATE(',',param1) > 0 then
  .. param2=<extract the string after the ',' from param1> ..

#1