MySQL-存储过程

MySQL-存储过程

概述：

​ 存储过程是一组预编译的 SQL 语句，它们被命名并存储在数据库中，可以像调用函数一样被调用。存储过程可以接受参数、执行 SQL 查询、控制流程、执行逻辑判断等。使用存储过程可以提高数据库的性能、安全性和可维护性。

好处：

​ 1、简化操作，提高了sql语句的重用性，减少了开发程序员的压力

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

​ 3、减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）

​ 4、减少了 SQL 语句暴露的风险，也提高了数据查询的安全性

分类：

​ 1、没有参数（无参数无返回）

​ 2、仅仅带 IN 类型（有参数无返回）

​ 3、仅仅带 OUT 类型（无参数有返回）

​ 4、既带 IN 又带 OUT（有参数有返回）

​ 5、带 INOUT（有参数有返回）

注意：IN、OUT、INOUT 都可以在一个存储过程中带多个

创建存储过程

DELIMITER //  #改变 MySQL 的语句分隔符，将其从默认的分号 ; 更改为双斜线 //，这样可以在存储过程中使用分号。
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...) #形参类型可以是任意类型。
[characteristics ...]  #表示创建存储过程时指定的对存储过程的约束条件
BEGIN
存储过程体
END
DELIMITER;

#IN ：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。
#OUT ：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
#INOUT ：当前参数既可以为输入参数，也可以为输出参数

# characteristics 取值如下：
# LANGUAGE SQL
# | [NOT] DETERMINISTIC
# | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
# | SQL SECURITY { DEFINER | INVOKER }
# | COMMENT 'string'
	#LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
	#[NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
	#{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL语句的限制
		#CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
		#NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
		#READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
		#MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
		#默认情况下，系统会指定为CONTAINS SQL。
	#SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
		#DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
		#INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
	#COMMENT 'string' : 注释信息，描述存储过程

调用存储过程

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

#调用in模式的参数：
CALL sp1('值');

#调用out模式的参数：
SET @name;
CALL sp1(@name);
SELECT @name;

#调用inout模式的参数：
SET @name=值;
CALL sp1(@name);
SELECT @name;

相关示例

#无参
DELIMITER //
create procedure No_Params()
begin
    select * from rqtanc;
end //
DelIMITER ;
call No_Params();

#in
DELIMITER //
create procedure rqtanc_age (in number int)
begin
    select age from rqtanc where number = number;
end //
DELIMITER ;
call rqtanc_age(15);

#out
DELIMITER //
CREATE PROCEDURE out_params(OUT age_result INT)
BEGIN
    declare age_value int ;
    SELECT age  FROM rqtanc LIMIT 1; -- 为输出参数赋值
    set age_result = age_value;
END //
DELIMITER ;
call out_params(@age_result);

#inout
DELIMITER //
create procedure InOut_params(inout age_result int)
begin
    declare age_value int;
    select sum(age) from rqtanc;
    set age_result = age_value;
end //
DELIMITER ;
call InOut_params(@age_result);

查看存储过程

#使用SHOW CREATE语句查看存储过程和函数的创建信息
show create procedure 存储过程名;
#使用SHOW STATUS语句查看存储过程和函数的状态信息
SHOW PROCEDURE STATUS LIKE '存储过程名';
#从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='存储过程名' AND ROUTINE_TYPE = 'PROCEDURE';

修改存储过程

ALTER PROCEDURE 存储过程或函数的名 [characteristic ...]
#characteristic取值与创建略有不同，相关取值如下：
#	{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
#	| SQL SECURITY { DEFINER | INVOKER }
#	| COMMENT 'string'

删除存储过程

DROP PROCEDURE IF EXISTS 存储过程名

总结

优点：

​ 1、存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。

​ 2、可以减少开发工作量。将代码 封装 成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以 重复使用 ，在减少开发工作量的同时，还能保证代码的结构清晰。

​ 3、存储过程的安全性强。我们在设定存储过程的时候可以 设置对用户的使用权限 ，这样就和视图一样具有较强的安全性。

​ 4、可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。

​ 5、良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要 连接一次即可 。

缺点：

​ 1、可移植性差。存储过程不能跨数据库移植

​ 2、调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。

​ 3、存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。

​ 4、它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护， 增加数据库的压力 ，显然就不适用了。