MySQL存储过程与函数

晨曦chenxi
101 阅读3分钟

存储过程与函数

存储过程的概念：

存储过程就是一组经过预先编译的sql语句的封装

执行过程：存储过程预先存储在服务器上啊，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列的sql语句全部执行

好处：

1.简化操作，提高了sql语句的重用性，减少了开发人员的压力

2.减少操作过程中的失误，提高效率

3.减少网络传输量（客户端不再需要把sql语句通过网络发给服务器）

4，减少了sql语句暴露在网上的风险，也提高了数据查询的安全性

和视图，函数的对比

他和视图有着同样优点，清晰，安全，还可以减少网络传输量，不过和视图不同的是，视图是虚拟表，通常不对底层进行操作，而存储过程是程序化的sql，可以直接操作底层的数据表，相比于面向集合的操作方式，能够实现一些更复杂的逻辑的处理 相比于函数他没有返回值

存储过程的创建与调用
-- 创建存储过程
	
	-- 类型一：无参数无返回值
	DELIMITER $   -- 修改结束符
	CREATE PROCEDURE select_all_data()
	BEGIN
	     SELECT * FROM employees;
  END$
	DELIMITER ;


-- 存储过程的调用

CALL select_all_data();

DELIMITER $
CREATE PROCEDURE show_min_salary1(OUT ms DOUBLE)
BEGIN 
SELECT MIN(sal) INTO ms
		   FROM employees;
END $
DELIMITER ;

-- 调用
CALL show_min_salary1(@ms);

SELECT @ms;

-- 类型三：带IN的
#输入一个员工的姓名查询他的工资
DELIMITER $
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN 
		SELECT sal FROM employees 
		WHERE ename = empname ;
END $
DELIMITER ;


-- 调用
SELECT * FROM employees
CALL show_someone_salary('JONES')
-- 调用方式二
SET @empname = 'JONES';
SET @empname := 'JONES';
CALL show_someone_salary(@empname);

-- 类型四：IN和OUT 
-- 使用in输入姓名，使用out输出工资
DELIMITER $
CREATE PROCEDURE get_someone_salary2(IN empname VARCHAR(20),OUT salary DOUBLE)
BEGIN 
		SELECT sal INTO salary
		FROM employees 
		WHERE ename = empname ;
END $
DELIMITER ;

SET @empname = 'JONES'；-- 创建自定义变量
CALL get_someone_salary2(@empname,@salary);
SELECT @salary

说明：

在创建存储过程时可以传入参数，这些参数根据其作用可以分为三种IN类型，OUT类型，INOUT类型 IN类型为传入的变量，OUT为输出的变量，INOUT既是输入的变量又是输出的变量。在紧接着创建语句的下面可以指定存储过程的特征。

存储函数的调用与创建
-- 获取名为'JONES'的员工的工资
DELIMITER $
 
CREATE FUNCTION sal_by_name()
RETURNS DOUBLE
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
 BEGIN 
       RETURN (SELECT sal FROM employees WHERE ename = 'JONES');       
 END $
 DELIMITER ;
 
 SELECT * FROM employees
 
 
 SELECT sal_by_name();

说明：存储函数的参数列表总是默认为IN。在创建存储函数的时候必须要指明存储函数的返回值类型，这也意味着存储函数必须有返回值。

对比存储过程存储函数
返回值没有，一个，多个只能有一个
使用场景一般用于更新一般用于查询结果并且返回
存储函数与存储过程的修改与删除
-- 存储过程和存储函数的修改和删除
 
 SHOW CREATE FUNCTION/PROCEDURE...
 
 -- 获取状态信息
 
 SHOW FUNCTION/PROCEDURE STATUS ..
 
 -- 从infromation_schema.Routine 表中查询
 SELECT * FROM information_schema.Routines 
 WHERE ROUTINE_NAME = 'sal_by_name' AND ROUTINE_TYPE ='FUNCTION';
 
 
 -- 修改
 ALTER PROCEDURE show_someone_salary 
 SQL SECURITY INVOKER
 COMMIT '查询某人的工资'
 
 -- 存储过程的删除
 USE dbtest15
 DROP PROCEDURE IF EXISTS get_someone_salary
对存储函数与存储过程的使用场景的说明：

在一些公司的开发手册或者规范中强制不能使用存储过程【典型的像的阿里的开发手册中就禁止使用存储过程与存储函数】，存储函数这是因为存储过程与存储函数有着一下的缺点

可移植性差

调试困难

存储过程的版本管理很困难

它不适合高并发的场景，存储过程会变得难以维护，增加数据库的压力

但是这是并不意味着使用存储函数不好而是这些公司看重其缺点，在一些小型项目的开发中还是有公司会去使用存储过程与存储函数的，这位主要取决于不同的公司对于其优缺点的看重程度。

avatar
文章
阅读
粉丝