在 MySQL 中创建一个带有可选参数的函数

2021-11-17 00:00:00 function arguments mysql

我想在 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 存储过程中设置可选参数.
但是，您可以在 MySQL UDF 中设置可选参数.

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

你知道 MySQL 有一个 AVG 聚合函数?

You do know that MySQL has an AVG aggregate function?

解决方法如果您可以面对这种变通方法的丑陋之处，这里的示例代码使用逗号分隔的字符串作为输入并返回平均值.

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.

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 ;

使用 concat_ws 提供函数.

Use concat_ws to feed the function.

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

您还可以编写 UDFC(++) 或 Delphi/Lazarus

相关文章