Mysql 基础03-存储过程-函数【2021-10-21】

1、存储过程

1.1 变量

1、系统变量

变量由系统提供。

全局变量：

会话变量：针对一次连接

使用的语法：

• 查看系统变量

SHOW VARIABLES;

查看全局变量

SHOW GLOBAL VARIABLES;

查看会话变量【不写session默认就是会话变量】

SHOW SESSION VARIABLES;

• 查看满足条件的部分变量 使用like

SHOW GLOBAL VARIABLES LIKE '%char%';

• 查看指定的某个系统变量的值

# SELECT @@系统变量名
SELECT @@character_set_system
SELECT @@global.character_set_system

• 为某个系统变量赋值

# SET GLOBAL 系统变量名=值;
# SET @@global.系统变量名 = 值;

如果是全局变量，必须加 GLOBAL 关键字，如果不加GLOBAL 默认是 session

全局变量对所有连接有效，但是不能跨重启，想要重启依然生效需要修改配置文件。

SET @m = 1;
SET @n = 2;
SET @sum = @n + @m;
SELECT @sum;

2、自定义变量

用户自定义的，不是由系统提供的。作用域是当前会话有效

用户变量

• 声明并初始化

SET @用户变量名=值;
# 方式2
SET @用户变量名:=值;
# 方式3
SELECT @用户变量名:=值;

• 赋值

SET @用户变量名=值;
# 方式2
SET @用户变量名:=值;
# 方式3
SELECT @用户变量名:=值;

#方式4
SELECT 字段 INTO @变量名 FROM 表;

SET @count=100;
SELECT COUNT(*) INTO @count FROM employees;

• 查看

SELECT @用户变量名;
SELECT @count;

用户变量可以放在任意地方

局部变量：仅仅在定义它的begin end 中有效，应用在begin end 中的第一句话

声明：

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

赋值：

SET 局部变量名=值;
# 方式2
SET 局部变量名:=值;
# 方式3
SELECT @局部变量名:=值;

#方式4
SELECT 字段 INTO 变量名 FROM 表;

查看：

SELECT 局部变量名;

1.2 存储过程和函数

存储过程和函数类似于 java 中的方法

存储过程：一组预先编译好的sql语句的集合。

使用存储过程，能够减少编译次数（存储过程之后被编译一次），简化操作，减少了和数据库的连接次数，提供了效率。

1.2.1 创建

语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体
END

参数列表包含三部分：参数模式、参数名、参数类型

例如：

IN stuname VARCHAR(20)

1、参数模式有3个：

IN、 进口，该参数可以作为输入，需要调用方传来值

OUT、出口，该参数可以作为输出，即作为返回值

INOUT 进出口，该参数即可以作为输入、又可以作为输出，调用方即需要传递值过来，也会返回给调用方。

2、begin end 就相当于是一对大括号，当存储过程体只有一句话时， 可以省略不写。

3、存储过程体中的每条sql语句结尾都必须加上分号，而存储过程本身的结尾可以使用 delimiter 重新设置。语法

delimiter 结束标记

delimiter $

1.2.2 调用、删除、查看

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

1、创建无参的存储过程

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,PASSWORD)
	VALUES('john1','0000'),('lily','0000'),
	('rose','0000'),('jack','0000'),
	('tom','0000');
END $

CALL myp1();

1、创建带IN的存储过程

创建存储过程 实现 根据女性信息，查询对应的男性信息

# 创建 IN类型参数的 存储过程
CREATE PROCEDURE myp2(IN beantyName VARCHAR(20))
BEGIN
	SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beantyName;
END;
# 调用
CALL myp2('热巴');

创建存储过程实现，用户是否登录成功

