数据库——MySQL存储过程、函数和流程控制结构

一、存储过程

1. 定义

• 一组经过预先编译的sql语句的集合，可理解为批处理语句

2. 好处

• 提高了sql语句的重用性
• 简化操作，减少了开发程序员的压力
• 减少了编译次数和数据库服务器的连接次数，提高效率

3. 创建语法

每条sql语句结尾必须要加分号

create procedure 存储过程名(in|out|inout 参数名 参数类型)
begin
	存储过程体;
end

/* 
1.参数列表(in|out|inout 参数名 参数类型)
1.1 参数模式
in:该参数只能作为输入（该参数不能做返回值），默认是in
out：该参数只能作为输出（该参数只能做返回值）
inout：既能做输入又能做输出
1.2 参数名
1.3 参数类型

2.存储过程体
2.1 存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略begin end 
2.2 存储过程体中每条sql语句结尾必须要加分号
2.3 存储过程体的结尾可以使用delimiter设置

3.示例：
delimiter $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
BEGIN
	sql语句1;
	sql语句2;
END $
*/

类似于

修饰符 返回类型 方法名(参数类型 参数名,...)
{
	方法体;
}

4. 调用语法

call 存储过程名(实参列表);

5. 示例

5.1 空参的存储过程

案例：插入数据

mysql> select * from stu;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

mysql> DELIMITER $
mysql> CREATE PROCEDURE pro1()
    -> BEGIN
    -> INSERT INTO stu(NAME)
    -> VALUES('d'),('e'),('f'),('g');
    -> END $
Query OK, 0 rows affected (0.27 sec)

mysql> call pro1();
    -> $
Query OK, 4 rows affected (0.15 sec)

mysql> select * from stu;
    -> $
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
|  6 | f    |
|  7 | g    |
+----+------+
7 rows in set (0.00 sec)

5.2 带in模式参数的存储过程

案例：创建存储过程，根据部门名称查询包含的员工信息

DROP TABLE IF EXISTS emp;
CREATE TABLE emp
(
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(50),
	did INT
);
INSERT INTO emp(ename,did) VALUES ('a',1),('b',2),('c',NULL);
SELECT * FROM emp;
+----+-------+------+
| id | ename | did  |
+----+-------+------+
|  1 | a     |    1 |
|  2 | b     |    2 |
|  3 | c     | NULL |
+----+-------+------+

DROP TABLE IF EXISTS dept;
CREATE TABLE dept
(
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	dname VARCHAR(50)
);
INSERT INTO dept(dname) VALUES ('dep1'),('dep2'),('dep3');
SELECT * FROM dept;
+----+-------+
| id | dname |
+----+-------+
|  1 | dep1  |
|  2 | dep2  |
|  3 | dep3  |
+----+-------+

CREATE PROCEDURE pro2(IN dname VARCHAR(50))
BEGIN
	SELECT e.id,e.ename,e.did,d.dname 
	FROM dept d
	RIGHT JOIN emp e
	ON e.did=d.id
	WHERE d.dname=dname;
END $
call pro2('dep2');$
+----+-------+------+-------+
| id | ename | did  | dname |
+----+-------+------+-------+
|  2 | b     |    2 | dep2  |
+----+-------+------+-------+

案例：创建存储过程，查询有部门信息的员工个数

# 数据字段同上
CREATE PROCEDURE pro3(IN did INT)
BEGIN
	DECLARE result INT DEFAULT 0;
	
	SELECT COUNT(*) 
	FROM emp e
	WHERE e.did=did;
	
	SELECT result;	
END $

CALL pro3('2')$

5.3 带out模式参数的存储过程

案例：根据员工姓名返回部门名称

CREATE PROCEDURE pro4(IN ename VARCHAR(20),OUT dname VARCHAR(20))
BEGIN
	SELECT d.dname INTO dname
	FROM dept d
	INNER JOIN emp e 
	ON e.did=d.id
	WHERE e.ename=ename;	
END $

CALL pro4('b',@name)$
SELECT @name$

5.4 带inout模式参数的存储过程

案例：将a员工姓名修改为姓名+部门名

+------+-------+------+----+-------+
| id   | ename | did  | id | dname |
+------+-------+------+----+-------+
|    1 | a     |    1 |  1 | dep1  |
|    2 | b     |    2 |  2 | dep2  |
| NULL | NULL  | NULL |  3 | dep3  |
+------+-------+------+----+-------+
CREATE PROCEDURE pro5(INOUT ename VARCHAR(20))
BEGIN
	SELECT CONCAT(d.dname,'-',e.ename) INTO ename
	FROM emp e
	RIGHT JOIN dept d 
	ON e.did=d.id
	WHERE e.ename=ename;	
END $
# 一定要新定义一个变量
set @x='b'$
call pro5(@x)$
select @x$
+--------+
| @x     |
+--------+
| dep2-b |
+--------+

6. 删除语法

drop PROCEDURE 存储过程名;

7. 查看存储结构的信息

show create PROCEDURE 存储过程名;

二、函数

1. 和存储过程的区别

区别	存储过程	函数
返回值个数	0个或多个	1个
适用范围	批量插入、更新	处理数据，拿到返回结果

2. 创建语法

CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
	函数体
END
# 参数列表：参数名，参数类型
# 函数体只有一句话，可以省略begin end
# 使用delimiter设置结束标志

3. 调用语法

SELECT 函数名(实参列表);

4. 示例

4.1 无参函数

