MySQL存储过程

存储过程

1. 存储过程概述

概述：存储过程的英文是"stored procedure"。它的思想很简单，"就是一组经过"预先编译"的 SQL 语句的封装"。

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

好处：

简化操作，提高了sql语句的重用性，减少了开发程序员的压力
减少操作过程中的失误，提高效率
减少网络传输（客户端只需通过命令调用即可）
减少了 SQL 语句暴露的风险，提高了数据查询的安全性
SQL 语句安全性高，可以设定数据库用户的访问权限

存储过程和函数的对比：

使用存储过程就像使用函数一样简单，直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的。

2. 分类

存储过程的参数类型可以是 IN，OUT和INOUT。根据这点分类如下：

没有参数（无参数无返回）
仅仅带 IN 类型（有参数无返回）
仅仅带 OUT 类型（无参数有返回）
即带 IN 又带 OUT （有参数有返回）
带 INOUT （有参数有返回）

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

3. 创建存储过程

3.1 语法分析

语法格式：

delimiter $
# 修改sql语句的结束标记为 $
create procedure 存储过程名称(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
begin
# 存储过程体（方法体）

end $
delimiter ;
# 修改sql语句的结束标记为 ;

类似于java中的方法（void）：

修饰符 void 方法名(参数类型 参数名,...){
// 方法体
}

3.1.1 无参数创建

delimiter $
create procedure 存储过程名称()
begin
# 存储过程体
end $
delimiter ;

3.1.2 带（OUT）参数创建

delimiter $
create procedure 存储过程名称(OUT 参数名 参数类型,...)
begin
# 存储过程体
# 输出参数：结果集 into = 参数名 例如：select 字段 into out参数名 from .......
end $
delimiter ;

3.1.3 带（IN）参数创建

delimiter $
create procedure 存储过程名称(IN 参数名称 参数类型,...)
begin
# 存储过程体
# 使用输入参数：直接使用 参数名称即可 例如 select * from xld where id = 参数名称;
end $
delimiter ;

3.1.4 带（INOUT）参数创建

delimiter $
create procedure 存储过程名称(INOUT 参数名称 参数类型,...)
begin
# 存储过程体
# 使用输入输出参数：支持 IN/OUT 的语法 例如：select 字段 into 参数名称,字段,... from xld where id = 参数名称; 
end $
delimiter ;

3.2 说明

3.2.1 参数前面的符号的意思

IN：当前参数为输入参数，也就是表示入参；

存储过程"只是读取这个参数的值"，如果没有定义参数种类，默认就是 IN，表示输入参数。

OUT：当前参数为输出参数，也就是表示出参；

当执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数，得到这个参数的返回的值了

INOUT：当前参数既可以为输入参数，也可以为输出参数。

3.2.2 形参类型

形参类型可以是 MySql 数据库中的任意类型。

3.2.3 characteristics 特征（约束条件）

该类型是用来表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL
[NOT] DETERMINISTIC
{CONTAINS SQL,NO SQL,READS SQL DATA,MODIFIES SQL DATA}
SQL SECURUTY {DEFINER,INVOKER}
COMMIT '描述'

LANGUAGE SQL

说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
[NOT] DETERMINISTIC：指明存储过程执行的结果是否确定？
1. DETERMINISTIC 表示结果是确定的。每次执行存储过程是，相同的输入会得到相同的出输出。
2. NOT DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的输出。
3. 默认为：NOT DETERMINISTIC 。
{CONTAINS SQL,NO SQL,READS SQL DATA,MODIFIES SQL DATA}：指明子程序使用SQL语句的限制
1. CONTAINS SQL：表示当前存储过程的子程序包含SQL语句，但不包含读写数据的SQL语句。
2. NO SQL：表示当前存储过程的子程序中不包含任何SQL语句
3. READS SQL DATA：表示当前存储过程的子程序中只包含读数据的SQL语句
4. MODIFES SQL DATA：表示当前存储过程的子程序中只包含写数据的SQL语句
5. 默认为：CONTAINS SQL
SQL SECURUTY {DEFINER,INVOKER}：指明当前存储过程的权限。那些用户能够执行那些存储过程？
1. DEFINER：表示只有当前存储过程的创建者或者定义者才能执行当前存储过程。
2. INVOKER：表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
3. 默认为：DEFINER
COMMENT '描述' 注释信息，描述存储过程的信息。

3.2.4 存储过程体

存储过程体可以有多条 SQL 语句，如果仅仅只有一条 SQL 语句，则可以省略 BEGIN 和 END。当然存储过程都是些需要复杂的 SQL 语句组成的。

....

3.2.5 设置新的结束标记

DELIMITER 新的结束标记
# 一般结合 DELIMITER ; 使用

4.存储过程的调用

4.1 无参数调用

call 存储过程名称();
# 例如
CALL xld_procedure();

4.2 有（OUT/IN/INOUT）参数调用

call 存储成功名称(@参数);
# 例如
CALL xld_procedure(@param);
# 查看该参数值
select @param;
# 创建/设置自定义变量
set @param = '值';

二. 存储函数

1.创建存储函数

1.1 语法分析

语法格式：

delimiter $
create function 函数名(参数名称 参数类型)
returns type #返回值类型 
characr...
begin
# 函数体
return (select 语句 ... );
end $
delimiter ;

类似于java中的方法：

修饰符 返回值类型 方法名(参数类型 参数名,...){
// 方法体
  	return 返回值;  
}

1.2 举例

delimiter $
create function 函数名称()
returns 返回值类型
charact ... # 必要的特性配置
# 1.函数执行结果是否确定
# 2.指明子程序对 SQL 语句的限定(可以指明多个 ... 一般指明包含SQL语句和读的SQ语句)
begin
return (select ... );
end $
delimiter ;

例如

# 无参
delimiter $
CREATE FUNCTION xld_function()
RETURNS VARCHAR(25)
	NOT DETERMINISTIC
	CONTAINS SQL
	READS SQL DATA
BEGIN
RETURN (SELECT DISTINCT id FROM xld WHERE id = 2 ); #注意函数的返回值只能是一个值
END $
delimiter ;

# 有参
delimiter $
CREATE FUNCTION xld_function_id(xld_id INT)
RETURNS INT
	NOT DETERMINISTIC
	CONTAINS SQL
	READS SQL DATA
BEGIN
RETURN (SELECT DISTINCT id FROM xld WHERE id = xld_id ); #注意函数的返回值只能是一个值
END $
delimiter ;

1.3 函数创建时的问题

错误：[Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

解决方式1：加上必要的函数特性"[NOT] DETERMINISTIC" 和"{CONTAINS SQL,NO SQL,READS SQL DATA,MODIFIES SQL DATA}"

# 在 returns 后 添加以下必要的配置
delimiter $
CREATE ...
RETURNS INT
    not deterministic
    contains sql
    reads sql data
BEGIN
# 函数体
END $
delimiter ;

解决方式2：

set global log_bin_trust_function_creators = 1;

2 说明

2.1 参数列表

参数列表：指定参数为IN，OUT或者INOUT只对PROCEDURE是合法的，FUNCTION中总默认为IN参数。

2.2 returns 返回值

RETURNS type 语句表示函数返回数据的类型。
returns 子句只能对 function 做指定，对函数而言这是"强制"的。它用来指定函数的返回类型，而且函数必须包含一个"return value"语句

2.3 characteristics 特征（约束条件）

在创建函数时指定的对函数的约束。取值和创建存储过程时相同。注意：在函数中必须指定。

2.4 其他

函数体可以有多条 SQL 语句，如果仅仅只有一条 SQL 语句，则可以省略 BEGIN 和 END。

3. 存储函数的调用

select 函数名(实参列表); # 实参列表已 , 分割

4. 对比存储函数和存储过程

名称	关键字	调用语法	返回值	应用场景
存储过程	procedure	call 存储过程名称(实参)	有0个或者多个(理解为引用传递)	呵呵哒，经验之谈...
存储函数	function	select 存储函数名称(实参)	只能是一个	一般用于查询结果为一个值并要求返回时使用

此外，存储函数可以放在查询语句中使用，存储过程不行，反之存储过程的功能更加强大，包括能够执行对表的操作（创建表，删除表等）和事务操作，这些功能是存储函数不具备的

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

1.查看

1.1 show create {procedure|function}查看（创建信息）

show create {procedure|function} 存储过程或函数名称;
# 例如
SHOW CREATE FUNCTION xld_function;
/
SHOW CREATE PROCEDURE xld_procedure;

1.2 show {procedure|function} status 查看（状态信息）

show {procedure|function} status like '存储过程或函数全名';
# 例如
SHOW PROCEDURE STATUS LIKE 'xld_procedure';
/
SHOW FUNCTION STATUS LIKE 'xld_function';

1.3 从 information_schema.ROUTINES 表中查看（详细信息）

select * from information_schema.ROUTINES 
where ROUTINE_NAME = '存储过程或函数名称' AND ROUTINE_TYPE = 'FUNCTION|PROCEDURE' # 注意 type 的值必须大写
# 例如
SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_NAME = 'xld_function' AND ROUTINE_TYPE = 'FUNCTION'
/
SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_NAME = 'xld_procedure' AND ROUTINE_TYPE = 'PROCEDURE'

2. 修改 - alter

注意：修改存储过程或函数，不影响存储过程或函数的功能，只是修改相关特性。使用 ALTER 语句实现。

alter {procedure|function} 存储过程或函数名称 charact....
# 例如
ALTER FUNCTION xld_function SQL SECURITY INVOKER COMMENT '小林当测试函数';
/
ALTER PROCEDURE xld_procedure COMMENT '小林当测试存储过程';

3. 删除 - drop

drop {procedure|function} [if existe] 存储过程或函数名称;
# 例如
DROP PROCEDURE IF EXISTS xld_procedure_drop;
/
DROP FUNCTION IF EXISTS xld_function_drop

关于存储过程使用的争议

尽管存储过程有诸多优点，但是对于存储过程的使用，一直都存在这很多争议。例如有些公司对于大型项目要求使用存储过程，而有些公司明确禁止使用存储过程，为什么会是这样的呢？

1. 优点

存储过程可以一次编译多次使用。创建时进行编译，之后的使用不需要编译，直接运行，提升SQL的执行效率
可以减少开发工作量。将代码"封装"成模块，实现模块之间可以重复使用。
存储过程的安全性强。可以设定用户的使用权限。
可以减少网络传输量。客户端只需使用简单的调用命令调用即可。减少网络传输量。
良好的封装性。将多条 SQL 语句封装成一个存储过程，只需连接一次调用即可。

2. 缺点

基于上面这些优点，不少大公司都要去大型项目使用存储过程，比如微软，IBM 等公司。但是国内的阿里不推荐开发人员使用，这是为什么？

阿里开发规范
禁止使用存储过程，因为存储过程难以调试和扩展，更没有移植性

可移植性差。存储过程不能跨库移植，MySql ，Oracle 和 SQL server 之间不能相互移植
调式困难。只有少数 DBMS 支持存储过程的调试，复杂的存储过程，开发维护都不容易。有但要收费
存储过程的版本管理很困难。数据表中的索引发送变化，可能会导致存储过程失效。存储过程本身没有版本控制
重点：它不适合高并发的场景。分库分表时，对数据库扩展要求很高，存储过程会的更加难以维护

3. 小结

存储过程即方便，又有局限性。尽管不同的公司对存储过程的态度不一，但是对于我们开发人员来说，不论在怎么样，掌握存储过程都是必备的技能之一。

四. 口诀

操作存储过程简单口诀：c 创 - d 删 - a 修改（procedure）

操作存储函数简单口诀：c 创 - d 删 - a 修改（function）

五. 存储过程和函数中定义和使用变量（局部变量）

posted @ 2023-11-22 13:45 小林当阅读(111) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

小林当