【MySQL数据库】第五章（下）：存储过程和函数、流程控制结构

#一、创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体(一组合法的SQL语句)
END

/*注意：
1、参数列表包含三部分
参数模式  参数名  参数类型 */
# 举例：
IN stuname VARCHAR(20)

# 参数模式：
IN：该参数可以作为输入，需调用方传入值
OUT：该参数可以作为输出，可以用作返回值
INOUT：可作为输入又可作为输出，该参数既需要传入值，又可以返回值

2、如果存储过程体仅一句话，BEGIN END 可以省略；
存储过程体中的每条SQL语句的结尾要求必须加分号；
存过程的结尾可以使用DELIMITER 重新设置 
语法：
DELIMITER 结束标记
# 如：
DELIMITER $



#二、调用语法
CALL 存储过程名(实参列表);

案例：

#1.空参列表
#案例：插入到admin表中五条记录
SELECT * FROM admin;
DELIMITER $

CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username, `password`)
	VALUES('john1', '0000'),
	('lily', '0000'),('rose', '0000'),('jack', '0000'),('tom', '0000');
END $
#调用
CALL myp1()$


#2.创建带in模式参数的存储过程
#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b
	ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
#调用
CALL myp2('小昭')$

#案例2：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0; #声明并初始化
	
	SELECT COUNT(*) INTO result #赋值
	FROM admin
	WHERE admin.username = username
	AND admin.`password` = PASSWORD;

	SELECT IF(result>0, '成功', '失败') 登录状态; #使用
END $
#调用
CALL myp3('张飞', '8888')$


#3.创建带out模式的存储过程
#案例1：根据女神名，返回对应的男神名
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
#调用
CALL myp4('小昭',@bName)$
SELECT @bName$


#案例2：根据女神名，返回对应的男神名和男神魅力值
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
	SELECT bo.boyName, bo.userCP INTO boyName, userCP
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $
#调用
CALL myp5('小昭', @bName, @userCP)$


#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b翻倍并返回
CREATE PROCEDURE myp6(INOUT a INT, INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $
#调用
SET @m=10, @n=20$  #定义两个变量用于输入值 + 存储返回值
CALL myp6(@m, @n)$
SELECT @m, @n$

删除和查看存储过程

#二、删除存储过程(做不到删除多个)
语法：DROP PROCEDURE 存储过程名
DROP PROCEDURE myp1$

#三、查看存储过程的信息
DESC myp3$ #不行
SHOW CREATE PROCEDURE myp3$

5.3.2 函数

含义：一组预先编译号的SQL语句的集合（批处理语句）

好处：与存储过程相同

区别：

存储过程：可以有0个返回，或多个返回(适合批量插入、更新)
函数：有且仅有1个返回(适合做处理数据后返回一个结果)

#一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END $


# 注意：
1.参数列表，包含两部分：参数名、参数类型

2.函数体：肯定会有return语句，如果没有会报错
若return语句没有放在函数体的最后也不报错，但不建议

3.函数体只有一句话时，begin end可以省略
4.使用delimiter语句设置结束标记


#二、调用语法
SELECT 函数名(参数列表)

案例：

#1.无参有返回
#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0; #定义变量
	SELECT COUNT(*) INTO c #赋值
	FROM employees;
	RETURN c;
END $

#调用
SELECT myf1()$


#2.有参数有返回
#案例1：根据员工名，返回工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0; #定义用户变量
	SELECT salary INTO @sal #赋值
	FROM employees
	WHERE last_name = empName;
	
	RETURN @sal;
END $

#调用
SELECT myf2('Kochhar')$


#案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE;
	SELECT AVG(salary) INTO sal
	FROM employees e
	INNER JOIN departments d
	ON d.department_id = e.department_id
	WHERE d.department_name = deptName;
	
	RETURN sal;
END $

#调用
SELECT myf3('IT')$


#三、查看函数
SHOW CREATE FUNCTION myf3;

#四、删除函数
DROP FUNCTION myf3;


#案例：创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM = num1 + num2;
	RETURN SUM;
END $

SELECT test_fun1(1.3, 2.5)$

5.4 流程控制结构

顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码

5.4.1 分支结构

1.if函数

功能：实现简单的双分支
语法：if(表达式1, 表达式2, 表达式3)；表达式1为true,返回表达式2，否则返回表达式3的值
应用：任何地方

2.case结构

情况1：类似C++的Switch case，一般用于实现等值判断

语法：

	CASE 变量|表达式|字段
	WHEN 要判断的值 THEN 返回的值1或语句1;
	WHEN 要判断的值 THEN 返回的值2或语句2;
	...
	ELSE 要返回的值n或语句n;
	END CASE;

情况2：类似if、if else、if，一般用于实现区间判断

语法：

	CASE 
	WHEN 要判断的条件1 THEN 返回的值1或语句1;
	WHEN 要判断的条件2 THEN 返回的值2或语句2;
	...
	ELSE 要返回的值n或语句n;
	END CASE;

特点：

可以作为表达式，嵌套在其他语句中使用:
可以放在任何地方，begin end中或外面
可以作为独立的语句去使用，只能放在begin end中
如果when中的值满足或条件成立，则执行对应的then后面的语句，并且结束case；如果都不满足，则执行·中的语句
else可以省略；若else省略，并且所有when都不满足，则返回null

#案例：创建存储过程，根据传入的成绩，显示等级：90-100(A),80-90(B),60-80(C),其余D
CREATE PROCEDURE test_case1(IN score INT)
BEGIN
	CASE 
	WHEN score>=90 THEN SELECT 'A';
	WHEN score>=80 THEN SELECT 'B';
	WHEN score>=60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE; 
END $

CALL test_case1(95)$

3.if结构

功能：实现多重分支
语法：

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n;】
end if;

应用场合：只能用在begin end 中

#案例1：创建存储过程，根据传入的成绩，返回级别：90-100(A),80-90(B),60-80(c),其余D
CREATE FUNCTION test_if1(score INT) RETURNS CHAR
BEGIN
	IF score>=90 THEN RETURN 'A';
	ELSEIF score>=80 THEN RETURN 'B';
	ELSEIF score>=60 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF;
END $

SELECT test_if1(86);

5.4.2 循环结构

分类：while、loop、repeat
循环控制：
- iterate 类似于continue(结束) 结束本次循环，继续下一次
- leave 类似于break(跳出) 结束当前所在的循环

1.**while**语法：

【标签:】while 循环条件 do
	循环体;
end while 【标签】;

类似while，先判断再执行

2.**loop**语法：

【标签:】loop
	循环体;
end loop 【标签】;

可以用来模拟简单的死循环

3.**repeat**语法：

【标签:】repeat
	循环体;
until 结束循环的条件
end repeat【标签】;

类似do..while，先执行一次再判断

案例：

#1.无添加循环控制语句
#案例1：批量插入，根据次数插入到admin表中多条记录
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username, `password`)
		VALUES(CONCAT('Rose',i), '666');
		SET i = i+1;
	END WHILE;
END $

CALL pro_while1(100)$


#2.添加leave语句
#案例：批量插入，根据次数插入到admin表中多条记录,如果次数>20则停止
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i <= insertCount DO
		INSERT INTO admin(username,`password`)
		VALUES(CONCAT('xiaohua',i), '0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $

CALL pro_while1(100)$


#3.添加iterate语句
#案例：批量插入，根据次数插入到admin表中多条记录,如果次数>20则停止
CREATE PROCEDURE pro_While1(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 admin(username,`password`)
		VALUES(CONCAT('xiaohua',i),'0000');
	END WHILE a;
END $

CALL pro_while1(100)$