CREATE PROCEDURE myp3(IN username VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
	DECLARE result VARCHAR(20) DEFAULT ''; # 局部变量声明并初始化
	SELECT COUNT(*) INTO result #赋值
	FROM admin WHERE admin.username = username
	AND admin.`password` = `password`;
	
	SELECT result; # 查询变量值
END;

# 调用
CALL myp3('john1', '10000');

加上 IF 看看

CREATE PROCEDURE myp4(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, '成功', '失败') AS 结果; # 使用
END;

CALL myp4('john1', '10000');

3、创建带 OUT 的存储过程

# 创建存储过程  实现 根据女性信息，查询对应的男性名
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END;

# 调用
CALL myp5('热巴', @boyName);
SELECT @boyName;

多个 OUT

# 创建存储过程  实现 根据女性信息，查询对应的男性名、魅力值
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
	SELECT bo.boyName, bo.userCP INTO boyName, userCP
	FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END;

CALL myp6('热巴', @boyName, @userCP);
SELECT @boyName, @userCP;

4、 带 INOUT 的存储过程

# 传入 a和b，最终a和b都翻倍

CREATE PROCEDURE myp7(INOUT a INT, INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END

# 调用
SET @a = 3;
SET @b = 5;

CALL myp7(@a, @b);
SELECT @a,@b;

如何删除存储过程呢？

DROP PROCEDURE 存储过程名;
# 例子
SHOW CREATE PROCEDURE myp1;

删除存储过程只能一次删除一个。

查看存储过程的信息

SHOW CREATE PROCEDURE 存储过程名;
# 例子
SHOW CREATE PROCEDURE myp2;

存储过程内部的逻辑语句是不能修改的，需要修改可以删除重新写一个。

# 创建存储过程实现传入一个日期，格式化输出。
CREATE PROCEDURE dateformatefunc(IN date DATETIME, OUT dateStr VARCHAR(20))
BEGIN
	SELECT DATE_FORMAT(date, '%y-%m-%d') INTO dateStr;
END;

CALL dateformatefunc(NOW(), @str);
SELECT @str;

2、函数

函数与存储过程的区别：

函数可以有0个返回，也可以有多个返回。

函数：有且只有1个返回值。

存储过程适合批量插入、批量更新

函数适合处理数据后返回一个结果。

2.1 函数的创建

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

参数列表包含2部分：参数名，参数类型。没有IN、OUT、INOUT 这些了

函数体一定有return语句，如果没有会报错。

如果 return 不是放在函数体最后，不会报错，但是不建议。

当 函数体只有一句话时，可以省略 begin end

使用 delimiter 设置结束标记。

函数的调用 使用 SELECT

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、有参

案例

# 根据员工名返回工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;
	SELECT salary INTO c FROM employees WHERE employees.last_name = empName;
	RETURN c;
END;

SELECT myf2('Ernst');

案例

# 根据部门名返回平均工资
CREATE FUNCTION myf3(depName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE c DOUBLE DEFAULT 0;
	SELECT AVG(salary) INTO c FROM employees e JOIN departments d 
	ON e.department_id = d.department_id WHERE d.department_name=depName;
	RETURN c;
END;

SELECT myf3('adm');

2.2 查看函数详情、删除

# SHOW CREATE FUNCTION 函数名;
SHOW CREATE FUNCTION myf3;
删除函数

# DROP FUNCTION 函数名;
DROP FUNCTION myf1;

2.3 函数的使用案例

# 创建函数，实现传入2个float 返回二者之和
CREATE FUNCTION testfunc1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE sum FLOAT DEFAULT 0;
	SET sum = num1 + num2;
	RETURN sum;
END;

SELECT testfunc1(1,4);

3、流程控制结构

顺序结构没啥说的。

3.1 分支结构

1、if 函数

SELECT IF(表达式1, 表达式2, 表达式3)
执行顺序：如果 表达式1 成立，返回表达式2 的值，否则返回表达式3的值

这个可以放在任意地方使用，包括上面的存储过程和函数。

2、case 结构

情况1：类似于switch 一般用于等值判断

CASE 变量|表达式|字段

WHEN 要判断的值 THEN 返回的值1或者语句1；

WHEN 要判断的值 THEN 返回的值2或者语句2；

WHEN 要判断的值 THEN 返回的值3或者语句3；

else 要返回的值n或者语句n；

END Case；

情况2：多重IF，实现区间判断

CASE

WHEN 要判断的条件1 THEN 返回的值1或者语句1；

WHEN 要判断的条件2 THEN 返回的值2或者语句2；

WHEN 要判断的条件3 THEN 返回的值3或者语句3；

else 要返回的值n或者语句n；

END Case；

特点：

• 可以作为表达式，嵌套在其他语句中，可以放在任何地方；也可以作为单独的语句，但是只能放在BEGIN END 中。

• 执行时，WHEN中的值或者条件成立，则执行对应的THEN后面的语句，并结束case

如果都不满足，则执行else。

• else 可以省略，如果else省略且所有when条件都不满足，则返回null

案例：创建存储过程，根据传入的成绩，显示等级

CREATE PROCEDURE test_case(IN score INT, OUT class CHAR(1))
BEGIN
	CASE 
		WHEN score BETWEEN 0 and 59 THEN SELECT 'D' INTO class;
		WHEN score BETWEEN 60 and 69 THEN SELECT 'C' INTO class;
		WHEN score BETWEEN 70 and 89 THEN SELECT 'B' INTO class;
		WHEN score BETWEEN 90 and 95 THEN SELECT 'A' INTO class;
		ELSE
			SELECT 'S' INTO class;
	END CASE;
END;

CALL test_case(85, @class);
SELECT @class;

3、IF 结构

if结构与if函数不同，功能就是实现多重分支

语法：

IF 条件1 then 语句1；

elseif 条件2 then 语句2；

elseif 条件3 then 语句3；

...

else 语句n;

end if；

else 也是可以省略的。

注意, if 结构只能应用在 begin end 中。

案例：创建函数，根据传入的成绩，显示等级

CREATE FUNCTION test_if(score INT) RETURNS VARCHAR(20)
BEGIN
		IF score BETWEEN 0 and 59 THEN RETURN 'D' ;
		ELSEIF score BETWEEN 60 and 69 THEN RETURN 'C' ;
		ELSEIF score BETWEEN 70 and 89 THEN RETURN 'B' ;
		ELSEIF score BETWEEN 90 and 95 THEN RETURN 'A';
		ELSE
			RETURN'S';
	END IF;
END;

SELECT test_if(85);

3.2 循环结构

循环结构必须放在BEGIN END 之间

分类 while、loop、repeat

循环控制：iterate 类似于continue、leave 类似于break

while 语法：先判断后执行

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

如果需要搭配循环控制，就需要写上标签。

loop 语法：没有条件的死循环

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

可以用来模拟死循环

repeat 语法：先执行后判断

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

案例：

DROP PROCEDURE IF EXISTS pro_while;
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
  DECLARE i INT DEFAULT 0;
	a:WHILE i<insertCount 
	DO
		INSERT INTO admin(username, password) VALUES(concat('rose', i), '666');
		SET i = i + 1;
	END WHILE a;
END;

CALL pro_while(10);

添加循环控制的案例

DROP PROCEDURE IF EXISTS pro_while;
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
  DECLARE i INT DEFAULT 0;
	
	a:WHILE i<insertCount DO
		INSERT INTO admin(username, password) VALUES(concat('haha', i), '000');
		IF i > 5 THEN LEAVE a;
		END IF;
		SET i = i + 1;
	END WHILE a;
	
END;

CALL pro_while(10);

DROP PROCEDURE IF EXISTS pro_while;
CREATE PROCEDURE pro_while(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;  # mod 函数是取余
		END IF;
		INSERT INTO admin(username, password) VALUES(concat('iter', i), '111');
	END WHILE a;
	
END;

CALL pro_while(10);