MySQL高级：函数（二）自定义函数

自定义函数

除了使用系统为我们提供的函数以外，我们也可以自定义函数，并使用我们自定义的函数进行数据处理，唯一比较麻烦的就是函数定义后不能修改，只能删了重新写。

基本语法

MySQL的函数与Java中的方法类似，也可以添加参数和返回值，可以通过CREATE FUNCTION创建函数：

CREATE FUNCTION test() RETURNS INT
BEGIN
RETURN 666;
END

定义函数的格式为：

• create function 函数名称([参数列表]) returns 返回值类型

• begin 和 end 之间写函数的其他逻辑，begin和end就相当于Java中的花括号{ ... }

• return后紧跟返回的结果

添加参数也很简单，我们只需要在函数名称括号中添加即可，注意类型需要写在参数名称后面：

CREATE FUNCTION test(i INT) RETURNS INT
BEGIN
RETURN i * i;
END

定义局部变量的格式为：

• declare 变量名称 变量类型 [, ...]

• declare 变量名称 变量类型 default 默认值

为变量赋值的格式为：

• set 变量名称 = 值

我们还可以在函数内部使用select语句，它可以直接从表中读取数据，并可以结合into关键字将查询结果赋值给变量：

BEGIN
DECLARE a INT;
-- select into from 语句
SELECT COUNT(*) INTO a FROM student;
RETURN a;
END

流程控制

接着我们来看一下如何使用流程控制语句，其中最关键的就是IF判断语句：

BEGIN
DECLARE a INT DEFAULT 10;
IF a > 10 THEN
	RETURN 1;
ELSE
	RETURN 2;
END IF;
END

IF分支语句的格式为：

• if 判断条件 then  ... else if 判断条件 then .... else ... end if;

我们可以结合exists关键字来判断是否为NULL：

BEGIN
DECLARE a INT DEFAULT 0;
-- IF EXISTS(SELECT * FROM student WHERE sid = 100) THEN
IF NOT EXISTS(SELECT * FROM student WHERE sid = 100) THEN
	SET a = 10;
END IF;
RETURN a;
END

我们也可以在函数中使用switch语句：

BEGIN
DECLARE a INT DEFAULT 10;
CASE a
	WHEN 10 THEN
		RETURN 2;
	ELSE
		RETURN 1;
END CASE;
END

SWITCH分支语句的格式为：

• case 变量 when 具体值或是布尔表达式 then ... when * then ... else ... end case;

与Java不同的是，它支持使用布尔表达式：

BEGIN
DECLARE a INT DEFAULT 10;
CASE
	WHEN 1 < 5 THEN
		SET a = 5;
	ELSE
		SET a = 10;
END CASE;
RETURN a;
END

我们以类似于elseif的形式进行判断，其实和上面直接使用是一样的。

我们接着来看循环语句，MySQL提供了三种循环语句，其中第一种是WHILE语句：

BEGIN
DECLARE a INT DEFAULT 10;
WHILE a < 11 DO
	SET a = a + 1;
END WHILE;
RETURN a;
END

格式为：

• while 循环条件 do ...  end while;

我们接着来看第二种循环语句，LOOP循环：

BEGIN
DECLARE a INT DEFAULT 10;
lp1: LOOP
  SET a = a - 1;
	IF a = 0 THEN
		LEAVE lp1; 
	END IF; 
END LOOP lp1;
RETURN a;
END

相比while语句，我们可以使用LEAVE精准控制结束哪个循环，有点类似于goto语句：

BEGIN
DECLARE a INT DEFAULT 0;
lp1: LOOP
  lp2: LOOP
	SET a = a + 1;
	IF a > 5 THEN
		LEAVE lp1; 
	END IF; 
	END LOOP lp2;
END LOOP lp1;
RETURN a;
END

类似于Java中的goto写法（在JavaSE阶段已经讲解过）：

public static void main(String[] args) {
    int a = 0;
    lp1: while (true){
        lp2: while (true){
            a++;
            if(a > 5) break lp1;
        }
    }
    System.out.println(a);
}

它的语法格式如下：

• 循环名称 loop ...(可以插入leave语句结束) end loop 循环名称;

接着我们来看最后一种循环语句，repeat语句：

BEGIN
DECLARE a INT DEFAULT 0;
REPEAT
	SET a = a + 1;
UNTIL a > 0 END REPEAT;
RETURN a;
END

它类似于Java中的do-while循环语句，它会先去执行里面的内容，再进行判断，格式为：

• repeat ... until 结束循环条件 end repeat;

全局变量

某些情况下，我们可以直接在一次会话中直接定义变量并使用，这时它并不是位于函数内的，这就是全局变量，它无需预先定义，直接使用即可：

set @x = 10;

我们可以将全局变量作为参数传递给函数：

select test(@x);

除了我们自己定义的全部变量以外，系统默认也有很多的变量，因此我们自己定义的变量称为用户变量，系统默认变量称为系统变量。查看系统变量的命令为：

show GLOBAL VARIABLES