Mysql自定义函数

来源：互联网发布：神笔获取宝贝数据失败编辑：程序博客网时间：2024/06/16 22:28

今天写一个简单点的自定义函数muniu：
实现两个数的加减muniu(a,b,op);

drop function if exists muniu;delimiter $$create function muniu(a int(11),b int(11), op varchar(50)) returns intbegin declare p varchar(80);select op into p;if(trim(p)="+")then return a+b;elseif trim(p) = "-"then return a-b;end if;end$$delimiter ;

将如上的代码放入mysql中运行，如下所示，实现了自定义的两个数的加法与减法运算

mysql> drop function if exists muniu;Query OK, 0 rows affected (0.00 sec)mysql> delimiter $$mysql> create function muniu(a int(11),b int(11), op varchar(50)) returns int    -> begin    ->  declare p varchar(80);    ->  select op into p;    ->  if(trim(p)="+")    ->          then return a+b;    ->  ***elseif trim = "-"#这里写错了***    ->          then return a-b;    ->  end if;    -> end$$Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql>mysql> select muniu(3,5,"  +  ");+--------------------+| muniu(3,5,"  +  ") |+--------------------+|                  8 |+--------------------+1 row in set (0.00 sec)mysql> select muniu(8,2,"  -  ");ERROR 1054 (42S22): Unknown column 'trim' in 'field list'mysql>mysql>mysql>mysql>mysql> drop function if exists muniu;Query OK, 0 rows affected (0.00 sec)mysql> delimiter $$####修改之后重新运行一遍mysql> create function muniu(a int(11),b int(11), op varchar(50)) returns int    -> begin    ->  declare p varchar(80);    ->  select op into p;    ->  if(trim(p)="+")    ->          then return a+b;    ->  elseif trim(p) = "-"    ->          then return a-b;    ->  end if;    -> end$$Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql>mysql>mysql> select muniu(3,5,"  +");+------------------+| muniu(3,5,"  +") |+------------------+|                8 |+------------------+1 row in set (0.00 sec)mysql> select muniu(8,2,"-  ");+------------------+| muniu(8,2,"-  ") |+------------------+|                6 |+------------------+1 row in set (0.00 sec)

自定义函数的意义：
官方函数是有限的，特殊的需求需要自定义函数来实现

1 0