【Mysql高级】谈谈你对存储过程的了解？

雨点打透心脏的1/2处 2023-02-14 10:51 37阅读 0赞

## 一、概述 ##

经过事先编译存储在数据库的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作。减少数据在数据库和应用服务器之间的传输，从而提高了数据处理效率。

存储过程和函数的区别：函数有返回值，存储过程没有。

## 二、存储过程维护 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70][]

调用存储过程

![20200602173802511.png][]

查看存储过程

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 1][]

删除

![20200602173739970.png][]

## 三、存储过程的语法结构 ##

### if条件判断 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 2][]

**示例**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 3][]

### 传递参数 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 4][]

**示例**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 5][]

### 输出参数 ###

声明一个out 变量

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 6][]

CASE结构

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 7][]

### 示例 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 8][]

### While循环 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 9][]

### 示例 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 10][]

## repeat结构 ##

![20200610164734676.png][]

### 示例 ###

![20200610164712489.png][]

## LOOP循环 ##

![20200610165021607.png][]

###  示例 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 11][]

## 游标/光标 ##

用来存储查询结果集的数据结构

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 12][]

### 示例 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 13][]

## 存储函数 ##

![20200610170905968.png][]

### 示例 ###

![20200610171951736.png][]

## 触发器 ##

在表的CURD执行之前或之后就会触发一个事件。确保数据完整性，日志记录，数据校验等操作。

### 创建触发器 ###

创建**插入的触发器**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 14][]

**创建修改的触发器**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 15][]

**创建删除的触发器**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 16][]

![2020061407134333.png][]

执行结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 17][]

查看触发器

show triggers;

删除触发器

drop trigger 【trigger\_name】

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200602173039422.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[20200602173802511.png]: https://img-blog.csdnimg.cn/20200602173802511.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200602173811891.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[20200602173739970.png]: https://img-blog.csdnimg.cn/20200602173739970.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200603072716971.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200603072736534.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200603072843255.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200603073108481.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200603073554329.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200603073752285.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200603075451945.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200603075648436.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20200603075629923.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[20200610164734676.png]: https://img-blog.csdnimg.cn/20200610164734676.png
[20200610164712489.png]: https://img-blog.csdnimg.cn/20200610164712489.png
[20200610165021607.png]: https://img-blog.csdnimg.cn/20200610165021607.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20200610165302728.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/20200610165459931.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/20200610170624964.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[20200610170905968.png]: https://img-blog.csdnimg.cn/20200610170905968.png
[20200610171951736.png]: https://img-blog.csdnimg.cn/20200610171951736.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 14]: https://img-blog.csdnimg.cn/2020061407114094.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 15]: https://img-blog.csdnimg.cn/20200614071303640.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 16]: https://img-blog.csdnimg.cn/20200614071506260.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[2020061407134333.png]: https://img-blog.csdnimg.cn/2020061407134333.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 17]: https://img-blog.csdnimg.cn/20200614071412547.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70