set global log_bin_trust_function_creators=TRUE $
CREATE FUNCTION func1() RETURNS INT
BEGIN 
	DECLARE i INT DEFAULT 0; # 定义局部变量
	SELECT COUNT(*) INTO i FROM emp; # 赋值
	RETURN i;
END $
select func1() $
+---------+
| func1() |
+---------+
|       3 |
+---------+

4.2 有参函数

案例：根据员工姓名返回所属的部门名称

+----+-------+------+-------+
| id | ename | did  | dname |
+----+-------+------+-------+
|  1 | a     |    1 | dep1  |
|  2 | b     |    2 | dep2  |
|  3 | c     | NULL | NULL  |
+----+-------+------+-------+
CREATE FUNCTION func2(ename varchar(20)) RETURNS varchar(20)
BEGIN 
	set @dname = '';  # 也可以定义用户变量
	SELECT d.dname into @dname
	from emp e
	left join dept d
	ON e.did=d.id
	WHERE e.ename=ename;  # 赋值
	RETURN @dname;
END $
select func2('b') $
+------------+
| func2('b') |
+------------+
| dep2       |
+------------+

5. 查看函数

show create function 函数名;

6. 删除函数

drop function 函数名;

三、流程控制结构

1. 分支结构

1.1 if语句

• 特点：可以用在任何位置
• 语法

if(表达式1，表达式2，表达式3)

• 执行顺序

如果表达式1成立，则返回表达式2的值，否则返回表达式3的值

1.2 if…else if语句

• 特点：只能用在begin end中
• 语法

if 情况1 then 语句1;
elseif 情况2 then 语句2;
...
else 语句n;
end if;

1.3 case语句

• 特点
  • 如果作为表达式，嵌套在其他语句中使用，则可以放在任何地方
  • 如果作为独立的语句去使用，则只能放在begin end中
  • else可以省略，如果省略else且所有when条件都不满足，返回null
• 语法

# 情况一：类似于switch，一般用于实现等值判断
case 变量、表达式、字段
when 值1 then 结果1或语句1;(如果是语句，需要加分号) 
when 值2 then 结果2或语句2;(如果是语句，需要加分号)
...
else 结果n或语句n(如果是语句，需要加分号)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）

# 情况二：类似于多重if，一般用于实现区间判断
case 
when 条件1 then 结果1或语句1(如果是语句，需要加分号) 
when 条件2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n或语句n(如果是语句，需要加分号)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）

1.4 示例

案例1：创建存储过程，将输入的百分制成绩转换成字母制成绩

CREATE PROCEDURE test_case1(IN score INT)
BEGIN
	CASE
	WHEN score>=90 AND score <=100 THEN SELECT 'A';
	WHEN score>=80 THEN SELECT 'B';
	WHEN score>=70 THEN SELECT 'C';
	END CASE;# 在begin end中需要添加end case
END $
CALL test_case1(95) $

案例2：将数据表中的百分制成绩转换成字母制成绩

SELECT stu.`id`,stu.`name`,stu.`score`,
CASE
	WHEN score >=90 AND score <=100 THEN 'A'
	WHEN score >=80 AND score <=90 THEN 'B'
	WHEN score >=70 AND score <=80 THEN 'C'
	WHEN score >=60 AND score <=70 THEN 'D'
	ELSE 'E'
END AS scorelevel # 放在select中不需要 end case
FROM stu;
+----+------+-------+------------+
| id | name | score | scorelevel |
+----+------+-------+------------+
|  1 | a    |    95 | A          |
|  2 | b    |    80 | B          |
|  3 | c    |    86 | B          |
|  4 | d    |    72 | C          |
|  5 | e    |    99 | A          |
|  6 | f    |    83 | B          |
|  7 | g    |    65 | D          |
+----+------+-------+------------+

2. 循环结构

2.1 分类

• While

[标签:] while 循环条件 do
	循环体;
end while [标签];

• Loop：模拟死循环

[标签:] loop 
	循环体;
end loop [标签];

• Repeat：类似do while

[标签:] repeat
	循环体;
until 结束循环的条件
end while [标签];

2.2 循环控制

• iterate：结束本次循环，继续下一次
• leave：跳出，结束循环

2.3 示例

案例1：批量插入数据，没有添加循环控制语句

//java
int i=1;
while (i<=n){
	i++;
	insert...
}

# sql
create procedure pro_while1(in insertcount int)
begin
	declare i int default 1;
	while i<=insertcount do
		insert into emp(ename,did) values(concat('emp',i),1);
		set i=i+1;
	end while;
end $
call pro_while1(100) $

案例2：添加leave语句，批量插入数据，如果数据大于20就停止

涉及到循环控制的，必须加标签

//java
int i=0;
while (i<=n){
	insert...
	if(i>=20) break;
	i++;
}

create procedure pro_while2(in insertcount int)
begin
	declare i int default 1;
	a:while i<=insertcount do
		insert into emp(ename,did) values(concat('emp',i),1);
		if i>=20 then leave a;
		end if;
		set i=i+1;
	end while a;
end $
call pro_while2(100) $

案例3：添加iterate语句，批量插入数据，只插入偶数次

//java
int i=0;
while (i<=n){
	i++;
	if(i%2==0) continue;
	insert...
}

create procedure pro_while3(in insertcount int)
begin
	declare i int default 0;
	a:while i<=insertcount do
		set i=i+1;
		if mod(i,2)!=0  then iterate a;
		end if;
		insert into emp(ename,did) values(concat('emp',i),1);
	end while a;
end $
call pro_while3(100) $