mysql 自定义函数的写法

参考：

[mysql创建函数报1064错误的解决方案_mysql 创建函数一直报1064-CSDN博客](https://blog.csdn.net/tuolingss/article/details/121234411#:~:text=%231064 – You have an error in your,syntax to use near ” at line 4在mysql中默认分号（%3B）为分隔符，也就是说，遇到分号，就认为你输入完了，需要系统执行了。)

1.语法

create function 函数名([参数列表]) returns 数据类型
begin
 sql语句;
 return 值;
end;

多参数时
create function 函数名(
	xxx VARCHAR ( 100 ),
	zzz VARCHAR ( 100 ),
	yyy VARCHAR ( 100 ) ) returns 数据类型
	begin
 sql语句;
 return 值;
end;

注意:还有要注意的是参数不要跟表的字段名称一样，不然虽然不会报错，但是查出来的数据会有异常

创建方法拼接SQL

DELIMITER $$
DROP FUNCTION IF EXISTS genPerson$$
CREATE FUNCTION genPerson(name varchar(20)) RETURNS varchar(50)
BEGIN
  DECLARE str VARCHAR(50) DEFAULT '';
  SET @tableName=name;
  SET str=CONCAT('create table ', @tableName,'(id int, name varchar(20));');
  return str;
END $$
DELIMITER ;

（1）DELIMITER $$ 定义结束符。MySQL默认的结束符是分号，但是函数体中可能用到分号。为了避免冲突，需要另外定义结束符。

（2）DROP FUNCTION IF EXISTS genPerson$$ 如果函数genPerson已经存在了，就删除掉。

（3）CREATE FUNCTION 创建函数genPerson，函数的参数是name，返回值是varchar(50)。

（4）函数体放在BEGIN 与 END之间。

（5）DECLARE 声明变量，str类型是varchar(50)，默认值是空。

（6）CONCAT连接多个字符串。

（7）RETURN 返回拼接后的字符串str。

DELIMITER $$
DROP FUNCTION IF EXISTS getNthHighestSalary$$
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
	SET N = N -1;
  RETURN (
      select ifnull((select distinct id from user order by id desc limit 1 OFFSET n),null)
  );
END

调用方法

select genPerson('student');

select getNthHighestSalary(2)

配置问题：

如果创建自定义函数时遇到下面这种报错：

Error Code : 1418 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

原因分析：
导致该错误的原因可能是一个安全设置方面的配置，查手册log_bin_trust_function_creators参数缺省0，是不允许function的同步的，一般我们在配置repliaction的时候，都忘记关注这个参数，这样在master更新funtion后，slave就会报告错误，然后slave stoped。
解决办法可以参考这里：

MySQL ERROR 1418 的解决方法（自定义函数）_mysql自定义函数1418-CSDN博客

实战

一定要有分割符不然有些创建函数的时候执行不了，我自己就当时创建没加分割符就一直提示报错1064

注意:还有要注意的是参数不要跟表的字段名称一样，不然虽然不会报错，但是查出来的数据会有异常

创建

DELIMITER $$
DROP FUNCTION IF EXISTS fun_get_pick_loc_code$$
CREATE FUNCTION fun_get_pick_loc_code(
	schedulecode VARCHAR ( 100 ),
	materialnum VARCHAR ( 100 ),
	lotcode VARCHAR ( 100 )
	) RETURNS varchar(100) CHARSET utf8
BEGIN
	SET @num1 =(
		SELECT
			IFNULL( loc_code, "" ) 
		FROM
			inventory_log 
		WHERE
			inventory_type = 'delivery' 
			AND order_type = 'picking' 
			AND lot_code = lotcode 
			AND material_num = materialnum 
			AND order_num = ( SELECT preparation_order FROM preparation_master WHERE schedule_code = schedulecode AND preparation_type = '0' LIMIT 1 ) 
			LIMIT 1 
		);
	RETURN ( @num1 );
END $$
DELIMITER ;

调用

SELECT
	rd.return_order,
	rd.schedlule_code,
	rd.material_code,
	rd.lot_code,
	fun_get_pick_loc_code ( rd.schedlule_code, rd.material_code, rd.lot_code ) loc_code 
FROM
	return_detail rd 
WHERE
	rd.is_shelves =0

创建的各种函数例子（创建时都需要加分隔符和判断是否存在，以下省略了这个步骤，使用时注意看清楚）

1、fun_get_iqc_qty

CREATE DEFINER=`root`@`localhost` FUNCTION `fun_get_iqc_qty`(rdid int) RETURNS int(11)
BEGIN 
 RETURN (select FLOOR(IFNULL(sum(real_qty),0)) from receipt_detail_lot where receipt_det_id=rdid);
END

2、fun_vendordate

CREATE DEFINER=`root`@`localhost` FUNCTION `fun_vendordate`(lotcode VARCHAR(50)) RETURNS varchar(50) CHARSET utf8
BEGIN 
 RETURN (SELECT  DATE_FORMAT(Input_Date,'%y%m%d') from a_iqc where Barcode=left(lotcode,16));
END

3、fun_vendorcode

CREATE DEFINER=`root`@`localhost` FUNCTION `fun_vendorcode`(lotcode VARCHAR(50)) RETURNS varchar(50) CHARSET utf8
BEGIN 
 RETURN (SELECT  Manufacturer from a_iqc where Barcode=left(lotcode,16));
END

4、fun_get_unit_qty

CREATE DEFINER=`root`@`localhost` FUNCTION `fun_get_unit_qty`(bomcode VARCHAR(50)) RETURNS float(22,9)
BEGIN 
  set @num1=IFNULL((select sum(use_qty) from bom_detail where bom_code=bomcode),0);
	set @num2=IFNULL((select sum(use_qty) from bom_replace where bom_code=bomcode),0);
 RETURN (@num1+@num2);
END

5、fun_get_stack

CREATE DEFINER=`root`@`localhost` FUNCTION `fun_get_stack`(sch_code VARCHAR(30)) RETURNS varchar(100) CHARSET utf8
BEGIN 
  set @num1=(select GROUP_CONCAT(distinct stack_code) from schedule_stack where schedule_code=sch_code); 
 RETURN (@num1);
END

6、fun_get_materialtype

CREATE DEFINER=`root`@`localhost` FUNCTION `fun_get_materialtype`(materialcode VARCHAR(50)) RETURNS varchar(30) CHARSET utf8
BEGIN 
   if(left(materialcode,4)='AM31') THEN
	set @scode='SMT-PG';
	else
	set @scode='SMT';
	 end if;
 RETURN @scode;
END

7、fun_get_pick_qty

BEGIN 
if(schedulecode='') then    
SET @i= 99999;
ELSE
 SET @i= (select IFNULL(sum(qty),0)
from inventory_log where inventory_type='delivery' and order_type='picking'
and material_num = materialcode 
and order_num = (select preparation_order from preparation_master  where schedule_code=schedulecode and  preparation_type='0' LIMIT 1 ));
end if;
RETURN (@i);
END

8、fun_get_pick_loc_code

CREATE DEFINER=`root`@`localhost` FUNCTION `fun_get_pick_loc_code`(
	schedulecode VARCHAR ( 100 ),
	materialnum VARCHAR ( 100 ),
	lotcode VARCHAR ( 100 )) RETURNS varchar(100) CHARSET utf8
BEGIN
	SET @num1 =(
		SELECT
			IFNULL( loc_code, "" ) 
		FROM
			inventory_log 
		WHERE
			inventory_type = 'delivery' 
			AND order_type = 'picking' 
			AND lot_code = lotcode 
			AND material_num = materialnum 
			AND order_num = ( SELECT preparation_order FROM preparation_master WHERE schedule_code = schedulecode AND preparation_type = '0' LIMIT 1 ) 
			LIMIT 1 
		);
	RETURN ( @num1 );
END