【Mysql基础】六.存储过程与函数

一.存储过程（Stored Procedure）

1.1理解

	含义：一组经过预先编译的sql语句的封装

优点：①简化操作，提高sql重用性
②减少操作过程中的失误，提高效率
③减少网络传输量
④减少了sql语句暴露在网上的风险，提高数据查询的安全性

和视图，函数的对比：
1.视图是虚拟表，通常不对底层数据直接操作。存储过程是程序化的sql,可
以直接操作底层数据表
2.函数有返回值，存储过程没有返回值

1.2分类

	1.没有参数（无参无返回）
	2.只带IN类型（有参无返回）
	3.只带OUT类型（无参有返回）
	4.IN和OUT类型（有参有返回）
	5.带INOUT类型（有参有返回）

2.1语法：

CREATE PROCEDURE 存储过程名（IN | OUT | INOUT 参数名 参数类型，…）
BEGIN
存储过程体
END

2.2创建存储过程

类型1：无参
案例1：创建存储过程select_all_data(),查看employees表的所有数据

DELIMITER $
	CREATE PROCEDURE select_all_data()
	BEGIN
		SELECT * FROM employees;
	END $
	DELIMITER ;

2.3存储过程的调用(call)

CALL select_all_data();

案例2：创建存储过程avg_employee_salary(),返回所有员工的平均工资

DELIMITER $
	CREATE PROCEDURE avg_employee_salary()
	BEGIN
		SELECT AVG(salary) FROM employees;
	END $
	DELIMITER ;

类型2：带OUT类型
案例3：创建存储过程show_min_salary(),查看employees表的最低薪资值，并将最低薪资通过OUT参数"ms"输出

DELIMITER $
	CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
	BEGIN
		SELECT MIN(salary) INTO ms
		FROM emplouees;
	END $
	DELIMITER ;

调用

CALL show_min_salary(@ms);

查看

SELECT @ms;

类型3：IN类型
案例4：创建存储过程show_someone_salary(),查看表的某个员工工资，并用IN参数empname输入员工姓名

DELIMITER $
	CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
	BEGIN
		SELECT salary FROM employees
		WHERE last_name=empname;
	END $
	DELIMITER ;

调用方式1

CALL show_someone_salary('Abel');

调用方式2

SET @empname ='Abel';
CALL show_someone_salary(@empname);

类型4：带IN和OUT类型
案例5：创建存储过程show_someone_salary2(),查看employees表某个员工的薪资，并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资

DELIMITER $
	CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
	BEGIN
		SELECT salary INTO empsalary
		FROM employees
		WHERE last_name=empname;
	END $
	DELIMITER ;

调用

SET empname='Abel';
CALL show_someone_salary2(@empname,@empsalary);
Select @empsalary;

类型5：带INOUT类型
案例6：创建存储过程show_mgr_name(),查询某个员工领导的名字，并用INOUT参数“empname"输入员工姓名，输出员工领导姓名

DELIMITER $
	CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
	BEGIN
		SELECT last_name INTO empname FROM employees WHERE employee_id=(
			SELECT manager_id FROM employees WHERE last_name =empname
		);
	END $
	DELIMITER;

调用

SET @empname :='Abel';
CALL show_mgr_name(@empname);
SELECT @empname;

二.存储函数的使用

一.语法：

	**CREATE FUNCTION 函数名(参数名 参数类型，…)
	RETURNS 返回值类型
	[characteristics …]
	BEGIN
		函数体 （函数体中肯定有return语句)
	END**

案例1：创建存储函数，名称为email by name(),参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型

DELIMITER $
	CREATE FUNCTION email_by_name() 
	RETURNS VARCHAR(25)
		DETERMINISTIC
		CONTAINS SQL
		READS SQL DATA
	BEGIN
		RETURN (SELECT email FROM employees WHERE last_name='Abel');
	END $
	DELIMITER ;
	

案例2：创建存储函数，名称为email_by_id(),参数传入emp_id,该函数查询emp_id的email并返回，数据类型为字符串类型

SET GLOBAL log_bin_trust_function_creators=1;//设置charactistic
	DELIMITER $
	CREATE FUNCTION email_by_id(emp_id INT)
	RETURNS VARCHAR(25)
	BEGIN
		RETURN (SELECT email FROM employees WHERE employee_id=emp_id);
	END $
	DELIMITER ;

调用

Select email_by_id(100);

二.存储过程与存储函数的对比：

存储过程（PROCEDURE）:

调用方式：CALL 存储过程()
返回值：有0个或多个
应用场景：一般用于更新

存储函数（FUNCTION）：

调用方法：SELECT 函数()
返回值：只能是1个
应用场景：一般用于查询结果为一个值并返回时

三.存储过程和函数的查看，修改，删除

一.查看

1.show create 语句查看存储过程和函数的创建信息

SHOW CREATE PROCEDURE show_mgr_name;
		
SHOW CREATE FUNCTION email_by_name;

2.使用show status 语句查看存储过程和函数的状态信息

SHOW PROCEDURE STATUS LIKE 'show_max_salary';
	
SHOW FUNCTION STATUS LIKE 'email_by_name';

3.从information_schema.Routines表中查看存储过程和函数的信息

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='email_by_id';

二.存储函数和存储过程的修改

只修改特性，不修改函数体，使用ALTER语句实现

ALTER PROCEDURE show_min_salary SQL SECURITY INVOKER COMMENT '查询最低工资';

三.存储过程、函数的删除

DROP FUNCTION IF EXISTS count_by_id;

四.优缺点

一.优点

1.存储过程可以一次编译多次使用
2.减少开发工作量
3.存储过程的安全性强
4.减少网络传输量
5.良好的封装性

二.缺点

1.可移植性差
2.调试困难
3.存储过程的版本管理很困难
4.不适合高并发的场景

如果你喜欢我的文章的话记得点赞加收藏支持我一下哦~
想要获得更多资源，敬请关注“小呆呆学编程”微信公众号，你的关注就是我最大的动力