创建MySQL中的可选参数的函数 [英] Create a function with optional arguments in 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有href=\"http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_avg\" rel=\"nofollow\"> AVG的？

You do know that MySQL has an AVG aggregate function?

解决方法
如果你能面对这个解决办法的丑陋这里的样本code使用逗号分隔字符串值作为输入并返回平均值。

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;

您也可以写一个UDF 在<一个href=\"http://stackoverflow.com/questions/1914776/mysql-aggregate-udf-user-defined-function-in-c\">C(++)或<一个href=\"http://stackoverflow.com/questions/5894258/creating-a-udf-for-mysql-in-delphi\">Delphi/Lazarus

这篇关于创建MySQL中的可选参数的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！