MySql编写自定义函数、存储过程语法（附详细案例，已解决问题：运行自定义函数报错，语法limit @变量名报错）

问题场景

尝试编写mysql函数对记录中的数据排序完后，根据需求取第几条数据时，sql中的limit @变量,1 语法一直不通过编译。
报错信息如下：

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@N,1

问题原因

limit n,m语句语法要求是，后面的两个参数必须为整数常量，不可以为变量。所以在函数中select * from a order by xx limit @变量,1就会函数编译不通过。

解决方法

1、使用整数常量

首先根据函数参数接收一个动态的整数参数，如果整数参数不需要运算那直接放在limit后面。
例如：当select test(1)；时就会返回select xxx limit 1,1的数据

CREATE FUNCTION test(N INT) RETURNS double
BEGIN
  RETURN select distinct gz from gzb order by gz desc limit N,1;
END

如果函数参数需要参与运算后才能得出实际值，也是使用常量，因为limit (n-1),1也是会编译不通过的。因为无论是2-1或者n-1都是变量。所以我们得把值提前处理变成常量。
例如：

CREATE FUNCTION test(N INT) RETURNS double
BEGIN
  set cl = N-1
  RETURN select distinct gz from gzb order by gz desc limit cl,1;
END

这样就不会报错，因为cl的值是已经通过n-1得到了才执行sql，而limit (n-1),1或者set @cl = n-1;limit @cl,1都是等到sql执行后才计算值，所以就会编译不通过。

2、编写存储过程+预编译

在mysql-5.0.7以后的版本，更新了可以预处理sql语句，在处理的时候再放入变量执行。

CREATE PROCEDURE test(in n INT)
BEGIN
  set @mmsql = 'select distinct gz from gzb order by gz desc limit ?,1';
  set @cl = n-1;
  prepare exce from @mmsql;
  EXECUTE exce USING @cl;
  DEALLOCATE prepare exce;  //删除预编译语句
END

或者直接使用select into进行变量赋值，根据b表进行循环查或插入操作

drop procedure if exists test;
CREATE PROCEDURE test(in n INT)
BEGIN
  declare i int;
  set i = 0;
  select count(1) into @j from b;
  while i < @j do
  //查询或插入语句
  end while
END

提示

声明变量的两种类型和范围

-- 局部变量
--Declare 变量名 类型 default 默认值;
declare N int defaul 0;  -- 声明变量名为N的整型变量，默认值为0

-- 全局变量
-- set @变量名 = 值
set @N = 'xxx';     -- 声明全局变量@N，值为字符串xxx

存储过程语法

调用存储过程： call 存储名称(传参);
查看所有存储过程：show procedure status;
查看存储过程创建语句：show create procedure 存储名称;
删除存储过程：drop procedure 存储名称;

create procedure 名称(in|out|inout 参数名 参数类型) -- 多个参数使用逗号分隔
begin
 -- sql语句;
end

-- in 代表参数的值从用户输入得到(默认为 in)
-- out 代表参数的值会向用户输出，但是存储过程中，传入该参数的值为NULL
-- inout 既可以接收参数，也可以输出参数

-- 例如：
set @a = 1;
set @b = 2;
set @c = 3;
select @a,@b,@c;  -- 1,2,3

create procedure demo(a int, out b int, inout c int)
begin
 select a,b,c;   -- 1,NULL,3
 set a = 10;
 set b = 10;
 set c = 10;
 select a,b,c;   -- 10,10,10
end

call demo(@a,@b,@c);

select @a,@b,@c;  -- 1,10,10

执行完后，很明显@b，@c都被改变了

函数语法

调用函数： select 函数名称(传参);
查看所有自定义函数：show function status;
查看函数创建语句：show create function 函数名称;
删除函数：drop function 函数名称;

CREATE FUNCTION 函数名称(参数名 参数类型) RETURNS 返回值类型
begin
  -- xxxx
end

-- 例如：输出1+2+...+n的结果
create function Accumulate(n int) returns int
begin
  declare i int default 1;
  declare sum int default 0;
  while i <= n do
    set sum = sum + i;
    set i = i + 1;
  end while;
  return sum;
end
-- 或者使用loop和leave进行循环和跳出循环实现累加
create function Accumulate(n int) RETURNS int
begin
  declare i int default 1;
  declare sum int default 0;
  add_num:LOOP
		set sum = sum + i;
    set i = i + 1;
    IF i = n + 1 THEN LEAVE add_num;
		END IF;
  END LOOP add_num;
  return sum;
end
-- 调用函数
select Accumulate(5);  -- 15 = 1 + 2 + 3 + 4 + 5