李玉婷MYSQL进阶14-存储过程和函数

存储过程和函数：类似java中放入方法

好处：1.提高代码的重用性 2.简化操作

存储过程

含义：一组预先编译好的s q l语句的集合，理解成批处理语句

好处：1.提高代码的重用性 2.简化操作 3.减少了编译次数和数据库服务器连接次数，提高效率

一、创建语法

CREATE PROCEDURE 存储过程名（参数列表）

begin 

         存储过程体

end

注意：

1.参数列表包含三部分

参数模式。 参数名。  参数类型

举例：IN stuname VARCHAR（20）

参数模式：

IN：可以作为输入，也就是该参数需要调用方传入值

OUT：可以作为输出，也就是该参数可以作为返回值

INOUT：输入+输出，需要传入值又可以返回值

 2.如果存储过程体仅仅只有一句话，begin end可以省略

存储过程体每一条SQL语句的结尾要求必须加分号；

存储过程的结尾可以使用delimiter重新设置

语法：

delimiter 结束标记

案例：DELIMITER $

二、调用语法

CALL 存储过程名（实参列表）；

 

1.空参列表

案例：插入到admin表中五条记录

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN 
				INSERT INTO admin(username,`password`)
				VALUES('john',1),('lily',2),('rose',3),('jack',4),('tom',5);
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 VARCHAR(20) DEFAULT '';//声明并初始化
				
				SELECT COUNT(*) INTO result//赋值
				FROM admin
				WHERE admin.username=username
				AND admin.`password`=PASSWORD;
				
				SELECT result;//使用
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 myp5('小昭'.@bname)$
SELECT @bName$

案例2:根据女神名，返回对应的男神名和男神魅力值

4.创建inout模式参数的存储过程

案例1:传入a、b两个值，最终a,b都翻倍返回

CREATE PROCEDURE myp8 (inout a int, inout b int)

CREATE PROCEDURE myp8 (inout a int, inout b int)
BEGIN
			SET a=a*2;
			SET b=b*2;
END $

//调用	
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

 

删除存储过程

语法：

drop PROCEDURE myp1;

只支持删除单个

查看存储过程的信息

desc myp2 ❌

show create procedure myp2;

 

6个案例

 

函数的介绍

含义：一组预先编译好的s q l语句的集合，理解成批处理语句

与存储过程的区别：

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新

函数：有且仅有1个返回，适合做处理数据后返回一个结果

一、创建语法

CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
	函数体

return 值；

END

注意：

1.函数列表包含两部分：参数名 参数类型

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

3.函数体中仅有一句话，则可以省略begin end

4.使用delimiter语句设置结束标记

二、调用语法

SELECT 函数名（实参列表）

案例演示

1.无参有返回

案例：返回公司的员工个数

2.有参有返回

案例1：根据员工名返回工资

案例2:根据部门名，返回该部门的平均工资

三、查看函数

SHOW CREATE FUNCTION myf3;

四、删除函数

DROP FUNCTION myf3;

 

案例：

一、创建函数