【MySQL】存储过程与存储函数

存储过程与存储函数

1 存储过程

1.1介绍

存储过程是事先经过编译并存储在数据库中的一段SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库SQL 语言层面的代码封装与重用。

使用存储过程的好处有以下几点：

• 封装，复用 -----------------------> 可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。

• 可以接收参数，也可以返回数据 --------> 再存储过程中，可以传递参数，也可以接收返回值。

• 减少网络交互，效率提升 -------------> 如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

1.2 基本语法

先准备属下数据：

student表：

course表：

student_course表：

tb_user表：

存储过程的基本语法如下：

1.创建存储过程

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ]) 
BEGIN

  -- SQL语句 

END ;

2.调用存储过程

CALL 名称 ([ 参数 ]);

3.删除存储过程

DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ；

4.查看存储过程

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指 定数据库的存储过程及状态信息

SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义

接下来我们来演示一下存储过程的基本使用：

1.创建并调用存储过程

-- 创建 
create procedure p1() 
begin
    select count(*) from student; 
end; 

-- 调用 
call p1(); 

执行结果如下：

2.查看存储过程

-- 查看所有存储过程
select * from information_schema.ROUTINES

-- 查看所有含有'itcast'的存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';

-- 查看指定存储过程的创建语句
show create procedure p1; 

3.删除指定存储过程（如果存在）

-- 删除 
drop procedure if exists p1;

另外需要注意的是，当我们在命令行中执行创建存储过程的SQL时会出现以下错误

这是因为当我们的sql语句编写到select count(*) from student;时，由于带有";"，因此命令行认为我们的sql语句已经编写完了，没有编译后面的"end;"，故而报错

这时我们需要通过关键字 delimiter 手动指定SQL语句的结束符，例如将"$$"作为sql语句的结束符

需要注意的时，如果这时我们再编写普通的sql语句，会出现下列情况：

因为这时";"已经不再是sql语句的结束符了，我们需要使用"$$"作为sql语句的结尾

---

2 变量

在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。

2.1 系统变量

系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）

关于系统变量的基本语法如下：

1.查看系统变量

SHOW [ SESSION | GLOBAL ] VARIABLES ;   -- 查看所有系统变量 

SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......';   -- 可以通过LIKE模糊匹配方 式查找变量 

SELECT @@[SESSION | GLOBAL] 系统变量名;   -- 查看指定变量的值

2.设置系统变量

SET [ SESSION | GLOBAL ] 系统变量名 = 值 ; 

SET @@[SESSION | GLOBAL]系统变量名 = 值 ;

使用系统变量时需要注意以下几点：

• 如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
• mysql服务重新启动之后，所设置的全局参数会失效（指变为默认值），要想不失效，可以在 /etc/my.cnf文件中配置。
• 全局变量针对于所有的会话，会话变量针对于单个会话，在另外一个会话窗口就不生效了。这点在下面会结合具体案例演示。

演示示例：

1.查看系统变量

-- 查看所有会话变量 
show session variables ;

-- 查看以auto开头的会话变量
show session variables like 'auto%'; 

-- 查看以auto开头的全局变量
show global variables like 'auto%'; 

-- 查看全局变量autocommit
select @@global.autocommit; 

-- 查看会话变量autocommit
select @@session.autocommit;

2.设置系统变量

-- 设置会话变量autocommit为1，表示当前会话中的事务自动提交
set session autocommit = 1; 

-- 设置全局变量autocommit为0，表示设置所有会话中的事务为手动提交
set global autocommit = 0; 

在这里演示一下全局变量和会话变量的区别：

首先需要明确一点，以autocommit为例，session的autocommit与global的autocommit的变量是互不干扰的两个变量，二者之间比较像是公共配置与个性配置之间的区别，session的autocommit值只在当前会话生效，而global的autocommit是在所有会话都生效的，通俗点说，会话变量是会话独有的，全局变量是会话之间共享的。当同名的会话变量与全局变量的值发生冲突时，优先选取会话变量的值。那一个会话的范围又是多大呢？

以图形化界面DataGrip为例子，我们每打开一个查询控制台就是一个会话，例如下图中的console_3和console_4就是不同的两个会话

当我们使用set session autocommit = 0;（关闭事务自动提交）修改console_3的会话变量autocommit之后，我们可以执行以下指令观察会话变量autocommit与全局变量autocommit的区别：

-- 查看全局变量autocommit
show global variables like 'autocommit';

-- 查看会话变量autocommit
show session variables like 'autocommit';

执行结果分别如下：

可以看到，会话变量autocommit已经变成了OFF，而全局变量autocommit为ON（mys