MySQL 自定义函数-马育民老师

# 创建自定义函数

### 语法

```
CREATE FUNCTION 函数名([参数名 参数类型,...])
RETURNS 返回值类型
RETURN 返回值;
```

**注意：** `RETURN` 后面必须有 `;`

### 例子：定义加法函数

```
create  function fun_add2( a  decimal(7,2) ,b decimal(7,2))

returns decimal(7,2)
return a + b;
```

# 删除自定义函数

```
DROP FUNCTION 函数名;
```

# 列出所有自定义函数

```
SHOW FUNCTION status;
```

# 条件语句

```
IF search_condition THEN statement_list 
[ELSEIF search_condition THEN statement_list] ... 
[ELSE statement_list] 
END IF;
```

**注意：** `END IF;` 后面必须有 `;`

### 例子：定义绝对值函数

```
create function fun_abs( x DECIMAL(7,2) )
-- 定义返回值类型
RETURNS DECIMAL(7,2)
-- 结构体
BEGIN
	IF x < 0 THEN
		return -x;
	ELSE
		return x;
	END IF;
END
```

测试：

```
select 1,-2,fun_abs(1),fun_abs(-2) from emp2
```

### 例子2

编写一个函数，实现下面功能：

-- 工资超过6000，显示高
-- 工资在 3000-6000，显示中
-- 其他工资显示低

```
CREATE FUNCTION fun_pan_gongzi2( x DECIMAL(10,2) )

RETURNS VARCHAR(5)
IF x >= 6000 then return '高';
ELSEIF x>= 3000 then return '中';
else return '低';
END if;
```

测试：
```
select ename,gongzi,fun_pan_gongzi2(gongzi) from emp2
```

# 声明变量和赋值

搭配语句使用

```
declare  #声明变量
```

```
SET parameter_name = value[,parameter_name = value...];  #为变量赋值
SET @param_name = value  #全局变量
```

**注意:**

- `SET` 末尾必须有 `;`
- 以DECLARE声明的变量都会被初始化为 NULL。
- 会话变量（即 `@` 开头的变量）则不会被再初始化，在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量。

# 结构函数体

如果需要执行 **多行代码** 时，那么需要使用复合结构

```
CREATE FUNCTION 函数名([参数名 参数类型,...])
RETURNS 返回值类型
BEGIN #开始
     代码.....
RETURN 返回值;
END #结束
```

### 例子：定义绝对值函数

```
create function fun_abs( x DECIMAL(7,2) )
-- 定义返回值类型
RETURNS DECIMAL(7,2)
-- 结构体
BEGIN
	-- 定义返回值
	DECLARE res DECIMAL(7,2);
	IF x < 0 THEN
		SET res = -x; -- 赋值
	ELSE
		set res = x; -- 赋值
	END IF;
	return res;
END
```

测试：

```
select 1,-2,fun_abs(1),fun_abs(-2) from emp2
```
### 例子2

编写一个函数，实现下面功能：

-- 工资超过6000，显示高
-- 工资在 3000-6000，显示中
-- 其他工资显示低

要求：定义局部变量，在 if 分支中赋值，最后在if语句之后在返回

##### 错误写法

**原因：**函数中要执行多行语句：

- 声明变量
- if语句
- return语句

需要 `begin end`

```
CREATE FUNCTION fun_pan_gongzi3( x DECIMAL(10,2) )
RETURNS VARCHAR(5)
DECLARE res VARCHAR(5);
IF x >= 6000 then set res = '高';
ELSEIF x>= 3000 then set res = '中';
else set res = '低';
END if;
RETURN res;
```

##### 正确写法

```
CREATE FUNCTION fun_pan_gongzi3( x DECIMAL(10,2) )
RETURNS VARCHAR(5)
BEGIN
	DECLARE res VARCHAR(5);
	IF x >= 6000 then set res = '高';
	ELSEIF x>= 3000 then set res = '中';
	else set res = '低';
	END if;
	RETURN res;
END
```

测试：

```
select ename,gongzi,fun_pan_gongzi2(gongzi) from emp2
```

# case when 语句

### 语法

```
CASE case_value 
WHEN when_value THEN statement_list 
[WHEN when_value THEN statement_list] ... 
[ELSE statement_list] 
END CASE ;
```

### 简单写法

```
CASE  
WHEN when_value THEN statement_list 
[WHEN when_value THEN statement_list] ... 
[ELSE statement_list] 
END CASE ;
```

### 例子

编写一个函数，实现下面功能：

-- 工资超过6000，显示高
-- 工资在 3000-6000，显示中
-- 其他工资显示低

要求：定义局部变量，在 if 分支中赋值，最后在if语句之后在返回

```
CREATE FUNCTION fun_pan_gongzi4( x DECIMAL(10,2) )
RETURNS VARCHAR(5)
BEGIN
	DECLARE res VARCHAR(5);
	case
  when x >= 6000 then set res = '高';
	when x>= 3000 then set res = '中';
	else set res = '低';
	END case;
	RETURN res;
END
```

测试：
```
select ename,gongzi,fun_pan_gongzi3(gongzi) from emp2
```

# WHILE 循环语句

WHILE语句也是有条件控制的循环语句。WHILE语句是当满足条件时，执行循环内的语句。

### 语法

```
[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label];
```

### 例子

定义函数，传入100时，就执行 `1 + 2 + 3 + ... + 100`

```
CREATE FUNCTION fun_leijia( x int)
RETURNS INT
BEGIN
	DECLARE i,total int;
	set i = 0,total = 0;
	while i<= x do
		set total = total + i;
		set i = i + 1;
	end while;
	return total;
END
```
测试
```
select fun_leijia(100) from emp2
```

# LOOP循环语句

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环,但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。

### 语法
```
[begin_label:] LOOP 
statement_list  
END LOOP [end_label];
```

### LEAVE语句

`LEAVE` 语句是 **跳出整个循环**，然后执行循环后面的程序。相当于 `break`

### ITERATE

`ITERATE` 语句是 **跳出本次循环**，然后直接进入下一次循环。相当于 `continue`

### 例子

定义函数，传入100时，就执行 `1 + 2 + 3 + ... + 100`

```
CREATE FUNCTION fun_leijia2( x int)
RETURNS INT
BEGIN
	DECLARE i,total int;
	set i = 0,total = 0;
	st:loop
		if i > x then LEAVE st;
		end if;
		set total = total + i;
		set i = i + 1; 
	end loop st;
	return total;
END
```

测试：

```
select fun_leijia2(100)
```

原文出处：http://malaoshi.top/show_1GW11l7LQju0.html