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

您所在的位置：网站首页 › mysql函数与存储过程 › 【MySQL】存储过程与存储函数

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

2024-06-29 14:07| 来源: 网络整理| 查看: 265

存储过程与存储函数 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

【本文地址】

公司简介

联系我们